SSAS (SQL Server Analysis Services)

Steps to Setup (or) Create a Data cube

The main usage of SSAS is to build OLAPS / online analytical processing. The basic idea To create OLAP is to trade increased Storage space for Speed of Querying. OLAP does This by pre-calculating and storing aggregates when you identify the data that you want To store in an OLAP Database, analysis Services analyze it in advance.

The Basic Concepts of OLAP

Cube :

 The Basic Unit of Storage and analysis Services is the cube.

A cube is a collection of data that is been aggregated to allow Queries to return data quickly

Cubes are ordered into Dimensions and measures. Dimensions come from dimension Table measures that come from the next table.

Dimension Table :

It contains hierarchical data by Which you would like to summarize.

For example :

An order table that you might group by year, month, week, and day of receipt.

Dimension:

Each cube has one (or) more dimensions. Each dimension based on one (or) More dimension tables.

Fast table :

       If contains basic information that you want to summarize.

Note:

     Any table that you have used with SUM, the Average function in a total query is a good bet To be a fact table.

 

 

Measure:

Each cube will Contain one (or) more measures. Measures is a key value to analysis (or) to evaluate the performance of the organization.

Schema:

   There are two types of schema in OLAP

1. Star schema, Every dimension table related directly to the fact table.

In snowflake schema, Some dimension tables are related indirectly to the fact table.

Steps to Configure (or) Create a Data cube :

To build a new data cube using the business intelligence development studio.We need to perform the following steps:

1. Create a new analysis Service project.
2. Define a Data source.
3. Define a Data Source view.
4. Invoke a Q wizard.

At TekSlate, we offer resources that help you in learning various IT courses. We avail both written
 material and demo video tutorials. To gain in-depth knowledge and be on par with practical
 experience, then explore MSBI Training.

Create a new analysis Service project :

 Open Business Intelligence Development Studio          ------------>

Select File Menu         ------------>

New         ------------>

Project         ------------>

Select project Analysis Services         ------------>

Change project name and location         ------------>

Click ok

Define a Data Source:

In Solution Explorer select Data Source         ------------>

Right-click and select ‘New Data source’         ------------>

Welcome to the Data Source Wizard         ------------>

Click Next         ------------>

Click New to create a new connection Manager and set,         ------------>

Server name – localhost         ------------>

Database name – Adventure Works D W         ------------>

Click ok         ------------>

Click Next         ------------>

Check default Radio button         ------------>

Click next         ------------>

Data Source Name - product details         ------------>

Click Finish

Define a Data Source view:

In Solution Explorer select Data Source         ------------>

Right-click and select New Data source View         ------------>

In Welcome to the Data Source Wizard         ------------>

Click Next         ------------>

Select the available Relational Data source [Product details]         ------------>

Click Next         ------------>

Fact Resellers Sales table from available object list         ------------>

Click Add Related tables to include all the tables Which are Referenced   Sales table         ------------>

Click Next         ------------>

Set,         ------------>

Name - Fact Resellers View         ------------>

Click Finish         ------------>

Cube:

In Solution Explorer select cube         ------------>

Right-click and select New cube         ------------>

In Welcome to the Cube Wizard, click Next         ------------>

In the Select Build method page, Check to Build the cube using a data source radio button and also Make                Sure that the Auto build Checkbox is checked. To create attributes and hierarchy’s from The Specified dimension table.         ------------>

Click Next         ------------>

In the Select Data source view page, Select the available Data source view named as product Information [Select the data source view that will provide the data for the cube]         ------------>

Click Next         ------------>

Click Next         ------------>

In identify facts and dimension tables Page select a dimension table from the drop-down list         ------------>

Click next         ------------>

In the Select time period page select any Column [time Key] to create hierarchy’s on-time dimension         ------------>

Click next         ------------>

Click Next         ------------>

Click Next         ------------>

Click Finish

Process the cube :

Select project [ In Solution Explorer]         ------------>

