OBIEE- Understanding Selection Steps logic and forcing it to be based on Measures

In this post I will give a reason to use selection steps, explain that Selection Steps logic is base on dimension, or actually, non-measure columns and will describe a way of forcing the Selection Step logic to work on measure values.

I assume you understand the basics of Selection Steps or read the post OBIEE 11g Hierarchical Columns and Selection Steps or something similar.

I will describe the same example described in the post "OBIEE- Understanding Selection Steps logic and forcing them to be based on Measures".

 It's a simple analysis:

With columns: Year, Product_Type and Revenue... The only not totally trivial part is the % of year calculation that is 100*Revenue / (Sum Revenue by Year)   (the formal code is 100*"Base Facts"."Revenue"/sum("Base Facts"."Revenue" by "Time"."Per Name Year")).

This how the result looks like, with totals by Year:

The request was to see the above 4 columns combination, only for Product Type greater than 150,000, but to maintain the correct "% of Year".   

The naive approach would be to add filter Revenue >150,000

But in this case the "% of Year" is totally wrong, since it's based only on the selected data:

 

------------------------------------------------------ a remark --------------------------------------------------
Actually OBIEE is very smart and on a slightly different problem (% of Year > some percentage) is doing great with filters, since in that case, we are doing the filter on the problematic column:

------------------------------------------------------ a remark -------------------------------------------------- 

The natural solution is to use selection steps, since they are executed after the initial select statement. 

But here we have a slight problem. Selection steps are not defined on measures, actually they are defined on non-measure columns. Why do I care? 

Because the naive approach would be to try the following selection step:

Per Product Type, keep only Revenue>150,000.

But the result is not as desired (I color-coded red all the "less than 150,000" cases):

 What happened?
 To understand, lets see the initial analysis with Years excluded:

The result of the Selection Step was to remove "Maintenance" and "Install" Product Type, with no consideration of the Year column. Why? Because of the nature of Selection Steps, it's applying measure values on a specific non-measure / dimension column. The only exception of this rule is the "For" option covered here, but it doesn't help in our case.

How can I solve this problem?

I will create a new dummy column that combines Year and Product Type, hide it, and use the same Selection Step.

(I'm lucky since I don't have to cast year to character, it's character by default):

The formula:

The Selection Step:

Hiding the dummy column:

The result (as desired):

You have more than 2 non-measure columns? Concatenate them all.

Summary:

1. Selection Steps in OBIEE are done on non-measure / dimension columns, using measure values.

2. To force / trick the Selection Step to work on measures values directly, we have to create a dummy column that combines all the non-measure keys, and is used as index of measure values in the analysis.