Logical Modeling and Design: Assignment

Added on - 17 Oct 2019

  • 25


  • 3038


  • 242


  • 0


Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 6 of 25 pages
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 animplementation neutral representation for a future DW known as Dimensional Model. TechnicallyLM&D can be aligned with relational model (RM), multidimensional model (MM) or a hybrid model, acombination of RM and MM. For two fundamental reasons our course will be to prepare DW design forrelational model implementation: a) educational benefits of building on theoretically grounded and wellstandardized technology (our students are familiar with RM theory and the SQL from a pre-requisitedatabase course), and b) relational implementation scales much better (due to avoiding sparsity and tostability/services of a proven mature relational database technology). Furthermore, evolution of relationalDW implementation technologies exploits data sourcing from RDBMS and on the other hand RDBMSengines evolution itself incorporates successful concepts from multidimensional technology (both Oracleand 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 viewscandidates) and will be further supported in our lectures and assignments by industrial strength modelsand 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. Interms 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 LogicalModeling and Logical Design for DW strictly in a relational implementation setting. Before outliningsteps 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 assnowflake and constellation (multiple-stars sharing dimensions) schemas. See definitions from thetextbook’s pp 221 and on.Star schema consists of a fact table (typically with a set of measurement attributes) and a set ofcorresponding dimensional tables referencing the fact table. A multidimensional view of data is obtainedby combining (SQL joins) fact table with related dimensional tables.Snowflake schema involves at least partially normalized dimensions, and a constellation schema involvesseveral 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 ofdimensional dynamics (types of changes and modeling alternatives to support various temporalscenarios). Additional practical solutions for DFM translation into DM require mastery of star schemamodel 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 forlogical models within relational model (ROLAP) approach and the OMA selection of MVs:dim ens iondim ens ionsdim ens ionLogical Modeling - DM otionswithin 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 usingnatural keys in DWA lternative logical representationusing convenience keys,sometimes called DW surrogatesf or all entities or a f act surrogatekey 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 representationusing convenience keys,sometimes called DW surrogatesf or all entities or a f act surrogatekey 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.keyPand ft.keyS = DT2.keySand ft.keyD = dt1.keyDwill produce the cube for analytics,three more joins would be needed for a snowflakeDATEkeyDdatemonthquarteryeardayweekholidaySALESkeyD (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
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document