Theory Flashcards
Theory
Benefits of splitting a dimension
Normalised data, no duplication, more joins between Fact (costly). However, querying is the same.
About Hierarchy
Join Query is more expensive, tables are normalised, its not about drilling down, doesn’t offer better roll-up features. However both need the same amount of queries.
About No Hierarchy
Join query is simpler, tables are un-normalised (prone to insert, update, delete) but anomalies are irrelevant. However, both need the same amount of queries.
How to lower aggregation levels from schema-1 to schema-0
Do not use aggregated fact measures, add more details to each dimension, do not have any user based groupings for dimensions, add more dimensions
Does the aggregation change if you change the fact measures
sane level of aggregation because the fact measures are different. One schema does not contain any more detail than the other.
Can we drill down in an operational database
not preferred because the operational database is noy accessible or built from several different databases.
do managers prefer low levels or high levels of aggregation?
higher levels of aggregation (more general) because business decisions often focus in aggregated data. Managers can then drill down to more detailed information
Why is level of aggregation relative?
Level of aggregation is relative. However not 0/1 if there are aggregated fact measures or aggregated dimensions.
Does hierarchy increase or lower aggregation
Adding hierarchy eg time to quarter and year increases level of aggregatiob
Compare v1 schema (determinant dimension)and v2 schema (more fact measures)
v2 - determinant (more efficient storage, lower requirements, number of attributes in fact increases, looks complex and crowded) and v1 - more fact measures (concise and compact model, easier to understand, expensive storage, and increase in storage, preferred by management)
When do we need a determinant dimension?
If an aggregate function beside COUNT (number of…) is being used (sum/min/max/avg)
