BI's SSAS full combat Tutorial 3--Create the first cube. docx

We have completed the preparation of the data source, and now we start to create the first cube.

article outline

• To create a cube using the Cube Wizard
• Summarize the Cube Designer introduction
• Refinement of dimensions
• Summarize

I. Creating a cube using the wizard

In Analysis Services, you can build cubes in 3 ways: top-down, bottom-up, or based on an empty cube.

Generally we build from the existing relational database from the bottom up.

In a bottom-up approach, a DSV is required as the basis for building a cube.

The cube in as is comprised of two parts:

1. One or more measure groups whose data comes from one or more fact tables in the relational data source. Typically, each fact table corresponds to a measure group, and the measure group contains one or more measures (for example, sales, cost, product count sold)

2. The cube also contains one or more dimensions (such as product and time) from the relational dimension table.

When building a cube, you specify the fact tables and dimension tables that you want to use. Each cube must contain at least one fact table that determines the contents of the cube.

When you use the wizard later, you can see that if you do not select a measure group, you will be prompted: at least one measure group table must is selected.

Before you build a cube, you must create a dimension based on the dimension table.

Let's briefly summarize: cube= measure group + dimension

The Cube Wizard packs together all the steps involved in creating a cube, creating a simple sequential process that creates the steps:

1. Right-click Cubes à New Cube ...

Click Next to enter the next step

Select the default value, next

2. In the Select Measure Group table page, select FactInternetSales and FactResellerSales as the measure group table, and the next step

3. On the Select Measures page, you can select a specific column from the measure group table as the measure value.

By default, all columns in the measure group table are selected (except for key columns)

Following the default selection, the next step

4. On the Select New dimensions page, the Cube Wizard displays the possible dimensions and their attributes. By default, the Cube Wizard contains key attributes in each dimension, and key attributes are highlighted on this page, such as. Uncheck the fact Internet Sales and fact Reseller sales dimension, next.

5. Follow the default name and click Finish to finish creating the cube.

When the wizard finishes, the dimension is created accordingly and appears in the Solution Explorer.

second, the Cube Designer introduction

The Adventure Works DW Cube opens in Cube Designer, such as

As you can see, the Cube Designer has multiple tabs that allow you to perform various operations on the cube. The default page that opens after the Cube Wizard finishes is cube Structure

Other pages include: Dimension Usage, Caculation, KPIs, Actions, partitions, aggregations, perspectives, translations and browser

The cube structure page consists of 3 panes: Measures (Measure), Dimensions (dimension), and data source view.

Measure groups and measures can be added or modified in the measures pane.

Use the dimensions pane to add or modify the dimensions of a cube.

The Data Source View pane displays the fact and dimension tables used in the cube and distinguishes them with different colors (yellow means fact table, blue indicates dimension table)

Right-click inside each pane to see the various actions that can be done in each pane.

third, the dimension of refinement

The Cube Wizard adds only some of the most basic features to the dimension that you create. To analyze the data in a cube, further refinement of these dimensions is required, and later articles we specialize in a refinement of the dimension, let's begin with a dimension refinement example.

1. In Solution Explorer, double-click the Dim Date.dim Dimension.

The Dim date dimension opens in Dimension Designer.

Dimension Structure page contains 3 panes: Attributes, hierarchies, and DSV

2. In the DSV pane, select all columns except DateKey in the Dimdate table and drag to the Attributes pane. This action creates an attribute for each column in the Dimdate table.

3. Rename the key attribute from date key to date

4. Drag and drop the fiscal quarter feature from the attributes pane to the Hierarchies pane. The operation creates a hierarchy, called hierarchy.

Drag and drop the month number of year attribute below the Fisical quarter attribute in the Hierarchies pane. This action creates another level in the hierarchy.

Similarly, drag and drop the date attribute below the month number of the year attribute.

Right-click the hierarchy and select Rename, rename the hierarchy to fiscal quarter–month number of year, and the final effect is as follows:

We also make some adjustments to other dimensions.

Dim Currency.dim

Dim Customer.dim

Rename key attribute from custom key to custom

Drag and drop all columns from the DimCustomer table (except Custom key) in the DSV to the attributes pane

Dim Sales Territory.dim

Rename the key attribute from sales Territory key to sales Territory

Drag and drop all columns except SalesTerritoryKey and image in the DimSalesTerritory table from the DSV pane to the Attributes pane.

Dim Product.dim

Renaming key attributes from product key to product

Drag and drop all columns except ProductKey and LargePhoto in the DimProduct table from the DSV to the Attributes pane.

Dim Promotion.dim

Rename key attribute from Promotion key to Promotion

Drag and drop all columns except Promotionkey in the DimPromotion table from the DSV pane to the Attributes pane.

Drag and drop the English Promotion category feature from the Attributes pane to the Hierarchies pane. The operation creates a new hierarchy.

Drag the discount pct attribute from the attributes pane and drop it under the English Promotion category feature in the Hierarchies pane. The operation creates a new level in the hierarchy.

Drag the promotion attribute from the Attributes pane and drop it below the discount pct level in the Hierarchies pane.

Rename the hierarchy to 中文版 Promotion Category–discount Pct, as

Dim Reseller.dim

Rename key attribute from reseller key to Reseller

Drag and drop all columns except Resellerkey in the DimReseller table from the DSV pane to the Attributes pane.

Drag and drop the annual revenue attribute from the attributes pane to the Hierarchies pane. The change operation will create a new hierarchy.

Drag the number employees attribute from the Attributes pane and drop it under the annual revenue attribute in the hierarchies pane. The operation will create a new level that becomes number Employees

Drag the reseller attribute from the Attributes pane and drop it below the number employees level in the hierarchies pane

Rename the hierarchy to annual revenue–number of Employees, as

Dim Employee.dim

This dimension has the 3 key attributes created by the Cube Wizard, and in contrast, other dimensions create a key attribute. This is because the wizard detects a parent-child relationship within the Dim employee dimension (Follow-up tutorial introduction)

Rename Employee key attribute to employee

Drag and drop all columns except EmployeeKey, Parentemployeekey,sales Territorykey, and Employeephoto in the DimEmployee table from the DSV pane to the Attributes pane.

Drag and drop the department name attribute from the attributes pane to the Hierarchies pane. The operation creates a new hierarchy.

Drag and drop the title attribute from the Attributes pane to the department name hierarchy in the Hierarchies pane.

Drag the employee attribute from the Attributes pane and drop it into the hierarchies pane under the title attribute.

Rename hierarchy to Department Name-title

Iv. Summary

Now that you have successfully created a cube with SSDT and refined the dimensions. The follow-up will continue to expand around this example.

Finally, we summarize the content of this article:

1. You need to understand the order in which the wizard creates the cube:

Select Creation Meathod (use existing tables)

-->select Measure Group Tables

-->select Measures

-->select New Dimensions

-->completing the Wizard

2. Structure of the Cube Designer:

Contains multiple tabs, each tab containing multiple panes, familiar with the tabs and panes.

3. Preliminary understanding of some of the operations of dimension refinement (follow-up will have thematic dimension operations)

The next article will show you how to deploy this project to SSAS and perform a simple analysis.

Wish Learning Progress:)

Starting blog Garden by Miroyuan, reprinted article must be in the article page obvious location to the author and the original link , otherwise reserves the right to pursue legal responsibility.

BI's SSAS full combat Tutorial 3--Create the first cube. docx