OBIEE - Creating reusable target lists

When working with lists (customers, products, periods...), we often find an interesting list we would like to explore from other aspects. OBIEE gives us several option to do it. I'll cover today a saved Analysis and saved filter option and discuss the advantages of each.

The most common case is when the Analysts are working with a dashboard, using prompts and find list of interest, they would like to investigate.
Lets have an example:

I have a simple dashboard with Products, Customers, Dates and revenue and a Prompt. In the Analysis the columns have "Is Prompted" Filter. I will create a list of Customers that purchased specific product between specific dates with minimal amount. Those customers I want to analyze later and do some more research, or send some offers to them.


So I filled some parameters in the prompt and have a list of customers:

Now I have 3 clear options:
1. export to Excel and send it to someone... We will not talk about it. In our case I am that "someone".
2. Press Analyze and save the Analysis.
3. Press Analyze and save the Filter.

First of all, to be sure I have the "Analyze" option, it can be turned-on in "Edit Dashboard" mode. For the entire Dashboard Page at Tools->Page Report Links

Or at the Analysis level (Report Links)

When I press the "Analyze",I see the filter with the values I selected in the prompt:

Now I can:
1. Save it as a new Analysis. I will name it "list" (any other name is OK).
2. save the filters only. I will name it "filter list" (any other name is OK).

The filter can be saved in various locations. I'll save it under My Folder.

Now lets examine what can be done with each one.

There are 2 natural approaches:
1. continue working with the "list" analysis (or using the "filter list").
2. Using the "list" as filter (with "is based on results of another request" option).

Example of option 1, working with the "list" analysis or the filter:
I will delete all columns I don't want and add anything I prefer:

 

...

A "cleaner" option to do the same is to use only the filter, without any columns:

  
This method might become inadequate. For example, what will I do if I want to check the revenue of the customers in the list in the year 2012? The combination of (Calendar Date  is between  01/01/2011 and 01/04/2011) and Year=2012, will not return data.

So we switch to option 2:
Using "list" as filter.

Now I can create a new analysis with Revenue at year 2012 and use the "list" analysis in filter, using "is based on results of another request" option:

There is an inherited limitation in using "is based on results of another request". By default we are limited in OBIEE to specific number of values.
There is a solution to the problem as described in the support note 1339708.1 - OBIEE 11g: Error: Upgraded 10g Report Filtering on "Use Request Results" Fails With Error Codes: "OPR40NWY:U9IM8TAC:OI2DL65P...Query failed: [nQSError: 42029] Subquery contains too many values for the IN predicate. (Thank You Yossi Belitsky for the link).

The short version:

Change MAX_EXPANDED_SUBQUERY_PREDICATES (default is 8192) in NQSConfig.ini or add set variable DISABLE_CACHE_SEED=1, DISABLE_CACHE_HIT=1; in the Analysis Advanced Tab->Advanced SQL Clauses->prefix.