Introduction to Named Calculations in SQL Server Analysis Services

Introduction

In this Tips I will show you How can you create Virtual Column in Data Source View (DSV) of SQL Server Analysis Services Project in BIDS, How can it benifit to you.

Named Calculation provides you facility to create virtual Column in data source view of SSAS project without changing base table in SQL Server. You can extend your relational schema of the table using Named calculation. Named Calculation gets processed during processing of Cube.

You can Achive Various goals by defining Valid Expression in the box while creating Named Calculation.

Background

This Article is extension of My Previous Article Create First OLAP Cube in SQL Server Analysis Services (SSAS)

Using the code

Example 1: Concatination of Two fields from Customer Table

FirstName +' '+ 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

Creating Named Calculation

Open Your SQL Analysis Services Project in Business Intelligence Development Studio.

Step 1 :

Right Click On DimProduct Table In Data Source View --> Click New Named Calculation

Step 2 :

Right Click On DimProduct Table In Data Source View --> Click New Named Calculation --> Assign Column Name --> Give Description of Column(optional)--> Specify Expression --> Click OK

Step 3: You can see Calculated Column Created Using Named Calculation in Dim Store Table .

If you find This Information is useful then do not forget to Book Mark this Tips.

Enjoy the Intelligence.

History

You Can Also Refer to My Previous Articles and Tips to Learn Data Warehouse Concepts and Creation of OLAP Cube using Microsoft SQL Server Analysis Services.