Reviewing Cube and Dimension Properties

After you use the Cube Wizard to define a cube, you can review the results in Cube Designer. In the following task, you will review the structure of the cube in the Analysis Services Tutorial project, to understand the properties of the dimensions and the cube as defined by the Cube Wizard. In the next lesson, you will modify some of these properties to increase usability.

Understanding the Cube Designer Tabs

In Cube Designer, you can view and edit various properties of a cube. The designer contains the following tabs, which display different views of the cube.

  • Cube Structure
    Use this tab to modify the architecture of a cube.
  • Dimension Usage
    Use this tab to define the relationships between dimensions and measure groups, and the granularity of each dimension within each measure group. If you use multiple fact tables, you might have to identify whether measures do not apply to one or more dimensions. Each cell represents a potential relationship between the intersecting measure group and dimension.
  • Calculations
    Use this tab to examine calculations that are defined for the cube, to define new calculations for the whole cube or for a subcube, to reorder existing calculations, and to debug calculations step by step by using breakpoints. Calculations let you define new members and measures based on existing values, such as a profit calculation, and to define named sets.
  • KPIs
    Use this tab to create, edit, and modify the Key Performance Indicators (KPIs) in a cube. KPIs enable the designer to quickly determine useful information about a value, such as whether the defined value exceeds a goal or falls short of the goal, or whether the trend for the defined value is getting better or worse.
  • Actions
    Use this tab to create or modify drillthrough, reporting, and other actions for the selected cube. Actions provide to client applications context-sensitive information, commands, and reports that end users can access.
  • Partitions
    Use this tab to create and manage the partitions for a cube. Partitions let you store sections of a cube in different locations with different properties, such as aggregation definitions.
  • Perspectives
    Use this tab to create and manage the perspectives in a cube. A perspective is a defined subset of a cube, and is used to reduce the perceived complexity of a cube to the business user.
  • Translations
    Use this tab to create and manage translated names for cube objects, such as month or product names.
  • Browser
    Use this tab to view data in the cube.

