(Lecture 3) Star Schema.pdf

Star Schema

1. Star Schema
Asimpledatabasedesigninwhichdimensionaldataareseparated
fromfactoreventdata.Adimensionalmodelisanothernamefora
starschema.
1

2. Fact tables and Dimension tables
Facttablescontainfactualorquantitativedata(measurementsthatare
numerical,continuouslyvalued,andadditive)aboutabusiness,suchas
unitssold,ordersbooked,andsoon.
Dimensiontablesholddescriptivedata(context)aboutthesubjectsof
thebusiness.Thedimensiontablesareusuallythesourceofattributes
usedtoqualify,categorize,orsummarizefactsinqueries,reports,or
graphs;thus,dimensiondataareusuallytextualanddiscrete(evenif
numeric).
2

3. NOTE:Adatamartmightcontainseveralstarschemaswithsimilar
dimensiontablesbuteachwithadifferentfacttable.Typicalbusiness
dimensions(subjects)areProduct,Customer,andPeriod.Period,or
time,isalwaysoneofthedimensions.
3

4. Components of a Star Schema
4

5. An example for Star Schema
1. Which cities have the highest sales of large products?
2. What are the average monthly sales for each store manager?
3. In which stores are we losing money on which products? Does this
vary by quarter?
5

6. 6

7. Surrogate Key
Everykeyusedtojointhefacttablewithadimensiontableshouldbea
surrogate(nonintelligent,orsystem-assigned)key,notakeythatusesa
businessvalue(sometimescalledanatural,smart,orproductionkey).
7

8. GRAIN OF THE FACT TABLE
Thelevelofdetailinafacttable,determinedbytheintersectionofall
thecomponentsoftheprimarykey,includingallforeignkeysandany
otherprimarykeyelementsiscalledgrain.
Anexampleofacommongrainwouldbeeachbusinesstransaction,
suchasanindividuallineitemoranindividualscanneditemona
productsalesreceipt,apersonnelchangeorder,alineitemona
materialreceipt,aclaimagainstaninsurancepolicy,aboardingpass,or
anindividualATMtransaction.
8

9. Estimating the size of the fact table
1.Estimatethenumberofpossiblevaluesforeachdimension
associatedwiththefacttable(inotherwords,thenumberofpossible
valuesforeachforeignkeyinthefacttable).
2.Multiplythevaluesobtainedinthefirststepaftermakingany
necessaryadjustments.
9

10. Anexample
10

11. An example (cont…)
With the grain of the table changed to daily item totals, the number of
rows is computed as follows:
11

12. MODELING DATE AND TIME
Becausedatawarehousesanddatamartsrecordfactsabout
dimensionsovertime,date,andtime,hence‘Date’isalwaysa
dimensiontable,andadatesurrogatekeyisalwaysoneofthe
componentsoftheprimarykeyofanyfacttable.
Becauseausermaywanttoaggregatefactsonmanydifferentaspects
ofdateordifferentkindsofdates,adatedimensionmayhavemany
nonkeyattributes.Also,becausesomecharacteristicsofdatesare
countryorevent-specific(e.g.,whetherthedateisaholidayorthereis
somestandardeventonagivenday,suchasafestivalorfootball
game),modelingthedatedimensioncanbemorecomplex.
12

13. Modeling dates Illustration
13

14. Variations of the Star Schema
Multiple fact tables with conformed dimensions
Conformed Dimensions: One or more dimension tables associated with
two or more fact tables for which the dimension tables have the same
business meaning and primary key with each fact table.
In general, conformed dimensions allow users to do the following:
• Share nonkeydimension data
• Query across fact tables with consistency
• Work on facts and business subjects for which all users have the same
meaning
14

15. 15

16. FACTLESS FACT TABLES
There are applications for fact tables that do not have nonkey(fact)
data but do have foreign keys for the associated dimensions.
16

17. Factlessfact table showing occurrence of an
event
17

18. Factlessfact table showing coverage
18

19. Using Helper tables for multivalued
dimensions
19

20. Snowflakeschema
An expanded version of a star schema in which dimension tables are
normalized into several related tables.
When the dimension tables are further normalized by using helper
tables (sometimes called bridge tables, or reference tables), the simple
star schema turns into a snowflake schema.
20