SSAS 2012 – Multi-Dimensional Handout

Topics

Dimensional Modeling

Dimensional Modeling is the name of a set of techniques
and concepts used in data warehouse design.
In this model, all data is contained in two types of
tables:

Create a denormalized
relational model made of tables with attributes relationships defined by
keys and foreign keys.
Create dimension tables which contain
all the textual attributes with their key.
Create Fact tables that contains
Measures/Fact with key columns.
Make relationship between
Fact and Dimension in the form of STAR schema.
If required, we can use Snow flake schema.

Building a Cube

Create a new SQL Server
Analysis Project
Right click on the Data
source folder in solution explorerè Click
New Data source. Specify Source server name and database.
Right click on the Data
Source View folderè Click New Data Source Viewè Select
the required fact table and their
associated dimension tables for the cube.
Right click on the Cube
folder è Click New Cubeè It will start the cube wizard.
Select Existing tables in “Creation Method” window so
that it will refer Data source view tables.
Select your Fact
table/tables and their associated measures.
Select the Dimensions for
your cube.
Click finish to complete
wizard and save the cube with suitable name.

Dimension Usage

In the Cube Designer, go
to the Dimension Usage, it will show us relationship between fact tables
and dimension tables.
For changing relationship
between any fact and dimension table, click on the browse button of any
fact table available in measure group.
Following are the
relationship types available:

Select the Relationship
type that you want to implement between fact and dimension.
Make the relationship by
using key column of fact/dimension table or any intermediate table.

Calculated Member

Go to the Calculation tab
in the Cube Designer
Click on “New Calculated
Member”è Give the name of the calculated member and select its parent
hierarchy.
Give the expression for
Calculated member e.g.
[Measures].[Sales
Amount]-[Measures].[Tax Amt]
Set the format string to
display value in specific format.
Set the folder name for
the Calculated member.
Color and Font expression
can also be added.

Named Set

Go to the Calculation tab
in the Cube Designer
Click on “New Named Set”è Give the name of the Named Set and select its parent hierarchy.
Set the expression for
Named Set e.g.

Exists([Reseller].[Reseller
Name].[Reseller Name].Members)

Key Performance Indicator

Drill Through Action

Go to the Action Tab in
the Cube Designer
Click on New Drill
through actionè give the name of the Drill through and set its measure group.
Condition property is optional if we want , we can
add some filtering condition e.g [Measures].[Sales Amount] > 0
Set the Drill through
Columns, Select columns from Fact as well dimensions
Maximum rows: we can set
limit for the records
Set the caption for the
Drill through
Caption in MDX: If this
property is True, we need to write Captions in double quotes.

Partition

Go to the partition tab of the Cube designer.
By default, Each measure group has its own
partition. If you have single fact
then complete cube looks like a single partition.
If we want to divide the
default partitions of measure
groups then click on the source in respective partition and select Binding
Type as “Query Binding” .
It will open the Query
for the partition. Add filter to this partition by using where clause. Click ok
Then click on new
Partition, it will start the partition wizardè select
your fact tableè then specify the query for the partition.
Set the Processing
location & Storage location & give the name of Partition.
If you want to design
aggregation, we can select “Design Aggregation for now” option and click
finish.

Design Aggregation

For creating
aggregations, go to the aggregation tab of cube designer.
Right click on your
measure groupè click Design Aggregation, It will start the design aggregation
wizard.
Set the Include and
exclude the attributes for the aggregation in ” Review Aggregation
Window”.
Set the Aggregation
option – how much performance gain should reach or how much storage space
extend.
Now Save the aggregation.
We can deploy and process
the aggregation also.

Perspective

Go to the Perspective tab
of the Cube designer.
Click “New Perspective”
and give some name for perspective e.g. SalesDeparment or HRDepartment.
Select your required
Dimensions and Measures that we want to include in our perspective
Save it.
Once we process the cube,
we can see this perspective.

Translation

Go to the Translation tab
of the Cube Designer and click new translationè select
the language in which we want to translate the attributes of the cube.
It will add additional
column for writing translation for the attributes.
Save it.
Process the cube to see
the translation.

Browse the Cube

Go to the Browse tab of
the Cube Designer.
Drag and drop the
Dimension attributes/ measures in the cube for which we want to see the
data.
If we want to add filter
then drag and drop the attribute to filter and set the condition. It will
give filtered data.
We can browse the Cube
from Excel also. Open the excelè Data tabè From Other Sourcesè Analysis Servicesè Give Analysis service instance nameè Select
Analysis DBè Select Cubeè Click finish. It will open cube in the form of Pivot table.
Select the dimension
attributes/Measures for which we want to see the data.

Cube Deployment

For Deploying the Cube,
go to the SSAS Solutionè Right click on the top node è
Properties èDeployment è Give the server name and database for deployment.
Right click on top node
of the project and click on Deploy. It will deploy the cube to respective
Server and database.
We can also deploy Cube
using Analysis Service Deployment wizard that deploys the cube from Cube
Solution.
Go to All Programsè SQL Server 2012è Analysis Service è Deployment Wizard
Give the path for
Analysis Database created in bin folder of your solution e.g.
C:UsersxyzDocumentsVisual Studio 2010ProjectsSSASSSASbinSSAS.asdatabase
Give server name and
Database name for deployment
It will give various
options for deployment. E.g. Patitions only, Dimesion only etc.

Processing of Cube

For processing the Cube
in SSAS solution, right click on the Cubeè Click
on Processè Click on Run to process the cube.
We can process the Cube
from Analysis Service instance alsoè Connect
to the serverè go to Analysis Service databaseè select
cubeè Right Click on the Cube and process.
There are various option
for the Cube Processing:

Process Full : Does full rebuild
Process Clear : Empties object
Process Default : Builds only unprocessed
objects
Process Data : Discards contents and reloads
data (no indexes)
Process Incremental : Loads data using filter
Process Indexes : Preserves data and appends
indexes
Process Update : Affects dimensions only,
Preserves data, Performs incremental update
(new, changed, or deleted members)
Process Add : Affects dimensions only, Adds new
members only
Process Script Cache : Evaluates and persists
MDX script in cube

Related Posts