Dimension Intelligence using the Business Intelligence Wizard

Analysis Services 2005 provides you with a Business Intelligence wizard that allows you to enhance your dimensions with appropriate calculations added to your cube. Three specific enhancements are discussed in this section: Account intelligence, Time intelligence, and Dimension intelligence. Analysis Services 2005 supports the Account dimension natively in the engine. In this way, Analysis Services is able to aggregate data for members in the Account dimension based on the account names. The Time intelligence enhancement creates calculations for common business questions, such as year-over-year or quarter-over-quarter revenue. Such calculations are typically created as session calculated measures by various client tools in Analysis Services 2000. Analysis Services 2005 enables support of these calculations natively, so that all client tools and custom tools can take advantage of these calculations. The Dimension intelligence enhancement allows you to map your dimension to commonly used dimension types, so that client tools can discover and present them to customers in a unique way that is easily interpreted.

Account Intelligence

In Chapter 6 you learned about measures and aggregation functions that are specified for measures such as Sum, Count and Distinct Count. Analysis Services 2005 supports calculations specifically for the Account dimension so that an appropriate aggregation function is applied based on account names. In fact, there is a special type of aggregation function called ByAccount. Based on the type of account Analysis Services can apply the right aggregation function. The account intelligence wizard allows you to qualify dimension as an Account dimension and then map type of accounts to well known account types. Based on these mappings the wizard informs you of the type of aggregation function that will be applied for the accounts. If your Account uses a specify account type and aggregation function then you will be able to specify that at the database level. Follow the steps below to map the dimension with name Account as a dimension of type Account and specify necessary attributes so that appropriate aggregation functions are applied to account types.

1. Double click on the dimension with name Account to open it in Dimension Designer.

2. Launch the Dimension Intelligence Wizard from the menu item Dimension→Add Business Intelligence or by clicking on the first icon in the Dimension Designer. If you see the welcome screen click the Next button.

3. In the Choose Enhancement page select Define account intelligence as shown in Figure 8-25 and click Next.

   
   Figure 8-25

4. In the Configure Dimension Attributes page, Figure 8-26, you need to define the mapping between the attributes in the current dimension named Account to the standard attributes of the Account dimension. Map the Chart of Accounts to the parent-child hierarchy Accounts, Account Name to the key attribute Dim Account that contains the name of the Accounts, Account Number to the Account Code Alternate Key ( Account Code Alternate Key uniquely identifies a member in the account) and the Account Type to the attribute Account Type and press Next.

   
   Figure 8-26

   The Account Type identifies the type of an account member and is used by Analysis Services to use the appropriate aggregation function for measures that have the AggregationFunction property set to ByAccount.

5. In the Define Account Intelligence page the account types from the source table are mapped to the built-in account types in Analysis Services 2005. If the name of account types in the source table do not directly map to the built-in account types you would need to map them correctly in this page. In this example all the account types are mapped correctly and hence press Next.

6. The final page of the Business Intelligence Wizard shows the various account types along with the aggregation functions associated with accounts. Please review the aggregation functions and click Finish (see Figure 8-27 and Figure 8-28).

Figure 8-27

Figure 8-28

You have now successfully enhanced your Account dimension. If you look at the properties for the Account dimension you will see the Account dimension property Type is set to Accounts. The aggregation functions for various account types are pre-defined in Analysis Services. However Analysis Services allows you the flexibility to add additional account types as well as to make changes to the AggregationFunction for the account types to suit your business needs. To make modifications right click on the project name AnalysisServicesTutorial in solution explorer and select Edit Database. You will see a new designer to make changes at the database level as shown in Figure 8-29.

Figure 8-29

As mentioned earlier the enhancement made to the Account dimension will only be applicable for measures that aggregation function defined as ByAccount. Open the Adventure Works DW cube. In the Cube Structure pane select the measure Amount in the Fact Finance measure group and set the Aggregation Function for this measure to ByAccount as shown in Figure 8-30. Deploy your changes to the Analysis Services instance.

Figure 8-30

In the Cube Browser you can now browse the measure Amount along with the Account dimension members, and you can notice that the right aggregation functions are used to aggregate the measure values based on the account type. However you will notice that the member Statistical Accounts is not visible in the OWC browser. This is due to the fact that accounts of type Statistical should have the value rolled up based on the aggregation function Sum; however all the children of member Statistical Accounts have unary operator column set to ~ which means those values are not rolled up to the parent. In order to view the Statistical Accounts member and its children you need to define a value for member. Switch to the Calculations tab in the Cube Designer. Right click on the Script Organizer pane and select New Script Command. In the script command window enter the following statement to set the Amount for the Account member Statistical Account to be NA. Save the changes and deploy them to your Analysis Services instance.

     ( [Account].[Accounts].&[95], [Measures].[Amount] ) = "NA" 

