Custom Rollups

The name "custom rollup" is very much self describing. Custom refers to the "user defined" nature of a rollup, such that a measure value for a member is not a simple sum of values of its children as you move up a hierarchy. Rollup describes how those calculations typically start at the leaf or lower-level node and move (roll) up toward the root. There are several ways in which you can apply a custom rollup to a hierarchy: by using the attribute property CustomRollup column, using unary operators (used for parent-child hierarchies), and by using MDX scripts to specify custom rollup for members in a level. Note that unary operators can be used on non-parent-child hierarchies too.

A business scenario will help you better understand the need for and concept of the custom rollup. Perhaps you are familiar with the word depreciation.Technically, the definition of depreciation is "mapping an asset's expense over time to benefits gained through use of those assets." It simply means that the value of an asset decreases over time. When working on the financial side of business intelligence it is only a matter of time before you encounter the concept of depreciation, so if you don't already know it, study this carefully. As you may already know, the value of a car decreases over the years. In fact, after the moment you drive off the lot, the new car's value starts to decrease. This is a common example of depreciation.

There are two types of depreciation you should be familiar with and understand. They are called straightline and accelerated. Typically, businesses keep two (sometimes more) sets of accounting books, which, by the way, is completely legal. One set of books is for the IRS and one set is for investors. The books for the IRS often use accelerated depreciation because this provides optimal tax benefits (less taxable income is initially reported) and the books for investors use straight-line depreciation because this yields higher net earnings per share for that quarter or year and a more favorable ROE (Return on Equity), which is the net income divided by the shareholder's equity.

Accelerated depreciation on a delivery van, illustrated in Figure 8-1, can be thought of as "front loaded" depreciation; the percentages associated with each year indicate the percentage of total value depreciated or "written off" for that year. The fact that 40% is depreciated the first year and 10% the last year speaks to the notion that the depreciation is front loaded. There is no cash involved in recognizing depreciation, yet there is a reduction in asset value and an expense is logged; hence it is called a noncash expense. The company can write off more of the van's value earlier on their taxes. That means less taxable income for the company in the earlier years and that is a good thing.

Figure 8-1

Straight-line depreciation on the same delivery van, illustrated in Figure 8-2, again happens over time until the van is essentially worthless with no salvage value (in this case after five hard-driving years). Notice that the rate at which the van falls apart is the same, regardless of how depreciation is logged on the financial records. In this case, the non-cash expense is logged in equal amounts over the life of the asset at 20% per year. The effect this method has is that an investor sees an asset retaining value for a longer period. That is a good thing because value ultimately relates to stock price.

Figure 8-2

Having learned about depreciation you can understand the need for custom rollup of member values in a hierarchy to their parent — it all depends on the type of depreciation being logged and therefore will be custom, by definition. In order to calculate the net profit of your company, you would typically add up the sales revenue, any increase in asset values, and subtract the expenditures (Cost of Goods Sold) and depreciation values appropriately. You might be wondering if these are just measure values, what is so complex about them. Why not just write a calculated measure appropriately? That would be a reasonable question to ask. And if it is just measures you're dealing with, you don't have a problem.

By definition, depreciation indicates that values change over time. So, your calculations that reflect the value of physical assets (like delivery vans) should be adjusted for appropriate percentage changes based on the Time dimension you are querying. Similarly, there might be other dimensions or measure groups that calculations might depend on. For example, say you have a Budget measure group and a Sales measure group in your cube. Your budget for next year might depend on the sales of the previous year and you need to use appropriate MDX expression to arrive at the budget amount. You can create such custom calculations using MDX scripts with appropriate SCOPE statements, but it will be quite a lengthy script, especially if the calculations are different for each member in a dimension. Also, verifying that your calculations are giving the correct values will be time-consuming. Analysis Services 2005 allows you to specify the calculations through MDX expressions as a property to the hierarchy.

Here is an example. If there is an Account dimension that indicates the types of accounts of your company, such as asset, liability, income, and expenditure, and you have a measure called Amount, the rollup of the values to the parent member is not a simple sum. In such a case, you need to specify a custom rollup. If the hierarchy is a parent-child hierarchy, Analysis Services 2005 allows you to perform a custom rollup using a feature called the unary operator if it is a simple operation (addition, subtraction, do not rollup the values), which is discussed in the next section. However, if the value of a member has not derived from its children or you have a user hierarchy where you have to rollup the values to the parent using a complex operation or custom formula, you specify the custom rollup using a property called CustomRollupColumn for an attribute hierarchy.

