Using Properties to Customize Dimensions

Analysis Services provides several properties for use with hierarchies and dimensions. If every property were explained with illustrations and examples, this chapter would be quite large, so in this section you learn about the properties that are likely to be used in fulfillment of most common business requirements.

Ordering Dimension Members

In Chapter 5 you learned to order dimension members of a hierarchy based on key or named columns specified for the attribute. Based on that, Analysis Services sorted the members in the hierarchy and you were able to see the order while browsing the dimension. In certain business scenarios, you might have a need to sort the members based on a specific value or based on some other condition. Analysis Services 2005 provides properties to sort members of a hierarchy in a variety of ways other than the key or named columns.

Analysis Services 2005 provides you an option to sort members of a hierarchy based upon another attribute that is related or included as member property for the primary attribute you want to sort. For example, if you have an Employees hierarchy and if you have the age of the employees defined as a related attribute, you can set the appropriate properties to achieve a sorting of employees based on age. You need to set the property OrderByAttribute to age and then set the property OrderBy to either AttributeKey or AttributeName depending on your requirements, and then deploy the changes to the Analysis Services instance. These properties are shown in Figure 8-18. You can see the changes take effect by viewing the members in the Dimension Browser.

Figure 8-18

The All Member, Default Member and Unknown Member

Each hierarchy within a dimension has a member that is called the default member. When a query is sent to Analysis Services, Analysis Services uses the default member for all the hierarchies that are not included in the query in order to evaluate the results for the query. If a default member is not specified, Analysis Services uses the first member in the hierarchy based on the default ordering for the hierarchy. If the property IsAggregatable is set to True, that means that the values of members of the hierarchy can be aggregated to form a single member. This single member by default is called the "All" member and is usually the default member for the hierarchy. You can change the name of the All member for the attribute hierarchies within the dimension by changing the dimension property AttributeAllMemberName, as shown in Figure 8-19. In order to select the Properties window for the dimension, you can click the dimension name in the Attributes pane in the dimension editor or anywhere in the Hierarchies and Levels pane. If you deploy the change for AttributeAllMemberName as shown in Figure 8-19, you will see that the All member for various hierarchies now shows up as AllMember. For multi-level hierarchies the property to set the default member is AllMemberName.

Figure 8-19

You can change the default member for each hierarchy. To change the default member, you need to set the property DefaultMember with the correct member in the hierarchy. Click the ellipses next to the DefaultMember property for the Employees hierarchy and you will see the Set Default Member dialog shown in Figure 8-20. You have three options for specifying the default member: using the system default member, selecting the member by browsing the hierarchy, or specifying an MDX expression to arrive at the default member. For the last option you can paste the MDX expression that evaluates the default member for the hierarchy or use the dialog to build the MDX expression that will evaluate the default member. Once the default member has been set for a specific hierarchy, Analysis Services uses that default member in query evaluation. You can send the following MDX query to ensure the default member you have set in the property is being used by Analysis Services:

     SELECT [Dim Employee].[Employees].defaultmember on 0     from [Adventure Works DW] 

Figure 8-20

UnknownMember is a property for each dimension in your database. If there are referential integrity issues in your relational database, then during partition processing Analysis Services will raise appropriate errors. If you have set specific processing options to ignore errors, but include the fact data corresponding to errors in the cube, then Analysis Services allocates the fact data to a member called the Unknown member in the dimensions for which it is unable to find members due to referential integrity issues. You have the option of allowing the Unknown member to be visible or hidden, using the dimension property UnknownMember. When the UnknownMember is set to be visible, the member name is set to UnknownMember and will be included in the results of the MDX queries that contain the hierarchy. Similar to the All member name, Analysis Services gives you the option of changing the name of the Unknown member to a more meaningful name corresponding to that specific dimension.

Error Configurations for Processing

