Tất cả các sản phẩm bằng cách lãnh thổ do quý Danh mục sản phẩm của khu vực của bộ phận sản phẩm quý của khu vực bởi các quý Tất cả các sản phẩm theo vùng Danh mục sản phẩm quý của tất cả các cửa hàng của bộ phận sản phẩm quý của tất cả các cửa hàng theo loại sản phẩm phần tư lãnh thổ của bộ phận sản phẩm năm lãnh thổ của năm | 246 DIMENSIONAL MODELING ADVANCED TOPICS Product category by region by month Product department by region by month All products by region by month Product category by all stores by month Product department by all stores by month Product category by territory by quarter Product department by territory by quarter All products by territory by quarter Product category by region by quarter Product department by region by quarter All products by region by quarter Product category by all stores by quarter Product department by all stores by quarter Product category by territory by year Product department by territory by year All products by territory by year Product category by region by year Product department by region by year All products by region by year Product category by all stores by year Product department by all stores by year All products by all stores by year Each of these aggregate fact tables is derived from a single base fact table. The derived aggregate fact tables are joined to one or more derived dimension tables. See Figure 11-15 showing a derived aggregate fact table connected to a derived dimension table. Effect of Sparsity on Aggregation. Consider the case of the grocery chain with 300 stores 40 000 products in each store but only 4000 selling in each store in a day. As discussed earlier assuming that you keep records for 5 years or 1825 days the maximum number of base fact table rows is calculated as follows Product 40 000 Store 300 Time 1825 Maximum number of base fact table rows 22 billion Because only 4 000 products sell in each store in a day not all of these 22 billion rows are occupied. Because of this sparsity only 10 of the rows are occupied. Therefore the real estimate of the number of base table rows is 2 billion. Now let us see what happens when you form aggregates. Scrutinize a one-way aggregate brand totals by store by day. Calculate the maximum number of rows in this one-way aggregate. AGGREGATE FACT TABLES 247 PRODUCT STORE Figure .
