Cube Dimensions

The Cube Wizard helps you create your cube object from the DSV by creating appropriate dimension objects. The wizard detects the relationships between dimension tables and fact table(s) in the DSV, creates appropriate dimensions if needed, and establishes appropriate relationships between the dimensions and measure groups within the cube. As mentioned in the previous section a cube contains an instance of the database dimension referred to as cube dimension. There can be multiple instances of a database dimension within a cube There exists a relationship between the cube dimension and the measure groups within the cube. In this section you will learn about various types of relationships between the cube dimensions and the measure groups within, as well as refine the Adventure Works DW cube created by Cube Wizard by addition of a new dimension.

The Cube Wizard establishes relationships between the measure groups and cube dimensions based on the analysis of relationships in the DSV. You might have to refine these relationships based on your business needs. You can change these relationships in the Dimension Usage tab of the cube editor. If you switch to the Dimension Usage tab you will see the dimensions, measure groups of the cube, and the relationships between them, as shown in Figure 6-13.

image from book
Figure 6-13

The cube dimensions and measure groups are represented in a matrix format as rows and columns, respectively, where the relationship between them corresponds to the intersection cell. The intersection cell shows the dimension type along with the attribute that is used in the relationship to join.

Dimension Types

Six different relationships can exist between a dimension and a measure group: No Relationship, Regular, Fact, Referenced, Many-to-Many, and Data Mining. In Figure 6-13 you see two of the six relationship types; No Relationship and Regular. Cells corresponding to a specific dimension and measure group can have an attribute specified that indicates that the dimension type is Regular. Further, such attributes can be used in the join condition between the dimension and the measure group. Often this attribute is the key attribute of the dimension and is called the granularity attribute. The granularity attribute can be an attribute that is above the key attribute of the dimension. When you browse a dimension along with measures of a measure group where the dimension and measure group have a regular relationship then Analysis Services aggregates the data appropriately. The relationship between Dim Customer and Fact Internet Sales measure group is a regular relationship. The granularity attribute is shown in the cell intersecting the dimension and measure group as shown in Figure 6-13.

Cells that are shaded gray indicate there is no relationship between the dimension and measure group. Whenever there is no relationship between a dimension and measure group, the measure group property IgnoreUnrelatedDimension controls the results of queries involving any hierarchy of that dimension and any measure from the measure group. The measure values will either be null (IgnoreUnrelatedDimension=False) or the same value for each member of the dimension (IgnoreUnrelatedDimension=True). For example, there is no relationship between dimension [Dim Employee] and the [Fact Internet Sales] measure group. If you browse the Gender hierarchy of [Dim Employee] and measure [Internet Sales Amount] you see that the measure values for each member of Gender hierarchy are the same value as the Grand Total as shown in Figure 6-14. This is because the IgnoreUnrelatedDimension value is set to True by the Cube Wizard as a default. You learn more about properties of measure groups and measures later in this chapter.

image from book
Figure 6-14

When a table is used as both a fact and dimension table, it constitutes a unique relationship between the dimension and measure group called the fact relationship. The relationship is similar to that of the regular dimension, but specifying it as a Fact dimension helps improve query performance for a certain class of MDX queries which you will learn more about in Chapter 9.

Typically there is a one-to-many relationship between a fact and a dimension member for regular relationships. When you have a one-to-one relationship between a fact and a dimension member, you typically have a fact relationship. When there is a many-to-many relationship between a fact and a dimension member, the dimension member has a one-to-many relationship with various facts and a single fact is associated with multiple dimension members. The definition for a many-to-many relationship can be well understood via an example. Assume you have a fact table (for sales of books data) that is related to a dimension table containing author information. There is another fact table that contains authors' salary information, which is related to the Authors dimension table as well as the geographical information of the publisher who is paying the authors. In this example you have a one-to-many relationship between authors and books. The salary fact data is related to the publisher's geographical information and the authors. If you want to analyze the book sales based on the geographical information of the publisher, the Geography dimension of publishers acts as a many-to-many relationship with the fact Book Sales. You learn the usage of fact and many-to-many relationships in Chapter 9.