One of the challenges in designing a data warehouse is creating a perfect schema without any referential integrity issues. However, this is often not possible and a significant amount of the time spent in designing a data warehouse is typically spent in data cleansing. Analysis Services, by default, will stop processing dimensions whenever it encounters specific referential integrity issues. Some data warehouse designers might want to ignore the referential integrity issues by ignoring the records causing errors and include corresponding fact data to Unknown member of dimensions so that they can see the results of their cube design. Analysis Services gives you fine-grain control for various referential integrity issues that can happen during processing. The dimension property ErrorConfiguration allows you the fine grain control for dimension processing. If you click the ErrorConfiguration property and select Custom, you will see all the properties that allow you fine-grain control as shown in Figure 8-21.

Figure 8-21

The possible errors Analysis Services can encounter while processing a dimension are related to key attributes of the dimension. Typically, when you have a snowflake dimension you can encounter dimension key errors while processing whenever Analysis Services is unable to find corresponding keys in the dimension tables involved in the snowflake schema. The main errors that Analysis Services encounters are duplicate key errors (multiple occurrences of the key attribute in the dimension table), key not found error (unable to find a key in the dimension table in the snowflake schema), and null keys being encountered when you do not expect null keys to be present in the dimension tables. You can set properties to stop processing after a specific number of errors have been reached, continue processing by reporting the errors, or ignoring all errors. We believe the error configuration properties are self-explanatory. We leave it to you as an exercise to set various error configurations while building your data warehouses.

Storage Mode

Analysis Services supports two storage modes for dimensions. Your dimensions can be configured to be MOLAP or ROLAP dimensions. If a dimension is configured as MOLAP, at the time of processing Analysis Services reads all the dimension data from the relational data sources and stores the data in a compressed format. Due to the proprietary patented format, Analysis Services is able to retrieve dimension data efficiently, resulting in fast query response times. When the storage mode is set to ROLAP, Analysis Services does retrieve the data from the relational data source. At the time of processing it updates appropriate metadata information. For each query involving retrieval of data from the ROLAP dimension, Analysis Services sends corresponding SQL queries directly to the relational data source to retrieve the members in the dimension, performs necessary calculations on the Analysis Services engine, and then results of the query are provided to the client. Typically, ROLAP storage mode is chosen whenever there are a large number of members in a dimension (on the order of hundreds of millions of members). You need to evaluate the trade-off between query performance versus storage or business requirement for your business and set the correct storage mode for your dimensions. Disks have become quite cheap these days and we recommend setting the storage mode for dimensions as MOLAP. However, in certain business scenarios where you have the dimension data constantly changing and you need real time data, you might want to set the storage mode to ROLAP. Even in cases where your customers need real-time data, you might be able to set the property called Proactive Caching that helps in providing real-time data to your customers. For more details on real-time data, see Chapter 18. Figure 8-22 shows the dimension property for storage mode.

Figure 8-22

Grouping Members to Form a Single Member

Some of the hierarchies might have continuous data, and typically you might not be interested in viewing each and every member. An example of such a hierarchy is the salary of customers. Typically one would be interested in customers within a specific salary range rather than querying for customers with a specific salary. In such circumstances, Analysis Services allows you to model your hierarchy so that the members of the hierarchy are ranges rather than individual values. Analysis Services provides two properties to control this behavior so that you can group a set of members to a single group.

Follow these steps to understand the behavior:

1. Open the Dim Customer dimension in the Dimension Designer.

2. The Yearly Income attribute hierarchy of Dim Customers has the salaries for all the customers. In order to group the values into a few members, you need to set the properties DiscretizationBucketCount and DiscretizationMethod. Set the DiscretizationBucketCount to 10 and the DiscretizationMethod to Automatic, as shown in Figure 8-23. The DiscretizationBucketCount instructs Analysis Services to generate N members in the hierarchy where N is the number of members. The DiscretizationMethod specifies the way in which you want the customer salaries to be grouped. The Automatic setting instructs Analysis Services to find the most efficient way of grouping the values after analyzing all the values. Deploy the changes to your Analysis Services instance.

   
   Figure 8-23

You will see the various buckets generated by Analysis Services in the Dimension Browser, as shown in Figure 8-24.

Figure 8-24

You have learned most of the commonly used properties that will help you in enhancing your dimensions and hierarchies for your business. In the next section you add intelligence to your dimensions by means of the wizards provided by Analysis Services 2005. These business intelligence wizards help in creating calculations that are widely used in business with the help of a few selections.