Our Model Store Dimension Product Dimension Total Region

  • Slides: 15
Download presentation
Our Model Store Dimension Product Dimension Total Region District Stores Total Manufacturer Brand Products

Our Model Store Dimension Product Dimension Total Region District Stores Total Manufacturer Brand Products Copyright © 1995 -1996 Archer Decision Sciences, Inc.

Another View Year Region Mgr. Region Quarter Manufacturer District Month Brand Store Date Product

Another View Year Region Mgr. Region Quarter Manufacturer District Month Brand Store Date Product Sequence Current Flag City Day of Week State Size Color Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Classic” Star Schema Store Dimension STORE KEY Store Description City State District ID

The “Classic” Star Schema Store Dimension STORE KEY Store Description City State District ID District Desc. Region_ID Region Desc. Regional Mgr. Fact Table Time Dimension STORE KEY PRODUCT KEY PERIOD KEY Dollars Units Price PERIOD KEY Period Desc Year Quarter Month Day Product Dimension PRODUCT KEY Product Desc. Brand Color Size Manufacturer Copyright © 1995 -1996 Archer Decision Sciences, Inc.

A Word About Indexing. . . Compound Keys STORE DISTRICT REGION STORE DESCRIP PL

A Word About Indexing. . . Compound Keys STORE DISTRICT REGION STORE DESCRIP PL 43 A 7 AR 43 A 6 PO 12 B 3 LA 12 A 6 NULL TEXA EPEN NULL SOUTH NORTH PLANO#3 ARLINGTON#2 POTTSTOWN LANSDALE NULL LEVEL STORE DISTRICT REGION Compound Keys Copyright © 1995 -1996 Archer Decision Sciences, Inc.

A Word About Indexing. . . Concatenated Keys CONCATENATED STORE KEY PL 43 A

A Word About Indexing. . . Concatenated Keys CONCATENATED STORE KEY PL 43 A 7 TEXASOUTH AR 43 A 6 TEXASOUTH PO 12 B 3 EPENNORTH LA 12 A 6 EPENNORTH NULL TEXASOUTH NULL EPENNORTH NULL NORTH STORE DESCRIP PLANO#3 ARLINGTON#2 POTTSTOWN LANSDALE NULL LEVEL STORE DISTRICT REGION Concatenated Keys Copyright © 1995 -1996 Archer Decision Sciences, Inc.

A Word About Indexing. . . Generated Keys STORE KEYSTORE DISTRICT REGION 101 102

A Word About Indexing. . . Generated Keys STORE KEYSTORE DISTRICT REGION 101 102 103 104 105 106 107 PL 43 A 7 AR 43 A 6 PO 12 B 3 LA 12 A 6 NULL TEXA EPEN NULL SOUTH NORTH STORE DESCRIP PLANO#3 ARLINGTON#2 POTTSTOWN LANSDALE NULL LEVEL STORE DISTRICT REGION Generated Keys Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Classic” Star Schema w A single fact table, with detail and summary data

The “Classic” Star Schema w A single fact table, with detail and summary data w Fact table primary key has only one key column per dimension w Each key is generated w Each dimension is a single table, highly denormalized Benefits: Easy to understand, easy to define hierarchies, reduces # of physical joins, low maintenance, very simple metadata Drawbacks: Summary data in the fact table yields poorer performance for summary levels, huge dimension tables a problem Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Classic” Star Schema The biggest drawback: dimension tables must carry a “level” indicator

The “Classic” Star Schema The biggest drawback: dimension tables must carry a “level” indicator for every record and every query must use it. In the example below, without the level constraint, keys for all stores in the NORTH region, including aggregates for region and district will be pulled from the fact table, resulting in error. Example: Select A. STORE_KEY, A. PERIOD_KEY, A. dollars from Fact_Table A where A. STORE_KEY in (select STORE_KEY from Store_Dimension B where region = “North” and Level = 2) and etc. . . Level is needed whenever aggregates are stored with detail facts. Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Level” Problem Level is a problem because it causes potential for error. If

