0

What is the Date Dimension? How to use it in Incorta?

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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:

     

    Column

    Sample Data

    Date

    1-Jan-80

    Day_Number

    1

    Day_Name

    Tuesday

    week_of_month

    Week-1

    week_of_year

    Week-1

    Week_Start_Date

    12/31/1979

    Month_Name

    January

    Month_Number

    1

    Month_Seq

    1

    Month_Year

    Jan-80

    Month_Year_Number

    198001

    Month_Start_date

    1/1/1980

    Month_End_date

    1/31/1980

    QuarterName

    Q1/1980

    Quarter_Number

    Q1

    Quarter_Year_Number

    19801

    Quarter_Start_date

    1/1/1980

    Quarter_End_date

    3/31/1980

    Year

    1980

    Year_Start_date

    1/1/1980

    Year_End_date

    12/31/1980

    day_seq

    0

    week_seq

    0

    year_seq

    0

    week_ago_date

    12/25/1979

    day_ago_date

    12/31/1979

    month_ago_date

    12/1/1979

    quarter_ago_date

    10/1/1979

    day_type

    HOLIDAY

    workday_seq

    0

    holiday_name

    USNewYearsDay

    Using the Date Dimension

    1. Upload the Date_US.csv as a CSV source
    2. Add the date table to a schema
    3. Add joins linking date dimension (parent) on "Date" column to table(s) with measures (child) on a date column
    4. 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:


     

    Reply Like
  • Do we have any function for months between two dates?

    Reply Like
    • DILIP KUMAR RACHAMALLA This is not supported in the current version, but we have it on our roadmap.
      A possible workaround is to use the daysBetween function and divide the result by 30.42 (that is 365/12). But that is not so accurate since there is an average of 30.5 days/month in a leap year.

      Reply Like
    • Moataz-Bellah Mishrif 

      Thank you for the response. Yes, we did implement the daysBetween function and facing issues with leap year. So we are just checking if any function available. Any ETA for this?

      Reply Like
Like Follow
  • Status Answered
  • 7 mths agoLast active
  • 4Replies
  • 603Views
  • 3 Following