When designing a Data Warehouse we usually create a dedicated Time dimension table so that our Business Analysts can analyze the data by Year by Quarter by Month or whatever time period they require. It is our responsibility to create and populate that dimension table in the Data Warehouse. However, Analysis Services can build a time dimension table for you, called a Server time dimension. Let’s take a look.
The Server time dimension is a special dimension table that is built by and stored on the Analysis Server, hence the name. Therefore, a time dimension does not have to be created and maintained in the Data Warehouse itself. What is needed is some sort of date in your Measure Group (read Fact Table) that can be linked to via a relationship in the Dimension Usage tab of the Cube Designer. This technically turns the Fact Table into a Fact/Dimension Table since it now contains dimension information.
So why would you do this? Well, maybe your Data Warehouse was designed by someone else and they decided to not have a dedicated time dimension table. Or maybe your data source was not designed as a data warehouse at all, as in real-time analytics where the data source of the Cube is actually an operational database (don’t try this at home…). Or maybe you know that this neat feature exists and you want to save some storage and trouble. But most of all, you may want to use the Server time dimension for the built-in intelligence of different types of calendars and time periods. All for free.
So how do you do it? Well, assuming you are in the Solution Explorer in an Analysis Services Project you can right-click on the Dimensions folder and select New Dimension… to enter the New Dimension Wizard. When you get to the Select Dimension Type window, there is a radio button for Server time dimension. Then you can select the start and end of the time data (i.e. what years do you want to support) and the time periods that you want to analyze, for instance, Year, Quarter, Month, Week etc. By default you will be able to analyze by Gregorian calendar but you will also be prompted to analyze by Fiscal, Reporting or Manufacturing calendars if you wish. And you get to define what each of these calendars looks like. There is even support for the ISO 8601 calendar if you need it.
A nice new feature in SQL Server 2008 is that you can have the New Dimension Wizard actually generate and populate a Time dimension table in your Data Warehouse (assuming you have the privilege). That’s a powerful function. Then you can adapt the generated Time dimension to whatever you want.
Once the Server time dimension is created you can then define a relationship in the Dimension Usage tab of the Cube Designer. Just click the Add Cube Dimension button and select the new Server time dimension from the list. Then click on the cell that intersects the dimension and the measure group that has the actual date. Usually this is a regular relationship between the Fact table and the new dimension table. Specify the Granularity attribute as Date and select the actual date in the Fact table under “Measure group Columns”. Then deploy the cube and assuming the date ranges match, you can test out all the new date functionality in the Cube Browser.
And then, as they say, you’ll be in business…
Cheers
Brian
Recent Posts:
ReportBuilder 2.0 – focus on the end user…
SSMS 2008: Prevent saving changes that require table re-creation…