The CustomRollupColumn property of an attribute should be set as a column in the relational table that will contain the custom rollup calculation — which is an MDX expression. For example, in the Account dimension in Adventure Works DW sample, the value for Account Average Unit is calculated from the Accounts Net Sales and Units, which are members in the Account dimension under different parents. In order to specify the custom formula for a member, the column in the relational table should contain an MDX expression that evaluates the value for the member. You need to specify an MDX expression for each member a custom formula needs to be applied to. In Analysis Services 2000 you had properties to specify custom formulas to members and Custom Rollup Formulas to levels within dimensions. You can consider the CustomRollupColumn property for a hierarchy in Analysis Services 2005 as merging the two properties in Analysis Services 2000. It would have probably been better to name this property CustomFormula instead of CustomRollup. Follow the steps below to understand the behavior of a custom rollup by using the sample Adventure Works DW relational database.

1. Create a new Analysis Services project called AnalysisServicesTutorial using BIDS.

2. Create a data source to the AdventureWorksDW relational database on your SQL Server instance.

3. Create a Data Source View that contains all the tables except AdventureWorksDWBuildVersion, DatabaseLog, and ProspectiveBuyer within the Adventure Works DW data source, as shown in Figure 8-3.

   
   Figure 8-3

4. Create the dimensions using the Dimension Wizard and select the defaults so that you end up with the dimensions shown in Figure 8-4. Include the table DimProductCategory and DimProductSubcategory along with DimProduct while creating the DimProduct dimension so that you have a snowflake dimension. All the remaining dimensions are created using the appropriate dimension table from the DSV. The Dimension Wizard will automatically detect parent-child hierarchies for dimensions DimEmployee, DimAccount, DimDepartmentGroup, and DimOrganization. The wizard will create appropriate parent-child hierarchies with that information. Go ahead and create the Time dimension Dim Time by defining appropriate time periods.

   
   Figure 8-4

5. Create a new cube using the Cube Wizard. In the "Identify Fact and Dimension Tables" dialog mark all tables beginning with the word Fact as fact tables only, all tables beginning with Dim as dimension tables, and specify the time dimension table as shown in Figure 8-5. Add all the dimensions created in step 4 as cube dimensions and complete the Cube Wizard with the defaults. You will now have Adventure Works DW cube created. The Cube Wizard detects the relationship between the fact and dimension tables based on relationships in the DSV and creates appropriate dimension usage. If you do need to change the relationships you can do so on the Dimension Usage tab within the cube editor. If you look at the relationships in the Dimension Usage tab you will notice that there are multiple instances of certain dimensions. For example, you will see four instances of the DimTime dimension Dim Time, Dime Time (Due Date), Dim Time (Order Date), and Dim Time (Ship Date). Instead of creating four database dimensions and using them within the cube, Analysis Services 2005 creates cube dimensions from a single dimension. Typically when you have multiple relationships between a fact table and dimension table then the optimal way to model them is with a single database dimension. Such dimensions are called role playing dimensions.

   
   Figure 8-5

6. Double-click the Dim Account dimension in Solution Explorer. Notice the attribute hierarchies of Dim Account dimension within the Dimension Designer. If you switch to the Dimension Browser and browse the parent-child hierarchy, you will see the account numbers. In order to view the Account names while browsing the parent-child hierarchy you need to specify the NameColumn property for the key attribute, which you learned about in Chapter 5. Select the key attribute Dim Account and specify the relational column AccountDescription as the NameColumn property for the key attribute and then deploy the changes to the Analysis Services instance. If you go to the Dimension Browser you will see the name of the accounts.

   In the properties for an attribute you will see a property called CustomRollupColumn, as shown in Figure 8-6. This property needs to be set to the relational column that has the MDX expression for custom rollup formula. The MDX expression specified in this property gets evaluated on the cells when the value's corresponding members of the hierarchy are being retrieved in the query.

   
   Figure 8-6

