Tuesday, March 12, 2013

OBIEE 11g Hierarchical Columns and Selection Steps

So we know what are Hierarchical Columns. What are Selection Steps?
Many people don't notice they exist is OBIEE 11g:
So what can we do with Selection Steps?
First of all they are not specific for Hierarchical Column.  Selection steps just like filter are intended to allow you to limit the data to fit your specific needs.
In the Users Guide, Chapter 5  there is a discussion about the difference between filters and Selection Steps.
  • Filters are applied before Selection Steps
  • Selection Steps are applied in the order you defined them
  • Selection Steps are applied after the query was aggregated and affects only the members displayed and not the resulting aggregates.
  • You can save Selection Steps for reuse.
  • Unlike filters, if you exclude or delete a dimension from you analysis, the Selection Steps are excluded/deleted as well (despite all my efforts).
In Selection Steps you can:
 Select Members, Add Group of Calculated Items, Apply a Condition and Select Members based on Hierarchy.
There are 3 types of action you usually do with them:
  • Keep - keep only the selected members
  • Add  - add the selected members to the result
  • Remove - remove the members from a result
We can have a long discussion about each option. We might do it one day, but lets have an example.
I want to create a set of previous 35 days based on time hierarchy. (I can do it with filters as well... The actual case was a bit more complicated).

So I can select"Apply a Condition in Time Hierarchy.
Now Select Condition type of "Last 3 Month":
In my case I want to keep only 
the last 35 values of day level 
in the Time Hierarchy.
I also want to override the value of 35 with a Presentation Variable, whenever I want. 



As a result the Time hierarchy values are now in Day level.


This is very "OLAP like" behavior. Selected Month level, would cause the report to show values of month and not dates.


I can now remove some of the days from the list as well, for example the last 32 days. So I'm left with only 3 days (it also fits very nicely with the not perfect data of the Sample).

Next I can save the Selection Steps for future use.
You can only save steps for a specific dimension.







So I'll save the steps in a selected folder.
In the screen:
The Name is the name of the members file.
Display Label is what you see in the Analysis (if I leave it empty, the name will be used).

The important question is what to Save: general steps, so it will give dynamic results, or just the members of the Results.

In our case the difference is between selecting each time the 3 days, or using the specific 3 days of this result, each time.

Now each time I create a new analysis, I can use saved Selection Steps:

I can even Edit the Start with all members
Then select a group of Calculated Item. That would be our Steps.

The result is:
(Please notice the little menu option near the Move arrow. You can select there "add" or "add members". The later will show you the days and not the "35 days" group you see in the result).

Another option is to add our Steps to existing "start with all" (or any other start with...)
This is what you get:
 

2 comments:

  1. Explained in a very neat way.. Thanks for the that.. was new to 11g and this selection steps.. It gave me an basic understanding atleast at higher level..
    - Harish

    ReplyDelete
  2. Hi , how can we pass the prompts to dimensional hierarchy pulled in reports in a dashboard

    ReplyDelete