Multidimensional Modeling (IV)

enabling SQL Server and analysisservice Services

Open VisualStudioto create analysisservice Multidimensional Data mining projects and solutions.

Create a data source :adventureworksdw2008r2

To create a data source view: DimCustomer,DimDate,DimGeography,dimproduct, factinternetsales

Click on a table or column in the diagram, and the right-click property can change the corresponding name.

Select SalesTerritoryKeyin the Sales table, drag the mouse down to SalesTerritoryKeyin the Region table, create a sales table and The foreign key relationship for the region table .

Create dimension : Right-click dimension , create dimension ...

Select Create dimension with existing table , next select Main Table , key column: datekey ,fulldatealternatekey ,englishmonthname ,calendarqurter ,calendaryear ,calendarsemester

Create Cube : Right-click cube , new cube ...

using an existing table , select the data source view AdventureWorks dw2008r2, click Suggestions , tick the sales table.

In the sales measure , remove the 4 columns:promotionkey ,currencykey , SalesTerritory key,revisionnumber (promo keyword, currency keyword, sales area keyword, revision number)

By default, the wizard chooses to use all numeric columns in the fact table that are not linked to the dimension as measures. But these four columns are not actual measures. The first three columns are the key values that connect the fact table with the dimension tables that are not used in the initial version of this cube.

Next, Select the existing dimension , select the date dimension

Next, Select the new Dimension , select the product, customer, region three dimensions, the sale is removed tick.

Next, Finish!

When you are done, you can see the metrics and dimensions of the dataset. If the dimension is not available, you can right-click to add a dimension. Every step of the operation, it is customary to save all.

Add attributes to Dimensions: Add attributes like customer dimensions, region dimensions, and Product Dimensions .

Double-click the customer in the dimension . Dim. In the Properties pane, customerkey and geographykey("Customer keyword" and "Geography keyword") properties already exist.

Now drag the following columns from the Customer table to the Properties pane.

BirthDate、MaritalStatus、Gender、EmailAddress、Yearlyincome、Totalchildren、Numberchildrenathome、englisheducation、englishoccupation、Houseownerflag、numbercarsowned、Phone、Datefirstpurchase、commutedistance

Similarly, double-click the region dimension to add fields from the region table to the Region dimension attribute: city,stateprovincename ,englishcountryregionname ,PostalCode

Similarly, double-clickDimension of Product, add the fields from the product table to the Product dimension properties:StandardCost、Color、Safetystocklevel、ReorderPoint、ListPrice、Size、Sizerange、Weight、DaysToManufacture、ProductLine、DealerPrice、Class、Style、modelname、StartDate、EndDate、Status

Similarly, double-click the date dimension to add the fields from the date table to the date dimension attribute:datekey ,fulldatealternatekey ,englishmonthname ,calendarqurter ,calendaryear ,calendarsemester

Now look at the cube:AdventureWorks dw2008r2.cube, the attributes in the dimension have been added! ~

deployment Project : Right-click the Cube Project and tap properties to open the Properties page .

Set the output information and the server database information for the deployment.

deployment Project : Right-click the Cube Project and tap deploy .

Deployment Complete!

Reference: Multidimensional Modeling (Adventure Works Tutorial)

Multidimensional Modeling (IV)