Right-click and select process option         ------------>

Click Yes         ------------>

In-Process Database Wizard, click Run         ------------>

Click close         ------------>

Open SQL Server Management Studio (SSMS)         ------------>

Click connect and set,         ------------>

Server type - Analysis Server         ------------>

Server Name – Localhost         ------------>

Click connect

Exploring the Data cube:

In BIDS Create a Cube and process (or) deploy it to Server. BIDS includes a Built-in cube Browser that lets you explore the data in any cube that has been deployed (or) Processed. To open the cube Browser. In Solution Explorer double click on cube.[Product information ]

Select browse tab         ------------>

Drag and Drop the column on columns (or) Row (or) Filter area.

Introduction to MDX

Multi Dimension expression (MDX) is a language created to allow the users to Work With multidimensional data in Microsoft SQL Server Analysis Service. It Works as a Query language with OLAP (Online Analytical Processing) Cubes. We can also use It to Create calculated members or use the various function provided which can be Used to query data.

Differences in SQL in MDX

If you are a little familiar with SQL (Structured Query Language), You may fell there Is some similarity with MDX in some aspects. The SELECT clause, FROM clause, and WHILE clause are similar to SQL and MDX. MDX Provides various ways with Which A cube can be queried it provides different functions with MDX we can even create, Modify and delete cubes of require

Syntax of MDX Query (Multi Dimension Expression):

 

WITH

MEMBER exp 1 and exp 2

Select

{ - (Set Operator)

<axis – Specification 1>,

<axis – Specification 2>,

} ON Columns

<axis – Specification > on Rows

FROM [ cube Name]

Where [Slicer]

Axis Specification :

A collection of members from different dimensions Organized as a set of types [columns]

Members :

Select dimension attribute that are included in output cube.

Slicer :

It is a filter that Selects cells in the output cube.

Set Operator [{}] :-

The set operator is used to display a collection of members On a single axis. In MDX Set Operator is {} (or) Generate ()

NOTE:

Generate MDX Function is a set of members

Functions in MDX:

Order function:

It is used to sort the data either in ascending (or) descending order.

Top Count :

It is used to display a specified number of rows from the top.

For example:

Top count   ([Dim product].[English product Name]. all member, 5) or rows.

Bottom Count :

It is used to display a specified no. of Records from the Bottom.

For example :

Bottom count   ([Dim product].[English product Name]. all member, 5)

Comma Operator:

it is used to construct a set by Enumerating types. For example :

Select{

[measures].[order Quantity],

[measures].[order Amount],

[measures].[order Amount],

} on columns

• Colen Operator:

It is used to specify a range with discrete (or) Continuous data.

For example:

Where [Fact Internet Sales – Due Data] . [Time key] & [i] :

[Fact Internet Sales – Due Data] . [Time key] & [15]

.Dot Operator :

It is used to return a set of all the numbers from the specified dimensions

 

For ex:

[Dim Product] .[English Product Name]. all members on rows

With operator:

It is used to specify Calculated measures

fore x:-

With Member [Measures].[total amount] as

[Measures].[sales amount] *

[Measures] .[Order Quantity]

NON EMPTY :

It is used to Remove null Records either from columns (or) Rows.

For ex: Select nonempty

{[Measures] .[order Quantity]} on columns non empty

[Dim product] . [English product Name] all members on rows.

Comments :

- - ==>

