MS SSAS 2008 Unleashed - Chapter 9. Multidim - Models and BI Development Studio

After you have created your DSV, you are ready to start creating the dimensions for your multidimensional model. For details about dimensions, see Chapter 5, “Dimensions in the Conceptual Model.”

Creating a Dimension

Review the names of the dimensional tables before you start designing your dimensions.

You use the Dimension Wizard to create your dimensions.

Modifying an Existing Dimension

Now we are going to use the Dimension Editor in BI Dev Studio to modify the Customer dimension. Double-click the Customer dimension in the Solution Explorer, and the Dimension Editor will open, with the Dimension Structure tab showing, as shown in Figure 9.8.

Working with Attributes

You can create a new attribute on the Dimension Structure tab by dragging a column from a table in the Data Source View pane to the Attributes pane. You can modify an existing attribute on the Dimension Structure tab by selecting an attribute in the Attributes pane; its properties appear in the Properties pane in the lower-right corner. You can change the properties there.

When you create a dimension in the Dimension Wizard, you do not have the opportunity to define the relationships between the dimension attributes. We strongly recommend that you define the relationships between the attributes in your dimension before you use it in the cube. Figure 9.9 shows the Attribute Relationship tab of the Dimension Editor as it appears when you are working on defining relationships. Attribute Relationship tab is new in Analysis Services 2008. It shows you the relationship chains and enables you to easily create attribute relationships.

To create a new attribute relationship object, in the Attribute Relationship tab drag one attribute on top of another. The attribute that you want to drag to another should be the “more granular” (in other words, the attribute that will appear higher in the level hierarchy). For example, drag the City attribute on top of State Province attribute to create a relationship.

By default, Analysis Services creates the RelationshipType property as Flexible. You can see a relationship’s properties in the Properties pane if you select the relationship in the main pane. Change the property to Rigid if you are sure that the members of the City attribute are not going move to another state. Specifying a Rigid relationship guarantees that an aggregation based on attributes with rigid relationships will not get dropped after an incremental update of the dimension.

If you made a mistake and dragged a wrong attribute and created an unwanted relationship, you have an option to delete an attribute relationship by selecting an arrow in a flow chart diagram in the main drawing pane or by selecting a relationship in the Attribute Relationship pane in the lower middle.

In many cases, as in our example, you want the Name column and the Key column to be different. We have already done this for the Customer attribute in the Customer dimension. Here’s how we did it:

Working with Hierarchies

When an end user connects to the server, typically he will see list of hierarchies and a list of the levels in each hierarchy. From these lists, he can choose what sort of data he wants to view.

There are two types of hierarchy in Analysis Services: attribute hierarchies and user hierarchies.

An attribute hierarchy is a hierarchy that contains a single level with the same name as the attribute it is based on. Attribute hierarchies provide you with the flexibility to arrange a report in a number of ways. For example, you can place a Gender level from the Gender attribute hierarchy on top of the Age level from the Age attribute hierarchy.

You are going to pay a price for this flexibility. If the only hierarchies you have in your dimension are attribute hierarchies, you need to define ways to drill down from one level to another. Otherwise, the performance of your system will be significantly degraded.

For the attributes that are not queried often, we recommend that you set the AttributeHierarchyOptimizedState property to NotOptimized.

User hierarchies are the hierarchies you create in your dimension by dragging and dropping attributes into the Hierarchies and Levels pane in the Dimension Editor. (You can accomplish the same thing by editing the DDL.) Go back to Figure 9.8 to see the Customer dimension with a single hierarchy (Customers). The hierarchy contains levels, which we created by dragging attributes into the hierarchy.

There are two types of user hierarchies: natural hierarchies and unnatural hierarchies. See Chapter 5, “Dimensions in the Conceptual Model,” for details about natural and unnatural hierarchies.

In a natural hierarchy, every attribute is related to its parent attribute in the hierarchy. An attribute relationship object points to the parent of an attribute. In our example of the Customers hierarchy in Figure 9.9, you see the City attribute is related to the State Province attribute.

Although unnatural hierarchies give you a great deal of flexibility, they lead to poor performance. It is always better to create natural hierarchies in your dimensions.

Working with Translations

Analysis Services offers powerful multilingual support for multidimensional models. You can define translations for every visible caption in your dimensions and in your cubes. You use the Translations tab in the Dimension Designer to define translations.

We will turn once again to our FoodMart 2008 sample. Click the Translations tab in the Dimension Designer and you should see (in Figure 9.10) the way we have initially defined our translations.

For FoodMart 2008, we have defined a Russian translation for the Customer dimension. All the authors speak Russian, so that was our first choice. In the Properties pane, you can see the locale identifier for the translation selected for the City attribute: 1049. That is the identifier for Russian. You would get the same value for Translation for any of the attributes of the Customer dimension.

When your users browse the cube and see the City attribute in Russian, they probably want to see the name of each city also in Russian. They want to see not only the City attribute caption translated to Russian, but also all the members for the City attribute translated to Russian. To accomplish this, you specify that the CaptionColumn property points to the column in the relational database that contains the Russian translations of each of the member cities.

Using the Browser Tab

Click the Browser tab to browse your dimension. When you open the Browser tab, you will see one of the hierarchies of your dimension. At the top of the screen is a text box titled Hierarchy, with a drop-down list of the hierarchies in your dimension. Another text box (Language) contains a drop-down list of languages. This drop-down list is likely to contain more languages than you have defined for your dimension. Select a language to view the hierarchy in the selected language.