Problem
SSAS includes Time, Account, and Dimension Business Intelligence Wizards; what do these functions do and how should they be used?
Solution
The Time, Account, and Dimension Intelligence Wizards allow you to quickly and easily setup specialized dimension and attribute structures based on a set of inputs. The wizards lead you through a set of questions and based on these answers, create a “cookie cutter” set of dimension properties / calculations. They are part of the Business Intelligence Wizards which is defined by SSAS as a group of enhancements that provide an easy way to create a set of dimension or measure objects which otherwise would take a significant time investment to create. In this tip we will cover the three Business Intelligence Wizards.
Account Intelligence Wizard
The Account Intelligence Wizard allows you to quickly assign standard account classifications to a chart of accounts in an accounting or ERP data warehouse. TechNet contains a list of available classifications at: http://technet.microsoft.com/en-us/library/ms189947(v=sql.110).asp; examples of these include Assets, Liabilities, Expense, and Income. Using the Account Intelligence Wizard creates aggregates for these classifications. Let us show you an example for further classification.
We will use the AdventureWorks 2012 Data Warehouse MultiDimensional samples database. Once you have the SQL Server and OLAP databases installed, you will start SQL Server Data Tools (SSDT); SSDT is the new name for Business Intelligence Development Studio in SQL Server 2012. Then as illustrated below, you will open the AdventureWorks DW 2012 MultiDimensional Solution (assuming you have previously opened the solution / project; otherwise you would click open project and locate the project files in your file system).
With the project now open, there are actually two ways to access the Business Intelligence Account Intelligence Wizard. The first method is to open the Solution Explorer, shown below, and then double click on the Cube.
Once the cube is open, the Business Intelligence Wizard button will be the left most button on the cube tab. It is somewhat hidden, and often I will have to remind myself where the button is (expecting it to be on a specific tab and further to the right: not only is it on the main tab, but it appears on all the cube tabs.
Alternately, you can access the Account Intelligence Wizard by opening up a dimension by double clicking on the dimension name in the Solution Explorer.
Guess what, that same Business Intelligence button exists on all the dimension tabs also.
Finally, now that we know where the Business Intelligence Wizards can be accessed, we can now setup an Account Intelligence. Upon clicking the Business Intelligence button, the choose enhancement window appears, as shown below. Note, the number of enhancements listed will depend on where the Business Intelligence button was clicked. For this example, we will select the Define account intelligence and then click next.
The Configure Dimension Attributes window opens as displayed below. Here is where the selected criteria becomes critically important.
For Account Intelligence, four possible values are available to classify the account. Thus, you would need to have planned out what fields in your dimension match up with the Chart of Account, Account Name, Account Number, and Account Type. The Account Type field is the most important of the assignments. However, you should note that all of the types do not need to be used. As seen on the next illustration, we are assigning the account type based on the Color Attribute. In essence, for this example, we are assigning each color to the SSAS Built-In Account Types, such as Asset, Liability, and Income.
Following this example and upon completion of the wizard, the dimension type is now set to Accounts. Using this type, allows certain client reporting tools to utilize this type for summarizing data into the different categories.
The Account Intelligence is now set for all related attributes in the dimension hierarchy.
Dimension Intelligence
The Dimension Intelligence Wizard is actually quite similar to the Account Intelligence Wizard; it provides a way to classify the standard type for a particular dimension. Similarly, these types can often flow through to client applications for easier review and analysis of data. To start the Dimension Intelligence Wizard, open the cube, and then click on the Business Intelligence Wizard button.
Once Dimension Intelligence is chosen, the dimension type must be selected, as show on the next screen print.
The dimension type selection then drives the list of available values which is noted below. In this example, the Product dimension type allows the cube designer to assign each of the attribute types, listed in the second column in the screen print below, to specific fields in the dimension. You must check the include check box in order to be able to assign the attribute type to a specific attribute. Furthermore, you do not have to include all the attribute types in order to complete the wizard.
Finally, the completion window is displayed which shows the changes to be made to the dimension.
As illustrated below, now the dimension will show, in this example, as a Product Type; again, these type details are passed to whatever client is being used to query the cube.
You will need to note that not all clients adhere to all the “suggestions” implemented with the Account and Dimension Intelligence Wizards.
Time Intelligence
The Time Intelligence enhancement provides a way for you to quickly generate a range of calculated measures which provide details such as:
- Month to Date
- Year to Date
- Quarter To Date
- Year over Year Growth and %
- Month over Month Growth and %
- Multiple Month Moving Average
The actual available calculations is based on the date hierarchy that is selected as the basis for the time calculation. For example as shown in the first illustration below, when a year attribute is selected, only year related calculations are available. To the contrary, when a full date hierarchy is selected, see second illustration, a much larger number of available values can be selected.
Ray Barley provided an excellent example in the use of the Time Intelligence functionality in his tip, Built in Time Dimension and Time Intelligence in SQL Server Analysis https://www.mssqltips.com/sqlservertip/1454/built-in-time-dimension-and-time-intelligence-in-sql-server-analysis/. Specifically, his discussion on Time Intelligence starts about half way through the article and although the article is several years old, the instructions, for the most part, still apply. Thus, I will not go over the entire process in detail; however once you start the Business Intelligence Wizard, you select Define time intelligence > Select the applicable calendar hierarchy > Select the related time calculations > Select the Measures to apply the calculations. Finally, the following confirmation screen appears.
The Completing the Wizard screen actually displays the actual MDX of the calculations to be created. This BI Enhancement method is a handy way to quickly create all your time related calculations without having to hand code each one. However as with any “shortcut” a few caveats exist. First, the Wizard calculations generated is an all or nothing process in that you either add all the suggested calculations or you add no calculations. Fortunately, once the calculations are created, you can easily open up the cube in SSDT and navigate to the Calculations tab, as noted in the below illustration.
Within the calculation tab, you can now adjust, add, or delete the calculations generated by the Wizard. A second caveat with the Wizard pertains to the fact that each time you run the Time Intelligence Wizard, the Wizard will generate the entire set of calculations over again, which may cause duplicate calculated measures if you are not careful. Last, as you add new measures to the cube, you will need to either manually add the new time / period related calculated measures by manually copying the existing calculations and adjusting the measures involved or by running the Time Intelligence Wizard again for just added measures. Even with all these caveats, if you have a large number of measures to “periodize”, the Wizard can be a significant time saver.
Conclusion
The Business Intelligence Wizard provides a multitude of enhancements to assist in the development and use of an OLAP cube. Throughout this tip, we discussed three of the most common enhancements, the Time Intelligence Wizard, the Dimension Intelligence Wizard, and the Account Intelligence Wizard. Both the Account Intelligence and Dimension Intelligence Wizards set the type properties on a particular dimension whereas the Time Intelligence Wizards creates a group of calculated measures which can be used for time related comparisons such as year over year growth percentages and YTD summations.
Next Steps
- Define Time Intelligence Calculations using the Business Intelligence Wizards– http://msdn.microsoft.com/en-us/library/ms175440.aspx
- Add Dimension Intelligence to a Dimension–http://msdn.microsoft.com/en-us/library/ms175438.aspx
