Almost all measurements in a BI project are defined at specific points of time and most measurements are repeated over time. This derives the need to have a separate date or time dimension table to navigate and aggregate these measurements over time. This makes it easy to drill down on different calendars of the date dimensions. Most used calendars are Gregorian, Religious, National, Fiscal, Solar Hijri calendar, Lunar Hijri calendar… etc.
The most common and useful time dimension is the calendar date dimension with the granularity of a single day. So, you would have 365 rows for every normal year and 366 days for every leap year. Each row may contain several columns depending on the calendar(s) used in the business. Several types of calendars may be used at the same time (e.g. Gregorian and Fiscal). Besides the usual attributes (such as month name and year), other columns like holidays, work days, fiscal periods, quarters, week numbers, last day of month flags, and other navigational attributes must be embedded in the calendar date dimension and all date navigation.
The best way to generate the calendar date dimension is to use a spreadsheet and build it by hand using the spreadsheet builtin date functions. Ten years worth of days is less than 4000 rows. In special cases, if the business requires more details, the granularity may go down to an hour or a minute.
Date Dimension in Incorta
Incorta is shipping a date dimension file with every Demo tenant. The is called Date_US (Date_US.csv). The date dimension covers the period from 01-Jan-1980 to 31-Dec-2030. The date dimension file is structured like:
Using the Date Dimension
- Upload the Date_US.csv as a CSV source
- Add the date table to a schema
- Add joins linking date dimension (parent) on "Date" column to table(s) with measures (child) on a date column
- Use date dimension in insights with columns from Date dimension as "Grouping elements", and measures from liked fact tables in the "measures elements"
The following example illustrates the use of the Year, Quarter and Month from the Date dimension and Revenue measure from Sales table of sample Sales schema: