OBIEE TimeStamp Calculations:

David T showed some nice Timstamp calculations on the OTN forum:

http://forums.oracle.com/forums/message.jspa?messageID=4315664#4315664

1) TIMESTAMPADD(SQL_TSI_DAY, (DayOfMonth(CURRENT_DATE))*-1, CURRENT_DATE) will give you the last day of the previous month...

2) TIMESTAMPADD(SQL_TSI_DAY,DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, (DayOfMonth(CURRENT_DATE))*-1, CURRENT_DATE))*-1,TIMESTAMPADD(SQL_TSI_DAY, (DayOfMonth(CURRENT_DATE))*-1, CURRENT_DATE)) will give you the Saturday prior to the last week of the month.

3) TIMESTAMPADD(SQL_TSI_DAY,1,TIMESTAMPADD(SQL_TSI_DAY,DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, (DayOfMonth(CURRENT_DATE))*-1, CURRENT_DATE))*-1,TIMESTAMPADD(SQL_TSI_DAY, (DayOfMonth(CURRENT_DATE))*-1, CURRENT_DATE))) will give you the Sunday of the last week of the previous month.

Till Next Time

OBIEE Like to bet?

If you like to place a little bet on the GA of OBIEE11G. Here is a change:

http://obiee101.blogspot.com/p/obiee-11g-ga-bet.html Just put your entry as a comment line to the article.

Till Next Time

OBIEE Playing with TopN part 3 the Rank Function

The TOPN function is basically the RANK function with a filter. Knowing this can help us to get around the following error message:

You can translate TOPN(<<column>>,10) to a Where RANK(<<column>>) <=10.

How BottmN(<<column>>,10)?.

Try this: COUNT(<<column>>) – RANK(<<column>>) <=9 {0 based}.

So the BottomN 10 customers become:

SELECT "D0 Time"."T05 Per Name Year" saw_0, "D1 Customer"."C0  Cust Key" saw_1, "D1 Customer"."C1  Cust Name" saw_2, "F1 Revenue"."1-01  Revenue  (Sum All)" saw_3, COUNT("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year")-RANK("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year") saw_4 FROM "Sample Sales" WHERE COUNT("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year")-RANK("F1 Revenue"."1-01  Revenue  (Sum All)" by "D0 Time"."T05 Per Name Year") <= 9 ORDER BY saw_0, saw_4

Till Next Time

OBIEE Brighton 2010

Our dad presented at Brighton 2010, and all he brought back where shirts the size of our nightgowns:

He also brought back a ton of ideas he will be blogging in the near future.

The main themes of the conference were data modelling within OBIEE and the launch of “the product currently in our labs soon to be released but we won’t tell you when…” (Normal people just call it OBIEE 11g).

It was great to meet Kurt Wolf and hear from the OBIEE’s original father how the basics of OBIEE where thought up (while it was still NQuire).

My complements for people from Oracle: Phil Bates, Mike Durran, Adam Bloom and Craig Stewart for NOT telling us when OBIEE 11g will be general available. (Even under intense pressure of everybody else ;-) )

Some other highlights were:

• Robin Muffat (aka RNM1978) had brilliant presentation on how you can load test your OBIEE system and get valid results.
• Emiel van Bockel showed us that his modelling ideas he presented last year actually work.
• Antony Heljula give us some useful thoughts on how to determine your system requirements realistically.

From all the other presentations you could start to see the integration of the whole BI stack around Oracle’s Fusion Middle Ware package.

All and all it was a nice conference and hopefully by next year we can see the first implementations of OBIEE 11g.

Till Next Time

OBIEE Playing With TopN Part 2

In part 1 we ended with:

Let’s try to add the top2 product2 for each customer in the top 10. Start with adding the product column:

You will notice that the revenue for each customer has dropped. This means we first have to “lock” the revenue on a customer / year level:

Okay that fixed, let’s get the revenue by product:

Some how I don’t think this correct. Let’s get the details for one customer:

He has bought several products so let’s tweak this some more. We need to add the TOPN2 Filter for product by customer by year.

Just add as an extra column.

Let’s put it in a pivot to make it even more readable:

Till Next Time

OBIEE Playing With TopN Part 1

Basic question Give back the Top10 Customers:

Simple add a TopN filter to request:

Most manager won’t be satisfied with this report because there isn’t a time element present. Let’s add a year:

 

Okay what about the top10 for each year? Here is one solution:

First convert the filter to SQL:

Add a by to the TOPN Part:

TOPN("F1 Revenue"."1-01  Revenue  (Sum All)",10 by "D0 Time"."T05 Per Name Year" ) <= 10

What about showing the top 2 products for each top10 customer? I will discus that in part2.

Till Next Time

OBIEE Conditional format based on other column in charts

When you want to do a conditional format on a chart in OBIEE by default you only have two choices:

Based on a value or based on a presentation variable.

But what if you want to do it based on an other column. Let’s say you want the bar to be completely red if it’s below the line and completely green if it’s above the line:

First add two ‘dummy’ conditions one for red and one for green:

Next goto the advanced tab and located the conditions in the XML:

<saw:conditionFormats>
            <saw:conditionRule>
               <sawx:expr xsi:type="sawx:comparison" op="less">
                  <saw:columnRef columnID="c1"/>
                  <sawx:expr xsi:type="xsd:decimal">0</sawx:expr></sawx:expr>
               <saw:visualFormats>
                  <saw:visualFormat className="barProp" name="normalBar" color="#FF0000"/></saw:visualFormats></saw:conditionRule>
            <saw:conditionRule>
               <sawx:expr xsi:type="sawx:comparison" op="greaterOrEqual">
                  <saw:columnRef columnID="c1"/>
                  <sawx:expr xsi:type="xsd:decimal">0</sawx:expr></sawx:expr>
               <saw:visualFormats>
                  <saw:visualFormat className="barProp" name="normalBar" color="#00FF00"/></saw:visualFormats></saw:conditionRule>

Chance <sawx:expr xsi:type="xsd:decimal">0</sawx:expr> to the column you want use like: <sawx:expr xsi:type="sawx:sqlExpression">"11 Time Series"."1-07  Revenue  (Qago)"</sawx:expr>

Don’t forget to press the set XML button:

hmmmm this is not what we want!

What went wrong? The expression builder for the chart conditional format accepted our tweak:

The problem is the way the Presentation-server tells the Corda Engine to create the graph. Since the Corda engine can’t ‘see’ the data it needs to get it’s conditions HardCoded. This means the Presentation server will evaluate the first value of the SQL expression and sent it to the Corda engine.

Is there a workaround? It’s actually quit simple. Add two more columns to your table RED and GREEN:

Add them to your chart and make it a stacked bar:

Remove the legend:

Add it to your compound layout and add a extra legend:

Till Next Time

OBIEE Events Calendar

 
First of all Kudos to Hitesh for laying the ground work: http://hiteshbiblog.blogspot.com/2010/04/obiee-showing-data-on-calendar.html
First you have to go to the MooTools site and download the basics:
The mootools core: http://mootools.net/download => choose the uncompressed version, it makes debugging easier.
Next get the More building blocks Date, Scroller, Tips: http://mootools.net/more
Finally get the calendar control: http://dansnetwork.com/mootools/events-calendar/download/
Put everything in a subfolder of the Res folder (if you are using OC4J as webserver, you have to sync both RES folders):

Let’s get some base data to work with: Startdate, Enddate, dayofweek, brand, revenue:

Now we add a narrative view:

In the prefix part we first select the size of the calendar control:

<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECalLarge.css">
or
<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECal.css">
or
<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECalSmall.css">

Next we add the references to the javscript:

<script language="javascript" src="./res/mooTools/mootools-1.2.4-core-nc.js"></script>
<script language="javascript" src="./res/mooTools/mootools-1.2.4.4-more.js"></script>
<script language="javascript" src="./res/mooTools/mooECal.js"></script>

as div tag to hold the body:

<div id="calBody"></div>

and a function to set the background and font color:
<script language="javascript">

function getDiv(holFlag)
{
if(holFlag=='1' || holFlag=='7')
{
return '<div style="background-color:#990000;color:#ffffff;">';
}
else
{
return '<div style="background-color:#999900;color:#ffffff;">';
}
}

finally the header of the control:

