Named Calculations are defined in the Data Source View. A named calculation is a SQL expression (or some static value or combination of existing columns) represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation lets you extend the relational schema of existing tables or views in a data source view without modifying the tables or views in the underlying data source.
In simple words, a Named Calculation is nothing but creating new column and add it to a Table or View in Data Source View
Consider the following examples:
- Create a single named calculation that is derived from multiple columns in a fact table (for example, creating Tax Amount by multiplying a tax rate by a sales price).
- Construct a user friendly name for a dimension member.
- As a query performance enhancement, create a named calculation in the DSV instead of creating a calculated member in a cube. Named calculations are calculated during processing whereas calculated members are calculated at query time.
- We can create Named Calculation to hold any static value also
Note: You cannot add a named calculation to a named query, nor can you base a named query on a table that contains a named calculation.
Scenario: we want to create a new measure for Total Profit. Using the existing fields from the FactSales table, we know that we could calculated the Total Profit using an expression such as:
(UnitPrice – UnitCost – DiscountAmount) * (SalesQuantity – ReturnQuantity)
Creating Named Calculations in SSAS:
Therefore, we can open the Data Source View and right click the FactSales header in the diagram and select the command: New Named Calculation.
Select the New Named Calculation option from the context menu
When you click on the New named calculation option a Create Named Calculation window form (as shown in below screenshot) will open to create the named calculation.
- Column name is the Name you want to display to the end-user, obviously, it must be a unique name for the table.
- Description is to describe what this Named Calculation will do. It is optional but very important in real-time.
- Expression is the place used to write SQL expression or any Static Value.
Click ok button to finish creating Named Calculation and check the Data Source View. You can see the Calculated Column (Created by Named Calculation) in Dim Employee Table. You can also observe small tiff before the column name.
Using the code
Example 1: Concatination of Two fields from Customer TableFirstName +' '+ LastName
Example 2: Calculated Column on DimProduct Table (Profit= salescost-actualcost)
ProductSalesCost – ProductActualCost
Example 3: Ignore Null Value in Character Field
ISNULL(FirstName,'') + ISNULL(LastName,'')
Example 4: Apply various Condition on a Column Values and get MonthName
Case [Month]
When 1 Then 'January'
When 2 Then 'February'
..
End
Example 5: Convert one data type to another data type
SalesDocumnetNo+Convert(Varchar(50),[LineNo]))+
(Convert(Varchar(50),[DOCSRNO]))+role)
Example 6: Replace any value in attribute
REPLACE(BRANCH,'MUMBAI','MUM')
SSAS Named Calculations
In SQL Server Analysis Services, a Named Calculation is nothing but creating new column and add it to a Table or View in Data Source View. Named Calculation is created based on an expression or some static value or combination of existing columns. Following are the important features of the Named Calculations
- Named Calculation allows you to add an extra column to the Tables or Views present in the SSAS Data Source View.
- All the Named Calculation belongs to Data Source View only and they are independent of underlying Data Source (Server).
- We can create Calculated Column by combining one or more columns from underlying data source View. For instance finding the Full Name by combining First Name and Last Name
- We can create Named Calculation to hold any static value also
- We can create Named Calculation using SQL Expressions. For instance calculating the profits, Tax, Product waste etc
- All the Named Calculations are calculated during the processing time, this may slow down the processing time.
Creating Named Calculations in SSAS
Click on the Data Source Views folder in Solution Explorer and then click on the created Data Source View. Please refer SSAS Data Source View article to understand, How to create Data Source View in SSAS.
Below screenshot will show you Data Source View
Right Click on DimEmployee Table in Data Source View will open the Context Menu with multiple options.
Select the New Named Calculation option from the context menu
When you click on the New named calculation option an Edit Named Calculation window form (as shown in below screenshot) will open to create the named calculation.
- Column name is the Name you want to display to the end-user
- Description is to describe what this Named Calculation will do. It is optional but very important in real-time.
- Expression is the place used to write SQL expression or any Static Value.
In this example we are creating Full Name by combining the FirstName and LastName Column in DimEmployee table.
Expression: FirstName + ‘ ‘ + LastName
Click ok button to finish creating Named Calculation and check the Data Source View. You can see the Calculated Column (Created by Named Calculation) in Dim Employee Table. You can also observe small tiff before the column name.
Thank you for Visiting Our Blog
******************************End of the Document********************************
