SQLCircuit: 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:\Users\xyz\Documents\Visual Studio 2010\Projects\SSAS\SSAS\bin\SSAS.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

Pages