Creating a Cube using Cube Wizard

Cubes are the principal objects of an OLAP database that help in data analysis. Cubes are multidimensional structures that are primarily composed of dimensions and facts. The data from a fact table that is stored within the cube for analysis are called measures. In Analysis Services 2005 you can store data from multiple fact tables within the same cube. In Chapter 2 you became familiar with the Cube Wizard and in this chapter you see more details of the Cube Wizard followed by refinements to your cube in the Cube Designer.

Similar to the Dimension Wizard you used in Chapter 5, the Cube Wizard facilitates creation of cube objects from the DSV. For this exercise, you continue with the AnalysisServices2005Tutorial project you created in Chapter 5, which contained the dimensions Geography, Employees, and Time. To start with a clean slate, please delete the existing cube Adventure Works DW if it is still there from Chapter 2. To delete, just right click on the cube in Solution Explorer and select Delete. To completely understand the functionality of the Cube wizard follow the steps below to build a new cube.

1. Right-click the Cubes folder and select New Cube, as shown in Figure 6-2. Click Next on the introduction page to proceed.

   
   Figure 6-2

2. In the Select Build Method page you have the option to build the cube from an existing data source or without a data source. In this tutorial you build the cube from the Adventure Works DW data source. In Figure 6-3 please notice the checkbox option, Use Auto Build. If you select this option the Cube Wizard will detect and create dimensions as part of the creation of the cube. Further, you have the option to enable Cube Wizard detection of attributes and hierarchies within dimensions and to create them as part of the cube building process. Or you can have the detection only for the creation of dimension attributes. Select the option for creating attributes and hierarchies. You can refine the attributes and hierarchies after creation using the dimension editor. If you do not use the Auto build option you can add existing dimensions to the cube in the Cube Wizard or Cube Editor. You can also launch the Dimension Wizard and create dimensions using the Cube Wizard. Accept the defaults as shown in Figure 6-3 and click Next.

   
   Figure 6-3

3. The next page of the Cube Wizard is the DSV selection page. If you have multiple DSVs you need to select the DSV upon which you are creating the cube. In the current project you only have the AdventureWorksDW DSV. The Cube Wizard allows you to select the DSV from which you want to create the cube. Select the AdventureWorksDW data source and Click Next.

   The Cube Wizard now scans the DSV to detect the fact and dimension tables in the DSV and presents these in the Detecting Fact and Dimension Tables page as shown in Figure 6-4. Any table that has an outgoing relationship is identified as a fact table, whereas a table that has an incoming relationship is detected as dimension table.

   
   Figure 6-4

   Click Next after the fact and dimension detection has been completed.

   On the Identify Fact and Dimension Tables page you see the tables that have been identified as fact and dimension tables by the Cube Wizard as shown in Figure 6-5. You have the option to select or deselect a table as a fact or dimension table. The wizard has identified the FactInternetSales, FactResellerSales, and DimReseller tables as fact tables and identified the DimReseller table and the remaining tables as dimension tables. The DimReseller table was detected as both a fact and dimension table because there are incoming as well as outgoing relationships from it. The default view of this page shows the tables with checkboxes indicating selection status.

   
   Figure 6-5

   You can also see a graphical view of the fact and dimension tables. If you click the Diagram tab you will see the tables as shown in Figure 6-6. In the diagram view, fact tables are shown in color yellow, the dimension tables are shown in color cyan, and the tables that have been identified as both dimension and fact tables are shown in green. In this tutorial DimReseller table is considered as only a dimension table and not a fact table.

   
   Figure 6-6

4. Switch to the Tables view and deselect the DimReseller table from being a fact table and click Next.

5. Figure 6-7 shows the Review Shared Dimensions page. This page is shown in the Cube Wizard only when you have tables selected as dimension tables in the Cube Wizard as well as shared dimensions already created from these tables in the database. Select all the dimensions from the Available Dimensions area and move them to the Cube Dimensions area as shown in Figure 6-7 and click Next.

   
   Figure 6-7

6. In the Select Measures page, the Cube Wizard shows all the columns from the fact tables that it detects as potential measures of the cube as shown in Figure 6-8. The Cube Wizard does not select the primary and foreign keys in a table as measures. There is a one-to-one mapping between a column in the fact table and a measure in the cube. The Cube Wizard groups measures from a fact table under an object called a Measure Group. Therefore, by default, there will be one measure group for each fact table included in the cube. In the DSV you are using there are two fact tables, and therefore two measure groups named Fact Internet Sales and Fact Reseller Sales are created. You can select or deselect the measures you want to be part of the cube in this page. Use the default selection and Click Next.

   
   Figure 6-8

7. In the Detecting Hierarchies page the Cube Wizard scans all the dimension tables for which dimensions have not been created to detect hierarchies. The Next button will be enabled after the Cube Wizard has analyzed the relations within the tables. Click the Next button as soon as it is enabled.

8. The Cube Wizard now shows all the dimensions that will be created by the Cube Wizard as shown in Figure 6-9. You can expand the dimensions to see the hierarchies and attributes that have been detected by the Cube Wizard for creation. Review the dimensions and hierarchies within the dimension on this page. You can deselect attributes, hierarchies and dimensions that are not needed for your cube on this page. Select the defaults on this page and click Next.

   
   Figure 6-9