To review cube and dimension properties in Cube Designer

  1. In the Measures pane of the Cube Structure tab in Cube Designer, expand the Internet Sales measure group.

    The measures that are defined for the Internet Sales measure group appear. You can change the order of these measures by dragging the measures into the order that you want. The order will affect how certain client applications order these measures. The measure group is named Internet Sales because the underlying fact table had the friendly name of InternetSales in the data source view. Notice that a space was added automatically, based on the capitalized letter "S", to increase the user-friendliness of the name. The measure group and each measure that it contains have properties that you can edit in the Properties window. In Lesson 3, you will learn how to modify the properties of these measures.

    The following image shows the measure group and measures in the Measures pane of Cube Designer.

    Measures pane of Cube Designer

  2. In the Dimensions pane of the Cube Structure tab in Cube Designer, review the cube dimensions that are in the Analysis Services Tutorial cube.

    Notice that while only three dimensions were created at the database level, as displayed in Solution Explorer, there are five cube dimensions in the Analysis Services Tutorial cube. The cube contains more dimensions than the database because the Time database dimension is used as the basis for three separate time-related cube dimensions, based on different time-related facts in the fact table. These time-related dimensions are also called role playing dimensions. The three time-related cube dimensions let users dimension the cube by three separate facts that are related to each product sale: the product order date, the due date for fulfillment of the order, and the ship date for the order. By reusing a single database dimension for multiple cube dimensions, Analysis Services simplifies dimension management, uses less disk space, and reduces overall processing time.

  3. In the Dimensions pane of the Cube Structure tab, expand Customer, and then click Edit Customer.

    The Customer dimension appears in Dimension Designer. (Note that Data Source View Designer and Cube Designer remain open.) Dimension Designer contains three tabs: Dimension Structure, Translations, and Browser. Notice that the Dimension Structure tab includes three panes: Attributes, Hierarchies and Levels, and Data Source View. The attributes that the Cube Wizard designed appear in the Attributes pane and the user hierarchy that the Cube Wizard defined appears in the Hierarchies and Levels pane. The Data Source View pane displays the tables in the data source view from which columns are used as attributes in this dimension.

    You add, remove, and edit hierarchies, levels, and attributes on the Dimension Structure tab of Dimension Designer. In Lesson 3, you will learn how to perform these tasks. For more information, see: Defining and Configuring Dimension Attributes, Defining and Configuring a User-Defined Hierarchy, Defining and Configuring an Attribute Relationship

    The following image shows the Dimension Structure tab of Dimension Designer.

    Dimension Structure tab of Dimension Designer

  4. Switch to Cube Designer by clicking the tab in the design environment or by right-clicking the Analysis Services Tutorial cube in the Cubes node in Solution Explorer and then clicking View Designer.

  5. In Cube Designer, click the Dimension Usage tab.

    In this view of the Analysis Services Tutorial cube, you can see the cube dimensions that are used by the Internet Sales measure group. When a cube has multiple measure groups, cube dimensions might be used with some measure groups but not with others. Also, you define the type of relationship between each dimension and each measure group in which it is used. In Lessons 4 and 5, you will learn more about dimension usage and dimension to measure group relationships.

    The following image shows the Dimension Usage tab of Cube Designer.

    Dimension Usage tab of Cube Designer

  6. Click the Customer field next to Customer at the intersection of the Internet Sales measure group and the Customer dimension, and then click the ellipsis button (...).

    The Define Relationship dialog box appears. In this dialog box, you define the custom dimension properties within a specific measure group. By default, dimensions have the same behavior in each measure group. However, they can have different behavior in different measure groups. Notice that the relationship of the Customer dimension to the Internet Sales measure group is a Regular relationship, which means that the DimCustomer dimension table is directly joined to the FactInternetSales measure group table. Notice also that the granularity of this dimension is at the lowest level, namely the Customer level, but that you can define different levels of granularity. In Lesson 5, you will learn about defining a custom granularity level.

    The following image shows the Define Relationships dialog box.

    Define Relationship dialog box

  7. Click Advanced.

    The Measure Group Bindings dialog box appears, which lets you change the binding of each attribute and define null processing settings. The binding for an attribute specifies the column in the underlying dimension table to which the attribute is bound. By default, this setting is inherited from the dimension; this setting is rarely changed at the measure group level. Null processing settings let you define how Analysis Services treats null values during processing at the measure group level; these settings override any settings at the dimension level. In Lesson 4, you will learn about defining null processing settings at the dimension level. For more information, see: Defining the Unknown Member and Null Processing Properties

    The following image shows the Measure Group Bindings dialog box.

    Measure Group Bindings dialog box

  8. Click Cancel, and then click Cancel again, to return to Cube Designer.

    We will not review the Calculations, KPIs, Actions, Perspectives, and Translations tabs in this task, because the related objects have not yet been defined in the tutorial project. You will learn more about these tabs in Lessons 6, 7, 8, and 9.

  9. Click the Partitions tab.

    The Cube Wizard defined a single partition for the cube, by using the multidimensional online analytical processing (MOLAP) storage mode without aggregations. With MOLAP, all leaf-level data and all aggregations are stored within the cube for maximum performance. Aggregations are precalculated summaries of data that improve query response time by having answers ready before questions are asked. Aggregations will generally be defined before the final deployment of an Analysis Services project to the production server, but are not defined during development. Notice that you can define additional partitions, storage settings, and writeback settings on the Partitions tab. This tutorial does not cover defining aggregations and partitions. For more information, see Partitions (Analysis Services), Defining and Configuring a Partition, Aggregations and Aggregation Designs (SSAS), Designing Partition Storage and Aggregations

    The following image shows the Partitions tab in Cube Designer.

    Partitions tab in Cube Designer

  10. Click the Browser tab.

    Notice that the cube cannot be browsed because it has not yet been deployed to an instance of Analysis Services. At this point, the cube in the Analysis Services Tutorial project is just a definition of a cube, which you can deploy to any instance of Analysis Services. When you deploy and process a cube, you create the defined objects in an instance of Analysis Services, and populate the objects with data from the underlying data sources.

    The following image shows the Browser tab in Cube Designer.

    Browser tab in Cube Designer

  11. In Solution Explorer, right-click Analysis Services Tutorial in the Cubes node and then click View Code.

    The XML code for the Analysis Services Tutorial cube is displayed on the Analysis Services Tutorial.cube [XML] tab. This is the actual code that is used to create the cube in an instance of Analysis Services during deployment. For more information, see: How to: View the XML Code for an Analysis Services Project

    The following image shows the XML code for the cube.

    XML code for the Analysis Services Tutorial cube

  12. Close the XML code tab.

Now that you have reviewed your initial Analysis Services Tutorial cube, you are ready to deploy it to an instance of Analysis Services. While much of the cube and dimension metadata can be edited without deploying the project, viewing the actual data in the cube and dimension designers assists you in the development process. For example, you must deploy and process the cube to view the order in which dimension members are sorted.

Next Task in Lesson

Deploying an Analysis Services Project

See Also

Other Resources

How to: Browse Dimension Data in Dimension Designer
Browsing Cube Data

Help and Information

Getting SQL Server 2005 Assistance