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.