9. In the final page of the Cube Wizard (shown in Figure 6-10) you can specify the name of the cube to be created and review the measure groups, measures, dimensions, attributes and hierarchies. Use the default name Adventure Works DW suggested by the Cube Wizard and click Finish.

   
   Figure 6-10

The Cube Wizard creates the dimension objects and the cube after you hit the finish button. The created cube Adventure Works DW is opened within the Cube Designer as shown in Figure 6-11. The Cube Designer contains several tabs that help perform specific operations that will refine the initial cube created by the Cube Wizard. The default tab is the Cube Structure as shown in Figure 6-11. The Cube Structure view you can see three panes that show the Measures, Dimensions, and the DSV. The DSV contains all the tables that are part of the cube. Operations such as adding or deleting tables in the DSV and zooming in or out with the DSV Designer are possible within the cube DSV. The Dimensions pane shows the dimensions that are part of the current cube and the Measures pane shows the measure groups and measures that are part of the current cube. You can add or delete measures and dimensions in the Cube Structure view. The dimensions within the cube shown in Dimensions pane are called cube dimensions. You can have multiple instances of the shared dimensions of the database within a cube. For example, the Fact tables FactInternetSales and FactResellerSales have relationship with the Dim Time dimension through Order Date, Ship Date and Due Date. Hence you can see three cube dimensions Ship Date, Due Date and Order Date in the Dimensions pane which refer to the Dim Time database dimension. A dimension such as Dim Time which plays the role of three cube dimensions is called as role playing dimension. You will learn role playing dimensions in Chapter 8. Within the Dimensions pane you have two views with which to see the Hierarchies and Attributes of each dimension.

Figure 6-11

What you have done so far is created the Adventure Works DW database containing the Adventure Works cube. You have to deploy the project to the Analysis Services instance so that you can analyze the data within the cube. You can deploy the project to the server in one of the following ways:

1. Select DebugStart Debugging from the menu.

2. Right click on the database AnalysisServices2005Tutorial in solution explorer and select Deploy.

3. Right-click the Adventure Works DW cube and choose Process — from which you will first be prompted to deploy the project and then followed by Process dialog to process the cube.

4. Use the shortcut key F5 to deploy and process.

When you deploy the project to the server, the BIDS sends an XML/A request containing object definitions to the default instance of the Analysis Server selected in the project. By default the Analysis Services project is deployed to the default instance of Analysis Services on your machine. The object definitions are of the cubes and dimensions you created. If you have installed your Analysis Services 2005 as a named instance, you need to change the deployment server name. The BIDS sends another request to process the objects within the database. If you are in the Cube Designer the BIDS assumes that you want to analyze the data after deployment and processing and therefore switches the Cube Designer from Cube Structure to Browser, which is discussed in the next section.

Browsing Cubes

If you deployed a cube to Analysis Services instance the BIDS switches to the Browser tab. Alternately you can open a cube in the Cube Designer and click on the Browser tab. In the Browser view you will see three panes: a Metadata pane, a Filter pane, and a Data pane along with a tool bar as shown in Figure 6-12.

Figure 6-12

The Metadata pane, at the left-side pane of the Cube Browser, shows the measure groups (includes measures) and dimensions (includes attributes and hierarchies) of the cube. The Data pane, at the bottom right, is the Office Web Components (OWC) control used for analyzing multidimensional data. You can drag and drop hierarchies on the rows and/or columns and measures in the data area to analyze the data. Indeed, you can have multiple hierarchies on a rows or columns. The OWC has a filter control that can be used to filter the data being analyzed. You can slice the data you want to analyze based on specific members of a hierarchy. The top-right pane is also a filter control to slice multidimensional data for analysis, but has additional options to slice data other than the ones provided by OWC. While the filter control in OWC allows you to select or deselect members of a hierarchy the Filter pane allows you to perform comparison operations like equal, not equal, contains, in, not in, begins with, range operations, and any MDX expression. With the help of the filter controls in Filter pane and OWC, you will be able to analyze your multidimensional data.

Suppose you want to analyze the Internet sales of products based on the promotions offered to customers and the marital status of those customers. First you would need to drag and drop [Dim Promotion].[English Promotion Type] from the metadata browser to the OWC rows. You will learn the MDX statements that are generated by OWC. SQL Server Profiler in SQL Server 2005 has the ability trace statements sent to Analysis Services instances. The MDX statements sent to the Analysis Services instance can be obtained by using SQL Server Profiler. For more information on how to obtain traces please refer to section on using SQL Server Profiler in Chapter 13.

The first statement sent from OWC to Analysis Service instance is:

     DROP VISUAL TOTALS for [Adventure Works DW] 

