Solved

SSAS conditional caculation

Posted on 2009-03-31
3
297 Views
Last Modified: 2016-02-12
I am new to SSAS in SQL Server 2005.  I want to create a calculated value, that has the value of an amount measure or zero depending the value of a dimension.  To outline a simple example assume I have a dimension called AccountCode with values of "4000", "4001", "5000", "5001", "5002", "6000", "6001", "6002", "6003", etc.  and I have a measure called "Amount".

I want to create 3 calculated values as outlined below:

Sales = Amount where AccountCode >= "4000" and AccountCode <= "4999" else 0
CostOfSales = Amount where AccountCode >= "5000" and AccountCode <= "5999" else 0
GeneralExpenses = Amount where AccountCode >= "6000" and AccountCode <= "6999" else 0

So in the OLAP Cube, I would have the measures Amount plus the three named above.
0
Comment
Question by:bandreini
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
tpi007 earned 500 total points
ID: 24037500
Ideally, I would recommend you investigate parent child heirarchies in SSAS 2005.
http://msdn.microsoft.com/en-us/library/ms174846.aspx  
http://blogs.technet.com/paolt/archive/2005/02/17/375497.aspx
Bascially you can have a parentid column in accountcode source table and use parentid column to represent structure, This is usually done  in source data. Calculated memebers can be quite slow as calculations are done on fly as opposed to a meaure.    
Alternatively you can add sql below into datasource view of data for fact table and reference the columns as measure in their own right. Heiracrchies are preferreed option for your scenario but can take a little time to setup and a little complex to implement. They also require the parentid column to be maintained as chnages to your account structure change.  
 

CREATE TABLE [dbo].[#tblCosts](
	[AccountCode] [int] NOT NULL,
	[Amount] [decimal](18, 2) NOT NULL
) 
 
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (4000,3.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (5000,4.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (5000,14.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (5800,3.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (4500,14.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (6700,24.00)
INSERT INTO #tblCosts (AccountCode,Amount) VALUES (6600,24.00)
 
select AccountCode,
(CASE WHEN AccountCode BETWEEN 4000 AND 4999 THEN Amount
      ELSE 0 END) AS Sales,
(CASE WHEN AccountCode BETWEEN 5000 AND 5999 THEN Amount
      ELSE 0 END) AS CostOfSales,
(CASE WHEN  AccountCode BETWEEN 6000 AND 6999 THEN Amount
      ELSE 0 END) AS GeneralExpenses
FROM #tblcosts

Open in new window

0
 

Author Closing Comment

by:bandreini
ID: 31565185
I do understand the reason why to this approach.  I have successfully implemented and tested it!  Thanks!  
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question