Adding Intelligence to the Cube

Similar to adding intelligence to Dimensions that you learned in Chapter 8 you can add intelligence to the Cube. Figure 9-48 shows the various enhancements that can be done to a Cube using the Business Intelligence Wizard. You have learned most of these enhancements in earlier chapters. In this chapter you will learn the enhancements to define and understand semi-additive behavior as well as defining currency conversion whenever fact data needs to be converted to appropriate local currency.

Figure 9-48

Semi-Additive Measures

Semi-additive measures are measures whose data is not aggregated as sum or count to over the various levels on a hierarchy. The semi-additive aggregate functions for measures are ByAccount, AverageOfChildren, FirstChild, LastChild, FirstNonEmpty, LastNonEmpty and None. For example, assume you have a Time hierarchy and the measure Sales value to be rolled up average of the sales of it's children. Assume the levels in the Time hierarchy are Year, Half Year, Quarters, Months and Date. If you have a member Quarter 1 of the year 2004 whose children are months July, August and September then the value for Quarter 1 will be average of of the sales values for the three months.

Using the Business Intelligence Wizard you can change the behavior of the Aggregation Function of various measures in the cube. Launch the Business Intelligence Wizard, select Define semi-additive behavior in the Choose Enhancement page and click Next. In the Define Semi-additive Behavior page (shown in Figure 9-49) you have three options. The default option is the detection of a dimension of type account which contains semi-additive members. You learned in Chapter 8 to define semi-additive behavior for various Account types. If this selection is made then the Business Intelligence Wizard sets the aggregate function to ByAccount for all the measures of the measure group that has relationship defined with the dimension of type account. In the sample Adventure Works DW database there is only one measure Amount that has the ByAccount aggregation function. The first option turns off all the semi-additive behavior for all the measures that have the AggregateFunction property set to a semi-additive behavior. When this option is selected any measure that has a semi-additive aggregation function will be set to the Sum aggregation function. The last option "Define semiaddtive behavior for individual measures" allows you to change the Aggregation Function for each measure. Once you make the selections in this Define Semiadditive Behavior page press Next. The final page of the Business Intelligence Wizard shows the new Aggregate Function for the measures that will be affected. You can review the changes to be applied to the measures and click Finish. BIDS then changes the AggregateFunction property for the measures.

Figure 9-49

You can verify the AggregateFunction property of the measures that are expected to be changed by the selections in the semi-additive behavior enhancement through the Business Intelligence Wizard. You learned the semi-additive behavior of the ByAccount AggregateFunction in Chapter 8. In order to see the results of LastNonEmpty and AverageOfChildrence semi-additive aggregate functions deploy the sample Adventure Works DW database and browse the measures in the Exchange Rate in the Cube Browser along with the Date.Fiscal hierarchy and DestinationCurrency.DestinationCurrency hierarchy as shown in Figure 9-50. You can see the value for the members in the Date.Fiscal hierarchy are calculated based on the aggregate functions LastNonEmpty (for End of Day Rate measure) and AverageOfChildren (for AverageRate measure) applied to their children.

Figure 9-50

Currency Conversion

If your organization does business in more than one country, then you might need to deal with converting currencies between countries. Analysts and managers may want to analyze transactions in the currency used for the transaction (also known as the local currency), while corporate management may want to convert all transactions to a single currency to get a complete view of all transactions globally. This scenario can be thought of as a many-to-one currency conversion. Or you might load data in the data warehouse in one currency, but need to report financial results in different currencies. This scenario describes a one-to-many currency conversion. Yet another possibility is a combination of these two scenarios in which transactions data is in the local currency and needs to be reported in more than one different currency — a many-to-many currency conversion. Fortunately, Analysis Services 2005 provides a wizard to make it easy for you to add currency conversions to a cube for any of the three scenarios described above.

Before you can use the wizard, however, you need to build a currency dimension and an exchange rate measure group in your cube. These database objects are already in the Adventure Works cube that you've been using throughout this chapter, which gives you an opportunity to take a look at the proper structure before you have to build your own.

To review database objects used for currency conversion, follow these steps:

1. Using BIDS, open the Adventure Works cube in the Enterprise version of the Adventure Works DW sample project (located at C:\Program Files\Microsoft SQL Server\90\Tools\Samples\ AdventureWorks Analysis Services Project\Enterprise). Double-click the Adventure Works cube in Solution Explorer to open the cube designer.

2. Click the Exchange Rates measure group in the Measures page, then take a look at the Type property for the measure group, shown in Figure 9-51. You must set the Type property value to Exchange Rates so that Analysis Services can correctly specify this measure group in the currency conversion calculations added to the MDX script when you use the wizard. The Exchange Rates measure group is based on a fact table that contains daily average and end of day exchange rates by day and by currency.

   
   Figure 9-51

3. Open the Exchange Rates measure group; click on the Average Rate measure, and look at its AggregateFunction property. As you learned earlier in this chapter, Average Rate is a semi-additive measure that cannot be summed to get value for the month, quarter, or year level. Instead the aggregate function AverageOfChildren is used.

