Over the years, many of us star schema architects have come up with our own standard for a table structure to house data for our date dimension. In addition, we have written countless scripts to populate data into our date dimension table. Ensuring we have the right attributes (columns) in our date table allows us to create various hierarchies (e.g. Calendar vs.. Fiscal Year, Year—>Quarter—>Month vs. Year—> Month etc.).
More often than not, if I am working with Excel (or PowerPivot), I pull down my date data (already pre-formatted in a relevant structure) from the Azure Data Market Place. It’s free, it’s easy, and once you start browsing around the data market on Azure, you are bound to find other data sets relevant to your analysis (sometimes free, sometimes not).
Importing Data from Azure Marketplace
1) When using the PowerPivot add-in in Excel 2013, one option for acquiring data is “From Data Service” in the “Get External Data” section of the ribbon. Click.
2) A window will open, and you will be brought to the catalog of data sets in the Azure Data Market Place.
3) In search bar in the upper right, type in “DateStream”. The results will yield the free data set provided by Boyan Penev. Click on “Subscribe”.
4) After clicking “Subscribe”, you will be prompted for your Windows Live ID and Password. Sign in.
5) Once you are signed in, you are provided some options for the data set. I selected the BasicCalendarUS, but as shown below, you could bring in date data in other languages. After selecting your data options, click “Select Query” in the bottom right.
6) The table import wizard will open, and you can optionally test your connection to Azure. Click Next.
7) Confirm the data was successfully imported.
Using the imported date dimension data
1) Once I have brought the date dimension data in from the Azure Marketplace, I need to think about how I am going to relate it to the other data in my PowerPivot model (i.e. my Fact records). In this example, I have a fact table called ActivityDetail that has a “CreatedDateTime” column which has a data type of datetime. I use a simple FORMAT function in DAX to convert the date to an integer (which I will use to join to the date dimension data I just pulled down from Azure).
2) I navigate to the “Diagram View” on the right side of the ribbon, and create a relationship between the DateKey column in my ActivityDetail table that I created in the previous step, with the DateInt column that is part of my BaseCalendarUS data that I imported from Azure.
3) To take full advantage of a date dimension as part of my model, I create a simple 2 level hierarchy that will allow me to navigation from Year to Month. You can accomplish this by right clicking a column (in this case YearKey), and choosing “Create Hierarchy”. The year becomes the default parent member of the hierarchy, and I simply drag the MonthName column below the year to create the hierarchy.
There is a wealth of data available either for free or via a paid subscription on the Azure Data Marketplace. Leveraging this data is quick, easy and enriches your analysis.