In this post, I describe how to quickly build a Date Dimension for your data warehouse by leveraging the Azure Data Marketplace and Excel PowerPivot. Rather than building the date table yourself, you can just download the date table from the Azure Data Marketplace using PowerPivot.
Start by signing up at the Azure Data Marketplace https://datamarket.azure.com/. Windows Data Azure Marketplace is envisioned to be the One-Stop shop for premium data and applications.
Next search for DateStream service.
The DateStream service provides all the dates from Year 1900 to 2100. You probably will not need all of the data, but that can be easily filtered out once you have all of the data downloaded. If you are not interested in extracting ~200 years worth of dates, you can apply filters at the source and extract only what you are interested in. Check out this post: http://www.bp-msbi.com/2011/10/range-queries-with-azure-datamarket-feeds/
The DateStream feed currently consists of a number of date tables:
- BasicCalendarEnglish
- BasicCalendarUS
- BasicCalendarDanish
- BasicCalendarHebrew
- BasicCalendarGerman
- BasicCalendarBulgarian
- ExtendedCalendar
You’ll need to subscribe to this data set. Once subscribed, you will be able to explore this data set right inside the marketplace.
I will download a connection to Excel PowerPivot which will download a ServiceQuery.atomsvc file. You could choose to download just a CSV file if you want.
Doubleclick on the ServiceQuery.atomsvc file and that should open the table import wizard. Provide your account key and proceed importing the data.
After you import the data:
After I downloaded this data, I created a count measure as follows: cnt:=COUNTROWS(DateStream). The table contained 73,414 rows since I didn’t apply any source filters.
I also created an additional column in the table and called it: MonthName2 with the following DAX: =[MonthOfYear]& " - " & [MonthName]. This will help me create a date hierarchy and sort the months in the right order. I switched to the “Diagram View” to create a date hierarchy – simply right click and create a hierarchy and drag and drop the columns that form the hierarchy.
Next, I created a Pivot table in Excel:
You don’t have to create a pivot table if all you are interested is building a Date table in your database. You should already have the data in CSV or Excel that you can import into your relational database.