4. Now double-click the Source Currency dimension in Solution Explorer. In the Properties window, you can see the Type property for this dimension is set to Currency. Click the Source Currency attribute in the Attributes pane. You can see its Type property is CurrencySource. Lastly, click the Source Currency Code attribute and verify its Type property is CurrencyIsoCode. You can make sure you get the property settings right by selecting Currency as the Dimension Type when using the Dimension Wizard to create the dimension. The wizard will prompt you for the column in your table containing the currency's ISO code and for the key attribute. Alternately you can change the properties after the dimension is created.

5. Now you're ready to start the Business Intelligence Wizard. The wizard will create a second currency dimension, one used for reporting the converted currencies, for you as well as updating the MDX script with calculations that ensure the currency conversion is correctly applied to affected measures. Right-click the Adventure Works cube in Solution Explorer, click Add Business Intelligence, click Next, click Define Currency Conversion, and then click Next.

6. In the Set Currency Conversions Options dialog the wizard looks for measure groups of type ExchangeRate and pre-selects that measure group. If such a measure group does not exist then it selects the first measure group. Click Exchange Rates in the "Select the measure group that contains exchange rates" list. In the "Specify the pivot currency" list box, click USD, and then click OK. Lastly, click the N USD Per 1 ARS radio button, as shown in Figure 9-52. The FactCurrencyRate table, on which the Exchange Rate measure group is based, has rates to convert one unit of local currency (such as one Australian dollar) into a standard currency (US dollars in this case), which is called a pivot currency. If the table contained rates to convert 1 unit of the pivot currency into the local currency, you would select the N ARS Per 1 USD radio button. The drop down list contains a pre-defined list of currencies to help you make the right selection. Click Next to continue.

   
   Figure 9-52

7. In the Select Members page select the check boxes next to Internet Sales Amount and Reseller Sales Amount, as shown in Figure 9-53. This page of the wizard identifies the members that will be converted. Another approach to currency conversion involves converting specific members in an attribute hierarchy of an Account dimension, such as certain expense accounts, or certain account types, such as all revenue accounts. You can select the measure from the measure group selected in the previous page of the dialog which is to be used for currency conversion. The Average Rate measure is selected by default. These options are useful when your cube is dedicated to financial data for balance sheets and profit and loss statements.

   
   Figure 9-53

8. Click Next to view the next page of the wizard, as shown in Figure 9-54. Here you describe your conversion scenario for the wizard. Your selection here determines what information you must supply on subsequent pages of the wizard. The selections are self explanatory. Select the Many-to-many selection and click Next.

   
   Figure 9-54

9. In the Define Local Currency Reference page (Figure 9-55), you define the location of the column that contains the currency key. After you specify whether it's in a fact table (which it is in the Adventure Works DW sample data) or in a dimension table, you select the attribute with which a currency is associated. If a currency attribute is in the fact table, it is likely to be a dimension key that is related to a dimension table. Otherwise, the attribute is a column in a dimension table that typically corresponds to geography, such as business divisions that are located in separate countries.

   
   Figure 9-55

   In this example, the Destination Currency is automatically selected because the currency conversion definition is already in the Adventure Works cube. If you were to start completely from scratch, you would choose the Source Currency dimension's key attribute on this page.

10. Click Next again, and then click the box to the left of Reporting Currencies to select all of the available items as shown in Figure 9-56. On this page, you identify the currencies to include in the reporting currency (called Destination Currency in the Adventure Works cube). The Business Intelligence Wizard builds a new dimension according to your selections here. If you forget to select a currency that exists in the exchange rate fact table, cube processing will fail — be careful!

    
    Figure 9-56

11. Click Next, and then click Cancel on the final page of the wizard. Because the Destination Currency and currency conversion calculations are already in the cube, the wizard doesn't need to do anything. But if you are adding this capability to your own cube, there's still more to explore to better understand what the wizard would do if you started the process with only a Source Currency dimension and an Exchange Rates fact table.

12. Double-click the Adventure Works data source view in Solution Explorer. In the Tables pane, right-click DimDestinationCurrency, and then click Edit Named Query. A query similar to the following query would be created by the Business Intelligence Wizard when creating a reporting currency. The WHERE clause of this query would detail currencies selected on the Specify Reporting Currencies page of the wizard (see Figure 9-56) if you picked some, but not all, available currencies. Notice that the main foundation for the named query is the DimCurrency table which is also used for the Source Currency dimension in the Adventure Works DW database.

         SELECT    CurrencyKey, CurrencyAlternateKey, CurrencyName     FROM      DimCurrency     WHERE    (CurrencyKey IN                             (SELECT DISTINCT CurrencyKey                             FROM            FactCurrencyRate)) 

13. Switch to the Cube Designer, click the Dimension Usage tab, and locate the relationships between the Exchange Rate measure group and other dimensions. Only the Date and Destination Currency dimensions have a regular relationship with Exchange Rate. Recall that you learned about many-to-many relationships earlier in this chapter. In the current example, Internet Sales has sales amounts in many local currencies which need to be converted — by way of Exchange Rates — to multiple destination currencies. Accordingly, Internet Sales has a regular relationship with Source Currency (representing the local currency) and a many-to-many relationship with Destination Currency with Exchange Rate as the intermediate measure group.

A simple Analysis Services project based on the AdventureWorksDW relational database, along with instructions to use the currency conversion wizard and verify the results, are available at the download site for this book.