is proudly partnered with Send instant feedback on this session via Twitter Send a direct message with the room number to CodeStock d codestock 503 This session is great For more information on sending feedback using Twitter while at ID: 200320
Download Presentation The PPT/PDF document "CodeStock" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
CodeStock
is proudly partnered with:
Send instant feedback on this session via Twitter: Send a direct message with the room number to @CodeStock d codestock 503 This session is great!For more information on sending feedback using Twitter while at CodeStock, please see the “CodeStock README” in your CodeStock guide.
RecruitWise
and Staff with Excellence - www.recruitwise.jobsSlide2
Wrox Press
Join the discussion
Facebook: www.facebook.com/wroxpressTwitter: @wroxSlide3
Tim Costello
Dimensional Design 101Slide4
Tim Costello
MCIPT
SQL 2005 AdministrationMCTS SQL 2008 Business IntelligenceTableau Certified ProfessionalDallas Tableau User Group leader.Business Intelligence Consultant
for Interworks Inc.
www.Interworks.comSlide5
Inman Corporate Information Factory (CIF)
Kimball Data Warehouse (DW)
- vs - Slide6
Inman Corporate Information Factory (CIF)
Kimball Data Warehouse (DW)
- vs - þSlide7
þ
Dimensional Bus
Things we will cover …Slide8
þ
Dimensional Bus
Fact TablesþThings we will cover …Slide9
þ
Dimensional Bus
Fact TablesDimension Tablesþþ
Things we
will
cover …Slide10
ý
Mega Data Warehouse
Things we will not cover …Slide11
ý
Mega Data Warehouse
OLAPýThings we will not cover …Slide12
ý
Mega Data Warehouse
OLAPETL (Extract Transform Load)ýýThings we will not
cover
…Slide13
ý
Mega Data Warehouse
OLAPETL (Extract Transform Load)Presentation Layerýý
ý
Things we will
not
cover
…Slide14
http://www.flickr.com/photos/comprock/4937334032/sizes/z/in/photostream/Slide15
http://www.flickr.com/photos/cpoyatos/4374856699/sizes/m/in/photostream/
http://www.flickr.com/photos/scottmontreal/2475391816/sizes/m/in/photostream/
http://www.tcpalm.com/photos/2009/aug/05/193893/Slide16
Transactional DatabaseSlide17Slide18
Dimensional DesignSlide19
Star SchemaSlide20
Snowflake SchemaSlide21Slide22
Dimensional BusSlide23Slide24
Fact TableSlide25
Foreign Keys
MeasuresDegenerate Dimensions
Fact Tables Contain** Sometimes.Slide26Slide27
3 Kinds Of Fact Table
Transactional Fact Table
þSlide28
3 Kinds Of Fact Table
Transactional Fact Table
Accumulating SnapshotþþSlide29
3 Kinds Of Fact Table
Transactional Fact Table
Periodic SnapshotAccumulating SnapshotþþþSlide30
Transactional Fact TableSlide31
Accumulating SnapshotSlide32
Periodic SnapshotSlide33
Dimension TablesSlide34Slide35
SELECT DateId
, FullDate , NextDayDate , Season
, CalendarYear , CalendarYearQuarter , CalendarYearMonth , CalendarYearDayOfYear , CalendarQuarter , CalendarMonth , CalendarDayOfYear , CalendarDayOfMonth …Slide36
, CalendarDayOfWeek
, CalendarYearName , CalendarYearQuarterName , CalendarYearMonthName
, CalendarYearMonthNameLong , CalendarQuarterName , CalendarMonthName , CalendarMonthNameLong , WeekdayName , WeekdayNameLong , CalendarStartOfYearDate , CalendarEndOfYearDate , CalendarStartOfQuarterDate …Slide37
, CalendarEndOfQuarterDate
, CalendarStartOfMonthDate , CalendarEndOfMonthDate ,
QuarterSeqNo , MonthSeqNo , FiscalYearName , FiscalYearPeriod , FiscalYearDayOfYear , FiscalYearWeekName , FiscalSemester , FiscalQuarter , FiscalPeriod , FiscalDayOfYear …Slide38
, FiscalDayOfPeriod
, FiscalWeekName , FiscalStartOfYearDate , FiscalEndOfYearDate
, FiscalStartOfPeriodDate , FiscalEndOfPeriodDate , ISODate , ISOYearWeekNo , ISOWeekNo , ISODayOfWeek , ISOYearWeekName , ISOYearWeekDayOfWeekName , DateFormatYYYYMMDD …Slide39
, DateFormatYYYYMD
, DateFormatMMDDYEAR , DateFormatMDYEAR , DateFormatMMMDYYYY
, DateFormatMMMMMMMMMDYYYY , DateFormatMMDDYY , DateFormatMDYY , WorkDay , IsWorkDayfrom dbo.Dim_DateSlide40
Conformed DimensionsSlide41
Role Playing Dimensions
EmployeeID
StartDateKeyEndDateKey…DateKeyFull_DateNextDayDateSeason…2011010220110103
dimDate
factEmployeeReviewSlide42
EmployeeID
StartDateKeyEndDateKey…
StartDateKeyFull_DateNextDayDateSeason…EndDateKeyFull_DateNextDayDateSeason…
dimStartDate
(View based on
dimDate
)
dimEndDate
(View based on
dimDate
)
factEmployeeReviewSlide43Slide44
Resources and LinksSlide45
Kimball Design Tip #18: Taking The Publishing Metaphor Seriously: http://
www.rkimball.com/html/designtipsPDF/DesignTips2001/KimballDT18Taking.pdf Kimball Design Tip #46: Another Look At Degenerate Dimension: http://
www.rkimball.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdfDesign Tip #113 Creating, Using, and Maintaining Junk Dimension: http://www.rkimball.com/html/09dt/DT113CreatingUsingMaintainingJunkDimensions.pdfDesign Tip #105 Snowflakes, Outriggers, and Bridges: http://www.rkimball.com/html/08dt/KU105Snowflakes_Outriggers_Bridges.pdfKimball Design Tip #51: Latest Thinking On Time Dimension Table: http://www.kimballuniversity.com/html/designtipsPDF/KimballDT51LatestThinking.pdfDesign Tip #69 Identifying Business Processes: http://www.rkimball.com/html/designtipsPDF/DesignTips2005/DTKU69IdentifyingBusinessProcesses.pdfKimball Design Tip #37: Modeling A Pipeline With An Accumulating Snapshot: http://
www.rkimball.com/html/designtipsPDF/DesignTips2002/KimballDT37ModelingPipeline.pdf
Kimball Design Tip #16: Hot Swappable Dimension:
http://
www.rkimball.com/html/designtipsPDF/DesignTips2000%20/KimballDT16HotSwappable.pdf
Kimball Design Tip #21: Declaring The Grain:
http://
www.rkimball.com/html/designtipsPDF/DesignTips2001/KimballDT21Declaring.pdf
Fundamental Grains:
http://
www.kimballgroup.com/html/articles_search/articles1999/9903IE.html?TrkID=IE199903_2Slide46
Twitter:
@
TimCostEmail:Tim.Costello@Interworks.comwww.TheDataRevolution.com