Measures and Measure Groups

You learned about editing cubes dimensions and establishing the right relationships between dimensions and measure groups in a cube. Similarly, you can add or delete objects called measures and measure groups in a cube. Measures are the focus point for data analysis and therefore they are the core objects of a cube. Measures are columns from the fact table that contain meaningful information for data analysis. Usually measures are of type numeric and can be aggregated or summarized along hierarchies of a dimension. You can specify the type of aggregation that needs to be applied for each measure. You can apply several types of aggregate functions to each measure. The most widely used aggregate functions are Sum, Count, and Distinct Count. A collection of measures forms an object called a measure group, and a collection of measure groups forms the dimension called Measures in the cube. Measures is a keyword in Analysis Services that refers to a special dimension that only contains the fact data.

If you click the Cube Structure tab in the cube editor you will see the Measures pane on the top-left corner. Click the cube named Adventure Works DW within the Measures pane to see the associated properties in the Properties window located on the bottom-right corner of the BIDS. Figure 6-21 shows the Measures and Properties panes. The Measures pane shows the cube name and the measure groups within the cube. You can see the two measure groups Fact Reseller Sales and Fact Internet Sales that correspond to the two fact tables. Fact table columns that become measures of the cube are contained within the corresponding measure group. There is typically a one-to-one relationship between a fact table and measure group in the cube.

Figure 6-21

In your source data, if you had partitioned your fact data into multiple fact tables across a specific dimension, that needs to be handled differently while designing the cube. For example, if you have Fact Internet Sales data stored in separate fact tables for each quarter (fact data has been partitioned into multiple fact tables across the Time dimension), then with respect to the cube all these are considered a single fact table because they have the same schema. You have partitioned your relational fact data into multiple fact tables due to design or scalability considerations, but when you want to analyze the data you will be looking forward to aggregating the data appropriately across various dimensions, especially the Time dimension. You can either merge the data from all the fact tables within the DSV with a named query or you can utilize the partitioning feature in Analysis Services so that Analysis Services aggregates the data correctly during browsing. You learn more about partitions in Chapters 12 and 13.

You can see several properties of the cube in Figure 6-21. The most important property is DefaultMeasure. As the name indicates, this property is used to define the measure used by default whenever queries are sent to the cube. The reason why the default measure is important is that whenever your MDX query does not contain the explicit measures specified, the default measure is returned. In addition to that, the default measure is used whenever restrictions are applied in the query with the WHERE clause, and based on the default measure your results can be different. If you select the DefaultMeasure property you can see a drop-down list box that shows all the measures of the cube. You can choose the measure you want to define as the default measure of the cube. If the default measure is not specified, the first measure of the first measure group of the cube (as seen in the Measures pane) will be the default measure of the cube.

The next most important property is the StorageMode property. This defines whether your fact data will be stored on Analysis Services or your relational data source or both. The Storage mode has three different options: Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP). The default value is MOLAP, which means that when the cube is processed Analysis Services reads the relational data and stores it in a proprietary format for fast retrieval. You learn more about the defining storage modes in Chapter 9. In Analysis Services 2005, you have the option to instruct the server to automatically update cube and dimension objects if there was a change in the relational data. The ProactiveCaching property helps in controlling the frequency of the update of the cube data based on changes in the relational data. You learn more about the ProactiveCaching with the help a complete scenario in Chapter 18. The ErrorConfiguration property helps in handling the various errors that can occur while processing the fact data and defining what actions should be taken under such error circumstances such to ignore the error, converting to a specific value or stopping processing when errors are encountered. One of the main features of an OLAP database is the ability to create aggregations that facilitate fast query response times. The AggregationPrefix property is used to prefix the name of the aggregations that are created for the cube. The remaining properties are self-explanatory and you can find detailed information for each property in Analysis Services 2005 product documentation.

If you click one of the measure groups, you will see the properties associated with that measure group. Most of the properties at the cube level are also applicable to the measure group. If you specify a value for a property at the cube level as well as the measure group level for a common property, the value specified at the measure group level will be honored by the server. Expand the measure group Fact Internet Sales and select the measure Sales Amount – Fact Internet Sales. The Properties pane now shows the properties of a measure, as shown in Figure 6-22. Now let us learn the important properties for the measures in detail.

Figure 6-22

The AggregateFunction property defines how the measure value is to be aggregated from one level to another level of a hierarchy in a dimension. For example, the Product dimension contains a hierarchy called Products that contains two levels, Model Name and Product Name. Each model contains one or more products. If you want the sales of a specific product to be aggregated to the model, you need to specify the aggregate function to be Sum. Whenever you browse the cube along the Products hierarchy, you will see that the sales of each product are aggregated to the corresponding model. However, sometimes you might not want the measure value to be aggregated while browsing a hierarchy. Therefore, Analysis Services 2005 provides you with several aggregate functions.

Other than the Sum aggregate function the most commonly used aggregate functions are Count and Distinct Count. The Count aggregate function, as the name indicates, is used whenever you want to count each occurrence of the measure value rather than add the measure values. For example, if you want to find the number of transactions in a day or number of customers in a day, you would use a Count aggregate function on a fact table column that indicates the customers who came to the store on a specific day. The Distinct Count aggregate function, on the other hand, can be used to identify the unique number of occurrences of a specific measure. For example, a customer can buy a specific product every month. If you want to find the unique number of customers who purchase a specific product, you use the Distinct Count aggregate function. You will see examples of Count and Distinct Count aggregate functions in this section. The None aggregate function is used when you do not want to aggregate the values of a specific measure across a dimension. An example of where the None aggregate function would be used is for the price of a specific product or discount provided on a unit product.

