asked on

Create case statement in SSAS OLAP design

I am trying to build a calculated member for an OLAP cube using Micro Soft Analysis Services. The attributes hold a boolean value. Basically I want to create a case statement which looks at the boolean value in 5 attributes and if the value is true in Attribute1then 'descriptor01', if the value is true in Attribute2 then 'descriptor02' ... to Attribute5. The aggregation at the various descriptor levels will always be more than the count of records as a record can have a true boolean value in more than one of the 5 dimensions.

ASKER CERTIFIED SOLUTION

srnar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mocha_Joe

ASKER

Is it possible to add this as a hierarchy to the dimension in the Anaysis services design where it will show in the browser of a cube or if you use excel to view the local cube? I do not want to write in a query editor in Analysis Services. I want to add to the .sln a hierarchy that will do this. I have seen this done with 'Data Beacon'. Please advise

srnar

Put your CASE logic into the relational layer. Than you can easily set a hierarchy based on a multiple attributes logic. I don't know Data Beacon - do you mean that reporting tool?

Mocha_Joe

ASKER

Yes, Data Beacon the reporting tool. It builds the mdx behind closed doors. Do you mean add the CASE logic the Data Source View when you refer to 'relational layer' or do you mean build a hierarchy based on the 5 separate attributes ?

srnar

Yes - the new hierarchy would use that attributes. I prefer the database view but you can use the data source also for case logic implementation.