- In the data publishing processes Often this will lead to the need for in an area of the database used for controlling the “staging tables” incoming data in 3NF before it gets stored in the Dimension tables Other terms you might see Kimball calls such tables the “Back Room” Bill Inmon calls it the “Data Store” Others call them the or sometimes an ODS (Operational Data Store) “staging tables”, 04-3.3 Aggregates Speed Up Retrieval Another Way – Another way to speed up retrieval is to pre-calculate and store aggregates (summaries) for commonly requested queries. Example: If users always run queries to get Total Sales by Day - A standard query would have to add up all the atomic fact rows for each day - Summarizing all products - All stores Result Can take a lot of time Solution: Having a pre-calculated total - Already defined and sitting in a table can speed up retrieval significantly These pre totaled values take up a lot of space 04-3.3 Aggregates • In a RDBMS -only implementation, the aggregates would be stored in their own fact tables in the database Recent versions of leading RDBMS are • “aggregate aware” - Meaning if you run a query to summarize data, - The query processor will check whether there is already an appropriate aggregate table stored in the database, and will use it instead of summing the details In a PowerPlay (by COGNOS) implementation, these aggregates are created by PowerPlay and • stored in the cube, thus serving much the same purpose as “aggregate aware” Space Used Aggregate tables can take up a lot of space in the data warehouse, especially if they only “lightly” summarize the atomic data - data summarized hly and yearly takes up space but summarized weekly, hly, quarterly and yearly takes up a lot more space.
- In the data publishing processes Often this will lead to the need for “staging tables” in an area of the database used for controlling the incoming data in 3NF before it gets stored in the Dimension tables Other terms you might see Kimball calls such tables the “Back Room” Bill Inmon calls it the “Data Store” Others call them the “staging tables”, or sometimes an ODS (Operational Data Store) 04-3.3 Aggregates Speed Up Retrieval – Another Way Another way to speed up retrieval is to pre-calculate and store aggregates (summaries) for commonly requested queries. Example: If users always run queries to get Total Sales by Day - A standard query would have to add up all the atomic fact rows for each day - Summarizing all products - All stores Result Can take a lot of time Solution: Having a pre-calculated total - Already defined and sitting in a table can speed up retrieval significantly These pre totaled values take up a lot of space 04-3.3 Aggregates • In a RDBMS -only implementation, the aggregates would be stored in their own fact tables in the database • Recent versions of leading RDBMS are “aggregate aware” - Meaning if you run a query to summarize data, - The query processor will check whether there is already an appropriate aggregate table stored in the database, and will use it instead of summing the details • In a PowerPlay (by COGNOS) implementation, these aggregates are created by PowerPlay and stored in the cube, thus serving much the same purpose as “aggregate aware” Space Used Aggregate tables can take up a lot of space in the data warehouse, especially if they only “lightly” summarize the atomic data - data summarized hly and yearly takes up space but summarized weekly, hly, quarterly and yearly takes up a lot more space.
04-3-Reading-Hierarchies-Sizing-Surrogate-Other.docx