DSS Schemas EDUCATION DSS Schemas Agenda The Consolidated

DSS Schemas EDUCATION

DSS Schemas -- Agenda • The Consolidated Star • The Normalized Star (Snowflake) EDUCATION

Example Logical Model Product Geography Time Division Region Year Dept Market Month Class Store Day Item Fact Sales EDUCATION

Consolidated Star Schema (1) Lookup Product_key Product_desc Item_id Class_id Dept_id Division_id Level Lookup Time Fact Sales Lookup Geography Product_key Geo_key Time_key Sales_Dollars Sales_Units Time_key Time_desc Date Month_id Year_id Level Geo_key Geo_desc Store_id Market_id Region_id Level EDUCATION

Consolidated Star Schema Lookups Star #1 Lookup Geography Geo_key Geo_desc Store_id Market_id Region_id Level Generic dimension key Null fields Level flag EDUCATION

Limitation of Consolidated Star (1) • How do we get the following desired report? Region Store Northeast South Boston Philly Baltimore Charlotte Atlanta Durham Sales $ 1, 000 2, 000 Necessary Tables: Lookup Geography Fact Sales Geo_key Geo_desc Store_id Market_id Region_id Level SELECT a 1. Geo_desc Region, a 2. Geo_desc Store FROM LU_Geo a 1, LU_Geo a 2, Fact a 3 WHEREa 1. Geo_Key = a 2. Geo_Key = a 3. Geo_Key a 1. level = 3 a 2. level = 1 GROUP BY Region, Store Product_key Geo_key Time_key Sales_Dollars Sales_Units • Self-join is necessary. • Self-joins are generally undesirable. EDUCATION

Consolidated Star Schema (2) Lookup Product_key Item_desc Class_desc Dept_desc Division_desc Level Lookup Time Fact Sales Lookup Geography Product_key Geo_key Time_key Sales_Dollars Sales_Units Time_key Date Month_desc Year_id Level Geo_key Store_desc Market_desc Region_desc Level EDUCATION

Consolidated Star Schema (3) Lookup Product_key Item_id Item_desc Class_id Class_desc Dept_id Dept_desc Division_id Division_desc Level Lookup Geography Lookup Time Fact Sales Product_key Geo_key Time_key Sales_Dollars Sales_Units Time_key Date Month_id Month_desc Year_id Level Geo_key Store_id Store_desc Market_id Market_desc Region_id Region_desc Level EDUCATION

Consolidated Star Lookup Comparison Cons. Star #1 Cons. Star #2 Cons. Star #3 Lookup Geography Geo_key Geo_desc Store_id Market_id Region_id Level Geo_key Store_desc Market_desc Region_desc Level Geo_key Store_id Store_desc Market_id Market_desc Region_id Region_desc Level EDUCATION

Consolidated Star Schema Fact Tables Fact Sales Product_key Geo_key Time_key Sales_Dollars Contains all possible levels of data. Product_key Geo_key Time_key Sales Item Class Dept Division Item. . . Store Market Region Store. . . Date Date Date Month. . . 100 200 300 500 200 350 500 600 500 450 750 900 1500. . . EDUCATION

Consolidated Star Schema Summary Lookup Product_key Product_desc Item_id Class_id Dept_id Division_id Level Lookup Geography Geo_key Geo_desc Store_id Market_id Region_id Level Lookup Time Fact Sales Product_key Geo_key Time_key Sales_Dollars Sales_Units Time_key Time_desc Date Month_id Year_id Level Select Product_desc, Geo_desc, Time_desc, Sales_Dollars, Sales_Units From Fact_Sales F, Lookup_Product P, Lookup_Geography G, Lookup_Time T Where F. Product_key = P. Product_key And F. Geo_key = G. Geo_key And F. Time_key = T. Time_key And G. Level = ? ? And P. Level = ? ? And T. Level = ? ? EDUCATION

Normalized Star Schema (1) Lookup Division Lookup Region Lookup Year Division_id Division_desc Region_id Region_desc Year_id Lookup Month Lookup Dept Lookup Market Dept_id Dept_desc Division_id Market_desc Region_id Lookup Day Lookup Class_id Class_desc Dept_id Lookup Item_id Item_desc Class_id Month_desc Year_id Lookup Store_id Store_desc Market_id Date Month_id Fact Sales Item_id Store_id Date Sales_Dollars Sales_Units EDUCATION

Normalized Star Schema Lookups Lookup Region Normalized Star #1 Region_id Region_desc Lookup Market Lookup Store Market_id Market_desc Region_id Store_desc Market_id EDUCATION

