Build your Own Date Dimension for Power BI – Part 2


In my previous post I talked about and demoed how to get connected to a date data set on the Azure Marketplace. The purpose was to access date information to build a date dimension for a Power BI solution. In this post I will focus on how one would pull in data from the said data set through Power Query, massage it to suit my requirements and finally model it in Power Pivot for consumption.

To do this, let us make use of a simple scenario where I want a date dimension with Years, Months and Dates that go from Year 2000 to 2015. There are two major steps that I will perform:

  1. Extract, Transform and Load the date data to Power Pivot
  2. Structure the loaded data table into a Date dimension

The first step (ETL) uses Power Query to pull in data from the data feed before transforming it to Power Pivot. After connecting to the data set from the Azure Marketplace, I find that there are columns that I do not need, plus dates from the year 1900 till now. In order to get things into shape, I perform a few cleanup operations: Remove columns that I do not need, rename the fields to more friendlier names, change the data type of the date column to date instead of date/time, etc. Once done, the data is loaded to Power Pivot:

ETL Date data from Azure Marketplace in Power BI

 

The next step happens within Power Pivot upon the loaded data. Here, I set the imported table as a Date table indicating the Date field as the key. I also hide a few fields such as MonthNumber and DayOfWeekNumber, since I do not want them being seen on the client side, and I only want them to be used to sort other fields such as Month and DayOfWeek. I also create a hierarchy using the Year, Month and Date columns:

Model the Date Dimension in Power Pivot

 

And that’s it! You’ve got a Date dimension. Just make sure that any subsequent fact tables that are added have a date field that matches the format of the Date field on your Date dimension. While you can still connect to Azure Marketplace from Power Pivot and pull in the same data, using Power Query gives you more flexibility in massaging your data.

Advertisements

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