Please enable JavaScript.
Coggle requires JavaScript to display documents.
Conceptual and Multidimensional Modeling (Schema Design (Comparison Star-…
Conceptual and Multidimensional Modeling
Schema Design modeling
input: User requirements, OLTP schemas
Conceptual Modeling
Logical Modeling
Physical Modeling
Dimensional Modeling
Choose Business Process
repair
Declare the grain
average total repair costs per week
Identify dimensions
Vehicle, Customer; Garage
Identify the facts
total costs = parts costs + wages
Conceptual Modeling Languages
learning by doing
Multidimensional ERM
Fact (Cube)
Level name
relationship set
Fact Cube (Vehicle Repair) in the middle, dimensions linked to the cube with relationships to greater hierarchies
ADAPT
similar to MERM
Fact cube with dimensions listed in cube already
Dimensions with
Build Fact cube (Vehicle Repair) with listing of Dimensions, then
hierarchy structure like MERM with relationships
H2 for Reporting (H2fR)
structure like folder structure
fact (blue cube)
dimensions with subdimensions
instances
possible to use filter
Build fact with folder structure and dimensions as well as subdimensions. You can model instances for examples or fixed items in the dimensions, otherwise too complicated
Schema Design
Star Schema
dimensions and attributes not normalized
Snowflake Schema
hierarchical dimensions
highly normalized
Galaxy Schema
Multiple fact tables linked to "same" dimensions
Comparison Star- and Snowflake Schema
(Snowflake Perspective)
Positive
better more efficient storage
reflects users way of thinking
Negative
performance bad, many joins
more complex then star schema
benefit of normalization is small
Hybrid Approach (Star and Snowflake)
mixing (de-)normalized dimension
adding shortcuts
splitting fact tables (galaxy)