If you reconnect to Analysis Services instance in the Cube Browser you will the Statistical Account member and its children as shown in Figure 8-31. You can see the Amount for Statistical Accounts is set to NA even though the aggregation function is Sum. This is because of the fact that Analysis Services applies Unary operation evaluation on a cell after the regular rollup across the dimension (in this specific case ByAccount aggregation).

Figure 8-31

Since the aggregation functions specific to an Account Type are done natively with Analysis Services rather than calculations in scripts you should expect better performance while querying appropriate cells.

Time Intelligence

There are certain calculations that are frequently used in business such as calculating Year to Date and Year over Year Growth for measures such as Sales. These calculations are related to the Time dimension and can be created within the scope of the query or session as necessary. Several client tools have utilized query or session scope calculations to create such calculations while using Analysis Services 2000. Analysis Services 2005 provides a wizard to enhance your cube to add such calculations. The Time Intelligence enhancement is part of the cube enhancement since calculations such as Year to Date and Year over Year Growth are all calculated in context with measures. Since this enhancement add appropriate calculations to the cube as well as attributes in the Time dimension, it is included in this chapter. Follow the steps below to define Time Intelligence enhancement.

1. Open the cube Adventure Works DW in Cube Designer.

2. Launch the Business Intelligence Wizard from the menu Cube→Add Business Intelligence or by clicking on the Add Business Intelligence icon (first icon in the Cube Structure tab). If you see the Welcome screen press next.

3. In the Choose Enhancement page select Define time intelligence as shown in Figure 8-32.

   
   Figure 8-32

4. In the Choose Target Hierarchy and Calculation page you need to select the calculations as well as hierarchy in Time dimension that is expected to use the calculations. In the drop down list box for Use the following hierarchy to analyze time calculations you will see the four Time dimensions Dim Time, Order Date, Delivery Date, and Ship Date. All these cube dimensions are role playing dimensions of the database dimension Dim Time. Select the multi-level hierarchy in the cube dimension Order Date as shown in Figure 8-33. Select the calculations Year to Date, Year Over Year Growth, and Year Over Year Growth % and press Next.

   
   Figure 8-33

5. In the Define Scope of Calculations you need to select the measures for which you need the time calculations to be applied. Select the measure Sales Amount as shown in Figure 8-34.

   
   Figure 8-34

6. The final page of the wizard shows the changes to the database Time dimension Dim Time as well as the calculations as shown in Figure 8-35. You can see that the wizard adds a column in the Dim Time table within the DSV and adds that as an attribute in the Dim Time database dimension in addition to the calculations that will be added to the cube's script.

   
   Figure 8-35

Switch to the Calculations tab of the cube and explore the calculations created by Business Intelligence Wizard. Also look at the Named Calculation added to the Dim Time table in the DSV. These will help you to understand the calculations you need to create if you didn't have the Time Intelligence enhancement. The Business Intelligence Wizard makes it easy for data warehouse designers to add the enhancements related to Time without defining and verifying the calculations which can take a considerable amount of time.

You have successfully enhanced your cube and Time dimension to analyze growth on Internet Sales (Sales Amount measure). To use the calculations for analysis deploy the enhancements to your Analysis Services instance and switch to the Cube browser. Drag and drop the measure Sales Amount to the data area, CalendarYear - CalendarSemester - CalendarQuarter - EnglishMonthName on Rows and CalendarYear - CalendarSemester - CalendarQuarter - EnglishMonthName Order Date Calculations on Columns. You will be able to see the Year to Date, Year over Year Growth, and Year over Year Growth % as shown in Figure 8-36.

Figure 8-36

Dimension Intelligence

Analysis Services 2005 provides you a way to define your dimensions to map to standard dimension types such as Customer, Organization, Currency. These mappings can help client tools which might have customized views of presenting such dimensions to end users. To map your dimensions to standard dimension types follow the steps below:

1. Open the Dim Organization dimension in Dimension Designer.

2. Launch the Business Intelligence Wizard by clicking on the icon Add Business Intelligence or from the menu Dimension→Add Business Intelligence. If you see the welcome screen press Next.

3. In the Choose Enhancement page select Define dimension intelligence (as shown in Figure 8-37) and press Next.

   
   Figure 8-37

4. In the Define Dimension Intelligence page enable the default Attribute Types Company and Ownership Percentage and map them to corresponding attributes in the Dim Organization dimension Organizations and Percentage of Ownership (see Figure 8-38) and press Next.

   
   Figure 8-38

5. The final page of the wizard shows the definitions specified in the previous page. It shows that Dim Organization is of standard dimension type Organization. This dimension contains several companies which are represented by the attribute Organizations and the PercentOwnership is determined by the attribute Percentage Of Ownership (see Figure 8-39).

   
   Figure 8-39

You have successfully defined dimension intelligence for the Dim Organization dimension. You can see that the property Type for the dimension and the attributes selected in the dimension intelligence enhance has been set appropriately. Apply the Dimension Intelligence Enhancement to the remaining dimensions. You will be able to view the effect of these only through client tools that utilize the property type.