Creating A Date Dimension

In this series of articles, we will look at some of the core knowledge for passing the Microsoft PL-300 (Microsoft Power BI Data Analyst) exam. In this article we will cover considerations for a Date Dimension when planning a Power BI data model.

As a Power BI consultant and Microsoft Certified Trainer, I was one of the experts that helped Microsoft create the syllabus for the PL-300 and the DP-500 exams. With that in mind I wanted to focus this article on a topic that will support anyone preparing for the PL-300.

With a Power BI data model, the most important dimension is the date dimension (or sometime called a calendar dimension). Whilst working with organisations on Power BI projects I’ve come across models that don’t have a date / calendar dimension. In these instances Power BI creates it’s own auto datetime tables (that are hidden) to allow for time intelligence. Power BI will create a hidden date table for any date columns in the model. This will work but can lead to performance and in-memory file size issues.

In this article we will look at:

• Using a date dimension.
• Requirement for a date dimension.
• How a date dimension will be fundamental to the model.
• What are the pros and cons.
• What to include in a date dimension.
• Creating a date dimension using a script.
• Power BI auto datetime.

So, let’s get started.

Using a Date Dimension

In majority of the cases there is a data source that will have a calendar dimension. With the modern technology this could involve either,

• a dedicated SQL pool where the data is hosted in the SQL server and loaded and transformed in the SQL server instance. In this case there will be a physical calendar dimension table with the data loaded. This table will be loaded based on an ETL process and schedule.
• or with current technology utilizing serverless SQL pools. With serverless SQL pools the data is hosted in storage containers often referred to as a lakehouse. As the data is in the lakehouse then the serverless engine only holds the table metadata. The data will be in the lakehouse in a delta or parquet format and will be referenced when a query is executed in the serverless engine. With Serverless SQL Pools database objects like views, tables, schemas etc can be created to develop a calendar dimension.

Using a calendar dimension from the source system like serverless or dedicated is, in my opinion, the best way for Power BI data modeling. But there are cases where this is not possible.

No Date Dimension in the Source, what to do?

There are times where there is no data warehouse or the ability to create a date dimension in the source. When this occurs, there is a need to write the code to create a date table in order to report across a timeline. This also then allows for the use of time intelligence effectively and efficiently.

So, what are the options?

If we can’t utilize a date table in the source data, then there are two options. The first is to use Power Query, and the second is to create a calculated table in Power BI using DAX.

• Date Dimension that is created in Power Query
  The Power Query engine uses a scripting language called M Code, and a date dimension can be purely coded using this language. But for the low code experience then the power Query ribbons allows developers to create the date table to a large extend code free.
  
  Code is required initially to get the dates between a start and end range. But thereafter majority of the work could be done using the built-in buttons and functions on the ribbon. Very much like using excel.

• Date Dimension as a Calculated Table
  A calculated table in the model will use DAX as the scripting language. The DAX code will then be executed at runtime when the user interacts with the report. There is not a lot of performance overhead with this method, but if the model is complex and you want to move the processing of the date table to the data source, or when the model is refreshed, this could be a better solution.

What are the Requirements for a Date Table in a Power BI model.

• In Power BI a date table needs to be “marked as a date table” for time intelligence to be used.
• Within the date table there needs to be a column that is a data type of “Date”.
• The date column needs to have unique date values.
• The date column needs to have continuous dates between the start date, and the end date.
• Finally, there can’t be any blanks in the date column.

Let’s talk about these requirements in more detail.

To use time intelligence in Power BI, and to create the calculated measures for YTD, MoM, Last year comparisons etc, there needs to be a date table with a specified column that is a continuous date range. How do we do this in Power BI.

Mark as a Date Table

When you select a table, in the Power BI, it takes you to the Table Tools ribbon.
With the table name selected click the “Mark as date table”.

When the Mark as date table dialogue box is visible, then select the date column that will have unique dates. Power BI will do the validation checks mentioned in the requirements above. Once validated, then click the OK button.

Power BI Auto Date / Time

Power BI by default creates in the background its own internal date table. This is not visible to the developers and users but will be part of the model. If you are a developer and use the Vertipaq Analyzer tool, then you can identify if auto date/time tables are being created. From the vertipaq analyzer you will also be able to see the performance impact of these date tables.

If like myself, you prefer to manage and use your own date dimension then there isn’t a need for the auto date/time tables. These tables then only create additional overhead from a performance point of view.

If you want to understand more about Power BI Auto date/time tables, then have a look at this great video from the Guy In A Cube team. Reduce Power BI dataset size by 60% with One Checkbox

For more information on the Power BI Auto Date/Time this is a link to the Microsoft documentation Auto date/time in Power BI Desktop – Power BI | Microsoft Learn

How to Switch off Auto Date/Time

When you have a dedicated date table for your time intelligence calculations then there isn’t a need for the Power BI Auto date/time tables. With this setting selected Power BI will create auto date/time tables that can increase the size of the model and reduce performance.

If there is a date table, then deselect this option in the settings. To do this:

File > Options and Settings > Options.

Either on the Global or Current File section select the Data Load page. Deselect the checkbox for the auto date/time. 

If you select the Global setting this will be changed for all reports in the future. If the Current file is selected the setting will only apply to that particular report file.

