Kilo Mike: August 2015

Saturday, August 29, 2015

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!

Friday, August 14, 2015

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.

Tuesday, August 11, 2015

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!