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

 

Enhance your IT skills and proficiency by taking up the SSAS Online Certification Training

Real-time Example:
 Real-time Example
Class Room Example:
 Class Room Example

Practical Information of CUBE

  1. Open BIDS
    2. File → new → project → template → analysis → service project → name
    Name: TEXTILE_CUBE
    Location: C:Documents and settingsvinayaka
    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 → LOCALHOST
    Select  or enter a database name
    LOCALHOST: 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 into 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
     DS_Cube_DB → RC →
    8. CUBES → RC → NEW CUBE → NEXT →
    Using Existing tables → next →
    Measure group tables
     Measure group tables
    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 database, this option useful
    b) Build → process: If data source data and structure changes to have the same in cube database, this option useful
    c) Build → build solution: It takes the required set up files in the solution folder

[Related Article: SSAS Interview Questions]

 

After Deployment

We need to ensure the cubes is deployed successfully to do this follow the below two general approaches
a) In BIDS, go to cube browser try to analyze and see the data
b) Source any table data that should match the cube database 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

If we are using other than Dimensions key column values in the fact table to the corresponding key, you may get an error because of a foreign key violation
EX: Assume there is a location table with the below locations
“HYD
MUM
USA”
If we are using other than these locations in the fact table then we get errors

USING THE CUBE DATABASE

There are many ways
A) Analyzing the cube database data in the BIDS browser
B) Using “pivot table” in excel applications to connect and work with cube database
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 the 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 displays cube design, Measure graphs, Measures, Dimensions, etc ….
–> We preview the data here for dimensions & facts.

Measure Group

It contains a 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

 Adding new measure Group
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
–> Maybe simple value or Aggregated value (SUM, SVG, MIN, MAX ETC…..)
Eg: Taking SUM(ACTUAL COST) as a measure to the measure group table
 Taking SUM(ACTUAL COST)

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:
 Attributal Type
3. Go To Cube Structure  –> Dimensions –> Rc –> Add Cube Dimension –> Select the table(xx) –> OK


Edit Dimension

1. We edit dimensions 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 the 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 contain multiple levels and members.
The bad hierarchy should have ‘2’ levels.
Eg:    Country Hierarchy                       Time Hierarchy
COUNTRY                                         YEAR (2009)
STATE                                                MON (JUN)
DISTRICT                                          25 (DATE)
MANDAL

MindMajix YouTube Channel

Creating Time Hierarchy:

TIME –> Edit Dimension –> drag year, Qtr, Month one by one to hierarchy section & rename the Hierarchy
 Creating Time Hierarchy
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.

[Related Topic: SSAS Processing]

New Linked Object

–> This wizard is used to link measure groups and dimensions in another analysis service date base or cube to the current database 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) filename: LOCALHOST
 Importing a calculation
Now the calculations are imported, deploy and use?                    

Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

 

List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSRS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

 

Job Support Program

Online Work Support for your on-job roles.

jobservice

Our work-support plans provide precise options as per your project tasks. Whether you are a newbie or an experienced professional seeking assistance in completing project tasks, we are here with the following plans to meet your custom needs:

  • Pay Per Hour
  • Pay Per Week
  • Monthly
Learn MoreGet Job Support
Course Schedule
NameDates
MSBI TrainingDec 07 to Dec 22View Details
MSBI TrainingDec 10 to Dec 25View Details
MSBI TrainingDec 14 to Dec 29View Details
MSBI TrainingDec 17 to Jan 01View Details
Last updated: 01 May 2023
About Author

 

Technical Content Writer

read less
  1. Share:
MSBI Articles