The “Level” Problem Level is a problem because it causes potential for error. If the query builder, human or program, forgets about it, perfectly reasonable looking WRONG answers can occur. One alternative: the FACT CONSTELLATION model. . . Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Fact Constellation” Schema District Fact Table District_ID PRODUCT_KEY PERIOD_KEY Dollars Units Price Region

The “Fact Constellation” Schema District Fact Table District_ID PRODUCT_KEY PERIOD_KEY Dollars Units Price Region Fact Table Region_ID PRODUCT_KEY PERIOD_KEY Dollars Units Price Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Fact Constellation” Schema In the Fact Constellations, aggregate tables are created separately from

The “Fact Constellation” Schema In the Fact Constellations, aggregate tables are created separately from the detail, therefor it is impossible to pick up, for example, Store detail when querying the District Fact Table. Major Advantage: No need for the “Level” indicator in the dimension tables, since no aggregated data is stored with lower-level detail Disadvantage: Dimension tables are still very large in some cases, which can slow performance; front-end must be able to detect existence of aggregate facts, which requires more extensive metadata Copyright © 1995 -1996 Archer Decision Sciences, Inc.

Another Alternative to “Level” Fact Constellation is a good alternative to the Star, but

Another Alternative to “Level” Fact Constellation is a good alternative to the Star, but when dimensions have very high cardinality, the sub-selects in the dimension tables can be a source of delay. An alternative is to normalize the dimension tables by attribute level, with each smaller dimension table pointing to an appropriate aggregated fact table, the “Snowflake Schema”. . . Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Snowflake” Schema Store Dimension STORE KEY District_ID Region_ID Store Description City State District

The “Snowflake” Schema Store Dimension STORE KEY District_ID Region_ID Store Description City State District ID District Desc. Region_ID Region Desc. Regional Mgr. Store Fact Table District Fact Table STORE KEY PRODUCT KEY PERIOD KEY District_ID PRODUCT_KEY PERIOD_KEY Dollars Units Price Region. Fact Table Region_ID PRODUCT_KEY PERIOD_KEY Dollars Units Price Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Snowflake” Schema No LEVEL in dimension tables Dimension tables are normalized by decomposing

The “Snowflake” Schema No LEVEL in dimension tables Dimension tables are normalized by decomposing at the attribute level Each dimension table has one key for each level of the dimension’s hierarchy The lowest level key joins the dimension table to both the fact table and the lower level attribute table How does it work? The best way is for the query to be built by understanding which summary levels exist, and finding the proper snowflaked attribute tables, constraining there for keys, then select’ing from the fact table. Copyright © 1995 -1996 Archer Decision Sciences, Inc.

The “Snowflake” Schema Additional features: The original Store Dimension table, completely denormalized, is kept

The “Snowflake” Schema Additional features: The original Store Dimension table, completely denormalized, is kept intact, since certain queries can benefit by its allencompassing content. In practice, start with a Star Schema and create the “snowflakes” with queries. This eliminates the need to create separate extracts for each table, and referential integrity is inherited from the dimension table. Advantage: Best performance when queries involve aggregation Disadvantage: Complicated maintenance and metadata, explosion in the number of tables in the database Copyright © 1995 -1996 Archer Decision Sciences, Inc.