Data Mining dimensions are another item type in the list of relationships; these are used to establish linkage between a cube and a dimension created from a Data Mining model. You learn more about this in Chapters 9 and 14.

When a dimension is related to the fact data through another dimension, you define this dimension as having a reference relationship with the measure group. You might recall that you added the Dim Geography dimension in the Review Shared Dimension page of the Cube Wizard. However the Cube Wizard was not smart enough to figure out there is a relationship between the Fact tables and the Dim Geography dimension table through other dimension tables. Hence the Cube Wizard did not add the Dim Geography dimension as a cube dimension. Since the relationship between the Dim Geography dimension and the measure groups in the Adventure Works DW are through another dimension you can say that there is an indirect relationship between Dim Geography dimension and the measure groups. This indirect relationship between the measure groups and dimensions is called a reference relationship.

Follow the steps below to add the Dim Geography dimension to the cube and establish the reference relationship:

  1. To add the Dim Geography database dimension to the cube, right-click in the Dimension pane of the Cube Structure tab and select Add Cube Dimension, as shown in Figure 6-15.

    image from book
    Figure 6-15

  2. A dialog showing all the shared dimensions within the project launches, as shown in Figure 6-16. Select the Geography dimension and click OK.

    image from book
    Figure 6-16

  3. The cube editor identifies a default relationship through an attribute between existing measure groups and the Geography dimension and defines a relationship. If you go to the Dimension Usage page you will see the relationship established by the editor as shown in Figure 6-17. This cube editor has established a regular relationship with the attribute Dim Sales Territory with both the measure groups. The BIDS detects there are columns named Sales Territory key in the dimension table Dim Geography as well as both the fact tables. Since the column names are matching it establishes a regular relationship. However if you look at the fact and dimension tables and the relationships in the DSV you will notice this is an incorrect relationship. There exists an indirect relationship between Dim Geography dimension and Fact Internet Sales measure group through the Dim Customer dimension. There is an indirect relationship between Dim Geography dimension and the Fact Reseller measure group through the Dim Reseller dimension. You need to define a reference relationship.

    image from book
    Figure 6-17

  4. To define the relationship between [Dim Geography] and [Fact Internet Sales] measure group, select the corresponding cell in the matrix and you will see an ellipses in that cell. Click the ellipsis ( "…"). This opens the Define Relationship dialog shown in Figure 6-18. Select Referenced from the Select Relationship Type drop-down list box. The [Dim Geography] dimension forms an indirect or reference relationship with the [Fact Internet Sales] measure group through the [Dim Customer] dimension. You define the intermediate dimension through the Intermediate Dimension option. Once you have defined the intermediate dimension, you need to select the attributes that are involved in the join of the relationship. Reference Dimension Attribute is the attribute in the reference dimension that is used in the join between the intermediate dimension ([Dim Geography]) and the reference dimension ([Dim Customer]). The Intermediate Dimension Attribute is an attribute of the intermediate dimension that is involved in the join between the reference dimension and the intermediate dimension. Define the Intermediate dimension as Dim Customer, Reference dimension attribute as Dim Geography, and Intermediate dimension attribute as Geography Key as shown in Figure 6-18 and click OK.

    image from book
    Figure 6-18

    In Figure 6-18 you see a check box with text Materialize. This check box is enabled by default. By enabling this check box you are ensuring Analysis Services will build appropriate indexes so that get improved query performance while querying fact data along with reference dimension hierarchies.

  5. Similar to step 4, establish a referenced relationship between the [Dim Geography] dimension and the [Fact Reseller Sales] measure group through the [Dim Reseller] dimension. Once you have completed specifying the relationship between [Dim Geography] and the two measure groups of the cube, your Dimension Usage tab will resemble Figure 6-19.

    image from book
    Figure 6-19

