One thing almost every Data Warehouse has in common is that they have a Time dimension. There’s almost always a need to view aggregated data by day, month, quarter, year, etc. In addition to the standards you might also have a fiscal calendar that’s slightly different than the standard calendar. Typically a decision is made to generate the Time dimension starting at point in time the business feels the data will be relevant to the warehouse, and then populated out into a point in time that’s in the distant future (I usually do about 10 years for starters.
Now you could spend a lot of time writing the sql script that is going to create and populate your time dimension. However, you can also use SQL Server Analysis Services (SSAS) to do the bulk of the work for you (even if you don’t actually plan on using SSAS…..or if SSAS is a little intimidating to you).
This walkthrough will guide you through using one of the built in wizards to generate and populate a Time dimension inside your SQL Server database, with very little effort. In this walkthrough I’ll be using SQL Server 2012, but the steps are very similar in SQL Server 2008. So let’s get to it:
1. First things first, we’ll need to create a new Analysis Services project in SQL Server Data Tools (or BIDS in 2008). If you’re using 2012, make sure to select the “Multidimensional and Data Mining” project, and not the “Tabular Project”. Name it whatever you want and click ok.
2. Once our project is created you should see several folders in your “Solution Explorer” (right hand side of your screen most likely). Right click on the Dimensions folder as shown below and then left click on “New Dimension…”.
3. Make sure to select “Generate a time table in the data source” and then click next.
4. On this screen we get to choose the start and end date ranges for the dates we want to populate our time dimension with. In addition we get to choose what additional attributes we want to include in our Time dimension such as Quarter, Hafl Year, etc. Make your selections, and then click next.
5. If you have Fiscal or reporting calendars that are offset from the standard calendar, you can choose to include additional attributes with those offset values in your calendar. In this example we’re not going to use any of these, but it’s important you know they’re available. Click next.
6. On this screen I’ve renamed the dimension from Time to DimTime just because I’m in the habit of always starting my dimension table names with Dim. Feel free to rename your dimension whatever you want here. YOU MUST MAKE SURE TO CHECK “Generate schema now” BEFORE YOU CLICK FINISH! That’s why I highlighted it in yellow all pretty like.
7. Now we’ve kicked off a new wizard (by selecting “generate schema now” in our previous screen and selecting finish) to generate our schema. Click next on the intro screen.
8. Now we need to create a data source that points to the database that we want our dimension table created in. Click new, and walk through the data source creation wizard. It’s exactly like the data source wizard that you find in SSRS, SSIS, or SSAS. Once you’ve created your data source, select it in the “Data source” dropdown and click next.
9. You can tell the wizard to create or not create various constraints on the table when it gets created. For this example we’re going to leave all options checked (default). Just make sure the “Populate time table(s)” dropdown has “Populate” selected and then click next.
10. This screen contains several options that should all be pretty self-explanatory. We’re going to leave them all as their default values and click next.
11. Aww yeaaaah! The finish screen! Your screen should look something similar to mine, just review it and as long as nothing looks out of place go ahead and click finish.
12. The schema generation process might take a few minutes. I’d guess the wider the range of the start and end dates you picked at the beginning of the wizard, the longer it will take. Ultimately you want to see “Generation Completed Successfully” down at the bottom left in green.
13. Now if I open up a query window in SQL Server Management Studio (SSMS) to the same database that I pointed my data source to during the walkthrough, I see that I now have a table named DimTime (though yours will be named whatever you named it…..it will be named “Time” if you didn’t specify anything). Running a quick select * statement shows me that it is indeed populated.
Well, that was pretty easy….Way more so than writing our own scripts to generate and populate this table. Well, I hope everyone found this useful. I also recently enabled comments on the blog (so feel free to leave a comment if this did or didn’t work out for you.)