CUBE: It is a multidimensional object constructed with dimensions and facts in a particular design for taking multidimensional decisions
Enhance your IT skills and proficiency by taking up the SSAS Online Certification Training
Class Room Example:
[Related Article: SSAS Interview Questions]
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]
Fire the above query in the below navigation
SSMS → Analysis services → text tiles_cube → RC → MDX → Query
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
If we are using other than these locations in the fact table then we get errors
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
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
[Related Topic: Create a Dimension using Dimension Wizard in SSAS]
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
–> It displays cube design, Measure graphs, Measures, Dimensions, etc ….
–> We preview the data here for dimensions & facts.
It contains a collection of measures.
–> Default measure group table is ‘Fact Table’ of the cube.
–> We can add new measure group tables.
1. Take measure group table in Data Source view
Note: Now 2 Measure group tables are available in the cube.
[Related Topic: Learn to Define Perspectives and Translations in SSAS]
–> 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
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:
3. Go To Cube Structure –> Dimensions –> Rc –> Add Cube Dimension –> Select the table(xx) –> OK
1. We edit dimensions to manage attributes and to create hierarchies.
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)
TIME –> Edit Dimension –> drag year, Qtr, Month one by one to hierarchy section & rename the Hierarchy
Save –> Deploy
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]
–> 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
Now the calculations are imported, deploy and use?
Explore SSAS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Technical Content Writer