Wednesday 22 June 2016

Date Dimension, DAX Script and SQL Script




I am going to provide the scripts/codes of the date dimension knows as DimDate to you in 3 main format. (Last updated and tested on 2016-10-21)
  1. SQL Script (Tested in SQL 2016)     (Click here to download)
  2. DAX (Tested in Power BI)                 (Download as Power BI File Or Text format)
  3. M Language (Tested in Power BI)
Some of you might see the final result and say why so many fields? my answers are...
  1. You don't have to use all of them, a good example is the week fields.
  2. In some scenarios I have seen that having extra field like [MonthEndDateFlag] will speed up my process or I get a better query plan from SQL server, so you will have to test test test.
  3. some visualization tools have limitation and because of that you will need more fields.
    a good example is the limitation of  pivot tables in excel.
    Try using fields from date dimension in the "Column", "Rows" and "Filters", for example can you extract dates from the past ten years only Q1 & Q4 and Jan & Dec month only.
    Assuming that you have your Dim in SSAS and you are using Excel, so you will need the [Year], [Quarter] and the [Month] fields and you will need 2 hierarchies in SSAS..
    .
           1- Lustrum => Year => Quarter => Month => Day
           2- Quarter=> Month
  4. A well designed dimension is a dimension with hierarchies.

1 comment: