Date Dimension Past Future in One Script Steve

Date Dimension: Past & Future in One Script Steve Wake, BI Developer, Chipotle

Please silence cell phones 2

Agenda • • • 3 What & Why of Date/Time Dimensions Date/Time Dimension Benefits What to Include in Date Dimension What to Include in Time Dimension Date/Time Dimension Scripts Demo

What is a Date Dimension? • Dimension to track dates by day • Can track multiple calendars • Gregorian, Julian, Fiscal… • Time in separate dimension (if needed) • One row per date/day 4

What is a Time Dimension? • Dimension to track time • Hours, Minutes, Seconds… • Keep time zones in mind (standardize or UTC? ) • Separate from Date Dimension for flexibility • Only needs to cover 24 hours 5

Why Date/Time Dimensions? Most reporting is done by date/time Track Holidays and other significant dates Do date calculations in SQL once Date/Time period comparisons Generate for years/decades easily • • • 6

Date/Time Dimension Types Calculated (Dynamic SQL, DAX) – Slow Tool Created (Excel, Power BI) – Tool Dependent Custom Physical (copy/paste) – Hard to Update Hybrid – Most Flexible, Custom Dev Time Steve’s Script – Flexible, Fast & Easy to Update • • • 7

What to Include in dim. Date? Date as Integer for PK to make reload not break links (20130806) Date in multiple formats (“ 08/06/2013”, “Tuesday, August 6, 2013”…) Multiple parts (Month, Day, Year, Month Text, Day Text…) Holiday indicator (domestic, international, religious…) Gregorian, Julian, and Fiscal Calendars Calculated measures (Day # in week, Day # in month, Day # in year, Week # in month, Week # in year…) • • • 8

What to Include in dim. Time? Time as Integer for PK to make reload not break links (130101) Time in multiple formats (“ 01: 01 PM”, “ 13: 01”…) Multiple parts (Hour, Minute, Second…) AM/PM indicators Daylight Saving Indicator? Time Zones? (GMT/UTC? ) • • • 9

Demo dim. Date Script dim. Time Script

Session Evaluations Submit by 5 pm Friday November 6 th to WIN prizes Your feedback is important and valuable. ways to access Go to pass. Summit. com 11 Download the Guide. Book App and search: PASS Summit 2015 Follow the QR code link displayed on session signage throughout the conference venue and in the program guide

Contact swake@chipotle. com @stevewake on Twitter Linked. In, Google+ Blog – http: //wakebi. com

Thank You