Sunday, 2 April 2017

Learning SSAS with Me – Implementing Calculation

Introduction
In this article we are trying to explain the Calculation of SSAS and how we can use it. If you go to the Google Search you can find 1000 of article related to it. The purpose of this article is to make the thing simple and to understand the SSAS quickly. Hope all of you agree with me, if you are a regular reader of our article.

The sole purpose is making simple, easy, and understandable for all.

What is Calculation
As we know from our SQL understanding, we can perform Calculation in SELECT statement. Same thing is also happening here but little complex.

There are two type of Calculation we can use in our SSAS.

o   Named Calculation
o   Calculated member Using Calculation tab of SSAS
   
Named Calculation is that we can create calculation in Data Source View and it is quite simple.

Select the Fact à Right Click à Choose New Named Calculation





The main thing is that, if we used the Named Calculation it is process when the CUBE is processed.

But if we used the Calculated Members using Calculated tab of SSAS, it will process data when the Query if Fired.

Let’s make the same calculation using the Calculated Members using Calculated tab of SSAS.





Here we can perform the Very complex Calculation also but we have to Understand MDX for that. The MDX script is creating automatically by using expressions.
If we click on the Script View we can find it


CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[SalesAmount]
 AS [Measures].[Qty Sold] * [Measures].[Sales Rate],
FORMAT_STRING = "#,###,#0.00;-#,###,#0.00",
NON_EMPTY_BEHAVIOR = { [Qty Sold], [Sales Rate] },
VISIBLE = 1 ,  DISPLAY_FOLDER = 'CalaulatedMeasures'
ASSOCIATED_MEASURE_GROUP = 'Fact Product Sales'  ;

Browsing the Cube
If we look at the Browser and re-connected it we can see both the calculated measures.





Now try to browse it






We are going to MDX example, when we learn MDX.

Hope you like it.







Posted by: MR. JOYDEEP DAS

No comments:

Post a Comment