Mindmajix

How to Create Cube in SSAS with Example

CUBE: It is a multidimensional object constructed with dimensions and facts in a particular design for taking multidimensional decisions

CUBE Creation

Steps:

  1. Open BIDS
  2. Create data source
  3. Create data source view
  4. Provide relationship between dimensions and facts
  5. Create a cube
  6. Manipulate the components (action, KPI ….. ETC )
  7. Deploy the cube
  8. Browser cube (or) perform re conclusion (or) unit testing

Real time Example:

Screenshot_82

Class Room Example:

Screenshot_83

Practical Information of CUBE

1. Open BIDS

2. File → new → project → template → analysis → service project → project name

Name: TEXTILE_CUBE

Location: C:\Documents and settings\vinayaka

Solution name: TEXTILE_CUBE

3. View → solution explorer

4. Create two data sources DS_textile 1

DS_textile R with the below procedure

Data sources → RC → New data source → Next → New → SERVER NAME → LOCAL HOST

  • Select  or enter data base name

LOCAL HOST: TEST TLES → OK → NEXT

  • Inherit → Data source name : DS_Test tiles 1 Finish

Like this create another data source DS_Text tiles 2

5. Data source views → RC → new data source view → next →

Relational data sources

DS_text tiles 1 → select → next –>

Create logical relationships by matching columns

           ↓

Next →  selects available objects

RAW MATERIAL LOCATION 1ku >RAW MATERIAL LOCATION 1 KU

  ↓

Next

NAME : DSV_TEXT  → FINISH

6. Go to DSV_CUBE_DB, For taking remaining (Time, Product, Text fact ) tables in to it follow this process DSV_CUBE_DB DESIGN → ADD/REMOVE table

        ↓

Data source: DS_text tiles 2

Available objects: Included object

TIME                                      TIME

PRODUCT            >             PRODUCT

TEXT_FACT                         TEXT FACT

      ↓

CLICK OK

7. Provide relationship between fact table to remaining dimension tables by dragging and drop column mappings from fact table columns to dimension column\

While connecting from fact column to dimension column it displays a message, click ok

The destination table of the newly created relationship had no primary key defined. Would you like to define a logical primary key based on the column used in this relationship?

                   ↓

Yes

After all dimensions column connections

DS_Cube_DB → RC → arrange tables, then it looks like this

Screenshot_84

8. CUBES → RC → NEW CUBE → NEXT →

Using Existing tables → next →

Measure group tables

Screenshot_85

Now various tabs opened and we can see the cube structure as well [ FACTS IN YELLOW, DIMENSIONS IN BLUE COLOR] **

Build → Deploy → TEST the cube.

  Note:  Important options

a) Build → display: If cube structure changed in BIDS to have the same in cube data base, this option useful

b) Build → process: If data source data and structure changes to have the same in cube data base, this option useful

c) Build → build solution: It takes the required set up files in the solution folder

After Deployment

We need to ensure the cubes is deployed successfully to do this follow the bellow two general approaches

a) In BIDS, go to cube browser try to analyze and see the data

b) Source any table data should match the cube data base table data

EX: No of rows in source (Text_fact) data (40 rows)

= SELECT [measures].[text fact count] ON COLUMNS From [ext tiles_cube]

(40 rows)

Fire the above query in the below navigation

SSMS → Analysis services → text tiles_cube → RC → MDX → Query

GENERAL ERRORS IN THE LAB

  1. If we are using other than Dimensions key column values in fact table to the corresponding key, you may get error because of foreign key violation

EX: Assume there is a location table with the bellow locations

“HYD

MUM

USA”

If we are using other than these locations in the fact table then we get errors

USING THE CUBE DATA BASE

There are many ways

A) Analyzing the cube data base data in BIDS browser

B) Using “pivot table” in excel applications to connect and work with cube data base

C) Using reporting tools (cognos, BO,SSRS —–) to generate reports

D) By writing MDX queries in the cube database

E) Using “data ProClarity” tool to analyze the data

ANALYSING IN THE BIDS BROWSER 

  • Take dimensions (or)  facts either row wise (or) column wise and analyze
  • Go to menu bar on the top for filtering the data in the browser

This bar can also be called as “FILTER BAR”

EX: take actual cost, estimated cost on column wise and location ID, product ID, raw material ID on row wise and analyze

To See the Data In The Dimensions / Facts

There are two different ways

a) Go to Data Source View –> Select table –> RC –> Explore Data

b) Go to Cube Structure –> Select table –> RC –> Explore Data

Working With Cube Structure

–> It dispalys cube design, Measure graphs, Measures, Dimensions etc ….

–> We preview the data here for dimensions & facts.

Measure Group

It contains collection of measures.

–> Default measure group table is ‘Fact Table’ of the cube.

–> We can add new measure group tables.

Adding new measure Group:

1. Take measure group table in Data Source view

2.

Screenshot_86

Note: Now 2 Measure group tables are available in the cube.

Measure

–> It is the numerical presentation value in fact table.

–> It describes a business information’s

–> May be simple value or Aggregated value (SUM, SVG, MIN, MAX ETC…..)

Eg: Taking SUM(ACTUAL COST) as a measure to the measure group table

Screenshot_87

Adding CUBE Dimension

1. Add the table (xx) in the data source view.

2. Solution Explorer –> Dimensions –> RC –> New Dimension –> Next –> Use an existing table

Next –>   Data Source view:   DSV_Textfiles

Main Table:   Select the table (xx) key columns xxID

Ensure Attributal Type:

Screenshot_90

3. Go To Cube Structure  –> Dimensions –> Rc –> Add Cube Dimension –> Select the table(xx) –> OK

Edit Dimension

1. We edit dimension to manage attributes and to create hierarchies.

(a) Taking all Attributes to Display in Browser & Analysis:

Select Dimension (eg: TIME) –> RC –> Edit Dimension

Select the required columns (eg: Year in Data Source view, drag and drop in attribute section

            ↓

SAVE –> DEPLOY

Go To Browser –> Reconnect & see all attributes of the

(b) Creating Hierarchies:

–> It is designed to provide top down and bottom up analysis

–> While analyzing we can drill down for deep dive, we can drill up for high level information.

–> hierarchies contains multiple levels and members.

Bad hierarchy should have ‘2’ levels.

Eg:    Country Hierarchy                       Time Hierarchy

COUNTRY                                         YEAR (2009)

STATE                                                MON (JUN)

DISTRICT                                          25 (DATE)

MANDAL

Creating Time Hierarchy:

TIME –> Edit Dimension –> drag year, Qtr, Month one by one to hierarchy section & rename the Hierarchy

Screenshot_88

Save –> Deploy

To see the Hierarchy Usage:

Go to cube browser –> Take Time_Hierarchy in browser pane, take location products dimension attributes, Actual cost, estimate measures and see the Time_Hiearachy drill down.

New Linked Object

–> This wizard is useful to link measure groups and dimensions in another analysis services date base or cube to the current data base or cube.

–> Linked objects appear the same to users as other measure groups and dimensions in the cube .

–> We can also we use this wizard to import KPIs, calculations and Actions.

Eg: importing a calculation (eg: SUMCOST) from another cube (textcube2)

New linked object –> Next –> Analysis services Data sources –> New Data sources –> Next –> Next

Server (or) file name : LOCALHOST

Screenshot_89

Now the calculations are imported, deploy and use

Enroll for Live Instructor Led Online MSBI Training


0 Responses on How to Create Cube in SSAS with Example"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.
Course Adviser

Fill your details, course adviser will reach you.