# Ofrecords 100million 031510 031510 prof navneet goyal

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: ggregates: Example Grocery Store 3 dimensions – Product, Location, & Time 10000 products 1000 stores 100 time periods 10% Sparsity Total no. of records = 100 million 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 26 Aggregates: Example Hierarchies 10000 products in 2000 categories 1000 stores in 100 districts 30 aggregates in 100 time periods 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 27 Aggregates: Example How many aggregates are possible? 1-way: Category by Store by Day 1-way: Product by District by Day 1-way: Product by Store by Month 2-way: Category by District by Day 2-way: Category by Store by Month 2-way: Product by District by Month 3-way: Category by District by Month 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 28 Aggregates: Example What is Sparsity? Fact tables are sparse in their keys! 10% sparsity at base level means that only 10% of the products are sold on any given day (average) As we move from base level to 1-way the sparsity _Increases! _______ What affect sparsity will have on the size of the aggregate fact table? 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 29 Aggregates: Example Let us assume that sparsity for 1way aggregates is 50% For 2-way 80% For 3-way 100% Do you agree with this? Is it logical? 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 30 Aggregates: Example Table Base 1-way 1-way 1-way 2-way 2-way 2-way 3-way Prod. 10000 2000 10000 10000 2000 2000 10000 2000 Store 1000 1000 100 1000 100 1000 100 100 Time 100 100 100 30 100 30 30 30 Sparsity 10% 50% 50% 50% 80% 80% 80% 100% Grand Total 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani # Records 100 million 100 million 50 million 150 million 16 million 48 million 24 million 6 million 494 million 31 Aggregates: Example An increase of almost 400% Why it happened? Look at the aggregates involving Location and Time! How can we control this aggregate explosion? Do the calculations again with 500 categories and 5 time aggregates 03/15/10 03/15/10 Prof. Navneet Goyal, BITS, Pilani 32 Aggregates: Example Table Base 1-way 1-way 1-way 2-way 2-way 2-way 3-way Prod. 10000 500 10000 10000 500 500 10000 500 Store 1000 1000 100 1000 100 1000 100 100 Time 100 100 100 5 100 5 5 5 Sparsity 10% 5...
View Full Document

## This note was uploaded on 03/14/2010 for the course CSE SS ZG515 taught by Professor Naveneetgoyal during the Summer '10 term at Birla Institute of Technology & Science.

Ask a homework question - tutors are online