OBIEE stacked bar chart grouped by values

Once in a while you need to create a stacked bar graph grouped by some values. Unfortunately, OBIEE doesn't quite support this, because the stacked bars are distributed evenly among the X axis. However, its possible to get it done with some workarounds.
Imagine you would have a fact table structure where in one row you would have two current year measures and two last year measures and you would like to show them by months as in the following picture.

Desirable outcome

OBIEE doesn't support this straight forward, but you can manage to pull it off with some tricks. I used a "UNION ALL" report in OBIEE answers to get it done and added three separate parts each part having the month column, the four measure columns and an additional dummy column to distinguish the three datasets.
The first part includes measures of the current year and SUM(0) in the column formula for the both LY columns.

First part of UNION ALL

 The second part includes SUM(0) for the current year measures and the actual LY measures.

Second part of UNION ALL

The third part includes SUM(0) for all four measures. This set will be the set that will serve as the divisor in the graph.

Third part of UNION ALL

Now hide the dummy column.

Next, arrange all the columns in the graph build dialogue in the following way.

Arranging columns in the graph

And there you have it!

OBIEE grouped stacked bars

Output the list of dashboards in OBIEE

Here I'm going to show how to output a list of all dashboard pages available to the particular user. Such a thing, for example, could be used to substitute the OBIEE default page with a custom made dashboard page.
This can be done with javascript and using the element in the dashboard page. This script will output a list of only available dashboards and a user will see only those ones, where he has an access.
Take the following piece of code and put it on the OBIEE dashboard page in the text element with the "Contains HTML Markup" checked:

<img id="loading" src="/res/s_FusionFX/common/page_lev_connected.gif" />

<div id="dash_list"></div>

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>

<script type="text/javascript">

$.ajaxSetup({

    beforeSend:function(){

        $("#loading").show();

    },

    complete:function(){

        $("#loading").hide();

    }

});



$.ajax({

url: "saw.dll?getDashboardList"

}).done(function( data ) {

    var start = data.indexOf('[');

    var end = data.lastIndexOf(']');

    var len = end-start+1;

    var json_str = data.substr(start,len);

    var json_obj = jQuery.parseJSON(json_str);

    var str = '<br/><br/><table style="width:100%"><tr>';

    $.each(json_obj, function() {

        if (this.folderName!=='Home Page'){

            str += '<td valign="top"><b><font size=4; color=#CCCC00;>' + '&nbsp;&nbsp;' + this.folderName + '</b></font><br/><br/>';

            $.each(this.portals, function() {
 if (this.portalName=='Management Dashboard'){
                   str += '&nbsp;&nbsp;&nbsp;&nbsp;' + '<img src="res/s_FusionFX/common/page_lev_connected.gif"><font size=3><b><a href="saw.dll?Dashboard&PortalPath=' + this.portalPath + '">' + this.portalName + '</b></font></a><br/>';}
else {
                   str += '&nbsp;&nbsp;&nbsp;&nbsp;' + '<img src="res/s_FusionFX/common/page_lev_connected.gif"><font size=3><a href="saw.dll?Dashboard&PortalPath=' + this.portalPath + '">' + this.portalName + '</font></a><br/>';}

            });

            str += '</td>';

        }

    });
str += '</tr></table>'
$('#dash_list').html(str);

});

</script>

The output of the script on the environment I was using:

You can also make some adjustments to the previous code to adjust it to your requirement. I highlighted 4 likely places that you might want and need to adjust.

<img id="loading" src="/res/s_FusionFX/common/page_lev_connected.gif" />
This would be the picture that shows up while the list is being populated (only a second or two though). Here I used the same Oracle logo from OBIEE top right corner, but you could as well add your own picture on the server and make the code point to your picture.