When you build and browse a cube you will see all the measures occurring under the dimension called [Measures] in the Metadata pane of the Cube Browser. If you want to organize the related measures in a logical structure that is more meaningful for the customers, you use the property called DisplayFolder. You can specify a measure to be part of one or more display folders by editing the DisplayFolder property. If you enter a name in the DisplayFolder property, that specific measure will become part of the newly entered display folder. You can make a specific measure part of multiple display folders by specifying the display folders separated by a semicolon. When display folders are specified then while browsing the cube you will see the display folders under the appropriate measure group name in the metadata pane of the Browser. Therefore you cannot have measures from different measure groups under a single display folder.

In some business applications you only allow access to the aggregated results of a measure. For such applications you need a way to aggregate the results of a measure but do not want to show the base measure. You can aggregate the results of a measure by specifying a measure called the calculated measure (you learn more about calculations a little later in this chapter) and hide the base measure. The measure property Visible allows you to hide the base measure from viewing for such applications.

The FormatString property allows you to show the measure value in a format of your choice. If you select the FormatString property you will see the various format options available. The MeasureExpression property is used for specifying expression that evaluate the value for the measure. For example if you have Sales information you might want to ensure the Sales information is presented in the local currency based on the currency conversion rates. In such a case you will define appropriate expression for measure expression property for the measure Sales. You learn about the MeasureExpression property in Chapter 9.

The easiest way to see the effect of some of the properties mentioned is to try them yourself in your own project. Specify two display folders named DisplayFolder1 and DisplayFolder2 for the measure Sales Amount. Because the measure Sales Amount – Fact Internet Sales is a currency data type, you can select the currency format. Select the #,#.0 format from the FormatString property drop-down list. The properties window for the measure Sales Amount – Fact Internet Sales should resemble the Figure 6-23.

Figure 6-23

You learned examples of where the aggregate functions Count and Distinct Count can be useful. In your Adventure Works DW project if you want to count the number of customers and distinct customers who have bought specific products, you need to use these aggregate functions. Customer Key identifies the customer who has bought a specific product in the fact table. Therefore in order to see the counts of customers you need to create two new measures using the fact table column Customer Key. To create the two new measures follow the steps below.

1. Drag and drop the Customer Key column from the Fact Internet Sales table in the DSV of the cube editor to the Measures pane. A measure called Customer Key is now created. In the Properties pane change the name for this measure from Customer Key to Distinct Customers by right clicking on the measure selecting Rename. Change the aggregate function for this measure to be Distinct Count.

2. Drag and drop the Customer Key column once again from the DSV to the Measures pane. A measure called Customer Key is created. Rename the Customer Key to Total Customers and change the aggregate function from Sum to Count.

3. The Unit Price – Fact Internet Sales of a product is the same value. Therefore this value should not be aggregated. In order to see the same value for a specific product you need to choose the aggregate function FirstNonEmpty.

4. Create a hierarchy called Products in the Dim Product dimension with two levels Model Name and English Product Name. Rename the level English Product Name as Product Name.

5. Deploy the project to the Analysis Services instance.

Once the deployment is completed you will be in the Browser tab. When you expand the Measures folder you will see two folders called DisplayFolder1 and DisplayFolder2 that contain the measure Sales Amount as shown in Figure 6-24. You might recall that we mentioned you cannot have measures from multiple measure groups grouped under the same display folder. Display folders specified for a measure are always expected to be within the scope of the measure group. However due to minor issue in BIDS Browser you can see that the second display folder DisplayFolder2 specified for a measure is not under the measure group as shown in Figure 6-24. You can in fact specify multiple display folders for a measure and thereby have measures from various measure groups grouped under the same display folder. However display folders are expected to be used by client tools and based on the client tool you are using you might not be able to do this. This tip is helpful only while using BIDS and the issue is expected to be fixed in future releases of the product.

Figure 6-24

Drag and drop the measures Sales Amount – Fact Internet Sales, Total Customers, Unit Price – Internet Sales Amount and Distinct Customers to the data area of the OWC. Then drag and drop the hierarchy Products from the Dim Product dimension to the rows and expand the member Classic Vest. You can now see that the values for the measures are aggregated for the hierarchy Products that contains two levels, Model Name and Product Name, based on the aggregate function chosen. Choosing the aggregate functions Count and Distinct Count will not only count the values for the members of a hierarchy, but will also aggregate the counts to the next level. You can see that the Unit Price – Internet Sales Amount is aggregated from the members in the Product Name level to Model Name level based on FirstNonEmpty aggregate function. You see the Total value for the measure Unit Price – Fact Internet Sales for Classic Vest model as 63.5. The Unit Price value shown for the Model-100 model name is the Unit Price Mountain-100 Silver 42, one of the members of the Model-100. In the example shown in Figure 6-24, all the products under the model name Classic Vest have the same unit price. If you expand the Model Name member Mountain-100 you will see different values for the Products under the model Mountain-100. If these were different values, you could see the effect of this aggregate function. Notice that the values of Sales Amount are formatted based on the format string you specified earlier.

You have now successfully enhanced the cube created by the Cube Wizard by adding cube dimensions and measures to the cube. In the process you have also learned about the properties of cube dimensions, measures, and measure groups. Most often businesses need complex logic to analyze the relational data. Analysis Services 2005 provides you with the ability to embed the complex calculations required for solving business problems in several ways. The most basic operation that every business will need is creating simple arithmetic operations on the base measures or dimension members. Objects created via such operations are called calculated members.