Normalized Star Schema (2) Lookup Division Lookup Region Division_id Division_desc Lookup Year Region_id Region_desc Year_id Lookup Dept Lookup Month Dept_id Dept_desc Division_id Month_desc Year_id Lookup Market_id Market_desc Region_id Lookup Class_id Class_desc Dept_id Division_id Lookup Item_id Item_desc Class_id Dept_id Division_id Lookup Day Lookup Store_id Store_desc Market_id Region_id Date Month_id Year_id Fact Sales Item_id Store_id Date Sales_Dollars Sales_Units EDUCATION

Normalized Star Schema Lookups (2) Lookup Region Normalized Star #2 Lookup Market Lookup Store Region_id Region_desc Market_id Market_desc Region_id Store_desc Market_id Region_id Denormalized attribute id column EDUCATION

Normalized Star Schema (3) Lookup Division_id Division_desc Lookup Region Lookup Year Region_id Region_desc Year_id Lookup Dept Lookup Month Dept_id Dept_desc Division_id Division_desc Lookup Market Lookup Class_id Class_desc Dept_id Dept_desc Division_id Division_desc Lookup Item_id Item_desc Class_id Class_desc Dept_id Dept_desc Division_id Division_desc Month_id Month_desc Year_id Market_desc Region_id Region_desc Lookup Day Lookup Store_id Store_desc Market_id Market_desc Region_id Region_desc Date Month_id Month_desc Year_id Fact Sales Item_id Store_id Date Sales_Dollars Sales_Units EDUCATION

Normalized Star Lookups Comparison Snowflake #1 Snowflake #2 Snowflake #3 Lookup Store_id Store_desc Market_id Region_id Store_desc Market_id Market_desc Region_id Region_desc EDUCATION

Normalized Star Schema Fact Tables Atomic level data Aggregate level data Fact Sales - Stores Fact Sales - Markets Fact Sales - Regions Item_id Store_id Date Sales_Dollars Sales_Units Item_id Market_id Date Sales_Dollars Sales_Units Item_id Region_id Date Sales_Dollars Sales_Units EDUCATION

Why ‘Higher Level’ Attribute Lookup Tables? • Below is pictured a Snowflake Schema without any higher-level lookup tables. Note its similarity to the Consolidated Star Schema. Fact Sales - Regions • Consider the questions below. Item_id Region_id Date Sales_Dollars Sales_Units Lookup Store_id Store_desc Market_id Market_desc Region_id Region_desc Fact Sales - Stores Lookup Item_id Item_desc Class_id Class_desc Dept_id Dept_desc Division_id Division_desc Item_id Store_id Date Sales_Dollars Sales_Units Lookup Day Date Month_id Month_desc Year_id EDUCATION

Why ‘Higher Level’ Attribute Lookup Tables • • Higher level lookup tables provide for more efficient browsing. Higher level lookup tables are what enable the use of aggregate fact tables. Lookup Region Lookup Division Fact Sales - Regions Division_id Division_desc Lookup Dept_id Dept_desc Division_id Division_desc Item_id Region_id Date Sales_Dollars Sales_Units Region_id Region_desc Lookup Store_id Store_desc Market_id Market_desc Region_id Region_desc Fact Sales - Stores Lookup Item_id Item_desc Class_id Class_desc Dept_id Dept_desc Division_id Division_desc Item_id Store_id Date Sales_Dollars Sales_Units Lookup Day Date Month_id Month_desc Year_id EDUCATION

Normalized Star Schema Summary Lookup Division Lookup Region Division_id Division_desc Region_id Region_desc Lookup Dept Fact Sales - Regions Dept_id Dept_desc Division_id Division_desc Item_id Region_id Date Sales_Dollars Sales_Units Lookup Store_id Store_desc Market_id Market_desc Region_id Region_desc Lookup Item_id Item_desc Class_id Class_desc Dept_id Dept_desc Division_id Division_desc Fact Sales - Stores Item_id Store_id Date Sales_Dollars Sales_Units Lookup Day Date Month_id Month_desc Year_id EDUCATION

The Consolidated Star vs. The Snowflake • Consolidated Star • • • Few Tables / Few Joins Easy SQL No support for M: M relationships Limited support for characteristic attributes Inflexible schema Not scalable vs. • Snowflake • • • More Tables / More Joins More complex SQL M: M is possible Characteristic attributes fully supported Flexible schema Scalable EDUCATION

Continue Case Study • Continue working on the Case Study. EDUCATION