?
Solved

SSAS conditional caculation

Posted on 2009-03-31
3
Medium Priority
?
309 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
2 Comments
 
LVL 8

Accepted Solution

by:
tpi007 earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

757 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