Previously, I blogged about Server time dimensions. This is where a time dimension table is generated automatically on the Analysis Server instead of having to be created manually in the Data Warehouse. Well, what if you want the automatic generation but you still want the table to be part of the Data Warehouse? Enter SQL Server 2008…
In SQL Server Business Intelligence Development Studio 2008 (really a subset of Visual Studio 2008), you can launch the New Dimension Wizard by right-clicking the Dimensions folder in the Solution Explorer for an SSAS project. The Server time dimension option is now called “Generate a time table on the server”. The new option is called “Generate a time table in the data source”. The description below states “You must have permission to create objects in the underlying data source”. This means that we can, retrospectively, create a time dimension table in our Data Warehouse, populate it and have it support multiple calendars automatically. Very cool.
The pop-up windows are very similar to those that appear when creating the Server time dimension except the table appears in the Data Warehouse instead, fully populated. You are asked for the date range (e.g. 01/01/2001 through 12/31/2008) and the time periods (e.g. Year, Quarter, Month, Week) that you wish to provide for.
Next you are prompted for the types of Calendars you wish to support. The regular calendar is selected by default but you can add Fiscal, Reporting, Manufacturing and ISO calendars just like in the Server time dimension. The Dimension Designer is then opened to show off your new dimension. The power of this functionality is shown in the new Attribute Relationships tab. This shows graphically the relationships between the attributes and reveals that the Reporting and Manufacturing weeks divide exactly into their corresponding months whereas the Fiscal and Regular weeks only divide exactly into their years. This is expected and just how the different calendars work.
There is a link to “generate data source view” and when this is clicked the new time dimension is generated and populated. Once this is completed, SSMS shows that, sure enough, a new table has appeared in your data source with data populated for every day since January 1st 2001. This feature saves us the trouble of carefully setting up a time dimension and inserting valid data for each date permutation and calendar type.
The next step is to add the new dimension into the Cube by using the Dimension Usage tab and the Add Cube Dimension button. When the new dimension is selected the tool is intelligent enough to ask if you want the new table to be added into your existing Data Source View that was used to generate the cube. Again, this is retrospective but helps sync up the data source, the data source view and ultimately the cube. You can then create a regular relationship (one-to-many) between the new dimension table and the Fact-Dimension table that contains the datetime column, such as OrderDate. After re-deploying the cube, you can test out the cube and it should work perfectly using all the new calendars. Awesome.
So, we have emulated a Server time dimension but with a permanent time dimension table being created in our Data Warehouse. This is very useful, especially if we want to use the Data Warehouse separately for other applications that require the time intelligence. And it eliminates the detailed mechanics of actually creating one manually.
OK, let’s pay out those developer bonuses. Thanks Microsoft!
Cheers
Brian
Recent Posts:
ReportBuilder 2.0 – focus on the end user…
SSMS 2008: Prevent saving changes that require table re-creation…
