OBIEE - is this column used anywhere and where?

There are times, I have a vivid image of myself in the role of Salvatore (The name of the rose). Banging on my head and shouting "Stupido, Stupido!" Unlike him, I really mean it.

Last week I was asked by a customer "How do I know where a column I want to modify is used? Maybe nobody uses and I can delete it?" Instead of giving him the answer immediately, I started mumbling... So this note is a mostly for myself (as most of this blog) and for that customer.

In OBIEE there is a tool named Catalog Manager.

It's easy to find (Start->All Programs...) on Windows installation and requires Graphical Display on  Linux/Unix (MWHOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\runcat.cmd). To the best of my knowledge, it comes with Admin installation, starting 11.1.1.6 as well.

You can open the catalog you want to research (preferably offline) and enter the column name. Select Search and Advanced Search. Remember to mark the XML option. 

In my example I created a new analysis with CHANNEL_CLASS column. Saved it and searched for CHANNEL_CLASS. I found my Analysis.

Now I deleted the analysis. And the new search returned empty.

 

What happens with Dashboards? Prompts? KPIs?

 To have a bigger test I created 4 object that contains the CHANNEL_CLASS column:

Dashboard, Prompt, Analysis and saved Selection Steps.

To find the first 3 you need to turn on the XML search option, but it doesn't find selection steps. For that you will need to turn on the Property Value option in the Advanced Search.

You can also search by specific Type and by dates...

They few other interesting tools in the Catalog Manager such as XML Search and replace, Report and new Multi-Tenancy option... all of that some other time.

Haaretz 28-5-2013 and misleading graphs

בכתבה בהארץ מתאריך 28-5-2013 (קישור) יש גרף.

הגפיקה יפה לעין. הרוחב של הסיגריות אחיד ואין בעיה של נפח לא פרופורציוני, כפי שקורה לעתים קרובות בגרפים מסוג זה. אז מה הבעיה ולמה אני מתעצבן?

יש שתי בעיות:
1. בילבול מכוון של הקורא בשם הגרפיקה היפה
2. אי שימוש בגרף מעניין

1. בילבול מכוון

הגרפיקאים החליטו שהם רוצים סיגריות עם פילטרים.זה מאפשר לו להראות בצורה גרפית יפה את ההבדל בין השנים. אבל ערכים נמוכים, כמו עישון של נשים ערביות, היו גורמים לו להציג רק חלק מהפילטר. לכן נעשתה החלטה שקו הפילטר של הסיגריה יהיה נקודת האפס של הגרף.
זה לא ברור כלל לצופה הממוצע. על כן, יש הטעיה ויזואלית של הערכים. לעתים (עבור ערכים קטנים) בסדרי גודל של 2 ו-3.
אני יכול להבין את הרצון להשתמש בפילטרים אבל לשם בהירות הגרף חשוב לציין את נקודת האפס. למשל (סלחו לי אני לא גרפיקאי):

 

2. אי שימוש בגרף מעניין

מה אומרת כותרת הכתבה (דו"ח חדש מצביע על ירידה חדה בשיעור המעשנים בישראל)? מה המידע הדרמטי והמעניין כאן? שיש ירידה משמעותית בין שני הסקרים בעישון. שיש עליה גדולה מאד בעישון נשים ערביות וירידה חדה בקרב נשים יהודיות...

מה הגרף האידיאלי לזה? גרף של אחוז שינוי משנה קודמת. אולי כתוספת לגרף הנוכחי (בהנחה שאחוז שינוי זה ערך שנה נוכחית פחות קודמת, כל זה מתחלק בשנה קודמת). הנה דוגמא מאד לא יפה שעשיתי ב2 דקות באקסל:

פתאום רואים מה השינוים המשמעותיים בצורה ברורה.

שני הגרפים תורמים כאן. המקורי מדגיש את ההבדלים בין האוכלוסיות השונות. הגרף הנוסף מדגיש את השינוי בין הסקרים.

מצגות פורום BI מ21 למאי בנושא אינטגרציה

מצגות פורום BI בנושא אינטגרציה, מ21 למאי, נמצאות באתר http://obiee.co.il/ בדף ההרשאות שבסרגל הצדדי.

OBIEE 11.1.1.7 - Include Null Values

In OBIEE 11.1.1.7 there is a change of behavior regarding Rows with Null Values. What is the difference between new features and change of behavior? It means that things that used to work in previous releases work differently.

Lets start with new features: In most relevant presentation object properties (table, pivot, chart...) there is a new flags regarding "Include Rows with only Null Values" or in Pivot, 2 new flags: "Include Rows with only Null Values" and "Include Columns with only Null Values".

This a result with this option turned off:

 

 Here is the same with optioned turned on:

You can see that now I have an Null Amount_Sold for Catalog Channel, I didn't have in the previous result.

In the past to achieve this functionality you had to do an right outer join in the Business Layer. Most DBAs I know hate outer joins because of performance reasons.

What happened here?

The regular (first query) did the following select:

WITH
SAWITH0 AS (select sum(T43863.AMOUNT_SOLD) as c1,
     T43790.CHANNEL_DESC as c2,
     T43790.CHANNEL_ID as c3
from
     CHANNELS T43790,
     SALES T43863
where  ( T43790.CHANNEL_ID = T43863.CHANNEL_ID )
group by T43790.CHANNEL_DESC, T43790.CHANNEL_ID)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select distinct 0 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c1 as c4
from
     SAWITH0 D1
order by c2, c3 ) D1 where rownum <= 65001

