Build your Own Date Dimension for Power BI – Part 1


The most common dimension used in a BI solution is the Date dimension. This is obvious since almost any type of analysis involves looking at data from some aspect of date; year, month, or even hours and minutes in some cases. In traditional BI scenarios, a Date dimension table is always created as a practice, as is an ETL to populate it. The usual case is a stored procedure which generates a range of dates between two given dates, and scheduled to run on a regular basis: yearly, quarterly etc.

In the case of a Power BI solution, one would not have the luxury of a date-generator stored procedure. Instead we could go for a purpose-created Excel file with all necessary fields with a range of dates. The issue with this would be that if you plan to use it across various disparate solutions, the Excel would have to be quite comprehensive with a large range of dates to support various scenarios. Locality will also have to be taken into consideration, such as a British calendar or a US one for example.

With Power BI having a plethora of data source options, the Azure Marketplace being one of them, a compelling option would be to make available a date dimension service that can be accessed via this option. And there are! A couple of them, and also for free. However, apart from a service being free on the Azure Marketplace, you also have to take a look at the number of transactions that are allowed by the service. The Date Dimension, though free only allows up to 10 transactions off of it per month – not too good a deal, especially when you are going to do a lot of R&D. My favorite is Boyan Penev’s DateStream. It’s free and allows unlimited number of transactions.

This video runs through the steps of accessing the Azure Marketplace for the first time using your Microsoft account, and selecting a data feed to source your dimension. The steps are:

  1. Select Azure Marketplace as the data source in Power Query
  2. Sign in with a Microsoft Account
  3. Allow Power BI to access the Azure Marketplace account and save the credentials
  4. Use the Add Data Feed option to choose and subscribe to a data set

In the demo, I first select the Date Dimension data set, and then move on to the more flexible-in-accessibility data set DateStream:

Connect to Azure Marketplace from Power Query

 

The next post will take you through the steps of making use of the data set by pulling in the data from the API,  massaging it to suit your needs, and finally modeling it to be used in your solution.

Advertisements

One thought on “Build your Own Date Dimension for Power BI – Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s