Total Station Surveying Total Station The total stationTotal Station Surveying Total Station The total station
TOTAL PROTECTION TOTAL CONFIDENCE Owens Corning Total ProtectionTOTAL PROTECTION TOTAL CONFIDENCE Owens Corning Total Protection
Our product is called SnapJack because Our productOur product is called SnapJack because Our product
Total Average and Marginal Products The Total ProductTotal Average and Marginal Products The Total Product
INTRODUCING OUR COUNTRY ITALY OUR REGION CALABRIA OURINTRODUCING OUR COUNTRY ITALY OUR REGION CALABRIA OUR
Ecological Characteristics of Region K Region K RegionEcological Characteristics of Region K Region K Region
Primary region Unimodal association region Heteromodal association regionPrimary region Unimodal association region Heteromodal association region
DESASTRES NATURALES BOLIVIA Region Andina Region SubAndina RegionDESASTRES NATURALES BOLIVIA Region Andina Region SubAndina Region
Region of Crete Greece a Smart Region RegionRegion of Crete Greece a Smart Region Region
Skin Continued Region 1 Region 2 Region 3Skin Continued Region 1 Region 2 Region 3
fixed factors variable factors total product average productfixed factors variable factors total product average product
Total Product Concept A product is a combinationTotal Product Concept A product is a combination
DISHWASHER Error Code Product Introduction Product Specification ProductDISHWASHER Error Code Product Introduction Product Specification Product
Product Overview Agenda Intro Product Overview Enhancements ProductProduct Overview Agenda Intro Product Overview Enhancements Product
MARKETING MIX PRODUCT WHAT IS A PRODUCT ProductMARKETING MIX PRODUCT WHAT IS A PRODUCT Product
SAMSUNG AND PRODUCT LINE PRODUCT WIDTH AND PRODUCTSAMSUNG AND PRODUCT LINE PRODUCT WIDTH AND PRODUCT
Product of 455 Product of 96 Product ofProduct of 455 Product of 96 Product of
Product planning management2 New Product Development New productProduct planning management2 New Product Development New product
Product Portfolio Product Mix Product Range ie theProduct Portfolio Product Mix Product Range ie the
Product Launch introduction Brighton Product Design Product LaunchProduct Launch introduction Brighton Product Design Product Launch
Product 1232020 Product QBR Agenda Web EM ProductProduct 1232020 Product QBR Agenda Web EM Product
Product Life Thinking Product Life Thinking Product LifeProduct Life Thinking Product Life Thinking Product Life
The Product Rule The Product Rule The productThe Product Rule The Product Rule The product
Sample Clustered Column Store Index Row StoreCol StoreSample Clustered Column Store Index Row StoreCol Store
Aspire Vertical Markets Retail Store Retail Store SolutionAspire Vertical Markets Retail Store Retail Store Solution
Store Summary Store Avg Init Lumi E 30Store Summary Store Avg Init Lumi E 30
Store Types Department Store n n Offers linesStore Types Department Store n n Offers lines
RMS Record Store static Record Store open RecordRMS Record Store static Record Store open Record
The Numbers Past Stores Store Summary Store AvgThe Numbers Past Stores Store Summary Store Avg
Store Summary Store Avg Init Lumi E 30Store Summary Store Avg Init Lumi E 30
Store Types Department Store n n Offers linesStore Types Department Store n n Offers lines
Store Summary Store Avg Init Lumi E 30Store Summary Store Avg Init Lumi E 30
Direct Store Delivery Back door traffic at storeDirect Store Delivery Back door traffic at store
Store Inventory Introduction v v Mu MMy StoreStore Inventory Introduction v v Mu MMy Store
Binary Hacks 94 n Store Buffer n StoreBinary Hacks 94 n Store Buffer n Store
A Store Wizard webruhz bemutatsa A Store WizardA Store Wizard webruhz bemutatsa A Store Wizard
Store Summary Store Avg Init Lumi E 30Store Summary Store Avg Init Lumi E 30
Retail Store Operations and Store Management Course ObjectivesRetail Store Operations and Store Management Course Objectives
www Elabore Store com OFFICIAL BRAND STORE Elaborewww Elabore Store com OFFICIAL BRAND STORE Elabore
UNIVERSITY OF BERGEN Store data store muligheter datavisualiseringUNIVERSITY OF BERGEN Store data store muligheter datavisualisering