The important part of the select statement is:
select sum(T43863.AMOUNT_SOLD) as c1,
     T43790.CHANNEL_DESC as c2,
     T43790.CHANNEL_ID as c3
from
     CHANNELS T43790,
     SALES T43863
where  ( T43790.CHANNEL_ID = T43863.CHANNEL_ID )
group by T43790.CHANNEL_DESC, T43790.CHANNEL_ID)

Now lets see the code for the "Include Rows with only Null Values" turned on:

We have 2 queries!

Q1:
select T43790.CHANNEL_DESC as c1,
     T43790.CHANNEL_ID as c2
from
     CHANNELS T43790
order by c1, c2
Q2:
select T43790.CHANNEL_DESC as c1,
     T43790.CHANNEL_ID as c2,
     sum(T43863.AMOUNT_SOLD) as c3
from
     CHANNELS T43790,
     SALES T43863
where  ( T43790.CHANNEL_ID = T43863.CHANNEL_ID )
group by T43790.CHANNEL_DESC, T43790.CHANNEL_ID
order by c1, c2

So what can I do to show the Null lines?

Option 1:

In the Criteria, Analysis properties, at Data Tab, mark the "Include Null Values":

Option 2:

Do it for each Visualization in the Analysis as shown before.

Great! No outer join, just a quick select on the dimension.

So why I was blabbering about "change of behavior"? This is just great new functionality!

Lets see what happens with outer joins...

I duplicated the Channels Dimension with an Alias and in the Business Layer have set a right outer join between the new Channels and Sales Fact.

I created an Analysis:

I expected to see 5 lines of results, but I have only 4!

 This is different from previous releases.

The SQL has outer join on Channels:

select T45370.CHANNEL_DESC as c1,
     sum(T43863.AMOUNT_SOLD) as c2
from
          SALES T43863 left outer join
          CHANNELS T45370 /* Channels_outer */  On T43863.CHANNEL_ID = T45370.CHANNEL_ID
group by T45370.CHANNEL_DESC
order by c1

 But the result doesn't show the null lines.

When applying the previously shown options it works well and I see the Null lines as well. I just have to want them explicitly!

This is a change of behavior.

P.S.
Thanks to Eira and Rakefet from Oracle ACS for their enthusiastic remarks about this feature.

OBIEE 11.1.1.6.10 is available (and bug fixed)

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



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

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 and  11.1.1.6.9 .

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

Since 11.1.1.7 is available, I suspect we will not see many more 11.1.1.6.X patches.

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