When you drag and drop members of a hierarchy on a row (or column), or a measure in the fields area, OWC creates a row (or column) called Grand Total, which will automatically provide totals of the measure value for that hierarchy. By default the OWC in Cube Browser shows you the totals of visible members of the hierarchy in the OWC. This is called Visual Totals because the total is calculated only for the members that are visible in the Browser. You have the option of disabling Visual Totals in the OWC. To do so, right-click OWC, select Command and Options, click the Report tab, and select the All Items option (including hidden items) for Calculate Totals. When OWC has the option of having a Grand Total to have the sum of visible members it uses Visual Totals. The above statement DROP VISUAL TOTAL for [Adventure Works DW] removes references to visual totals for cells and clears the memory cache for visual totals thereby ensuring values for current members selected is accurate. The full syntax for Drop Visual Totals statement is shown below where you can optionally specify the MDX set upon which the visual totals need to be dropped. If the MDX set expression is not specified the visual totals is dropped for the entire cube.

     DROP VISUAL TOTALS FOR <cube name> [ON '<MDX set expression>'] 

The second statement sent to Analysis Server by OWC is:

     CREATE SESSION      SET [Adventure Works DW].[ {}Pivot13Axis1Set0]     AS      '             {                     { [Dim Promotion].[English Promotion Type].[All] },                     AddCalculatedMembers ([Dim Promotion].[English Promotion     Type].[English Promotion Type].MEMBERS)             }     ' 

This statement creates a set called {}Pivot13Axis1Set0, which contains the members of the hierarchy [Dim Promotion].[English Promotion Type]. Since OWC creates the queries in an automated manner it includes the session id and dynamically creates a name (Pivot13Axis1Set0) that is attached along with the session id. The Analysis Server allows you to create sets and other MDX objects within a specific scope. You can create objects within the scope of the database or within the scope of your connection. In the previous statement OWC creates the set within the scope of the current session and the set will only be available for this specific session. Finally, OWC sends the following query to retrieve and show the members of the hierarchy [Dim Promotion].[English Promotion Type].

     SELECT      NON EMPTY [{}Pivot13Axis1Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS      FROM [Adventure Works DW]     CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR 

Next, drag and drop [Dim Customer].[Marital Status] from the metadata browser to the OWC columns. OWC now sends a series of MDX statements followed by an MDX query to retrieve the members on rows and columns. The following code shows the statements sent by OWC. First OWC drops visual totals followed by creating two sets for the members of the hierarchies selected on rows and columns of the OWC. OWC then queries the members from the created sets and finally drops the earlier set Pivot13Axis1Set0 since OWC has created new sets for members on rows and columns of the OWC.

     Drop visual totals for [Adventure Works DW]     CREATE SESSION      SET [Adventure Works DW].[{}Pivot14Axis0Set0]     AS      '             {                     { [Dim Customer].[Gender].[All] },                     AddCalculatedMembers ([Dim Customer].[Gender].[Gender].MEMBERS)             }      '      SET [Adventure Works DW].[{}Pivot14Axis1Set0]     AS      '             {                     { [Dim Promotion].[English Promotion Type].[All] },                     AddCalculatedMembers ([Dim Promotion].[English Promotion     Type].[English Promotion Type].MEMBERS)             }      '     SELECT      NON EMPTY [{}Pivot14Axis0Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,      NON EMPTY [{}Pivot14Axis1Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS      FROM [Adventure Works DW]     CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR     DROP SET [Adventure Works DW].[{7868741D-072F-458A-8A8D-     EA3FED4A3FA7}Pivot13Axis1Set0] 

Finally, drag and drop the measure [Sales Amount - Fact Internet Sales] to Detail Fields. OWC once again generates statements to drop existing sets and create new sets for members on rows and columns. These sets are used in the query to retrieve the measure along with the properties of the cells. The cell properties returned by the instance of Analysis Services are used by OWC to display values. From the query you can see the properties of formatted values, foreground color, and background colors are being retrieved by OWC. OWC uses the formatted value to display the cell values. The statements and query sent to Analysis Services by OWC are shown below.

     Drop visual totals for [Adventure Works DW]     CREATE SESSION      SET [Adventure Works DW].[{}Pivot15Axis0Set0]     AS      '             {                     { [Dim Customer].[Gender].[All] },                     AddCalculatedMembers ([Dim Customer].[Gender].[Gender].MEMBERS)             }      '      SET [Adventure Works DW].[{}Pivot15Axis1Set0]     AS      '             {                     { [Dim Promotion].[English Promotion Type].[All] },                     AddCalculatedMembers ([Dim Promotion].[English Promotion Type].[English Promotion Type].MEMBERS)             }      '     SELECT      NON EMPTY [{}Pivot15Axis0Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,      NON EMPTY [{}Pivot15Axis1Set0]      DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,      {             [Measures].[Sales Amount - Fact Internet Sales] }      ON PAGES      FROM [Adventure Works DW]     CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR     DROP SET [Adventure Works DW].[{7868741D-072F-458A-8A8D-     EA3FED4A3FA7}Pivot14Axis0Set0]     DROP SET [Adventure Works DW].[{7868741D-072F-458A-8A8D-     EA3FED4A3FA7}Pivot14Axis1Set0] 

If you hover over a particular cell you can see the cell values without formatting, along with the row and column member values that correspond to that cell as shown in Figure 6-12.