Sunday, November 30, 2014

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.
 

Thursday, November 20, 2014

Advisor Webcast: Using OBIEE with Big Data - Nov. 25

In case you missed it. There is a link at https://blogs.oracle.com/proactivesupportEPM/entry/aw_obiee_bigdata to Advisor Webcast: Using OBIEE with Big Data - Nov. 25.
Description:

Abstract:
This one-hour advisor webcast is recommended for users and administrators of OBIEE, who wish to know more about using it to query big data. Learn about how to connect to your big data appliance. Which ODBC drivers are supported? Are there any limitations on OBIEE when querying big data? Can you use BI Mobile against big data? A Demo will be included based on the Oracle Big Data VM.
Topics Include:
  • Learn about how to connect to your big data appliance.
  • Which ODBC drivers are supported?
  • Are there any limitations on OBIEE when querying big data?
  • Can you use BI Mobile against big data?
  • Demo based on the Oracle Big Data VM.
Duration: 1 hr



Tuesday, November 11, 2014

Oracle Documentation and Tutorials in the new Help Center

Oracle created a new access point to documentation and basic tutorials, Oracle Help Center. It's https://docs.oracle.com/ .It has a nice, friendly interface. I like it.

There is a link to help video.

Easy search interface:

Now we have the documentation and some tutorials in the same place https://docs.oracle.com/cd/E28280_01/nav/bi.htm:


Files can be directly downloaded without opening them in HTML.

Beyond the regular documentation there are some nice tutorials. A partial list is bellow. 

Here is an example:

I like the new look.