 |
|
Built in Time Dimension and Time Intelligence in SQL Server Analysis
|
By: Ray Barley | Read Comments (14) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More |
|
Problem
We are building an Analysis Services cube and trying to understand how to create a time dimension and take advantage of the built-in time intelligence features that provide many useful calculations such as period-to-date, period-over-period growth, etc. Can you provide a detailed explanation of how it all works?
Solution
Getting the time dimension configured correctly is a necessary first step in order to take advantage of the Time Intelligence features in SSAS which support the various queries that you mention. Just about every data warehouse and cube has a time dimension which represents the hierarchies that you use to filter your queries such as year, month and quarter. The dimension names time, date, calendar, etc. are generally used interchangeably. My naming preference is calendar because that's really what you're modeling in most cases and the name easily supports multiple calendars like fiscal year, reporting year, etc.
Probably the best way to understand how the SSAS built-in features for the time dimension work is to walk through an example. When you create an SSAS cube you have the option of building it from an existing data source or without using a data source. In the latter case the SSAS Cube Wizard can generate the relational schema that you need to populate the cube. This option comes in handy when you're trying to design your cube as the to-be solution; i.e. this is what I want versus creating a cube from an existing data source; i.e. this is what I have and let's use it to create a cube. Another advantage of creating the cube without a data source is that the important properties for configuring the time dimension get set for you automatically.
Let's walk through the steps to build a cube that uses the built-in SSAS features for the time dimension and associated time intelligence. The starting point is to create an SSAS project in Business Intelligence Developer Studio (BIDS) or Visual Studio.
After creating the SSAS project, right click on Cubes in the Solution Explorer and select New Cube to launch the Cube Wizard. On the Select Build Method dialog select Build the cube without using a data source. This allows us to create a cube and generate the relational schema that will be used to populate the cube.
Define a couple of measures. Measures are typically the dollar amounts that are monitored to determine how your business is performing. When the relational schema is generated, a table is created for each Measure Group; a column in the table is created for each Measure Name.
The Define New Dimensions dialog allows us to define dimensions for the cube. Dimensions are the attributes that we use to slice and dice the data. For our example we'll just create a time dimension. Check the Time type and enter the name Calendar. When the relational schema is generated, a table called Calendar will be created.
After selecting a Time-type dimension, you define the date range and the time periods. When the relational schema is generated, the time dimension will be populated automatically based on the selections below. The time periods allow us to perform queries that filter the measures by year, quarter, month, week or day. You also have the option to define a Fiscal Calendar, Reporting Calendar, etc. (this step is skipped in our example).
The Define Dimension Usage dialog allows us to link the measures to the respective dimensions. When the relational schema is generated, there will be a foreign key in each measure group table enabling a join to the dimension table.
The final dialog in the Cube Wizard summarizes the selections made and allows you to name the cube. Click the Generate schema now checkbox; this generates the relational schema that will be used to populate the cube.
When you check Generate schema now the Schema Generation Wizard is launched to guide you through the steps to generate the relational schema. The Specify Target dialog is used to identify the database where the relational schema will be generated. The Data source identifies the database; the Data source view is used to specify the tables, views, etc. from one of more data sources that will be used to populate the cube. The cube is always built from the data source view. Prior to this step you may want to create a new database to use as the data source.
For the remainder of the steps in the Schema Generation Wizard just accept the defaults. After the schema is generated, the cube needs to be processed. Right click on the cube in the Solution Explorer and select Process.
Let's review a few key points about the time dimension that was automatically created for us. Double click Calendar.dim under Dimensions in the Solution Explorer to review the dimension structure which shows the attributes and hierarchies. Two hierarchies have been created with the levels as shown. These hierarchies allow us to summarize and drill through the measures in the cube.
Click on Calendar (first entry under Attributes above) to display the dimension properties. The Type property (in the Basic group in the Properties window) has the value of Time which designates it as a Time dimension. If you click on the dropdown for the Type attribute, you will see that there are many other types of dimensions that have some sort of built-in support in SSAS.
For an individual attribute, there are several properties that must be set correctly as well. As an example click on the Year attribute to view its pertinent properties. The Type property must be set to Years. The Name column specifies the column in the generated relational schema that is used as the display name for the attribute ; note that it is a WChar type which is a string. The ValueColumn represents the underlying value of the attribute and it is a Date type. You can browse the other attributes and note their property settings. A key advantage to generating the time dimension in the Cube Wizard is that all of the important properties get set correctly for you automatically.
Click on the Browser tab for Calendar.dim to view the actual data in the dimension. This step shows the hierarchies in the time dimension, based on the time periods selected in the Cube Wizard. The hierarchy shown below allows us to query at the year, quarter, and month levels.
At this point we have created a cube with a properly configured time dimension. The next step is to add the built-in Time Intelligence which allows us to specify the types of time slicing we want to use in our queries. Click on the MSSQLTips cube in Solution Explorer then click Cube on the top-level menu and Add Business Intelligence to launch the Business Intelligence Wizard. Select Define time intelligence from the list of available enhancements. The prerequisites for adding Time Intelligence are a time dimension with one or more hierarchies that is linked to one or more measures.
Click on the Available time calculations that you want to add to the cube. A brief description of each is displayed as you click or scroll through the list.
Select one or more measures to implement the calculations you selected in the previous dialog. This is the final step in the wizard.
The Business Intelligence Wizard creates a number of calculated members in the cube which you can view on the Calculations tab of the cube. The wizard creates an additional hierarchy in the Calendar dimension. The hierarchy name is derived from the hierarchy that you pick in the Choose Target Hierarchy and Calculations step of the wizard. In this example the hierarchy name is Year - Quarter - Month - Date Calendar Calculations. Right click the MSSQLTips cube in the Solution Explorer and select Process to update the cube with the Time Intelligence calculations.
Our final step is to browse the cube and take advantage of the Time Intelligence to perform some queries. Double click the MSSQLTips cube in the Solution Explorer to display the cube designer then click the Browser tab. The Browser tab user interface is essentially the pivot table that you are familiar with from Excel or the Office Web Components. Note the instructions on where to drop column fields to browse the cube.
Perform the following steps to see an example of the Time Intelligence calculations:
- Drag and drop Sales and SalesCost from Measures onto the Drop Totals or Detail Fields here area
- Drag and drop Year from the Calendar dimension onto the Drop Row Fields here area
- Drag and drop Year - Quarter - Month - Date Calendar Calculations from the Calendar dimension onto the Drop Column Fields here area
- Click Year - Quarter - Month - Date Calendar Calculations in the Drop Column Fields here area and click Current Calendar, Year over Year Growth % and Year Over Year Growth
- Click Year on the Drop Row Fields here area and click the years 2006, 2007 and 2008
You should now have a pivot table that looks like this:
The above pivot table shows Sales and SalesCost for the years 2006 through 2008, as well as the Year Over Year Growth % and Year Over Year Growth (in dollars). The sample data used to populate the cube was a simple query that extracts data from the sample AdventureWorksDW database that comes with SQL Server 2005.
Next Steps
- Download a copy of the sample SSAS project here to experiment with the built-in Time Intelligence features in SSAS. Note that the project assumes you have an instance of SQL Server and Analysis Services running locally. Unzip the files into the folder "C:\MSSQLTips" to minimize changes to get the sample to run and follow the instructions in the README.txt file.
- While the Time Intelligence Wizard provides many calculation options, you may come up with others and you will have to implement these yourself. You can follow the pattern of the calculations created by the built-in Time Intelligence to do this.
- Before you run the Time Intelligence Wizard make sure that you have all of your measures defined in the cube. Each time you run the wizard it creates a whole new hierarchy in the time dimension for the calculations. You can manually edit the generated calculations from the Calculations tab in the cube designer if necessary.
Last Update: 3/13/2008
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Monday, June 23, 2008 - 10:30:15 AM - rachaeland | Read The Tip |
|
I tried this out and it works fine when I browse using the Business Intelligence Development Studio or Management Studio but the percent growth, etc calcuations don't show up when I try to access the cube using Excel 2007. How can I make them show up? |
|
| Monday, June 23, 2008 - 11:05:18 AM - raybarley | Read The Tip |
|
Check your PivotTable Options and make sure Show calculated members from OLAP server is checked; by default it is not checked. Once checked you should see the calculated members. |
|
| Monday, June 23, 2008 - 1:53:50 PM - rachaeland | Read The Tip |
|
Thanks. I can now bring them in but I have another problem. It brings in all the different calcuations but I only want to show to the quarter over quarter growth %. What I really want to do is pick two consecutive quarters and be able to show several measures for the two quarters and then shower the quarter over quarter growth % for the two quarters (only show it once). Any ideas of how I can do this? |
|
| Monday, June 23, 2008 - 2:02:50 PM - raybarley | Read The Tip |
|
There is some sort of limitation with Excel which doesn't allow this. You can take a look at this blog entry and see if this work around would be acceptable: http://sqlblog.com/blogs/marco_russo/archive/2007/09/02/datetool-dimension-an-alternative-time-intelligence-implementation.aspx If you're considering Performance Point, you could use the Analytic Grid report. It doesn't have the limitation that you have found in Excel. |
|
| Monday, June 30, 2008 - 4:00:05 PM - rachaeland | Read The Tip |
|
This worked well. Thanks. However, I have an additional need using Xcel 2007 to do the following shown below for the most recent quarter with data (I have a column on my date dimension that can identify this if necessary) without having to add the date dimension. I would like to add calcuations to the cube since this is information that is used by alot of production reports and reports that will be built by end-users using Xcel 2007. Is this possible? I'm really new to this so excuse my ignorance. Also, does anyone know of a good book about MDX and Analysis Services that would have examples. I would also like to find a user group in the Portland Oregon area. Measure Curr Value Prior Qtr Value Prior Qtr % Chg Clm Cnt Clm Pd Avg Pd etc. |
|
| Tuesday, July 01, 2008 - 3:56:02 AM - raybarley | Read The Tip |
|
Portland SQL Server User Group: http://www.pdxvbug.com/pdxuser.asp I think MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbaseis a good book http://www.amazon.com/MDX-Solutions-Microsoft-Analysis-Services/dp/0471748080
|
|
| Tuesday, July 01, 2008 - 8:12:29 AM - rachaeland | Read The Tip |
|
Thanks for the info. Any suggestions on the problem I'm trying to solve? |
|
| Saturday, July 19, 2008 - 5:40:12 AM - Riaz | Read The Tip |
|
I have tried this many times but still not working, I just tell you my situation then please suggest me what to do. I have Generated Cube with OLTP Data Source already in that cube i have one Fact Table ( Billing ) and two Dimensional Table ( Customer And Office Location ) now i want to apply built in time intelligence in that cube. I have tried your solution, it is creating time dimension table in OLTP database and creating one Calendar Dimension as well but i m unable to link it with Billing Fact Table and unable to Define Time Intelligence in my CUBE.
|
|
| Saturday, July 19, 2008 - 4:14:48 PM - raybarley | Read The Tip |
|
Did you add the time dimension to the Data Source View then add the foreign key relationships between the fact tables and time dimension? Then you add the time dimension to the cube. The article was written based on creating a cube without a data source. In order to add time intelligence to an existing cube, you open the project, select the cube, click the Cube menu, then select Add Business Intelligence which will launch the Business Intelligence Wizard. Select Define Time Intelligence and complete the wizard. It would probably be worth following the steps in the article and setting up the time intelligence in a test cube to get familiar with all of the settings.
|
|
| Saturday, July 19, 2008 - 11:09:56 PM - Riaz | Read The Tip |
|
I have added a Dimension which type is TIME, when I am trying it to add in my data source view, I am unable to find the relationship key, because in my Fact Table BillDate format is dd/mm/yy hh:mm:ss and in the Calendar Dimension it is only dd/mm/yyyy. How can I create the KEY is Fact Table. Time Type Dimension is not in the CUBE thats why this Time Intelligence Wizard is not working here.
|
|
| Sunday, July 20, 2008 - 5:25:15 AM - raybarley | Read The Tip |
|
You could add a Named Calculation to the fact table in the Data Source View to truncate the time portion of the date and expose that as a new column that you can use as a foreigh key to the time dimension. If your fact table is in SQL Server you could use an expression like this for the named calculation: convert(datetime, convert(varchar(10), <your datetime column goes here>, 120))
|
|
| Tuesday, September 30, 2008 - 10:13:30 AM - JohnCHill | Read The Tip |
|
I guess I am misssing something because, how can your table be populated if you created it without a datasource? When I follow the directions, I get the Calendar that is populated with dates but the fact table is empty. Am I missing a step? Thank you. John |
|
| Tuesday, September 30, 2008 - 2:24:17 PM - raybarley | Read The Tip |
| The tip doesn't explicitly say that you have to populate the schema that is generated but as you have pointed out, you do. There is a script file in the download which will copy some data out of the AdventureWorksDW database into the mssqltips_dw database. The script file is populate_fact_sales.sql. | |
| Wednesday, October 01, 2008 - 4:44:38 AM - JohnCHill | Read The Tip |
|
Great, thank you!
John |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |