Chapter 9: Advanced Cube Design

You landed the job! You got the coveted "Cube Enhancement Analyst" position, which coincidentally requires you to read this chapter before starting. But are you even qualified to read this chapter? The answer is yes if you meet the following criteria: you learned about creating and browsing a cube in Chapter 5, and in Chapter 8 you learned about enhancing dimensions by using special dimension properties and by adding business intelligence — oh, and some understanding of MDX, which you learned in Chapter 3, wouldn't hurt either. Please review those topics if you are not confident in your understanding of them. Indeed, this chapter builds on what you have learned already, focusing on enhancements to your cube in support of specific business requirements. The focus here is on working with measures in your cube by modifying properties to change how measures appear in cubes and how values are aggregated. There is also focus on using enhanced dimension relationships which are new to Analysis Services. You also work with Actions, an enhanced feature, and Key Performance Indicators (KPIs), a new feature, to add functionality that helps end users view and interpret data efficiently. To implement many of the techniques described in this chapter, you use advanced MDX. In some cases, you can use the Business Intelligence wizard to simplify the addition of complex MDX. Once you have the design techniques mastered, attention is turned to techniques for managing scalability through partitioning, assigning storage modes, and building aggregations.

If this book were the movie "The Matrix," now is the time when Morpheus would hold out a red pill in one hand and a blue pill in the other, and say, "You put the book down and take the blue pill — the story ends, you wake up in your bed and believe whatever you want to believe. You take the red pill — and I show you how to build powerful cubes." To be honest, this chapter might not be as dramatic as all that, but it will bring together many of those gnarly concepts you have learned so you can fine-tune the process of turning data into information (the essential theme of this book.) At the risk of belaboring the point, this chapter really is central, not just in the sense it is near the middle of the book, but in the sense it will help you understand some of the more important features in Analysis Services 2005. In this chapter, you use the Adventure Works DW sample project included with Microsoft SQL Server 2005 to understand how to apply advanced design techniques to cubes. So, gear up and get ready for a deep dive into the core of Analysis Services 2005.

Measure Groups and Measures

In Chapter 6, you learned about measure groups and measures within a cube. To recap, a cube can contain one or more measure groups and each measure group can contain one or more measures. You also learned about the various aggregation functions for each measure, and reviewed some MDX examples of how measure values are rolled up while browsing the cube. In this section, you'll learn how to use an MDX function to simplify querying measure groups and how to group measures within a measure group to help users navigate them more easily. You'll also learn how to use properties to control how measure values are aggregated when unrelated to dimensions in the same query or when performing currency conversions. Lastly, you'll learn how to reuse measure groups in multiple cubes.

With Analysis Services 2005 it is quite possible to end up with a cube containing several measure groups. If you 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) you see 11 measure groups for the Adventure Works cube as shown in Figure 9-1.

Figure 9-1

If you expand each measure group, you can see that most measure groups contain multiple measures. For example, the Internet Sales measure group has 9 measures and the Reseller Sales measure group has 11. Often, business analysis questions are targeted at measures within a single measure group rather than all the measures within a cube. One way to write an MDX query targeting a specific measure group is to include each measure one by one. For instance, if you want all visible regular measures (that is, no calculated measures and no measures with the Visible property set to False) within the Internet Sales measure group, your MDX query could look like this:

     select     {[Measures].[Internet Sales Amount],      [Measures].[Internet Order Quantity],      [Measures].[Internet Extended Amount],      [Measures].[Internet Tax Amount],      [Measures].[Internet Freight Cost],     [Measures].[Internet Total Product Cost],      [Measures].[Internet Standard Product Cost]     } on columns     from [Adventure Works] 

It is time-consuming to form an MDX query that includes each measure of the measure group within the query because you need to drag and drop each measure individually or type the name of each measure into the query. Fortunately, an MDX function called MeasureGroupMeasures is provided to retrieve all the measures within a measure group. The following query shows how to use this function to return the same results as the preceding query:

     SELECT MeasureGroupMeasures ("Internet Sales") ON 0     FROM [Adventure Works] 

Lots of measures in a single measure group can also be overwhelming for end users. Another feature can be used to create logical groupings of measures within each measure group so users can locate measures more easily while browsing a cube. Simply assign the same value to the DisplayFolder property of each measure (in the same measure group) that you want to group together.

Not only do you need to consider measure groups and how measures appear in a cube, but also consider how they interact with dimensions. For example, when you have multiple measure groups within a single cube, you will find that certain dimensions do not have relationships with certain measure groups. Recall from Chapter 6 that relationships between dimensions and measure groups are defined on the Dimension Usage tab of the cube designer. If you look at Dimension Usage in the Adventure Works cube of the Adventure Works DW sample project, you see there is no relationship between the Internet Sales measure group and the Reseller dimension. A query that includes a measure from the Internet Sales measure group and members from the Reseller dimension, as shown below, returns the same value for each Reseller member — the value for the All member:

     SELECT {[Measures].[Internet Extended Amount]} ON 0,     [Reseller].[Reseller Type].members ON 1     FROM [Adventure Works] 

If users find this result confusing, you can override this default behavior by changing the value of the IgnoreUnrelatedDimensisons property for the measure group, as shown in Figure 9-2.

If the IgnoreUnrelatedDimensions property is set to False, a query that includes a measure with a dimension having no relationship to it will return null values. If, for example, you change the IgnoreUnrelatedDimensions property of the Internet Sales measure group to False and then deploy the project, the previous MDX query returns null cell values for each member of the Reseller dimension except the All Reseller member.

Figure 9-2

IgnoreUnrelatedDimensions is a new property that provides the functionality of the ValidMeasure MDX function, often used when working with virtual cubes in Analysis Services 2000. A virtual cube, you may recall, simulates the consolidation of multiple fact tables within a single cube. The ValidMeasure function returns a cell value corresponding to the All member for a dimension that does not have a relationship with the current measure. In other words, the dimension is not represented by a foreign key column in the fact table containing the current measure.

To continue the previous example, with the IgnoreUnrelatedDimensions property for the Internet Sales measure group set to False, you can execute the following MDX query to see that the cell values for all the members in Reseller Type hierarchy have the same value for measure x, while the real measure Internet Extended Amount has null values:

     WITH MEMBER measures.x AS Validmeasure (([Measures].[Internet Extended Amount],     [Reseller].[Reseller Type]))     SELECT { measures.x,[Measures].[Internet Extended Amount]} ON 0,     [Reseller].[Reseller Type].members ON 1     FROM [Adventure Works] 

Another important measure property to know about is MeasureExpression. On the Cube Structure tab of the cube designer, click the Internet Sales Amount measure in the Internet Sales measure group to see its properties, as shown in Figure 9-3. A valid value for MeasureExpression is an MDX expression that typically includes product (multiplication operator) or ratio (division operator) of two measures (or constant). This type of expression is used for currency conversions or when aggregating values with many-to-many dimensions, which are both discussed later in this chapter. When you specify a measure expression, Analysis Services 2005 evaluates the expression for each dimension member first and then aggregates the values across the dimension.

Figure 9-3

Measures used in the MDX expression can be from the same measure group or from different measure groups. In Figure 9-3, for example, the MeasureExpression divides Internet Sales Amount, from the Internet Sales measure group, by Average Rate, from the Exchange Rates measure group. The Exchange Rates measure group contains Average Rate and End of Day Rate to be used for currency conversions.

If you look at the Dimension Usage tab of the Cube Designer, you can see both measure groups, Internet Sales and Exchange Rates, have a direct relationship with the Time dimension. You can also see that dimension Destination Currency is directly related to the Exchange Rate measure group, but has a many-to-many relationship with the Internet Sales measure group. These relationships are required when you store transaction data, such as sales amounts, in the fact table using the local currency, but need the ability to summarize that data in reports using a different currency. For instance, you have sales recorded in the fact table in Mexican pesos, but you need to report sales in Euros.

Since exchange rates vary over time, you might choose to average the exchange rate at the day or month level (depending on your business situation) to calculate the total sales in a specific currency. In the Adventure Works cube, the Average Rate measure is stored in the fact table at the day level. Because it's defined as a semi-additive measure, which you learn more about later in this chapter its value is determined by calculating the average of the children of the current member of the time dimension. That is, if the current member is a month member, then the average rate is calculated by averaging the Average Rate measure for all days in that month.

You can best see the effect of Average Rate on Internet Sales Amount by browsing the cube. Place Destination Currency on rows, and add the measures Average Rate and Internet Sales Amount as shown in Figure 9-4.

Figure 9-4

In Figure 9-4 you can see the U.S. dollar is the base currency because it has an Average Rate of 1. Please note that the Internet Sales Amount for the other currencies is not derived from the division of Internet Sales Amount shown in U.S. dollars by the destination currency's Average Rate. The MeasureExpression defined for Internet Sales Amount causes Analysis Services to calculate a value for each individual transaction, dividing Internet Sales Amount in US dollars by the Average Rate for that day, and then aggregating the calculated values to show the Internet Sales amount in the desired currency. (You'll learn later in this chapter how the individual transactions were converted to US dollars before the MeasureExpression is applied.) Incidentally, because the FormatString property for Internet Sales Amount is set to Currency, all values for this measure are shown as dollars in the browser. Ideally, you should use an MDX expression for this property to format the value based on the user's selected destination currency.

Once you have a measure group and its measures designed just right, you can add that measure group into another cube. If you are familiar with Analysis Services 2000, you will recognize this capability as the functional equivalent of adding a cube to a virtual cube without the associated activities related to creating and maintaining the individual cubes. With Analysis Services 2005, you can use the Linked Object wizard to add a measure group from another cube in the same database, a cube on the same server, or a cube in any other Analysis Services instance. You can launch the wizard from either the Cube Structure tab, as shown in Figure 9-5, or from the Dimension Usage tab.

Figure 9-5

Using the wizard, you define a data source for the Analysis Services database containing the measure group you want to include in your current cube, and then select the desired measure group from the list of available objects. The linked measure group wizard is self-explanatory and hence we leave it to you to add a linked measure group.

Take a look at the Mined Customers cube in the Adventure Works DW sample project to see how all measure groups in a cube can be linked measure groups, as shown in Figure 9-6. Linked measure groups are identified by a linking chain icon.

Figure 9-6

After adding a linked measure group, you still need to define the right relationships between the dimensions in the cube and the linked measure group. By using a linked measure group, you have access to data in the source cube without the maintenance overhead of separate measure groups for the same data.

Important

You can have a cube where all the real measures are hidden. All the measures exposed to the end users are calculated measures. Some Analysis Services customers design their cubes this way to model specific business requirements.