1 / 23

DSS Schemas

DSS Schemas. The Consolidated Star. The Normalized Star (Snowflake). DSS Schemas -- Agenda. Geography. Time. Year. Division. Region. Month. Dept. Market. Day. Class. Store. Item. Example Logical Model. Product. Fact Sales. Lookup Product

owen-ross
Download Presentation

DSS Schemas

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DSS Schemas

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

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

  4. Lookup Product Product_keyProduct_descItem_idClass_idDept_idDivision_idLevel Lookup Time Time_keyTime_descDateMonth_idYear_idLevel Fact Sales Product_keyGeo_keyTime_keySales_DollarsSales_Units Lookup Geography Geo_keyGeo_descStore_idMarket_idRegion_idLevel Consolidated Star Schema (1)

  5. Lookup Geography Geo_keyGeo_descStore_idMarket_idRegion_idLevel Consolidated Star Schema Lookups Star #1 Level flag Generic dimension key Null fields

  6. Lookup Geography Geo_keyGeo_descStore_idMarket_idRegion_idLevel Fact Sales Product_keyGeo_keyTime_keySales_DollarsSales_Units Region NortheastNortheast Northeast SouthSouth South Store BostonPhillyBaltimoreCharlotteAtlantaDurham Sales $ 1,000 1,000 1,000 2,0002,0002,000 • Necessary Tables: Limitation of Consolidated Star (1) • How do we get the following desired report? SELECT a1.Geo_desc Region, a2.Geo_desc StoreFROM LU_Geo a1, LU_Geo a2, Fact a3WHERE a1.Geo_Key = a2.Geo_Key a2.Geo_Key = a3.Geo_Key a1.level = 3 a2.level = 1GROUP BY Region, Store • Self-join is necessary. • Self-joins are generally undesirable.

  7. Lookup Product Product_keyItem_descClass_descDept_descDivision_descLevel Lookup Time Time_keyDateMonth_descYear_idLevel Fact Sales Product_keyGeo_keyTime_keySales_DollarsSales_Units Lookup Geography Geo_keyStore_descMarket_descRegion_descLevel Consolidated Star Schema (2)

  8. Lookup Product Product_keyItem_idItem_descClass_idClass_descDept_idDept_descDivision_idDivision_descLevel Lookup Time Time_keyDateMonth_idMonth_descYear_idLevel Fact Sales Product_keyGeo_keyTime_keySales_DollarsSales_Units Lookup Geography Geo_keyStore_idStore_descMarket_idMarket_descRegion_idRegion_descLevel Consolidated Star Schema (3)

  9. Lookup Geography Geo_keyGeo_descStore_idMarket_idRegion_idLevel Lookup Geography Geo_keyStore_descMarket_descRegion_descLevel Lookup Geography Geo_keyStore_idStore_descMarket_idMarket_descRegion_idRegion_descLevel Consolidated Star Lookup Comparison Cons. Star #1 Cons. Star #2 Cons. Star #3

  10. Fact Sales Product_keyGeo_keyTime_keySales_Dollars Consolidated Star Schema Fact Tables Product_keyItemClassDeptDivisionItemClassDeptDivision ItemClassDeptDivision Item... Geo_key Store Store StoreStoreMarketMarketMarketMarketRegionRegion Region Region Store... Time_keyDateDateDateDate DateDateDateDateDateDateDateDate Month... Sales1002003005002003505006005004507509001500... Contains all possible levels of data.

  11. Lookup Product Product_keyProduct_descItem_idClass_idDept_idDivision_idLevel Lookup Time Time_keyTime_descDateMonth_idYear_idLevel Fact Sales Product_keyGeo_keyTime_keySales_DollarsSales_Units Lookup Geography Geo_keyGeo_descStore_idMarket_idRegion_idLevel Consolidated Star Schema Summary SelectProduct_desc, Geo_desc, Time_desc, Sales_Dollars, Sales_Units From Fact_Sales F, Lookup_Product P, Lookup_Geography G, Lookup_Time TWhere 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 = ??

  12. Lookup Division Division_idDivision_desc Lookup Region Region_idRegion_desc Lookup Year Year_id Lookup Month Month_idMonth_descYear_id Lookup Dept Dept_idDept_descDivision_id Lookup Market Market_idMarket_descRegion_id Lookup Day DateMonth_id Lookup Class Class_idClass_descDept_id Lookup Store Store_idStore_descMarket_id Lookup Item Item_idItem_descClass_id Fact Sales Item_idStore_idDateSales_DollarsSales_Units Normalized Star Schema (1)

  13. Lookup Region Region_idRegion_desc Lookup Market Market_idMarket_descRegion_id Lookup Store Store_idStore_descMarket_id Normalized Star Schema Lookups Normalized Star #1

  14. Lookup Division Division_idDivision_desc Lookup Region Region_idRegion_desc Lookup Year Year_id Lookup Dept Dept_idDept_descDivision_id Lookup Month Month_idMonth_descYear_id Lookup Market Market_idMarket_descRegion_id Lookup Class Class_idClass_descDept_idDivision_id Lookup Day DateMonth_idYear_id Lookup Store Store_idStore_descMarket_idRegion_id Lookup Item Item_idItem_descClass_idDept_idDivision_id Fact Sales Item_idStore_idDateSales_DollarsSales_Units Normalized Star Schema (2)

  15. Lookup Region Region_idRegion_desc Lookup Market Market_idMarket_descRegion_id Lookup Store Store_idStore_descMarket_idRegion_id Normalized Star Schema Lookups (2) Normalized Star #2 Denormalized attribute id column

  16. Lookup Division Division_idDivision_desc Lookup Region Region_idRegion_desc Lookup Year Year_id Lookup Dept Dept_idDept_descDivision_idDivision_desc Lookup Month Month_idMonth_descYear_id Lookup Market Market_idMarket_descRegion_idRegion_desc Lookup Class Class_idClass_descDept_idDept_descDivision_idDivision_desc Lookup Day DateMonth_idMonth_descYear_id Lookup Store Store_idStore_descMarket_idMarket_descRegion_idRegion_desc Lookup Item Item_idItem_descClass_idClass_descDept_idDept_descDivision_idDivision_desc Fact Sales Item_idStore_idDateSales_DollarsSales_Units Normalized Star Schema (3)

  17. Lookup Store Store_idStore_descMarket_id Lookup Store Store_idStore_descMarket_idRegion_id Lookup Store Store_idStore_descMarket_idMarket_descRegion_idRegion_desc Normalized Star Lookups Comparison Snowflake #1 Snowflake #2 Snowflake #3

  18. Fact Sales - Markets Item_idMarket_idDateSales_DollarsSales_Units Fact Sales - Regions Item_idRegion_idDateSales_DollarsSales_Units Normalized Star Schema Fact Tables Atomic level data Aggregate level data Fact Sales - Stores Item_idStore_idDateSales_DollarsSales_Units

  19. Fact Sales - Regions Item_idRegion_idDateSales_DollarsSales_Units Lookup Store Store_idStore_descMarket_idMarket_descRegion_idRegion_desc Lookup Item Item_idItem_descClass_idClass_descDept_idDept_descDivision_idDivision_desc Fact Sales - Stores Item_idStore_idDateSales_DollarsSales_Units Lookup Day DateMonth_idMonth_descYear_id 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. • Consider the questions below.

  20. Lookup Division Division_idDivision_desc Lookup Region Region_idRegion_desc Lookup Dept Dept_idDept_descDivision_idDivision_desc Fact Sales - Regions Item_idRegion_idDateSales_DollarsSales_Units Lookup Store Store_idStore_descMarket_idMarket_descRegion_idRegion_desc Lookup Item Item_idItem_descClass_idClass_descDept_idDept_descDivision_idDivision_desc Fact Sales - Stores Item_idStore_idDateSales_DollarsSales_Units Lookup Day DateMonth_idMonth_descYear_id 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.

  21. Lookup Division Division_idDivision_desc Lookup Region Region_idRegion_desc Lookup Dept Dept_idDept_descDivision_idDivision_desc Fact Sales - Regions Item_idRegion_idDateSales_DollarsSales_Units Lookup Store Store_idStore_descMarket_idMarket_descRegion_idRegion_desc Lookup Item Item_idItem_descClass_idClass_descDept_idDept_descDivision_idDivision_desc Fact Sales - Stores Item_idStore_idDateSales_DollarsSales_Units Lookup Day DateMonth_idMonth_descYear_id Normalized Star Schema Summary

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

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

More Related