The reference relationship between a dimension and a measure group is indicated by an arrow pointing to the intermediate dimension as shown in Figure 6-19. This graphical view of the reference relationship helps you identify the type of relationship between a dimension and measure group when you are looking at the Dimension Usage tab of the cube editor. Similar graphical representations are available for fact, many-to-many, and Data Mining dimensions, and you learn about these relationships in later Chapters 9 and 14.

Browsing Reference Dimensions

Having added the [Dim Geography] dimension as the reference dimension to the cube, assume you want to analyze the Reseller Sales based on different business types in various countries. To do so you need to go to the Cube Browser and drag and drop the English Country Region Name hierarchy from the [Dim Geography] dimension to the rows, the Business Type hierarchy of Dim Reseller dimension to the columns, and the measure Sales Amount of the Fact Reseller Sales measure group to the details area. You can now analyze the Sales data based on the business type in each country, as shown in Figure 6-20. Based on this sales knowledge, the costs associated with the products, and your business goals, you can strategically promote the business type yielding the maximum profit for your company. Reference dimensions help you to analyze fact data even though they are not directly related to the facts.

image from book
Figure 6-20

OWC sends the following statements and queries to retrieve data for analyzing the reseller sales fact of various business types across in various countries of the resellers. OWC creates sets for the members on the columns and rows of OWC and then queries the facts added to the detail data along with the sets.

     Drop visual totals for [Adventure Works DW]     CREATE SESSION      SET [Adventure Works DW].[{}Pivot24Axis0Set0]     AS      '        {                     { [Dim Reseller].[Business Type].[All] },                     AddCalculatedMembers ([Dim Reseller].[Business Type].[Business     Type].MEMBERS)             }      '      SET [Adventure Works DW].[{}Pivot24Axis1Set0]     AS      '             {                     { [Dim Geography].[English Country Region Name].[All] },                     AddCalculatedMembers ([Dim Geography].[English Country Region Name].[English Country     Region Name].MEMBERS)             }      '     SELECT      NON EMPTY [{}Pivot24Axis0Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,      NON EMPTY [{}Pivot24Axis1Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,      {             [Measures].[Sales Amount]      }      ON PAGES      FROM [Adventure Works DW]     CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR     DROP SET [Adventure Works DW].[{76D2D6C7-D50B-4C12-8DBF-     DA53595646F5}Pivot23Axis0Set0]     DROP SET [Adventure Works DW].[{76D2D6C7-D50B-4C12-8DBF-     DA53595646F5}Pivot23Axis1Set0] 

OWC provides you with the option of slicing the data you are analyzing. Therefore OWC creates the MDX statements to create sets within the specified session. It then queries the multidimensional data on three different axes and displays them on the Rows, Columns, and Fields area. Because the query used by the OWC control retrieves data on three-dimensional axes, you cannot execute the same query in SQL Server Management Studio (SSMS). SSMS will only be able to display two-dimensional results. Therefore if you need to see the exact same results in SSMS, you need an MDX query that will retrieve results in a two-dimensional format. The MDX query generated by OWC can be re-written using the CrossJoin function or the cross join operator * so that the results can be retrieved on two axes. The simplified MDX query that will return the same results as the OWC is:

     SELECT      {              [Measures].[Sales Amount]      }      ON COLUMNS,      NON EMPTY {[Dim Reseller].[Business Type].members *      [Dim Geography].[English Country Region Name].members}      DIMENSION PROPERTIES MEMBER_NAME ON ROWS      FROM [Adventure Works DW]     CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR 

So far you have learned about Cube dimensions, how to add them to a cube, define relationships and then query data along with the dimensions. Cube dimensions and their attributes and hierarchies contain several properties. Some properties such as AttributeHierarchyEnabled, AttributeHierarchyVisible, and the AttributeHiearchyOptimizedState reflect the state of the cube dimension hierarchies or attributes in the shared dimension by default. You can override these properties so that appropriate settings are applied for the cube dimensions within the cube. The properties AggregationUsage for attributes and AllMember AggregationUsage for cube dimensions control the behavior of aggregations designed on the cube. You learn more about these properties in Chapters 9 and 13.

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: