Solved

SSAS conditional caculation

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now