Sunday, September 29, 2013

OBIEE - Power to the people - joining the unjoinable

In normal life Administrator creates the model, including the joins between objects and the users use them. From time to time the users require things that are not covered by the model. I see 3 option in such case:
1. Update the model - the proper way.
2. Allow the user to access Direct database request (I've seen System admins that told me they would rather drink cyanide).
3. Use "create Analysis from simple Logical SQL" - this topic.

Lets see an example. In my model I deleted the join between Customers table and Sales one:

 What I intend to do is to create 2 separate queries and join them in a third one.

I created the first analysis:

Naturally I will have an error if I try to add a column from Sales table:

At the Advanced Tab we can press "New Analysis" to get the query:

I copied the SQL and did the process again with the other query:








Now I have 2 queries:

1. SELECT "CUSTOMERS"."CUST_ID" saw_0, "CUSTOMERS"."CUST_LAST_NAME" saw_1, "PROFITS"."AMOUNT_SOLD" saw_2 FROM "SH" WHERE "CUSTOMERS"."CUST_ID" < 20

2. SELECT "SALES"."CUST_ID" saw_0, "SALES"."AMOUNT_SOLD" saw_1, "PRODUCTS"."PROD_CATEGORY" saw_2 FROM "SH" WHERE "SALES"."CUST_ID" < 20

I can create single query from both. For example:

Select Q1.saw_1 Cust, Q1.saw_0 Cust_id, Q1.saw_2 Cust_Amount, Q2.saw_1 Prod_cust_Amount, Q2.saw_2 Prod_cat 
FROM
(SELECT "CUSTOMERS"."CUST_ID" saw_0, "CUSTOMERS"."CUST_LAST_NAME" saw_1, "PROFITS"."AMOUNT_SOLD" saw_2 FROM "SH" WHERE "CUSTOMERS"."CUST_ID" < 20) Q1,
(SELECT "SALES"."CUST_ID" saw_0, "SALES"."AMOUNT_SOLD" saw_1, "PRODUCTS"."PROD_CATEGORY" saw_2 FROM "SH" WHERE "SALES"."CUST_ID" < 20) Q2
WHERE
Q1.saw_0=Q2.saw_0

(Don't bother too much with the new names, OBIEE will convert them to saw_x if you do it this way. See Remarks at the end.)

Now I'll take the SQL and paste it to a new analysis (or I can overwrite the SQL of one of the original queries)

This is working. I joined the 2 results as an end user:

A remark:

If you prefer to change the names while working with the SQL, and not after the results are ready, you can do it. 
In the following example I changed all the saw_X column to natural names. Intentionally I had different names in the external and internal SQL statements.

For this code:
Select Q1.CUST_LAST_NAME Cust, Q1.CUST_ID Cust_id, Q1.AMOUNT_SOLD Cust_Amount, Q2.AMOUNT_SOLD Prod_cust_Amount, Q2.PROD_CATEGORY Prod_cat 
FROM
(SELECT "CUSTOMERS"."CUST_ID", "CUSTOMERS"."CUST_LAST_NAME", "PROFITS"."AMOUNT_SOLD" FROM "SH" WHERE "CUSTOMERS"."CUST_ID" < 20) Q1,
(SELECT "SALES"."CUST_ID", "SALES"."AMOUNT_SOLD", "PRODUCTS"."PROD_CATEGORY" FROM "SH" WHERE "SALES"."CUST_ID" < 20) Q2
WHERE
Q1.CUST_ID=Q2.CUST_ID
the result was:


So we learned  OBIEE uses the internal SQL names and disregards the external (for example the first column name is CUST_LAST_NAME and not Cust).
  

Saturday, September 28, 2013

OBIEE - Nested selections or multi data tiers

We have a requirement:

1. We need to select offices who sold specific product.

2. Based on this offices list we need to select Revenue of all products ordered by those offices and the percentage of these revenues compared to the total orders per each product.

When I saw the requirement I thought it's native to OBIEE:
All we need is to create an analysis that returns the Offices based on a product prompt:


I named this analysis "offices selling selected product".
Naturally create a prompt for product...

 I had a little problem with the second part of the request. What comes naturally in OBIEE is to add those offices as a filter. Something like this:






But we had a request to compare it to the total sales of other offices as well.
I started thinking about options:
1. Should we use union all between this select and select with 1 more column of Revenue with all offices without the filter? It works but not elegant.

2. Should we create an external function to return total sales for given product and use Evaluate function? 

3. No, there is a native OBIEE way to do it: Just save the above filter and reuse it in a column filter:

 Create the second analysis, and add there 2 Revenue columns. In one of them Edit Formula and Select Filter there
Now in the Filter window just select the saved filter:
 Here we see a little problem:
The formula is:
FILTER("Base Facts"."Revenue" USING ("Offices"."Office" IN (SELECT saw_1 FROM (SELECT "Base Facts"."Billed Quantity" saw_0, "Offices"."Office" saw_1, "Products"."Product" saw_2, DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product") saw_3 FROM "Sample Sales Lite" WHERE ("Base Facts"."Billed Quantity" > 0)) nqw_1 )))
 (sorry I was a bit lazy and didn't remove unnecessary columns in the first analysis)
 
There is no trace to the "is prompted" part for the Product !

To fix it we have to change the prompt we created and add an option of setting a Presentation Variable to it:
You might (but don't have to, in regular cases) want to replace the is-prompted filter in the first analysis with  "Products"."Product" = ('@{nested}{Maintenance}') 

In the second analysis we can manually change the formula to:

FILTER("Base Facts"."Revenue" USING ("Offices"."Office" IN (SELECT saw_1 FROM (SELECT "Base Facts"."Billed Quantity" saw_0, "Offices"."Office" saw_1, "Products"."Product" saw_2, DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product") saw_3 FROM "Sample Sales Lite" WHERE (("Base Facts"."Billed Quantity" > 0) AND ("Products"."Product" = ('@{nested}{Maintenance}')))) nqw_1 )))

 Adding the AND ("Products"."Product" = ('@{nested}{Maintenance}'))
So the Where condition is:
WHERE (("Base Facts"."Billed Quantity" > 0) AND ("Products"."Product" = ('@{nested}{Maintenance}')))
instead of
WHERE ("Base Facts"."Billed Quantity" > 0)
we had before.

Now it's working.




Few remarks:

1. Why did I do manual modifications and didn't recreate the filter with the update to  "Products"."Product" = ('@{nested}{Maintenance}') of the first analysis?
Because even if I did, the result in Edit Formula would have been:
FILTER("Base Facts"."Revenue" USING ("Offices"."Office" IN (SELECT saw_1 FROM (SELECT "Base Facts"."Billed Quantity" saw_0, "Offices"."Office" saw_1, "Products"."Product" saw_2, DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product") saw_3 FROM "Sample Sales Lite" WHERE ("Products"."Product" = ('Maintenance')) AND ("Base Facts"."Billed Quantity" > 0)) nqw_1 ))) 
with where condition including:
("Products"."Product" = ('Maintenance'))

Instead of: 
("Products"."Product" = ('@{nested}{Maintenance}'))

2. If you try to recreate my steps with the Sample Sales, it wouldn't work, because the Product column has "Descriptor ID column" defined in the administration.  So I cheated and the prompt formula was actually: "Products"."Product" || ''

Monday, September 2, 2013

OBIEE 11.1.1.6.12 is available for OBIEE and Exalytics + bug fixed

The patch 11.1.1.6.12 is available from Oracle support site (support.oracle.com). The first part of the patch is Patch 16986663

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, 11.1.1.6.8, 11.1.1.6.9 , 11.1.1.6.10 and 11.1.1.6.11.

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 16986663 readme file. 

Since 11.1.1.7 is available, I suspect it's the last 11.1.1.6.X patches.

The Oracle Business Intelligence 11.1.1.6.12 patchset comprises a number of patches:
Patch Abstract
16986663 Patch 11.1.1.6.12 (1 of 7) Oracle Business Intelligence Installer
16986677 Patch 11.1.1.6.12 (2 of 7) Oracle Real Time Decisions
16986644 Patch 11.1.1.6.12 (3 of 7) Oracle Business Intelligence Publisher
16986692 Patch 11.1.1.6.12 (4 of 7) Oracle Business Intelligence ADF Components
16986703 Patch 11.1.1.6.12 (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)
16986558 Patch 11.1.1.6.12 (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) 
16986723 Patch 11.1.1.6.12 (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 13952743JDeveloper 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).
 I'm not aware of any new features in 11.1.1.6.12.



Important Notes To Read Before Installing The Oracle Business Intelligence 11.1.1.6.12 Patchset
Note the following:
  • Oracle Exalytics customers must only install this Oracle Business Intelligence patchset if it is certified for the specific Oracle Exalytics Patch or Patchset Update that they are applying. For more information see Oracle Fusion Middleware Installation and Administration Guide for Oracle Exalytics In-Memory Machine and the Oracle Exalytics certification information.
  • Oracle Fusion Applications customers must not install this Oracle Business Intelligence patchset.
  • The Oracle Business Intelligence 11.1.1.6.12 Patchset is cumulative and may include patches that you may have already applied to the BI System. Therefore, when you are installing the patchset, you might see warning messages indicating that earlier patches are being rolled back. These warnings simply indicate that oPatch is working correctly, and do not require any action.
  • If you have horizontally scaled out the BI system on to multiple machines, then you must apply the Oracle BI EE 11.1.1.6.12 patches and the JDeveloper patch 13952743 to all machines in the cluster.
  • Customers installing this Oracle Business Intelligence 11.1.1.6.12 patchset on the HP Itanium platform must first download and apply patch 14016752 from My Oracle Support. Note that you must apply patch 14016752 before installing the Oracle Business Intelligence 11.1.1.6.12 patchset. Follow the instructions in the Readme file for patch 14016752 carefully. In particular, be sure to set the ORACLE_HOME environment variable to the <Middleware Home>/<Oracle BI Home> directory (for example, prod_mwhome/Oracle_BI1) before applying patch 14016752.
  • When the patchset installation is complete and the BI System is running again, end users might experience unexpected behavior due to pre-existing browser sessions caching javascript from the earlier Oracle BI release. To avoid unnecessary support requests, ask all end users to clear their browser cache.
 

Bugs Fixed By This Patch

Publisher

114705265: CAN'T VIEW A REPORT WHEN DATA SOURCE IS ANSWER SAVED IN THE JAPANESE NAME FOLDER
15937202: DUP BUG 14182507 - COULD NOT LOAD SCHEMA INFORMATION ERROR WHEN USING QUERY BUI
15953794: UNABLE TO DELIVER LARGE FILE VIA SFTP
16297877: APP-FND-02938 MULTI-ORGANIZATION ROUTINE FAILED WHEN SELECT A RESP IN MYACCOUNT
16310357: BI PUBLISHER: REPORTS ARE SCHEDULED TO RUN EVERY 2 WEEKS BUT RUN EVERY WEEK.
16371395: NESTED GROUP CAN NOT BE DISPLAYED CORRECTLY IN EXCEL TEMPLATE
16703527: ANCHOR TWO PIVOT TABLES SO THEY APPPEAR SIDE BY SIDE IN RTF TEMPLATE IS FAILING
16738602: PERMISSION SETTING NOT DISPLAYED PROPERLY IF MOR THAN 31 ROLES
16883295: BI PUBLISHER PARAMETER NAMES TRANSLATION NOT RETAINED ON REPORT REFRESH - TRANSL
16927420: WEBSERVICE API - GETALLSCHEDULEDREPORTHISTORYINFO ALWAYS RETURNS NULL



EPM


Known Bug With This Patch:
13974353: CONNECTION STRING USED TO ACCESS LEASING TABLE IS INCORRECT


OBIEE

14060693: DASHBOARD IS DELETED WHEN SAVE DASHBOARD AS WITH THE SAME NAME AND LOCATION
14115628: TABLE VIEWS ARE STRETCHED WHEN USING PRINTABLE PDF OPTION
14305365: CUSTOM FOLDER HEADING USING VARIABLES NOT WORKING CORRECTLY
14478665: UNABLE TO EDIT ANALYSIS WITH SELECTION STEPS WHEN SELECTED VALUES ARE RELOADED
14542322: STRESS - SAWSERVER CRASHED DURING BI UI ENHANCEMENT STRESS TEST
14558044: PS SERVER CRASHING DUE FILTER IN DERIVED CRITERIA
14599136: EXPORT TO EXCEL IN FOREIGN LANGUAGE CAUSES DATA FIELD TO HAVE MANY DIGITS
14601649: HTML - JAVA SCRIPT USED IN STATIC VIEW DOES NOT WORK PROPERLY.
14635134: CANNOT BUILD FILTERS ON DATE COLUMNS IN BI COMPOSER
14781615: UNION SORT IS NOT WORKING ON TABLE VIEW
15955919: REPORT HEADER/FOOTER MISSING AFTER PROMPT VALUE CHANGED AND FILE EXPORT TO EXCEL
15976799: ASSERTION FAILURE ERROR WHEN HAVING GROUP BY SET IN ADVANCED TAB
16103936: GRAND TOTAL NOT DISPLAYED WHEN MULTIPLE GROUPS ARE USED IN ANALYSIS
16195598: DOUBLE CLICK ON DIMENSION COLUMN RESULTING IN DUPLICATED COLUMNS ADDED TO VIEW
16230685: ISLEAF ON ESSBASE GIVES ERROR- TARGET DATASOURE DOES NOT SUPPORT THE FUNCTION
16355919: 11.1.1.5.X FIX FOR BUG 16265027 CHART WITH SECTIONS EXPORTS TO PDF INCORRECTLY
16368595: CHARTS SOMETIMES DISAPPEAR WHEN UNDO DRILLS ARE CLICKED
16404175: ADMINTOOL VIEWMEMBER/UPDATEMEMERCOUNT FAIL ON LEVELS FROM MULTI-HIERARCHY DIM
16446150: CRASH IN PDF PRINT
16613671: PRIVILEGES IN WEB CATALOG FOR USER POPULATION - CAN LIST USERS REAPPEARING
16656067: GRAPH NOT RENDERED CORRECTLY WHEN USING A CUSTOM LOCALE
16727989: OBIEE 11G - DASHBOARD REPORT RETURNED - ERROR CODES: OPR4ONWY:U9IM8TAC:OI2DL65P
16758207: ACCESS DENIED IN DASHBOARD EDITOR AFTER COPY WEBCAT FOLDERS FROM DEV TO SIT
16783539: MIGRATION OBIEE 11.1.1.5 TO 11.1.1.6.7 CAST FUNCTION FAILING WITH ERROR
16820210: INVALID ALIAS FORMAT ERROR TABLE_NAME.COLUMN_NAME REQUIRED CASE STATEMENT SPLIT
16848280: DASHBOARD PROMPT BASED ON VARIABLE DOES NOT FILTER BIP REPORT LINK ON DASHBOARD
16855300: USER FOLDERS GETTING DELETED DURING CATALOG VALIDATION USING REPORT OPTION
16925168: BAD XML INSTANCE! ERROR WHEN SAVING DASHBOARD CUSTOMIZATION

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
14743304: FIX FOR BUG 14170374 REQUIRES ADDITIONAL CHANGE TO OPMN.XML
16531368: DOCUMENT THE ROW SPANNING ISSUE IN RELEASE 11.1.1.6.10
16687063: FIX FOR BUG 15911537 REQUIRES ADDITIONAL SETTING TO THE INSTANCECONFIG.XML FILE