new Calendar({calContainer:'calBody', newDate:'1/3/2007',
cEvents:new Array(

The date is the date on which the control wil be opened.
The total prefix should look like this:

<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECalLarge.css">
<script language="javascript" src="./res/mooTools/mootools-1.2.4-core-nc.js"></script>
<script language="javascript" src="./res/mooTools/mootools-1.2.4.4-more.js"></script>
<script language="javascript" src="./res/mooTools/mooECal.js"></script>
<div id="calBody"></div>
<script language="javascript">
function getDiv(holFlag)
{
if(holFlag=='1' || holFlag=='7')
{
return '<div style="background-color:#990000;color:#ffffff;">';
}
else
{
return '<div style="background-color:#999900;color:#ffffff;">';
}
}
new Calendar({calContainer:'calBody', newDate:'1/3/2007',
cEvents:new Array(

In the narrative part we fill the array:

{
title: getDiv('@3') +'@4: '+ '@5 </div>',
start: '@1',
end: '@2',
location: ''
}

In the postfix we close the array:

)
}); </script>

Set the separator to “,”
Warning if you try to save this from the narrative view you will get the ‘opaque’ save screen….

switch to the criteria view first and then save!
Add the narrative to your compound lay-out:

Till Next Time

OBIEE Remove line below guided navigation link

If you want to get rid of the small black line below the guided navigation link:

Found these solutions here:
http://forums.oracle.com/forums/thread.jspa?threadID=1001694&tstart=0

For all dashboards:

Edit the portalcontent.css change:

to

[code]

HR {
visibility:hidden;
}

[/code]

For one dashboard only:

add a text box to the dashboard page:

Add the following code (thanks Joe!)
[code]
<script type="text/javascript">
var aElm=document.getElementsByTagName('hr');
for (var i =0; i <aElm.length;i++)
    {
        aElm[i].parentNode.removeChild(aElm[i]);
    }
</script>
[/code]
And the black line is gone:

Till Next Time

OBIEE Open letter to Oracle

Dear OBIEE product manager,

I hope you are doing well. My friends and I are a bit worried because we haven’t heard from you in a while.

You seem to have missed the OBIEE10G fall 2009 and spring 2010 releases. We know you are working hard because there is a steady stream of bug fixes. Still it would be nice if you gave us a sign of your wellbeing every now and then.

How are things going with 10g new brother OBIEE11G?

We have been waiting some time now for it’s release. Yes, we know that you never gave us a delivery date. But we are really really curious of all the new features you promised.

Cool new stuff like working with unbalanced hierarchies and sweet new graphs are really what we need right now.

Please give us a (small) sign that you are doing well.

Best Regards

 

John Minkjan http://obiee101.blogspot.com/

p.s.: To my blog followers feel free to co sign this letter.

Update 14-MAY-2010 21:30

Just got of the phone with Matt Bedin, Director - Business Intelligence Product Management at Oracle. All is well :-).

The code is finished and internal beta testing is well on it’s way.

Matt could not give me any release date planning due to strict company rules.

Some of the key features in the first release are:

* Working with hierarchies will be a lot easier, (true OLAP style) you don’t have to drag all levels to the presentation layer anymore. Unbalanced / Ragged hierarchies are supported against most sources.

* Fully documented XUDML, repository updates will be a lot easier to automate.

* A complete overhaul of the graphical engine. (You probably will already have seen the slides…)

Till next Time

OBIEE Remove PDF print option for one dashboard only

Or how to get from:

to

 

add this to a textbox on the dashboard:

[code]

<script type="text/javascript">
    function RemovePDFOption() {

        var tds = document.getElementsByTagName('span');
        for (var td = 0; td < tds.length; td++) {

            if (tds[td].className != 'DashboardFormatLinks') {
                continue;
            }

            //alert(tds[td].innerHTML);
            var tHTML = tds[td].innerHTML;
            tHTML = tHTML.replace("PDF</a>","</a>");
            //alert(tHTML);
            tds[td].innerHTML = tHTML;

        }
    }
    window.onload = RemovePDFOption;
</script>

[/code]

Yeah i know this is very crude (the link is still somewhat hidden there), but if you play around with javascript .replace and indexof you can clean it up even more.

Till Next Time

OBIEE Naming Conventions – Presentation Layer

This Article is only a guideline to an OBIEE naming convention for the repository. The most important aspect of a naming convention is that you use it consistently. The used naming convention should be readable for any future development.

1.1 Presentation Catalog Name:

Format General: << For End User Logical Name >>

Format Custom Display Name: “VALUEOF(NQ_SESSION.CN_”+Business Model Name + “)” {*}

Format Custom Description: “VALUEOF(NQ_SESSION.CD_”+Business Model Name + “)”{*}

{*} Only used for “translated” Repositories

1.2 Presentation Table Name

Format General: << For End User Logical Name >>

Format Custom Display Name: “VALUEOF(NQ_SESSION.CN_”+Business Model Name + Business Model Table Name + “)” {*}

Format Custom Description: “VALUEOF(NQ_SESSION.CD_”+Business Model Name + Business Model Table Name + “)”{*}

{*} Only used for “translated” Repositories

1.3 Presentation Column Name

Format General: << For End User Logical Name >>

Format Custom Display Name: “VALUEOF(NQ_SESSION.CN_”+Business Model Name + Business Model Table Name + Business Model Column Name + “)” {*}

Format Custom Description: “VALUEOF(NQ_SESSION.CD_”+Business Model Name + Business Model Table Name + Business Model Column Name + “)”{*}

{*} Only used for “translated” Repositories

Till Next Time