if (this.folderName!=='Home Page'){
This would exclude a particular dashboard from the outputted list. It's pretty useless and you probably wouldn't want to include the same dashboard you're on here. You would need to modify the name according to your particular dashboard name then.

if (this.portalName=='Management Dashboard'){
You might want to highlight a particular dashboard on the list. For such case you can write a separate branch with a special font, picture or something else.

<img src="res/s_FusionFX/common/page_lev_connected.gif">
You can change the image that shows up before the each dashboard. Here I used the same Oracle logo, but you can also customize it to your needs.

Of course, there are a lot of other smaller or bigger adjustments you can do - font colors, sizes etc.

I already did and you probably could use this script for a custom home page and replace the Oracle default home page. In my case I made and added a custom logo of my company, added some custom links to related internal systems on one side, ETL status reports - on the other side and the script in the middle of the page to populate the list of available dashboards in the middle of the page. I also added custom images to the server to use as the bullets for each dashboard. Then I used the PORTALPATH repository variable to direct the user to this page and also modified header.js javascript file to redirect the "home" link from the top of the OBIEE page to this new customized page.

Save Postgres execution plan in database table

Postgres database doesn't provide a built-in decent way to follow statement execution plans at different points in time as it is in Oracle, for example. Imagine an ETL query takes two hours during the night while during the day it's only two minutes. Is it a different execution plan generated? In Postgres it's pretty hard to find that out unless you're sitting there during the night and following up on it.

One actually pretty simple way to get it done could be with your ETL tool and store the execution plan output together with the generation time in a database table. Here I'm using Kettle, but the same would most likely be possible with every other software.

First create a table in the target database:

CREATE TABLE save_xplan_output
(
  query_plan text,
  x_plan_date timestamp without time zone
)

Next, create a "Table Input" step and write the following query:

Then add a "Get System Info" step to get the date and time:

Next, add the target table for the plan:

After creating the hops between elements your transformation would look like this:

After executing the transformation you can check the results:

You could put this kind of transformation somewhere in the ETL flow next to the problematic statement and you could be almost sure that the statement would have the same statement. If the execution plan differs then you can try doing something about the table statistics, the query itself. If not, then see what's going on with the environment, parallel activities etc.
Thanks for your attention!

OBIEE Page Caption Changes

Among many different OBIEE customization things one might also want to change the page captions that show up at the browser. Not only it would personalize the system, but it might also be helpful for a user who has his 10+ tabs open to find his OBIEE environment.
That's what I'm gonna show in this post and in this example I will just change the "Oracle BI(...)" default messages to "Test Environment".

Fortunately, you don't need to change any system javascripts and/or .css files and you can simply do this with adding or modifying your custom skin XML files.

First, you should either add or modify the already existing productmessages.xml file in your messages folder and have these kind of entries there:

<WebMessage name="kmsgProductPortal"><TEXT>  Test Environment </TEXT></WebMessage>

<WebMessage name="kmsgProductAnswers"><TEXT>Test Environment - Answers</TEXT></WebMessage>

<WebMessage name="kmsgProductDelivers"><TEXT>Test Environment - Agents</TEXT></WebMessage>

<WebMessage name="kmsgProductAdvancedReporting"><TEXT>Test Environment - Publisher</TEXT></WebMessage>

<WebMessage name="kmsgProductGeneral"><TEXT>Test Environment</TEXT><HTML>Test Environment</HTML></WebMessage>

<WebMessage name="kmsgProductAnswers"><TEXT>Test Environment - Answers</TEXT></WebMessage>

Another file to modify or add is saw.catalog.xml file under the uicmsgs folder. You should have the following entries there:

<WebMessage name="kmsgCatalogWindowTitle"><TEXT>%0% - Test Environment - Catalog</TEXT></WebMessage><WebMessage name="kmsgCatalogStartingWindowTitle"><TEXT>Test Environment - Catalog</TEXT></WebMessage>

After the changes are done, you would need to reload the XML files from the Administration window in your OBIEE environment and the changes should be there.

Picture Addition for OBIEE Login Page

Here I'm gonna show how to add a nice background picture for the OBIEE login page.
After finding the desired picture you should place it in the following location:
"(...)\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\sk_blafp\login"

Next, you should take a backup of the login.css file located in the same directory and then open it up for modification. It's the first element "body" that you need to change. Take the following piece of code:

body
{
background-color: #fff;
background-image: url(background_blue_whitegradient_.png);
background-position: top left;
background-repeat: repeat-x;
margin-bottom: 0;
margin-left: 0;
margin-right: 0;
margin-top: 0;
}

And change it to(considering that the name of the new desired background is "background.jpg"):

body {
    background-image: url(background.jpg);
    background-position: top center;
    background-color: #fff;
    background-repeat: no-repeat;
    background-size: 100%;
    margin-left: 0;
    margin-top: 0;
    margin-right: 0;
    margin-bottom: 0;
}


Restart the OBIEE servers and enjoy the result.

Of course, you might also want to customize OBIEE and change the Oracle logo or the brand name at the top left corner, but there are already plenty of sources describing that and I won't cover that here.

Thanks for watching!

Event table bug

While implementing OBIEE physical cache clearance run into a weird behavior of  the event table and the non-relevant row handling. At first couldn't really find out the reason and the cure, but in the end came to the root of the problem. I'll describe it in this entry.

I assume that you're familiar with the OBIEE event table that is used for clearing the cache entries of the database tables, so I'm not gonna go through that.

At first, let's insert two new rows in the table with the SYSDATE as the update time.

Now let's wait for the interval to pass and the entries to be deleted from cache. After a few minutes, when we select from the event table, we can see that instead of two entries there are four ones with "gs" being the value in OTHER_RESERVED column. Why?

Didn't find anything useful in Google, there were people who have had the same problem, but no solution to the problem was in the topic.
My attention was drawn by a sentence in Oracle documentation about the UPDATE_TS column. It said: "This needs to be a key (unique) value that increases for each row added to the event table."
So, I tried inserting the same rows, this time with an incremental SYSDATE value. Like this:

After the regular cache clearance, you can see that there are no entries in the table anymore.

After doing some additional testing, it did turn out that the event table really does handle rows with identical UPDATE_TS columns in an awkward way. From the nqquery.log it didn't seem that the cache entries for the corresponding tables with duplicate rows were continuously purged though.

So, it might not be advisable to use a simple SYSDATE as the update timestamp for the event table. Using CURRENT_TIMESTAMP in a single transaction also wouldn't help, thus you could create a custom procedure that receives the table name and inserts it into the table with the highest current UPDATE_TS value incremented with one second and use SYSDATE, if the table is empty. For example:

create or replace procedure add_to_event_table (database_name in varchar2, catalog_name in varchar2, schema_name in varchar2, table_name in varchar2)
as
max_date date;
begin    
select NVL(max(update_ts+1/86400),SYSDATE) into max_date from S_NQ_EPT;
INSERT INTO S_NQ_EPT (UPDATE_TS, DATABASE_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME)
VALUES (MAX_DATE, DATABASE_NAME, CATALOG_NAME, SCHEMA_NAME, TABLE_NAME);
COMMIT;
end;

Concatenate distinct row values of multiple columns effectively

Converting columns to rows, rows to columns, can sometimes be a bit of a headache for database developers. Recently run into a case where I needed to modify a dataset in order to have all the distinct row values from two separate columns for one ID concatenated in one row in the same separate columns. Here's a visual example of a small test dataset:

What I needed to get was:
ID                            A                             B
1                        Juliet/Krisjanis          Kilo/Mike
2                        Tango                      Mike
3                        Juliet                        Bravo/Charley

 From the first look might seem easy to do, but actually isn't at all. You might to select the values from each column in a separate query and then join them by ID or have several nested queries or...

Here's a very nice and compact way of doing it without a need of several subqueries or similar struggles. Just use regepxp_replace and listagg functions together with a windowing function like this:

regexp_replace( listagg(trim(your_column), '/') within GROUP (
    ORDER BY your_column) OVER (PARTITION BY your_id) ,'([^/]+)(/\1)+', '\1')

So, here's how it looks on the previous dataset:

Thanks for your attention!

Customize OBIEE Export Options

The default export options at OBIEE 11g has a load of options and some of them might not even be really used. Therefore removing some of the unnecessary ones and restructuring the dialog could ease the life for users.

In this example I'm gonna show how to remove the old 2003 PPT and Excel, Web Archive and XML formats, rename Excel 2007 (to "Excel (formatted)"), PPT 2007 (to "PowerPoint") and Tab Delimited Format (to "Excel (raw data)") options and move them all in one column.




There are three separate places to export the report (dashboard, answers and the catalog) and each of these places require some separate changes.

First, let's do the changes for the export option names. If you have a customized skin, you would need to three files or modify the existing ones in the XML message folder. If not, you need to modify s_blafp skin XML files (viewmessages.xml, viewscontrolmessages.xml and uicmsgs/saw.catalog.xml).


Contents of viewmessages.xml:

<?xml version="1.0" encoding="utf-8"?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable system="CompoundViewUI" table="LinkText">
<WebMessage name="kmsgEVCLinkDownloadDataTab">
<HTML>Excel (raw data)</HTML>
</WebMessage>
<WebMessage name="kmsgCatalogActionExportExcel">
<HTML>Excel (formatted)</HTML>
</WebMessage>
<WebMessage name="kmsgEVCLinkDownloadPowerpoint">
<HTML>PowerPoint</HTML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>


Contents of viewscontrolmessages.xml:

<?xml version="1.0" encoding="utf-8"?>
<WebMessageTables sawm:systemMessages="false" xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable>
<WebMessage name="kmsgEVCDownloadLinks">
<HTML>
<script src="fmap:downloadguard.js" type="text/javascript"/>
<sawm:choose>
<sawm:when name="noMenu">
<a href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();Download('@{command}&amp;path=@{path}&amp;Format=excel&amp;Extension=.xlsx'); return false">
<sawm:messageRef name="kmsgEVCLinkDownloadExcel"/>
</a>&amp;nbsp;
<a href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();Download('@{command}&amp;path=@{path}&amp;Format=txt&amp;Extension=.csv'); return false">
<sawm:messageRef name="kmsgEVCLinkDownloadData"/></a>&amp;nbsp;
<a href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();Download('@{command}&amp;path=@{path}&amp;Format=mht'); return false">
<sawm:messageRef name="kmsgEVCLinkDownloadMHTML"/></a>
</sawm:when>
<sawm:otherwise>
<a href="javascript:void(null)" name="ReportLinkMenu" onclick="return NQWPopupMenu(event,'idDownloadLinksMenu@{jsStatePath}')" title="@{exporttitle}">
<sawm:messageRef name="kmsgEVCLinkDownload"/>
</a>
<div class="NQWMenu" id="idDownloadLinksMenu@{viewID}" onmouseover="NQWMenuMouseOver(event)">
<sawm:messageRef name="kuiMenuShadowBegin"/>
<a class="NQWMenuItem NQWMenuItemWIconMixin" href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();Download('@{command}&amp;path=@{path}&amp;Format=pdf&amp;Attach=1'); return false">
<table cellpadding="0" cellspacing="0" class="MenuItemTable">
<tr>
<td class="MenuItemIconCell">
<img alt="" src="fmap:catalog/exporttopdf_ena.png"/>
</td>
<td class="MenuItemTextCell">
<sawm:messageRef name="kmsgEVCLinkDownloadPDF"/>
</td>
</tr>
</table>
</a>
<a class="NQWMenuItem NQWMenuItemWIconMixin" href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();Download('@{command}&amp;path=@{path}&amp;Format=excel2007&amp;Extension=.xlsx');">
<table cellpadding="0" cellspacing="0" class="MenuItemTable">
<tr>
<td class="MenuItemIconCell">
<img alt="" src="fmap:catalog/exporttoexcel_ena.png"/>
</td>
<td class="MenuItemTextCell">
<sawm:messageRef name="kmsgCatalogActionExportExcel"/>
</td>
</tr>
</table>
</a>
<a class="NQWMenuItem NQWMenuItemWIconMixin" href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();return Download('@{command}&amp;path=@{path}&amp;Format=txt&amp;Extension=.csv');">
<table cellpadding="0" cellspacing="0" class="MenuItemTable">
<tr>
<td class="MenuItemIconCell">
<img alt="" src="fmap:catalog/exporttoexcel_ena.png"/>
</td>
<td class="MenuItemTextCell">
<sawm:messageRef name="kmsgEVCLinkDownloadDataTab"/>
</td>
</tr>
</table>
</a>
<a class="NQWMenuItem NQWMenuItemWIconMixin" href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();return Download('@{command}&amp;path=@{path}&amp;Format=powerpoint2007&amp;Extension=.pptx');">
<table cellpadding="0" cellspacing="0" class="MenuItemTable">
<tr>
<td class="MenuItemIconCell">
<img alt="" src="fmap:catalog/exporttoppt_ena.png"/>
</td>
<td class="MenuItemTextCell">
<sawm:messageRef name="kmsgEVCLinkDownloadPowerpoint"/>
</td>
</tr>
</table>
</a>
<a class="NQWMenuItem NQWMenuItemWIconMixin" href="javascript:void(null);" name="SectionElements" onclick="NQWClearActiveMenu();return Download('@{command}&amp;path=@{path}&amp;Format=csv&amp;Extension=.csv');">
<table cellpadding="0" cellspacing="0" class="MenuItemTable">
<tr>
<td class="MenuItemIconCell">
<img alt="" src="fmap:catalog/exporttocsv_ena.png"/>
</td>
<td class="MenuItemTextCell">
<sawm:messageRef name="kmsgEVCLinkDownloadDataCSV"/>
</td>
</tr>
</table>
</a>
<sawm:messageRef name="kuiMenuShadowEnd"/>
</div>
</sawm:otherwise>
</sawm:choose>
</HTML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

Contents of saw.catalog.xml:

<?xml version="1.0" encoding="utf-8"?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" table="Messages" system="saw.catalog">
<WebMessage name="kmsgCatalogActionExportTabDelimited"><TEXT>Excel (raw data)</TEXT></WebMessage>
</WebMessageTable></WebMessageTables>

This would do for the exports at dashboards and answers, but the catalog is a different story and in order to get that fixed you need to change two .js files.
Find the actionhandler.js and catalogaction.js files in (...)\web\appv2\res\b_mozilla\catalog and ${fmw_home}\user_projects\domains\airbit_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\catalog directories and take a backup.
First, let's modify actionhandlerimpl.js. Find the following piece of code:

d = d.concat([saw.catalogaction.ActionId.EXPORT, saw.catalogaction.ActionId.EXPORT_PDF, saw.catalogaction.ActionId.EXPORT_EXCEL_GROUP, saw.catalogaction.ActionId.EXPORT_EXCEL2K, saw.catalogaction.ActionId.EXPORT_EXCEL, saw.catalogaction.ActionId.EXPORT_POWERPOINT_GROUP, saw.catalogaction.ActionId.EXPORT_POWERPOINT, saw.catalogaction.ActionId.EXPORT_POWERPOINT_2007, saw.catalogaction.ActionId.EXPORT_MHTML, saw.catalogaction.ActionId.EXPORT_DATAGROUP, saw.catalogaction.ActionId.EXPORT_CSV, saw.catalogaction.ActionId.EXPORT_TABDELIMITED, saw.catalogaction.ActionId.EXPORT_XML])

And replace it with this:

d = d.concat([saw.catalogaction.ActionId.EXPORT, saw.catalogaction.ActionId.EXPORT_PDF, saw.catalogaction.ActionId.EXPORT_EXCEL, saw.catalogaction.ActionId.EXPORT_TABDELIMITED, saw.catalogaction.ActionId.EXPORT_POWERPOINT_2007, saw.catalogaction.ActionId.EXPORT_CSV])

Next, let's modify catalogaction.js. Find these separate entries and remove:

EXPORT_EXCEL_GROUP: "exportExcelGroup",
EXPORT_EXCEL2K: "exportExcel2K",
EXPORT_POWERPOINT_GROUP: "exportPowerpointGroup",
EXPORT_POWERPOINT: "exportPowerpoint",
EXPORT_DATA: "exportData",
EXPORT_MHTML: "exportMhtml",
EXPORT_DATAGROUP: "exportDataGroup",
EXPORT_XML: "exportXML",

And remove this part of the code:

if (a) {this.addAction(b.EXPORT_EXCEL2K, "kmsgCatalogActionExportExcel2003", "catalog/exporttoexcel_ena.png", null, b.EXPORT_EXCEL_GROUP).addAction(b.EXPORT_EXCEL, "kmsgCatalogActionExportExcel2007", "catalog/exporttoexcel_ena.png", null, b.EXPORT_EXCEL_GROUP)
} else {
this.addAction(b.EXPORT_EXCEL2K, "kmsgCatalogActionExportExcel", "catalog/exporttoexcel_ena.png", null, b.EXPORT)
}

And then find:

addAction(b.EXPORT_POWERPOINT, "kmsgCatalogActionExportPowerpoint2003", "catalog/exporttoppt_ena.png", null, b.EXPORT_POWERPOINT_GROUP).addAction(b.EXPORT_POWERPOINT_2007, "kmsgCatalogActionExportPowerpoint2007", "catalog/exporttoppt_ena.png", null, b.EXPORT_POWERPOINT_GROUP).addAction(b.EXPORT_DATA, "kmsgCatalogActionExportData", "catalog/exporttocsv_ena.png", null, b.EXPORT).addAction(b.EXPORT_MHTML, "kmsgCatalogActionExportMhtml", "catalog/exporttomhtml_ena.png", null, b.EXPORT).addAction(b.EXPORT_PDF, "kmsgCatalogActionExportPdf", "catalog/exporttopdf_ena.png", null, b.EXPORT).addAction(b.EXPORT_CSV, "kmsgCatalogActionExportDefaultCSV", "catalog/exporttocsv_ena.png", null, b.EXPORT_DATAGROUP).addAction(b.EXPORT_TABDELIMITED, "kmsgCatalogActionExportTabDelimited", "catalog/exporttocsv_ena.png", null, b.EXPORT_DATAGROUP).addAction(b.EXPORT_XML, "kmsgCatalogActionExportXML", "catalog/exporttoxml_ena.png", null, b.EXPORT_DATAGROUP)

And change it to this:

addAction(b.EXPORT_EXCEL, "kmsgCatalogActionExportExcel", "catalog/exporttoexcel_ena.png", null, b.EXPORT).addAction(b.EXPORT_POWERPOINT_2007, "kmsgCatalogActionExportPowerpoint", "catalog/exporttoppt_ena.png", null, b.EXPORT).addAction(b.EXPORT_PDF, "kmsgCatalogActionExportPdf", "catalog/exporttopdf_ena.png", null, b.EXPORT).addAction(b.EXPORT_CSV, "kmsgCatalogActionExportDefaultCSV", "catalog/exporttocsv_ena.png", null, b.EXPORT).addAction(b.EXPORT_TABDELIMITED, "kmsgCatalogActionExportTabDelimited", "catalog/exporttoexcel_ena.png", null, b.EXPORT)

This should be it. Now restart the presentation services server and see the results.



Thanks for watching! Hopefully, you will find this useful and helpful.