Solved

SSAS conditional caculation

Posted on 2009-03-31
3
285 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
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause in stored procedure 8 61
How to simplify my SQL statement? 14 55
Query 14 57
CROSS APPLY 4 57
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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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