7. The sample AdventureWorksDW relational database provides a column with custom rollups for the Account dimension. Right-click the DimAccount table in the DSV of the dimension DimAccount and select Explore Data. You will see the data in the relational table as shown in Figure 8-7. The relational column CustomMembers contains the MDX expression for custom rollup. In the AdventureWorksDW relational sample there is an MDX expression defined for an Account, which has AccountKey = 98 as shown in Figure 8-7. There are no children for the Account with AccountKey = 98 (Account Name is "Average Unit Price") and there are no corresponding fact rows in the fact table FactFinance. Therefore, if you browse the current Adventure Works DW cube in Cube Browser, you will see a null value for the Average Unit Price account. The MDX expression in the relational table for Average Unit Price account is:

        [Account].[Accounts].[Account Level 04].&[50]/[Account].[Accounts].[Account L 02].&[97] 

   
   Figure 8-7

   When evaluated this MDX expression will provide the amount for Account Average Unit Price from Accounts Net Sales and Units. Accounts Net Sales and Units are not children of the Average Unit Price account and hence the custom formula mentioned earlier calculates the value for Average Unit Price Account. In the preceding expression you see that the dimension and hierarchy names specified are Account and Accounts, respectively. Notice the members specified in the MDX expression for custom rollup include the level names Account Level 04 and Account Level 02. By default, the level names for parent-child hierarchies have the names "Level xx." Hence you also need to make sure you specify appropriate property to have the level names as shown in the MDX expression. Hence you need to change the name of the dimension and the parent-child hierarchy in your database.

8. Right click on the dimension name Dim Account and click Rename. Enter the name Account. When asked if the object name needs to be changed, click Yes. Open the Account dimension in Dimension Designer. Right-click the parent-child hierarchy Parent Account Key and rename it Accounts. You now need to specify the level names for the parent-child hierarchy. Enter the value "Account Level *;" next to the Accounts parent-child hierarchy property Naming template and set the property IsAggregatable to False. You learn more about these properties later in this chapter. Don't think you have completed all the renaming yet. You have currently renamed the dimension and hierarchy. When an MDX expression within a cube is calculated, the dimension name addressed with the cube is the cube dimension and not the database dimension that you just renamed. Hence you also need to rename the cube dimension name for dimension Account. Open the cube Adventure Works Cube. Click the Cube Structure tab; right-click the Account dimension in the Dimensions pane and select Rename. Enter the name Account. You have successfully made changes to your cube to define the custom rollup formula for the Accounts hierarchy.

9. Select the property CustomRollupColumn for the Accounts hierarchy in the Account dimension. Click the drop-down list box and select New. In the Object binding dialog select the CustomMembers column in the Dim Account table as the column for the CustomRollupColumn property as shown in Figure 8-8. You have successfully specified a custom formula for members of the Accounts hierarchy. In addition to specifying a custom formula using CustomRollupColumn, you can also specify the CustomRollupPropertiesColumn property to apply custom properties on the cell value. The CustomRollupPropertiesColumn property also takes a column in the relational table as input and that column should contain the MDX expression. Typically you would specify the cell properties such as background color and foreground color using an MDX expression. The sample relational database does not contain values for CustomRollupPropertiesColumn and hence we leave the exercise of exploring the properties to you.

   
   Figure 8-8

Deploy the project to the Analysis Services instance. To make sure the custom rollup column MDX expression is correctly evaluated for the Average Unit Price account, go to the Cube Browser and browse the Accounts dimension and the measure Amount from the Fact Finance measure group. You will see the value for Average Unit Price is now calculated using the MDX expression as shown in Figure 8-9. Using a calculator you can easily verify that the value for Average Unit Price is equal to the value of Net Sales divided by the Units.

Figure 8-9

You have now successfully learned to apply a custom formula to members of a hierarchy. In this example, a parent-child hierarchy was used for you to understand the CustomRollupColumn property; however, the custom rollup is not limited to parent-child hierarchies but can be used on any hierarchy. As mentioned, Analysis Services 2000 provided a way to specify a custom formula for a level within a hierarchy. You can specify such a formula in your MDX script or specify the custom formula for the members in a relational column and use the CustomRollupColumn property for that hierarchy. If an attribute hierarchy is part of multiple user hierarchies and you need to apply different custom rollup behavior based on the hierarchy, you need to apply these custom formulas in the MDX script. Analysis Services 20005 provides another way to aggregate data for members in parent-child hierarchies using a property called UnaryOperatorColumn. The next section provides further details.