Defining and Deploying a Cube (SQL Server Video)

Video Summary

This video demonstrates how to define and deploy an OLAP cube using Analysis Services.

Video Transcript

Hi, my name is Mary Brennan. I’m a technical writer for Microsoft SQL Server.

In this video, I’ll show you how to define and deploy an OLAP cube using Analysis Services.

First, I’ll use the Dimension Wizard to define a Date dimension:

• Right-click Dimensions, and select New Dimension.

  We’ll use an existing table.

• Select Date.

• Select the check boxes next to the Date Key, Full Date Alternate Key, English Month Name, Calendar Quarter, Calendar Year, and Calendar Semester attributes.

• I’ll set the attribute type of the Full Date Alternate Key from Regular to Date, the attribute type of English Month Name to Month, Calendar Quarter to Quarter, Calendar Year to Year, and Calendar Semester to Half Year.

  Here, you can see the Date dimension and its attributes.

To define measure groups and dimensions for a cube you use the Cube Wizard:

• Right-click Cubes and select New Cube.

  We’ll use existing tables.

• Click Suggest to have the cube wizard suggest tables to create measure groups from.

  It correctly selects InternetSales which is the measure group table. Measure group tables are sometimes also called fact tables. They contain measures such as the number of units sold. By default, the wizard selects all numeric columns in the fact table that are not linked to dimensions.

• Four of these columns are not actual measures so clear the check boxes for: PromotionKey, Currency Key, Sales Territory Key, and Revision Number.

• Select the Date dimension that you created earlier.

• Select the new dimensions to be created: Customer, Geography, and Product.

  I’ll call the cube, "Analysis Services Tutorial." You can see the InternetSales measure group and its measures. You can also see the Date, Customer, and Product dimensions. The cube appears in the Cubes folder, and the database dimensions appear in the Dimensions folder.

• Change the zoom level to 50 percent.

  The fact table is yellow and the dimension tables are blue.

To add attributes to the Customer and Product dimensions, we’ll use Dimension Designer:

• Open Dimension Designer for the Customer dimension.

  The Customer Key and Geography Key attributes were already created by the Cube Wizard.

• Use the zoom icon to view the tables at 100 percent.

• I’ll drag columns from the Customer table to the Attributes pane in order to create attributes. Hold down the control key to select multiple items.

• Next, I’ll drag columns from the Geography table to the Attributes pane to create attributes.

Next, we’ll add attributes to the Product dimension:

• Open Dimension Designer for the Product dimension.

  The Product Key attribute was already created by the Cube Wizard.

• I’ll drag columns from the Products table to the Attributes pane to create new attributes. I hold down the control key to select multiple items.

After you have defined a cube, you can review the results by using Cube Designer:

• Open the Cube Designer.

• Expand the Internet Sales measure group to reveal the defined measures.

  In the Dimensions pane, we can see the cube dimensions that are in the Analysis Services Tutorial cube. Although only three dimensions were created at the database level, there are five cube dimensions in the cube because the Date dimension is used as the basis for three role-playing dimensions. By reusing a single database dimension for multiple cube dimensions, Analysis Services simplifies dimension management, uses less disk space, and reduces overall processing time.

• Expand Customer, and then click Edit Customer.

  Dimension Designer contains these tabs: Dimension Structure, Attribute Relationships, Translations, and Browser. The Dimension Structure tab includes three panes: Attributes, Hierarchies, and Data Source View. The attributes that the dimension contains appear in the Attributes pane.

• Switch to Cube Designer.

• Click the Dimension Usage tab.

  You can see the cube dimensions used by the Internet Sales measure group. Also, you can define the type of relationship between each dimension and each measure group.

• 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. You can define additional partitions, storage settings, and writeback settings on the Partitions tab.

• Click the Browser tab.

  The cube cannot be browsed because it has not been deployed yet. 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.

• Click View Code.

  The XML code for the Analysis Services Tutorial cube is displayed. This is the actual code that is used to create the cube in an instance of Analysis Services during deployment.

To view cube and dimension data for the cube, you must deploy the project and then process the cube and its dimensions. Deploying a project creates the objects in an instance of Analysis Services. Processing copies the data from the underlying data sources into the cube. 

Let’s review the deployment properties of the project. This dialog box displays the properties of the Active(Development) configuration. The value for the Output Path property specifies where the XMLA deployment scripts are saved.

• Click Deploy.

  Business Intelligence Development Studio builds and then deploys the Analysis Services Tutorial project to the specified instance of Analysis Services.

I review the contents of the Output window and the Deployment Progress window to make sure that the cube was built, deployed, and processed without errors. We have successfully deployed the Analysis Services Tutorial cube to the local instance of Analysis Services, and then processed the deployed cube.

After you deploy a cube, the cube data is viewable on the Browser tab in Cube Designer, and the dimension data is viewable on the Browser tab in Dimension Designer:

• Switch to Dimension Designer for the Product dimension.

• Click the Browser tab to display the All member of the Product Key attribute hierarchy.

  Later, we’ll define a user hierarchy for the Product dimension that will let us browse the dimension.

• Switch to Cube Designer.

• Select the Browser tab, and then click the reconnect icon on the toolbar of the designer.

  The left pane of the designer shows the objects in the Analysis Services Tutorial cube. On the right side of the Browser tab there are two panes. The upper pane is the Filter pane and the lower pane is the Data pane.

Thanks for watching this video. I hope you found it helpful.