Saturday, December 2, 2017

SSAS 2012 – Multi-Dimensional Handout

Topics
  • Dimensional Modeling
  • Building a Cube
  • Dimension Usage
  • Namedset
  • Calculated Member
  • KPI
  • Drill Through
  • Partition
  • Design Aggregation
  • Perspective
  • Translation
  • Browsing the Cube
  • Cube Deployment
  • Cube Processing

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:
      • Dimension Table
      • Fact Table
  • Dimensional Modeling design process consists:
      • Choose the Business Process
      • Declare the grain
      • Identity the Facts
      • Identity the Dimensions
  • 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:
      • Regular
      • Fact
      • Referenced
      • Many to Many
      • Data Mining
  • 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)
  • Set the Display folder name
  • Save the solution

Key Performance Indicator
  • Go to the KPI tab of Cube Designer and click on “New KPI”.
  • Select the required  measure group for KPI.
  • Give the name of the KPI and set the expression for the following:
      • Value Expression
      • Goal Expression
      • Status Expression
      • Trend Expression
  • Set the Display folder name

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


No comments:

Post a Comment