Decode Problem

building semantic layer
1

Hi All

I am using Decode function in the Universe to select different measures dynamically.
Example:
decode(@Variable(‘secmes’),‘PCC’,@Select(Facts\Per claim cost),‘PMPM’,@Select(Facts\PMPM cost),‘3’,@Select(Facts\Exceeding Claim LOS Dur - Percentile))

In the above decode function: first measure(PCC-Medical Cost/Medical Count) is coming from single fact table i.e Medical Fact.
Second measure(PMPM - Medical Cost/Member Count) Medical Cost is coming from Medical Fact and Member count is coming from Member Fact.
Thrid measure is coming from Medical Fact.
Now the problem is when a user selects PCC, the sql is also having the MEmber Fact joins and the result is varying. but actually it should get only from Medical Fact. Any suggestions how can I achieve this in Universe only. I mean any other functionality to achieve this.

Any help in this regard will be of great help.

Thanks
Raki

Raki123 (BOB member since 2007-05-15)

2

Looks to me that you should be using @AggregateAware functionality rather than decode.

dessa :madagascar: (BOB member since 2004-01-29)

3

But here all the measures are at the same grain…

Correct me if I am wrong: generally we will be using @Aggregate when we have measures at different grain eg. year,quarter,month.
But in my situation all are at the Month level, so pls let me know any other alternative to achieve the results

Thanks
Raki

Raki123 (BOB member since 2007-05-15)

4

Every table referenced in the SQL is always going to be included in the “from” clause. Put another way, the SQL is generated before the prompts are interpreted, so there is no way to eliminate tables using prompts. Using aggregate away you can “pick” tables. It doesn’t matter if your aggregates are at the same grain as long as you can define a “best choice” as far as ordering them in a hierarchy.

Dave Rathbun :us: (BOB member since 2002-06-06)