Click here to Skip to main content
11,412,516 members (74,708 online)
Click here to Skip to main content

Introduction to Named Calculations in SQL Server Analysis Services

, 30 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Create Virtual Column Using Named Calculation 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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mubin M. Shaikh
Team Leader
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Design and Develop Business Intelligence Solutions using Microsoft BI.
(SQL Integration Services - SSIS, SQL Analysis Services - SSAS, Reporting Services - SSRS,SQL-Server,Dimension Modelling,Data Warehouse,Power Pivot, Power View, Power Map, Power query,.Net,C#,WCF)

Linked In Profile:

Click Here to View Linked In Profile

Change Will Not Come If We Wait for Some Other Person,or Wait for Some Other Time, We are the One We are Waiting For,We are the Change That we Seek.
Follow on   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 Pin
ssnasp at 20-Oct-13 3:02
memberssnasp20-Oct-13 3:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150427.1 | Last Updated 30 Sep 2013
Article Copyright 2013 by Mubin M. Shaikh
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid