OBIEE - Top 2 of top 10 with Selection Steps

In the past I talked about Hierarchical Columns and selection steps. 

(here: OBIEE 11g - Hierarchical Columns Introduction

 and here:OBIEE 11g Hierarchical Columns and Selection Steps).

Lets talk about additional sample of using Selection Steps. This time I don't use Hierarchical Column, just to show, we don't need to.

What I want is a report of Top 2 Offices with top 10 Products.

Lets start with a simple report of Department, Product and Revenue.

The result isn't exactly what I wanted, since the same products are sold by different  Departments.  

I want top 10 unique Products.

So lets create a column that gives a unique sum for each product ( SUM(Revenue by Product) ) and run the in Top 10 on it.

Now we have a list of 92 row, since they are 10 Departments.

Now I want the top 2 departments by its revenue from the 10 top products.

I could create another column and filter on it, but it's easier to use Selection Steps:

Lets concentrate on Offices - Department, and create a new condition.

 

Select the Top.. based on... condition

And select the "Keep Only", "is top", 2.

We can select the Measure (Revenue for our needs)

And run it for all members of the Products.

As a result we have the top 2 Departments that sell the top 10 Products:

You don't believe me?

Lets remove the Selection Step, and summarize by Department:

The result proves to be correct. Q.E.D.

Endeca Information Discovery 3.0 is available

The new version of Endeca Information Discovery 3.0 is available at edelivery .

As usual it's available for Linux and Windows 64bit systems.

You can see the documents of the product in the top of the documents link: http://www.oracle.com/technetwork/middleware/endeca/documentation/index.html.

Next stop OBIEE 11.1.1.7 ?

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:

 

OBIEE 11.1.1.6.9 is available and Bug Fixes

Please note, since OBIEE 11.1.6.11 is available (see here) you should consider it instead of 11.1.1.6.9.

Patch 11.1.1.6.9 is available from Oracle support site (support.oracle.com). 

The first part of the patch is Patch 16287811). 

It's the most updated / latest version (actually patch bundle) of OBIEE, installed on top of the basic 11.1.1.6.0 version.

It can be installed on OBIEE 11.1.1.6.0, 11.1.1.6.1, 11.1.1.6.2, 11.1.1.6.2 BP1, 11.1.1.6.4,11.1.1.6.5, 11.1.1.6.6, 11.1.1.6.7 and  11.1.1.6.8 .

As usual, the real patch has 7+1 parts. The installation steps for the first 7 are similar. I recommend reading the first readme – patch 16287811 readme file. To be totally correct it’s not the first 7, but first 6 since patch number 7 contains only client tools (BI Administration, MapViewer…) and to the best of my knowledge are not relevant to non-Windows installations.

The Oracle Business Intelligence 11.1.1.6.9 patchset comprises a number of patches:

Patch	Abstract
16287811	Patch 11.1.1.6.9 (1 of 7) Oracle Business Intelligence Installer
16287778	Patch 11.1.1.6.9 (2 of 7) Oracle Real Time Decisions
16237960	Patch 11.1.1.6.9 (3 of 7) Oracle Business Intelligence Publisher
16287840	Patch 11.1.1.6.9 (4 of 7) Oracle Business Intelligence ADF Components
16287854	Patch 11.1.1.6.9 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x PLEASE note, this is platform specific patch (Windows 32, 64, Linux X86, X86-64, IBM AIX Power 64, Solaris SPARC 64, Solaris X86-64, HP-UX Itanium)
16227549	Patch 11.1.1.6.9 (6 of 7) Oracle Business Intelligence PLEASE note, this is platform specific patch (Windows32,  64, Linux X86-64, IBM AIX Power 64, Solaris SPARC 64, Solaris X86-64, HP-UX Itanium)
16287884	Patch 11.1.1.6.9 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer

Additionally there is the patch number 8, it is named JDeveloper Patch, but it is for OBIEE as well. It's the same patch you used if you installed 11.1.1.6.2 BP1 or later. So if you already installed it, I see no reason to do it again.

Patch 13952743 - JDeveloper patch -   - RUP3 MLR4.3  CUMULATIVE: BASED ON JDEVADF_11.1.1.6.2_GENERIC_120329.1447.6268.1 (last release 11.1.1.6.2)

The installation steps are same as 11.1.1.6.2 BP1 as we described here.

Please note, that readme tell you to do the following backups:

• The ORACLE_HOME\bifoundation\server directory
  
• The ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository
  
• The ORACLE_BI_HOME\bifoundation\jee\mapviewer.ear\web.war\WEB_INF\conf\mapViewerConfig.xml, if you have modified it. This last bullet is relevant to those who configured the OBIEE Maps (described, in Hebrew here).

HP Itanium users, please note: HP Itanium platform must first download and apply patch 14016752 from My Oracle Support.

 I'm not aware of any new features in 11.1.1.6.9.

Bugs Fixed By This Patch

13404296: BI PROVISIONING FACADE MUST TRIM RPD IN INSTANCE FOR SELECTED FA OFFERINGS
13485655: T2P: ERROR MSG EXECUTING WLST SCRIPT APPEARS DURING BIINSTANCE PASTECONFIG
13504454: BLK: BIINSTANCE COPYCONFIG FAILURE: ESSBASE MAXL LOGIN IS FAILING
13510938: NEED OFFICIAL WAY TO INVOKE _CONFIGURERPD
13560245: BIDOMAIN/CONFIG/JDBC NOT FOUND
13565722: BLK: BI T2P PASTECONFIG ERRORED OUT WITH UNABLE TO PARSE JMS_WEBLOGIC_JNDI_URL
13589944: BI T2P: PASTECONFIG INSTANCE IS FAILING WITH JAVA.LANG.UNSATISFIEDLINKERROR
13619973: INCLUDE ESSSERVER HOST NAME IN THE MOVEPLAN
13628293: EMPTY PROCUREMENT DASHBOARD IN RC4
13683204:  STRESS:FA:CRM ERROR RESPONSE[NQSERROR: 43082] ILLEGAL OPERATION ATTEMPTED CLOSED

12877824: WEBSERVICE API - GETSCHEDULEDREPORTSTATUS ALWAYS RETURNS JOB STATUS AS SCHEDULED

13364491: REPORT VIEWER NOT SHOWING ANIMATED CLOCK FOR LARGE REPORT IN IE7 & SOMETIMES IE8

14605689: IN AIX USING DATA SET OF REMOTE WEB SERVICE RETURNS NULL DATA

14633340: REPORT WITH 2 DATE PARAMETERS:TIME PASSED THRU 1ST DATE CALENDAR IS SAME FOR 2ND

14813229: REPEATED MESSAGE POPS UP WHEN LEAVING DATA MODEL PAGE USING IE 9

15992028: REPORT WITH MORE THAN 4 PROMPTS DOES NOT WORK FOR INTERACTIVE VIEW IN DASHBOARD

9783769: CANNOT ADD CONDITIONS FOR SELECTED COLUMNS IN COLUMN SELECTOR VIEW

12887464: MISSING FORMAT OF EXPORTED EXCEL WHEN ENABLE ALTERNATING ROW "GREEN BAR" STYLING

13843053: UNABLE TO SET LABEL AS BLANK FOR A COLUMN PROMPT

13919339: "ALL COL VALUES" CHKBOX IN PROMPT IS NOT WORKING WITH DISABLED APPLY BUTTON

14043444: 11G GRAPH DYNAMIC SCALE MARKER NOT SCALING CORRECTLY

14133832: MULTIPLE PAGE DASHBOARD PROMPT DOES NOT CHANGE SELECTED VALUE

14201003: CRASH IN MODULES - GDCUSTOMLINKS::SERIALIZEJSON

14482274: SEGMENT DESIGNER POPUP WINDOWS ARE TOO SMALL

14488588: ACTION LINK PASSING TOO MANY PARAMETERS

14577963: CONDITIONAL FORMATTING IN PIVOT TABLE VIEW WITH COMPLEX FILTER PRODUCES ERROR

14622910: AN AGENT SENDS EMAILS THOUGH IT IS CONFIGURED NOT TO SEND AN EMAIL

14695698: METADATA DICTIONARY SHOWS INCORRECT MAPPING WHEN NAMES CLASH DUE TO TRUNCATION

14762038: SERVER VARIABLES ARE NOT AUTO-APPLIED TO SCORECARD VIEWS ON DASHBOARD PAGES

14762243: TODATE() IN KPI WITH TRENDING ENABLED CAUSES ODBC ERROR

15912205: ACTION LINKS WITHIN KPI NOT PASSING PARAMETERS LIKE REGULAR ACTIONS

16051890: CASE WHEN STATEMENT IN COLUMN FORMULA PRODUCES INCORRECT AGGREGATED RESULT

16090809: NESTED FOLDERS ARE FLATTENED WHEN VIEWED THROUGH ANALYTICS - >NEW->SEGMENT

16164220: JAPANESE CHARACTERS ARE INCORRECT IN EXPORTED PDF

16200883: OBIEE PATCHES FOR 11.1.1.6.5 HAS UNIX FORMAT XML FILES FOR WINDOWS PLATFORMS

16207947: DELIVERY OF CSV FORMAT FORCES UNICODE WHICH PREVENTS COMMA WORKING AS SEPARATOR

16307421: PATCHING FROM 11.1.1.6.0 TO 11.1.1.6.7 YIELDS INCONSISTENT CATALOG RESULTS

 Known Bugs With This Patch

14228519: ADMINTOOL CLIENT MISSING ALL FILES UNDER EXTENDER DIRECTORY IN CLIENT INSTALLER

14244844: NAVIGATE TO URL ACTIONS BASED ON ENCODED URLS MAY RESULT IN DOUBLE ENCODING

15854192: ERROR MESSAGE RETRIEVED WHEN TRYING TO CREATE AGENT WITH EMAILS AS RECIPIENTS

15854218: ODBC ERROR WHILE CREATING AN AGENT

15983904: 11.1.1.6.7 ADMIN TOOL CLIENT SHOWS INCORRECT VRSN IN THE CLIENT INSTALLER WINDOW

13974353: CONNECTION STRING USED TO ACCESS LEASING TABLE IS INCORRECT

14743304: FIX FOR BUG 14170374 REQUIRES ADDITIONAL CHANGE TO OPMN.XML

14771782: FIX FOR BUG 14070348 DOES NOT WORK IN CLUSTERED ENVIRONMENT

14772000: UNABLE TO SCROLL AS IE 8.0 PERFORMANCE POOR FOR MAX ROWS IN DASHBOARD

 

OBIEE 11g - Hierarchical Columns Introduction

One of the features of OBIEE 11g is Hierarchical Columns. 

Technically from the Administration side, it's not a big issue. All you need to do in the Administration is mark, in the logical dimensions you want to use as Hierarchical Columns,

 "Use for Update", at the desired levels.

 Now drag Hierarchy/Logical Dimension to the presentation folder:

Save and use it:

If your source is OLAP, you will get it by default, for each Hierarchy.

 

All is great, but the Hierarchical Columns is not only about clicking the Plus and Minus signs. 

First of all, what is missing in the picture?

Apparently, there is no Formula or Filter option for Hierarchical Columns. Lets accept the Formula part as expected, but what about Filters? 

You have 2 options:

1. Create filters on the regular Columns that participate in the Hierarchical Column, for example seting a filter on date will influence the content of the Time Hierarchy.

2. Use Conditions in Selection Steps. 

This is what the next post will talk about.

Embedding OBIEE - PortalPages vs. syndicate=Siebel

There are times you might want to embed a Dashboard page in a web page.

There are 2 ways to achieve it:

1. PortalPages

2. syndicate=Siebel

While the PortalPages is well documented in Integrator's Guide for Oracle Business Intelligence Enterprise Edition (6.2.3 Optional Commands and Parameters for the Oracle BI Presentation Services Dashboard URL), I couldn't find anything regarding the very old option of syndicate, in the documentation.

PLEASE NOTE: At 11.1.1.7.0 and 11.1.1.6.10 patch, the problem described here with PortalPages is solved. Unfortunately it remains in cases of navigate to BI content. 

Please Note 2: The problem with navigate to BI Content was solved as well at some 
point and at least for version 11.1.1.7.150120 is working correctly as well. 

So it seems that PortalPages is the recommended Oracle way.


How to create the relevant GO_URL?
In OBIEE, go to the page you want to embed and  select  in the "Page Options", "Create Bookmark Link":

In the URL line I got the following (my server name is bdahab-il and port 7001, replace with your own settings):
http://bdahab-il:7001/analytics/saw.dll?Dashboard&PortalPath=%2Fshared%2FSample%20Lite%2F_portal%2FQuickStart&Page=Overview&PageIdentifier=pp523ed1snrnjteh&BookmarkState=0duteshj7283qgbhsv3lhimhp6

I'm only interested in the first part :
http://bdahab-il:7001/analytics/saw.dll?Dashboard&PortalPath=%2Fshared%2FSample%20Lite%2F_portal%2FQuickStart&Page=Overview

Where "PortalPath=%2Fshared%2FSample%20Lite%2F_portal%2FQuickStart" is the dashboard, and "Page=Overview" is the page in dashboard.

Now I can create a URL. In my URL I will also add user and password parameters. Of course you don't need it if you have Single Sign on.
For PortalPages the URL is:
http://bdahab-il:7001/analytics/saw.dll?PortalPages&PortalPath=%2Fshared%2FSample Lite%2F_portal%2FQuickStart&Page=Overview&NQUSER=weblogic&NQPASSWORD=weblogic123

And for syndicate=Siebel it is:
http://bdahab-il:7001/analytics/saw.dll?Dashboard&PortalPath=%2Fshared%2FSample Lite%2F_portal%2FQuickStart&Page=Overview&NQUSER=weblogic&NQPASSWORD=weblogic123&syndicate=Siebel

* Please note: syndicate=Siebel requires the NQUSER and NQPASSWORD parameters. When running without them and getting to the LOGIN screen, it stops working.


By the way, the combination of both:
http://bdahab-il:7001/analytics/saw.dll?PortalPages&PortalPath=%2Fshared%2FSample Lite%2F_portal%2FQuickStart&Page=Overview&NQUSER=weblogic&NQPASSWORD=weblogic123&syndicate=Siebel
Is perfectly OK, as well.

What is the difference between them? Basically they cause the  same effect: you loose the top menu and logo. As a result you see the following:

Instead of this: 

 

The difference is what happens if you select another page in the dashboard. For example, if I click on the Details page in the Dashboard.

With PortalPage, the header returns:

This is usually not the desired behaviour.

With the syndicate=Siebel extension, the header remains hidden:

Navigate to BI Content, only syndicate=Siebel behaves well.
I will talk about a proper solution in later posts. Meanwhile, to hide the banner for specific dashboard, you can use the trick suggested by John Minkjan here:

http://obiee101.blogspot.co.il/2010/07/obiee-remove-whole-portalbanner.html

Add a text object in each target dashboard. Mark the contains HTML and paste the following text (11g code):

<script type="text/javascript">
    var tds = document.getElementsByTagName('table');
    for (var td = 0; td < tds.length; td++) {
        if (tds[td].className != 'HeaderTopBar' && tds[td].className != 'HeaderSecondBar ' ) {
            continue;
        }
        if (tds[td].className == 'HeaderTopBar') {
        //alert (tds[td].className);
        var x = tds[td].parentNode;
        //alert (x.className);
        x.removeChild(tds[td]);}
        if (tds[td].className == 'HeaderSecondBar ') {
        //alert (tds[td].className);
        var x = tds[td].parentNode;
        //alert (x.className);
        x.removeChild(tds[td]);}
        }
</script>
 
like this:

The result changes from this:

To this:

Usually you don't need it, but if you insist, how can you Sign Out now?
With additional URL: http://bdahab-il:7001/analytics/saw.dll?Logoff