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.
