Please enable JavaScript.
Coggle requires JavaScript to display documents.
W4 - Coggle Diagram
W4
What Makes a Good Dimensional Model?
Complete:
all relevant dimensions and facts
Appropriate
level of granularity
Understandable
: user-oriented data names and values
(descriptive text rather than codes)
Integrated:
conformed dimensions
Simple
:seven plus or minus two dimensions
Error proof:
avoid errors in queries by using additive facts;
not mixing levels of aggregation
What is Examinable?
Different types of facts
Slowly changing dimensions
Different types of fact tables
Conformed dimensions
Snowflake schemas
Surrogate keys
Advanced Dimensional Modelling
Conformed dimensions
Date is a common conformed dimension because its attributes (day, week, month, quarter, year, etc.) have the same meaning when joined to any fact table.
Slowly changing dimensions
One of the most important issues in dimensional modelling
Slowly... is a dimension table in which an underlying entity changes some importants attribute (the address of a customer
Kimball (1996): three standard approache
:
Overwrite old values, Create a new dimension record,Create a current value field
Create a New dimension record:
Advantage: maintains and partitions history
• Disadvantage: more complex than overwrite
Creating a new dimension record is usually the best
approach
Types of fact tables
Snapshot
(Balance, Transaction Count )(is the period, not the individual transaction)
Factless
Transaction
(Quantity, Extended Price)
Mini-dimensions
unmanageable for browsing
Most heavily used attributes can be separated out into a
mini-dimension table
an improve performance significantly for the most common queries
should contain a subset of attributes
that can be efficiently browsed
Less than 100,000 possible combinations of attribute values (<
100,000 rows
Ways of reducing combinations: 1.Use discretely valued attributes
Group continuously valued attributes into bands
Types of facts
Additive measures can be summed across any of the
dimensions associated with them
Snapshot (Balance, Transaction Count)
Some measures are completely non- additive, such as
ratios
From ER Models to Dimensional 维度 model
2. Denormalisation 非正常
Collapse hierarchies to form dimension tables
Summarisation
Consider summarisation for performance requirements
1. Dimensional models are restricted ER model
Subsetting - partition ER model by events
dimensions model
描述
strategic and tactical
架构快速上手，快速交付,OLAP, high read activity
ER model
实体加关系,描述
企业业务
架构, 规范性好、冗余少,OLTP, high create, read, delete, uqdate