Working with the Dimension Editor

The Dimension Designer, shown in Figure 5-13, is an important tool that helps you to refine the dimension hierarchies created by the Dimension Wizard. You can define the properties such as unary operators, custom roll-ups, and so forth which help you to define how data should be aggregated for cells referred to by members of hierarchies in the dimension. The Dimension Designer itself is composed of three main window panes called Attributes, Hierarchies and Levels, and the DSV. In addition to that you have the toolbar, which contains several icons that help you to enhance the dimension. The Attributes pane shows all the attributes, the Hierarchies and Levels pane shows all the hierarchies along with the levels, and the DSV pane shows the tables that are part of the dimension. If you hover over each one of the icons you will be able to see the functionality that is supported by the icon. Some of the icons are the same as the ones you saw in the DSV and are used for operations within the dimension DSV. The functionality of the remaining icons is discussed later in this chapter and in Chapter 8.

Figure 5-13

Attributes

Attributes are hierarchies that only have two levels; the All level and another level that has the name of the attribute that contains all the members. Each attribute directly corresponds to a column in the relational table. Therefore when the dimension is part of UDM that is used for relational reporting, attributes are selected from the UDM for the corresponding relational column. The Attributes pane in the Dimension Designer shows all the attribute hierarchies of the dimension. The default view of all the attributes within the Attributes pane window is called the Tree view as shown in Figure 5-14. The two additional views supported in the Dimension Designer are the List view and the Grid view. These views show the attributes and associated properties in different views. We have seen the Tree view to be the most flexible view in the Dimension Designer. In the Tree view you can see the attributes along with the member properties. Member properties are attributes within the same dimension that have a one-to-many relationship with the current attribute. For example, if you have the attributes Country, State, and City, you have one-to-many relationships between country and state, as well as between state and city. Member properties are also referred to as related attributes because these attributes have a one-to-many relationship between them.

Figure 5-14

Each dimension has to have at least one attribute that is defined as the key attribute. By definition, the key attribute has a one-to-many relationship with every attribute in the dimension. The Dimension Wizard automatically establishes relationships, such that all attributes of the dimension are related to key attributes. You can see the related attributes of the key attribute Dim Geography in the tree view of Figure 5-14. Just choose the view that best suits you to visualize and design your dimension easily. You can toggle between the different views by right-clicking in the Attributes pane and selecting the view type you desire, as shown in Figure 5-14.

Figure 5-15 shows the List view and Grid view of the attributes shown in Figure 5-14. The List view provides you a concise view where the attributes pane is below the Hierarchies and Level pane. The List view is useful when you have a lot of multi-level hierarchies in the dimension. Since you get a wider area for the hierarchies and levels pane you get a visually optimized view where you can see the attributes and hierarchies. In the Grid view you can see all the attributes with a subset of properties for each attribute. Each property specified in the Grid view is an editable drop-down list box so you can change the properties in this view. All the properties shown in the Grid view are also a part of the Properties window.

Figure 5-15

If you are aware of a one-to-many relationship between attributes, we highly recommend that you specify this relationship in the Dimension Designer as a related attribute. Specifying the member property helps improve query performance as well as changing the aggregation design so as to include the attributes that are part of a hierarchy. You learn more about this in Chapter 12. Because the Dim Geography dimension contains one-to-many relationships, you need to specify the member properties to get query performance improvement. In order to specify that the attribute State Province Name is a member property of City, you need to do the following:

1. Expand the node showing the attribute City.

2. Drag and drop the attribute State Province Name from the attribute list to the area shown as new attribute relationship under the City node. Establishing the relationship between attributes serves dual purpose – member properties as well as related attributes. Establishing the relationship between attributes not only helps in processing performance (you learn in Chapter 12) but also affects calculations that are aggregated across these attributes. You can define the type of relationship between the attributes you established now using the property Cardinality in the Properties window. By default the Cardinality is set to many. If you know that the relationship between the attributes is one to one then you can change the cardinality to one. For example the cardinality between a customer's id and their social security number is one to one, however the cardinality between state and city is one to many.

Follow the same two steps for the English Country Region Name attributes. You have now specified member properties for city attribute. Similarly specify English Country Region name as member property for attribute State Province Name. Often in business analysis when you are analyzing a specific member of a dimension, you need to see the properties of the dimension member to understand it better. In such circumstances, instead of traversing the complete hierarchy you can retrieve the member by querying the member properties. This once again is a performance improvement from the end user's perspective. A wide variety of client tools support the ability to retrieve member properties of a specific member when needed by the data analyst. You can add additional attributes by dragging and dropping a column from the DSV to the Attribute pane or delete an existing attribute by right-clicking that attribute and selecting Delete.

Hierarchies and Levels

Hierarchies (also called multi-level hierarchies) are created from attributes of a dimension. Each multilevel hierarchy contains one or more levels, and each level is an attribute hierarchy. Based on the attributes of the Geography dimension you created, the logical hierarchy to create would be Country-State-City-Dim Geography. Do not expand any of the attributes, stay at the highest level for now. You can create this hierarchy using the following steps:

1. Drag and drop the attribute English Country Region Name from the Attributes pane to the Hierarchy and Level pane. This creates a multi-level hierarchy called Hierarchy with one level English Country Region Name. This level actually corresponds to Country. Hence rename the "English Country Region Name" to "Country" by right clicking on the attribute within the multi-level hierarchy and selecting "Rename."

2. Drag and drop State Province Name from the Attributes pane to the Hierarchy pane such that the State Province Name attribute is below the Country in the multi-level hierarchy designer.

   Rename "State Province Name" to "State-Province" by right clicking on the attribute and selecting "Rename." Drag and drop attributes City and Dim Geography attributes to the multi-level hierarchy in that order so that you now have a four level hierarchy Country-State-City-Dim Geography.

3. The default name of the hierarchy you have created is Hierarchy. Rename the hierarchy to Geography by right-clicking the name and selecting Rename (see Figure 5-16). You can also rename the levels of the Geography hierarchy by selecting each attribute and changing its name value in the Properties pane.

   
   Figure 5-16

4. You have created a multi-level hierarchy called Geography that has four levels, as shown in Figure 5-17. You can click the arrows to expand the attribute in each level to see all the member properties. You can create additional hierarchies in the Hierarchy and Level window pane.

   
   Figure 5-17