What To Include in Your Date Dimension

With any Power BI model there needs to be a planning stage. As a Power BI consultant I always conduct a discovery session with the business to understand some core requirements and deliverables.

From this I will understand what time intelligence measures will be required, how much historic data will be in the model. This will tell me the start and end range of the date table. Also, I’m looking what additional columns will be needed in the date table.

There is a tendency to include all possible columns but are these all necessary. Are you making the model more complicated for the business users. Ask yourself the question, do I need some or all columns relating to Year, Season, Quarter, Month, Week, as well as the required date specific column. If you only require year, quarter, month then only include those.

Also consider:

• Fiscal Dates
  As well as calendar dates where January will be month 1. Organisation may also want fiscal years dates. Organisations fiscal year could be different to the calendar year. If the reports are needed for financial reporting, then think about the fiscal year columns needed.

• Sort Columns
  With a date dimension during the planning stage think about including columns that can be used for the sort order. Let’s look at a month column. For users they will want Month Name that will be January, February, March etc. But then you will require Month number. This month number will be used for the sort order of the Month Name.
  
  To set up the sort column, in Power BI select the column to apply the sort. In the example below it will be “MonthName”. Then in the ribbon click the “Sort by column”. A dropdown will appear with a list of columns in that particular table. Select the sort column. So below we are selecting “MonthNumber”. Power BI will check if this is a valid sort column to use, then Power BI will use MonthNumber to sort visuals that include MonthName.

• 1 To Many Relationship
  With any fact to dimension relationship then there can either be a 1 to 1 relationship, or in majority of cases a 1 to many relationship. With the date dimension the relationship will be 1 to many where there will be one date in the dimension and many rows in the fact table with the date key. This relates to the above requirements for a date table. We mentioned that there needs to be a unique column of dates to make sure that the 1 to many relationship exists.
  If you get a many to many relationship then there is duplicate keys in the date dimension. This needs to be avoided and investigate why.

• Fact Table with Multiple Date Columns
  There are times where there are multiple date columns in the fact table that need to be included in the data model. How do we deal with this?
  
  Lets take an example of a sales model, in the fact table there could be Order Date, Shipped Date, and Due Date etc. This is the case with the Microsoft Adventure Works DW dataset.
  
  In Power BI there can only be one active relationship between two tables. Other relationships can be created but they will be inactive (have a dotted line).
  
  If you want to create a measure that uses an inactive relationship, then you can do this using the USERELATIONSHIP DAX function. This function activates the relationship to allow the measure calculation to execute. It will only be active for the duration it takes for the DAX code to execute.

Syntax – USERELATIONSHIP (ColumnName 1, ColumnName 2)

Where:
ColumnName 1 is the column in the table on the many side of the relationship.
ColumnName 2 is the column in the table on the one side of the relationship.

Reference to the Microsoft DAX page
USERELATIONSHIP function (DAX) – DAX | Microsoft Learn

Script The Date Table

OK, you have got to the stage where you plan to script a calendar date table. There are two options here. Script it in Power Query or as a DAX table. If you choose to use DAX, then from Power BI desktop select the modeling tab and, in the ribbon, select the New Table.

DAX or M Query, which is the best approach?

Both options work well with Power BI but there are some pros and cons to each method. Let’s have a look at these.

• Execution of the script – DAX scripts execute at runtime, meaning that when the Power BI report is used by the users then the script will run. Think about the amount of users that you have that use the report.
  
  Alternatively with the Power Query method, The Power Query engine will execute the processing steps when the report is refreshed. Not when the report is being used. Running the script during the refresh means that it will run went the refresh schedule is set for. This could be middle of the night. But be aware that this could potentially increase the refresh time.

• Performance – Following on from above execution of the script, As the DAX script executes at runtime then if there are 200 users using the report, then the DAX method will run independently for all 200 users unless some caching is used. Using the Power Query method, the script will run once per refresh regardless of the number of users.

Using DAX

Start creating your DAX script.

Once that you have created your new table then you can start to script the code for the required columns. With every calendar dimension as mentioned above, there needs to be a continuous column of dates that does not include blanks, and can be set as a data type of date. The easiest wat to do this in DAX is to use one of the two DAX functions. The first is the CalendarAuto function. The second is the Calendar function.

CalendarAuto()

With CALENDARAUTO() it scans the model and will identify the min and max dates. With the max date the CALENDARAUTO() function will default to the end of the max year. So, if the max date in the model was October 15^th 2022, the function would use December 31^st 2022 as the max date.

From this it will create a table of continuous dates between the Min Date and Max Date.

Syntax – CALENDARAUTO()

For more information on this function CALENDARAUTO – DAX Guide

Calendar()
With the CALENDAR() function you need to specify the start and end dates in the function. The function returns a table of dates between the start date and the end date.

Syntax – CALENDAR( [StartDate] , [EndDate] )

For more information on this function CALENDAR – DAX Guide

Conclusion

For the PL-300 exam it’s important to understand data modeling, relationships, DAX functions, and also performance within Power BI. These are particularity relevant for implementing a date dimension. Every Power BI project that I’ve worked on has required a date table of some sort, so that users can report across timelines and for developers to implement time intelligence calculations. Understanding the requirements in the initial planning stage is key to optimizing the model and correctly setting up a date table.