CS 524 – High Performance Computing

22 slides
1.75 MB

Similar Presentations

Presentation Transcript


Dimensional Modeling – Part 2CS 543 – Data Warehousing


The Snowflake SchemaSnowflaking is a method of normalizing the dimension tables in a star schema Normalization increases the efficiency of certain queries, and reduces space requirements


Star Schema


QueryingSuppose the product table has 500,000 rows (different products). These products fall under 500 product brands, and these brands fall under 10 product categories Query: give me the total quantity of a specific product category sold in Jan 2004? All 500,000 rows in the product dimension table would have to be searched to find the products belonging to the specified product category


A Snowflake Schema


NormalizationPartially or fully normalize only a few dimension tables, leaving the others intact Partially normalize every dimension table Fully normalize every dimension table


Snowflaking?Advantages Small savings in storage space Normalized structures are easier to update and maintain Disadvantages Schema less intuitive and end-users are put off by the complexity Ability to browse through the contents difficult Degraded query performance because of additional joins




Some Query Examples (1)


Some Query Examples (2)Query: Total sales for customer number 12345678 during the first week of December 2003 for product Widget-1 Find and sum the sales quantity and sales dollars for all fact table rows where the customer key relates to customer number 12345678, the product key relates to product Widget-1, and the time key relates to the seven days in the first week of December 2003. Assuming a customer can make a single purchase on a given day, only seven rows of the fact table will be summed


Some Query Examples (3)Query: total sales for all customers in the south-central territory for the first two quarters of 2003 for product category Bigtools All fact table rows where the customer key relates to all customers in the south-central territory, the product key relates to all products in the product category Bigtools and the time key relates to about 180 days in the first two quarters of 2003. In this query, clearly a large number of fact table rows participate the summation How can we reduce the execution time?


Fact Table Size (1)


Fact Table Size (2)Credit card transaction tracking Time dimension: 5 years = 60 months Number of credit card accounts: 150 million Av. number of monthly transaction/account: 20 Max. number of base fact table records: 180 billion


Aggregating Fact TableTypically, queries require detailed data on some dimensions, while only summary data is needed for the other dimensions Example: assume one sale per product per store per week. Estimate the number of fact table rows required: Query involves 1 product, 1 store, 1 week Query involves 1 product, all stores, 1 week Query involves 1 brand, 1 store, 1 week Query involves 1 brand, all stores, 1 year Suppose now you have an aggregate fact table where each row summarizes the totals for a brand, a store, and a week. Now estimate the number of fact table rows required.


Multi-Way Aggregate Fact Tables (1)Utilize hierarchies in dimensions to create appropriate aggregate fact tables Single-way aggregate fact table aggregates along one dimension only; multi-way have more than one dimension aggregated


Multi-Way Aggregate Fact Tables (2)


Multi-Way Aggregate Fact Tables (3)


Goals for AggregationPrimary goal: improve overall DW performance Do not get bogged down with too many aggregates. Remember you have to create addition derived dimensions to support the aggregates Try to cater to a wide range of user groups Go for aggregates that do not unduly increase the overall usage of storage Keep the aggregates hidden from the end-users


Families of Stars


Snapshot and Transaction Tables


Conformed DimensionsSince multiple fact tables share dimension tables, it is essential that dimensions are conformed, i.e., they have the same meaning Conformed dimensions are essential for Building up an enterprise warehouse from data marts Running queries across data marts Consistent semantics of queries and their results Using conformed dimensions is a important responsibility of the DW project team


Standardizing FactsSince fact tables can be shared, they need to be standardized. Ensure same definition and terminology across data marts Resolve homonyms and synonyms Guarantee that the same algorithms are used for any derived units in each fact table Make sure each fact uses the right unit of measurement

Browse More Presentations

Last Updated: 8th March 2018

Recommended PPTs