/* * / ==> for multi

MDX notation for schema:  

[Dim time].[Fiscal].[2011].[Q 1].[April]

[Dim table] – Dimension

[Dim Time].[Fiscal]   -   Hierarchy

[Dim Time].[Fiscal].[2011].[Q 1] - Level

[Dim Time].[Fiscal].[2011].[Q 1].[April]   - Member Select non empty

{

[measures]. [order Quantity]. [sales amount]

} on columns

Non Empty

[Dim Product] . [English product Name] . all members on rows

From [product information] the above MDX Query Returns all the English product

Names on Rows and its Corresponding order Quantity and Sales amount displayed on Rows

From the specified cube called product information and it Return non Empty (Not Null) Records

WITH

MEMBER   [Measures] .[total amount] as

[Measures] .[sales amount] *

[Measures] .[order Quantity]

Format – String   = “$ # #, # # #. # #”

Select non Empty

{

[Measures] .[ order Quantity] , [sales amount], [total Amount ]

} on columns

Non Empty

[Dim Product]. [English Product Name] . all members on rows

From [Product information]

The above MDX Query New measures is calculated [measures].[total amount]

And its formatted as with two decimal digits .

Select non Empty

{

[Measures] .[ order Quantity] , [sales amount], [total Amount ]

} on columns ,

Non Empty

[Dim Product]. [English Product Name] . all members on rows

From [Product information]

In the above MDX Query Returns all the English product names on rows and its corresponding order Quantity and sales amount based on the Where clause [time key start from 1 to 15 th]

 

                   Format – String : It is used to format the measures in WITH Operator [format the calculated measures]

For ex : -

Format – String   = “$ # #, # # #. # #”

Format – String   n = “Currency”

Format – String   1 = “Percent”

Calculations

KPI (Key Performance Indication)

How to Calculate calculated measures using BIDA Wizard :

Open BIDS      ------------>

Create a new cube    ------------>

In cube , Select calculations tab    ------------>

Click on New Calculated member and set

Name - [total amount]

Parent Hierarchy   - select MEASURES from drop down list

Expression - Define the following Expression by drag and drop the

Measures from the measures Section (from fact table)

[measures] . [Sales Amount ]

[measures] . [tax Amount]    ------------>

Format String   - Select “Currency” option

Color – click   and select any color

Back ground Color   - Click and Select any color    ------------>

Click ok    ------------>

In Calculating tab Click process to deploy (or) process in to analysis Services Server

Select Browser tab Expand measures you Would observed that the total amount    ------------>

Calculated measure is available.

 

 

KPIS (Key Performance Indications)

In Analysis Services, Represent Data to measure Business Success at very high

Level (mostly at Organization level (or) Regional level)

Learn more about MSBI Interview Questions in this blog post.

Steps to Configure KPIS in a cube:

Open BIDS    ------------>

Create a valid cube    ------------>

Select KPIs tab in a cube     ------------>

Click on New KPI and It will open one template for filling the desired information ,

Name   - Provide any name to new KPI like Fact internet sales KPI

Associated measure group – KPI belongs to one particular Here you can Specify

A particular measure group (or) you can specify all.

Value Expression :

This is a actual value of KPI this may be a row measure (or) MDX numeric

Expression . Drag and Drop the following Expression to KPI Value

[Measures].[order Quantity]

Goal Expression :  Here our new crated measure will come in to picture. We Get value Expression from measure group (order Quantity)

But the goal value will be target measure groups.

Provide the following Expression as target (or) goal Expression.

[Measures]. [Total product Cost]

Status: It’s check the state of KPI at any point Status MDX Expression should

Return value between -1 to +1.

Provide the following Expression to find out the Status of the KPI

IIF (KPI VALUE (“[Measures]. [order Quantity]”)) > KPI GOAL

(“[Measures]. [Total product cost]”),-1,1)

In the above example if KPI value greater than KPI Goal then the status

Is negative (Bad performance) (or) else positive (good performance).

How to generate a Report from Analysis Services Cube:

Open BIDS    ------------>

In Analysis Service Create a valid cube and deploy it to analysis Service Server

Open BIDS Create Report Server project    ------------>

In Solution Explorer set shared Data sources    ------------>

Right click and select Add new Data source and Set,

Name - D src product sales details

Type - Select Microsoft SQL Server Analysis Server option from drop down list

Check Edit

Server Name - Local host (or) Actual Server name (or) IP Address

Data base Name - Cube Name (Product Details morning 8 : 30)    ------------>

Test connection    ------------>

Click ok thrice    ------------>

Select Report option    ------------>

Right click and Select Add    ------------>

New Item    ------------>

Select Reports template Rename it as product details from cube RDl    ------------>

Click add    ------------>

In Data tab add new Data set and set

Name – D set product details

Data source – Select D src product sales details

Command type - text    ------------>

Click ok

Drag and Drop the following fields from the available Meta data section

[Facts and dimension tables]

From Dim product ,

[English Product Name],

[class],

[color],

[Dealer price],

[List price]

From measure

[measure]. [Fright]

[measure].[unit price]

Press Alt + ctrl + D for Data set    ------------>

Go to layout tab design any Report item (table, matrix, check etc..,)    ------------>

Now the Report is displaying all the Reports from the specified cube.    ------------>

How to Set parameterized Reports from cube:-

From the above Report example in a cube.

Let as Start by creating a cube on adventure works DW data base.

1. Create Analysis Services project and name it product Details.
2. Add a new data Source in it , Which directs to Adventure Works Dw

Data base namely ‘Adventure Works DW’.

3. Add a new data source view (named Adventure Works DW’ ) which consists

Of tables Dim product , Dim product sub category, Dim product category,

Dim customer and Fact Internet Sales.

4. Create a cube With default Settings but Selective measures as order Quantity,

Unit price, Sales Amount and Internet Sales Count . Give cube name as Demo cube.

5. Go to the properties of the project (product Details) >

Select Deployment tab and enter Server name. (if you are working with default)

i.e local host )

6. After Successful deployment , open SSMS and connect to Analysis

Services to work MDX quires.

7. Create a new query , connect Product Details data base Enter following MDX query:

SELECT FROM Demo cube In this case default member for Order Quantity is

Displayed as 60938.

Enter the query as :

SELECT [Measure]. [order Quantity] ON 0

FROM Demo cube.

Will also give the same result . (0 can also be replaced by COLUMNS) which is the axis.

8. Let as add one more measures as follows:

In this Case We can see two measures Order Quantity and Sales Amount on columns.

9. Let us give query for a specific product category Bikes as follows.

SELECT [Product].[Product Category Key].[Bikes]

ON COLUMNS FROM Demo cube.

OR

SELECT [Product].[Product Category Key]. &[1]

ON COLUMNS FROM Demo cube.

1. Let us add rows now (add another axis)

SELECT [Product].[Product Category Key]. &[1]

ON COLUMNS.

[Customer].[customer key]. MEMBERS ON ROWS FROM Demo Cube.

With this, we get the Sales Amount for Bikes for all different customers.

This can be achieved by using Ordinals as 0 and 1 instead or COLUMNS and ROWS.

1. SELECT NON-EMPTY [Product]. [Product Category Key]. MEMBERS ON COLUMNS,

[customer]. [customer key]. MEMBERS ON ROWS

FROM Demo cube.

To get all categories (we added NON-EMPTY to get rid of NULL Values)

2. Let as See how to add a calculated number

WITH MEMBER Measures.[calculated Member] As

([Measures]. [order Quantity] * [Measures] [Unit Price])

SELECT NON EMPTY { Measures . [Calculated Member ] [Measures][Sales Amount] }

ON COLUMNS, NON-EMPTY [product] [Product Category key]. MEMBERS on ROWS

FROM DEMO CUBE

First, we calculate the member and we it with the field to View the result.

3. If we replace Members after product Category Key with CHILDREN We get the result as follows

SELECT NON EMPTY { Measures . [Calculated Member ] [Measures][Sales Amount] }

ON COLUMNS,

NON EMPTY [product] [Product Category key]. CHILDREN on ROWS

FROM Demo Cube.

We get only the (nonempty) children for categories, all categories are not displayed.

For an In-depth knowledge on MSBI click on:

• Report Server
• Transfer SQL server Objects Task
• Flat file source
• Slowly Changing Dimension
• Different ways to execute SSIS package