Patch	Abstract
16504136	Patch 11.1.1.6.10 (1 of 7) Oracle Business Intelligence Installer
16504143	Patch 11.1.1.6.10 (2 of 7) Oracle Real Time Decisions
16504148	Patch 11.1.1.6.10 (3 of 7) Oracle Business Intelligence Publisher
16504154	Patch 11.1.1.6.10 (4 of 7) Oracle Business Intelligence ADF Components
16504156	Patch 11.1.1.6.10 (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)
16427939	Patch 11.1.1.6.10 (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.10 (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.10.

Bugs Fixed By This Patch

Publisher:

13825454: ORACLE DB SECURITY DONOT HANDLE USR ACCT STATUS EXPIRED(GRACE) FOR AUTHORIZATION
15886696: PARAMETERS WITHOUT DEFAULT VALUES ALLOW MANUAL ENTRY OF DATA USING IE7
16036921: MDX QUERIES FAIL TO LOAD WHEN SAVING, OR IF SAVED, GENERATE NO DATA
16183449: TRIGGER DOES NOT ACCEPT VARIABLE PARAMETER {$SYSDATE()-7$}
16191483: UNABLE TO SEE REPORTS AFTER UPLOAD ON ONE OF TWO NODES
16303357: 10G RTF TEMPLATE FAILS IN 11G WITH XPATH ERROR DUE TO INVALID DATA TYPE CASTING

OBIEE:

8198137: GENERATE METADATA DICTIONARY FAILS A COMPLETE EXPORT ON BIG RPD (OUT OF MEMORY)
12320264: MULISELECT PROMPT AND CLIENTSESSIONEXPIRYMINUTES SETTINGS THROW ERROR
13531449: COMMON HEADER SHOWS UP WHEN CLICKING ON DASHBOARD PAGE TAB IN PORTAL PAGES
13533513: OBIEE GRAPH LEGEND LABEL CONCATENATION ISSUE
13950601: QA: RC2: CONDITIONS LIST OF VALUES DO NOT WORK, NOTHING GETS RETURNED
14136472: JAVASCRIPT IN TEXT DASHBOARD OBJECT WORKS IN 11.1.1.5.0 DOES NOT WORK 11.1.1.6.0
14278050: UNABLE TO APPLY CONDITIONAL FORMAT FOR COLUMN HAVING CASE STMT IN GRAPH PIVOTED
14372406: DELETE AGGREGATES RESULTS IN A CHECKIN FAILURE
14521397: DURING CONSISTENCY CHECK ERROR IN SACLIENTRP.H OR SOSECURERPGATEWAY.CPP
14697631: PERFORMANCE ISSUE ON QUERY TO MICROSOFT SSAS
14759014: ERROR WHILE RUNNING REPORT WITH 6 UNION QUERIES
14834396: OPENING THE ADMINISTRATION > MANAGE PRIVILEGE VIEW IS SLOW
14848445: ENCOUNTERING "[NQSERROR: 37005] TRANSACTIONAL UPDATE FAILED." IN ONLINE MODE.
15911357: DISPLAY AS RUNNING SUM NOT SHOWING ZERO WHEN IT IS A NULL VALUE
15916915: CRASH IN DASHBOARDVIEWSUBMITCONDITIONFUNCTOR::GETCONDITIONCURSOR
15989894: OBIEE 11G: UNABLE TO ADD BIN IN COMBINED REQUESTS.
16021684: NO RESULTS DISPLAYED IN A MDX QUERY AGAINST ESSBASE WITH A FILTER
16068402: GRAPHS NOT SHOWN ON IE WHEN OBIEE IS BEHIND A REVERSE PROXY
16098352: MARKETING SEGMENTATION GENERATE PREVIEW THROWS NQSERROR 14065 ILLEGAL CROSS JOIN
16179354: ESSBASE REPORTS RETURN NO RESULTS- WORKED IN 11.1.1.6.4
16195852: OBIEE11GBI SERVER NOT STARTING AFTER SETTING OBIEE_IMPLICIT_CONVERT_TIMESTAMPADD
16227537: MDX GENERATED AGAINST SSAS CUBE WILL FORCE SSAS TO BECOME UNRESPONSIVE
16232096: AGGREGATES CREATION IGNORED AT LEVELS DEFINED ACROSS MULTIPLE HIERARCHIES
16250380: ADMIN TOOL CRASH WHEN WE TRY TO MERGE 2 REPOSITORIES
16309432: SOAP HTMLVIEWSERVICE FILTEREXPRESSIONS IS BROKEN
16387359: PRESENTATION SERVICES CRASHES WHILE NAVIGATING VIA ACTION LINKS
16401538: 10G->11.1.1.6.7 UPGRADE RPT CRASH SAWSERVER WHEN NO COLS IN COL/MEASURE SECTION
16432997: PROMPT DEFAULT TO SESSION VARIABLE NOT SETTING PRESENTATION VARIABLES INITIALLY

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
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
15854192: ERROR MESSAGE RETRIEVED WHEN TRYING TO CREATE AGENT WITH EMAILS AS RECIPIENTS
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