Skip to main content

How Do I: Defining and Deploying a Cube?

Rate:  

About This Video:

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

This video available with closed captions. To view closed captions, click CC on the video control bar.

Transcript

Hi, my name's 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 and then select the check boxes next to the Date Key, Full Date Alternate Key, English Month Name, Calendar Quarter, Calendar Year, and Calendar Semester.

I'll set the attribute type of the Full Date Alternate Key to Date.

I'll set the attribute type of English Month Name to Month, the attribute type of Calendar Quarter to Quarter, the attribute type of Calendar Year to Year, and the attribute type of 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 a measure group table.

Measure group tables are also sometimes 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 Promotion Key, 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 Internet Sales 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%.

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

We'll use Dimension Designer to add attributes to the Customer and Product dimensions.

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%.

I'll drag the columns from the Customer Table to the Attributes pane in order to create attributes.

I 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.

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 into the Attributes pane to create new attributes.

I hold down the Control key to select multiple items.

After you've 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 disc 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 the Cube Designer.

Click the Dimension Usage tab.

You can see the cube dimension use by the Internet Sales measure group.

Also, you can define the type of relationship between each dimension and measure group.

On the Partitions tab, the Cube Wizard defined a single partition for the cube.

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.

Deploying an Analysis Services 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 properties 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.

After you deploy a cube, the cube data is viewable on the Browser tab in the 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 the Cube Designer.

Select the Browser tab and then click the Reconnect icon.

The left pane of the Designer shows the objects in the 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.

Thank you for watching.

I hope you found it helpful.

 

Presented by: Mary Brennan
Length: 08 minutes 08 seconds

 

Downloads

Video: WMV(Zip) | MP4 | WMV