Ask a question from expert
Logical Modeling and Design: Assignment
25 Pages3038 Words492 Views
Added on 2019-10-18
Logical Modeling and Design: Assignment
Added on 2019-10-18
BookmarkShareRelated Documents
Lecture Notes- Module 4 Logical Modeling and DesignWhat is LM&D and what we will cover in this module?Logical Modeling and Design of Data Warehouses is centered on the analysis and creation of an implementation neutral representation for a future DW known as Dimensional Model. Technically LM&D can be aligned with relational model (RM), multidimensional model (MM) or a hybrid model, a combination of RM and MM. For two fundamental reasons our course will be to prepare DW design for relational model implementation: a) educational benefits of building on theoretically grounded and well standardized technology (our students are familiar with RM theory and the SQL from a pre-requisite database course), and b) relational implementation scales much better (due to avoiding sparsity and to stability/services of a proven mature relational database technology). Furthermore, evolution of relational DW implementation technologies exploits data sourcing from RDBMS and on the other hand RDBMS engines evolution itself incorporates successful concepts from multidimensional technology (both Oracle and MS SQL Server included MM capabilities into their DB engines).The textbook coverage (from chapters 8 and 9) provides excellent background with focus on transitioningfrom DFM to Dimensional Models (DM) and grounded treatment of aggregate views (materialized views candidates) and will be further supported in our lectures and assignments by industrial strength models and experiences (including extensive case studies presented among the additional M4 readings) following insights primarily from the Kimball’s The Data Warehouse Toolkit”-2ed and the Adamson’s “Mastering Warehouse Aggregates” so that substantial value is added in terms of realistic examples. In terms of visual representation our practice will include Dimensional Model diagrams and visualization (specification of) logical views, both using the case tool ERwin. This module covers both Logical Modeling and Logical Design for DW strictly in a relational implementation setting. Before outlining steps and techniques to be elaborated a few terminology/definitional notes are in place. The multidimensional modeling in relational systems is based on a star-schema and variations known as snowflake and constellation (multiple-stars sharing dimensions) schemas. See definitions from the textbook’s pp 221 and on. Star schema consists of a fact table (typically with a set of measurement attributes) and a set of corresponding dimensional tables referencing the fact table. A multidimensional view of data is obtained by combining (SQL joins) fact table with related dimensional tables. Snowflake schema involves at least partially normalized dimensions, and a constellation schema involves several starts (facts tables). An outrigger is a secondary dimension not connected to any fact entities. Logical ModelingLogical modeling is covered by the textbook’s chapter 8 and elements from additional examples, industrial case studies, from supplemented cases. The key point in LM with DM is a treatment of dimensional dynamics (types of changes and modeling alternatives to support various temporal scenarios). Additional practical solutions for DFM translation into DM require mastery of star schema model upgrades with bridges, outriggers, mini-dimensions etc. and will require not only careful reading ofch8 but also a thorough review of selected cases relevant to your term project work.
An outrigger is a secondary dimension attached to a dimension, and a mini dimension is a separated (cleaved out attributes from) a large dimension (million of records). Before elaborating details of Dimensional Modeling and Design, let us review principal options for logical models within relational model (ROLAP) approach and the OMA selection of MVs:dim ens iondim ens ionsdim ens ionLogical Modeling - DM otions within RDBMS (ROLAP) DWsSTOREst oreKeyStoreDesc riptiv eAt trcountry _k eycountry _des criptiv eAtt rregion_keyregion_desc riptiv eAt trREGIO Nregion_keyregion_desc riptiv eAt trCOUN TR Ycountry _k eycountry _des criptiv eAtt rregion_key (FK)region_desc riptiv eAt trSTOREst oreKeyStoreDesc riptiv eAt trcountry _k ey (FK)country _des criptiv eAtt rregion_keyregion_desc riptiv eAt trDimensional elaborate, easier to conf orm in DWDimensional (DW) consolidatedTraditional (DB) NormalizedSTOREst oreKeyStoreDesc riptiv eAt trcountry _k ey (FK)COUN TR Ycountry _k eycountry _des criptiv eAtt rregion_key (FK)REGIONregion_keyregion_desc riptiv eAt tr
Order of Magnit ude Analy sis (OMA)Exam ple wit h Produc t Dim ensionAllProduct s_1Category _35Brand_700Product _7500035x20x100x ****** bingo!FACTdayst orecountry _etc _s mallerStepsy ear_et c_sm allerSt epsBrandDay Store_MVstart with 2+ OMs for initial MVs
SALESkey P (FK)key D (F K)key S (FK)quantit yreceipt sunitPricenum berOf CustomersDATEkey Ddatemonthquartery ea rdaywee kholidaySTOREkey Sst orest oreC ityst at ecountrysales Managersales Dis trictPRODU CTkey Pprod uc tty pecate gorydepart m entmarketingGroupbran dbran dC ityLogical representation using natural keys in DWA lternative logical representation using convenience keys, sometimes called DW surrogatesf or all entities or a f act surrogate key onlySTOREkey SStore_s urrogateN umberst orest oreC ityst at ecountrysales Mana gersales Dis trictSALESkey P (FK)key D (F K)key S (FK)quantit yreceipt sunitPricenum berOf Custom ersDATEke y Dda tem on thqu arte ry earda yweekho lidayPRODU CTkey Pproduc tty pecategorydepart m entm arketingGroupbrandbrandC ityAlternative logical representation using convenience keys, sometimes called DW surrogatesf or all entities or a f act surrogate key onlyPRODU CTke y Pprod uc t_surogat eN um berprod uc tty p eca te goryde part mentm arke tingGroupbran dbran dC itySTOREkey SStore_s urrogateN um berst orest oreC ityst at ecountrysales Managersales Dis trictDATEkey Ddate_s urogat eNum berdatem onthquartery eardayweekholidaySALESsales _s urogat eNum berquantit yreceipt sunitPricenum berOf Custom erskey D (FK)key S (FK)key P (FK)
Discuss difference between Star schema and a Snowflake schemaDimensional Models in Erwin:Star chema vs Snowflake schema (figs 8-3 and 8-5)select * from sales ft, data dt1, store dt2, product dt3where ft.keyP = dt3.keyP and ft.keyS = DT2.keyS and ft.keyD = dt1.keyDwill produce the cube for analytics, three more joins would be needed for a snowflake DATEkeyDdatemonthquarteryeardayweekholidaySALESkeyD (FK)keyS (FK)keyP (FK)quantityreceiptsunitpricenunberOfCustomersSTOREkeySstorestoreCitystatecountrysaleMngrsalesDisctrictPRODUCTkeyPproducttypecategorydepartmentmarketingGroupbrandbrandCityDATEkeyDdatemonthquarteryeardayweekholidaySALESkeyD (FK)keyS (FK)keyP (FK)quantityreceiptsunitpricenunberOfCustomersPRODUCTkeyPproductbrandbrandCitykeyT (FK)STOREkeySkeyC (FK)storesaleMngrsalesDisctrictCITYkeyCstoreCitycountryTYPEkeyTtypekeyCa (FK)marketingGroupCATEGORYkeyCacategorydepartment
Fig 7.1 DV 2.0DimCountrycountryKeyISO3CodenamecontinentFactAirpotVisistdateKey (FK)airportKey (FK)countryKey (FK)roleKey (FK)visitorCountDurationOfStayDimAirportairportKeynamecodeDimRoleroleKeynameDimDatedateKeyyearquartermonthdayOfMonthweekdayConformed dimensions (may be snow flaked, see Reinardi’s lecture on advanced DM)DimCarriercarrierKeycarrierCodeuniqueCarrierCodenameFactConnectioncarrierKey (FK)flightKey (FK)destinationAirport (FK)OrigAirport (FK)dateKey (FK)CRSDepTimedepTimedepDelaytaxiOutwheelsOfftaxiInarrTimearvDelayDimFlightflightKeyFlightNumDimAirportairportKeynamecodeDimDatedateKeyyearquartermonthdayOfMonthweekdayFactAirpotVisistdateKey (FK)airportKey (FK)countryKey (FK)roleKey (FK)visitorCountDurationOfStayDimRoleroleKeynameDimCountrycountryKeyISO3Codenamecontinent
End of preview
Want to access all the pages? Upload your documents or become a member.