Oracle Business Intelligence OBIEE 101

Monday, July 27, 2009

OBIEE Moving period to date aggregations

When using the OBIEE todate functionality the BI-server starts at the first entry next higher dimensional level. => TODATE(FACT, TIME.MONTH) means that it will start aggregating from the first month it will find on the TIME.YEAR level.

Recently on of out customers wanted a moving todate column, Starting 21 days ago and going on for the next 7 days after CURRENT_DATE. We couldn’t use a MSUM since we didn’t have an entry for each date and the total reporting period was greater then the aggregation period.

We fixed by creating a extra column on the calendar view on the database indentifying the period.

CASE WHEN DAY_DATE <SYSDATE –21 then ‘00-PREVIOUS’ ELSE CASE WHEN DAY_DATE > SYSDATE + 7 THEN ‘02-NEXT’ ELSE ‘01-CURRENT’ END END AS P21MINT7PLUSPERIOD

This will give ‘00-PREVIOUS’, ‘01-CURRENT’ or ‘02-NEXT’ for P21MINT7PLUSPERIOD. It’s important that you created this in the physical layer. The TODATE function doesn’t like dynamical column in the Business Model Layer.

Next when created an alternative hierarchy: TIME.TOTAL => TIME.P21MINT7PLUSPERIOD => TIME.DAY. Now we could use the TODATE function like TODATE(FACT, P21MINT7PLUSPERIOD) Which gives us a daily moving period.

Till Next Time

Sunday, July 26, 2009

OBIEE Google Maps an alternative ending

In these articles:

I showed you how to integrated Google maps in OBIEE. Some of you got into trouble when using it on dashboard with a Microsoft Internet Explorer. This is caused by the fact that IE doesn’t like multiple .onload events in a frameset.

Here is an alternative. In the postfix part of the narrative use this instead:

}
</script>
<script src="http://code.jquery.com/jquery-latest.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready( function(){initialize();return false;
            }
        );
    </script>

<body onunload="GUnload()">
     <div id="map_canvas" style="width: 600px; height: 400px"    > </div>
</body>

If your organisation doesn’t allow a live connection to the internet download the jquery JS script from here:

http://jquery.com/

Put it in a custom folder named res/my_custom_js as change the source to:

<script src="res/my_custom_js/jquery.js" type="text/javascript"></script>

(Sync with the OC4J folder if needed)

Till Next Time

Saturday, July 25, 2009

OBIEE How to get started

About two to four times a week I get an email or an OTN request from people who want to get started as an OBIEE consultant. In this “sticky” article I want to give you some basic starting points.
Get familiar with the basic terminology:

http://en.wikipedia.org/wiki/Business_intelligence

Before you start with anything get up to speed with dimensional modelling.

http://www.kimballgroup.com/

(Get familiar with the works of Ralph Kimball)
Brush up your modelling capabilities, ADAPT is a good method to start with:

http://www.symcorp.com/downloads/ADAPT_white_paper.pdf

Read up on your Data Warehouse / BI basics:

http://blogs.oracle.com/bimadeez/2010/06/data_warehousing_architect_-_a_beginners_guide.html

Get a clean (virtual) machine and first install an Oracle database. The 10g express version is OK for starters.

http://www.oracle.com/technology/software/products/database/index.html

Maybe you want to brush up your DB skills:

http://www.oracle.com/technology/obe/11gr1_db/index.htm

Next get the latest version of OBIEE

http://www.oracle.com/technology/software/htdocs/devlic.html?url=/technology/software/products/ias/htdocs/101320bi.html

Download the documentation:

http://www.oracle.com/technology/documentation/bi_ee.html

If you have everything installed work trough the OBE tutorials on OBIEE.

http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/index.html

Don’t not only copy the click but be sure you understand what is happening!
Download and install the extended sales sample RPD and CAT. Dissect every report and dashboard to understand how the reports work.

http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip

Have a good look around at the OTN forums. Most questions have been asked before.

OBIEE forum: http://forums.oracle.com/forums/forum.jspa?forumID=378
OBISE forum: http://forums.oracle.com/forums/forum.jspa?forumID=404

Read the works of Stephen Few on how to design a good dashboard from the visual standpoint:

Learn to create reports and dashboard which people need, which is often completely different of the reports and dashboard they intentionally asked for…

http://www.orm.net/

If your start an 11g project brush-up your weblogic skills:

http://www.oracle.com/technetwork/middleware/fusion-middleware/documentation/index.html

Configure your OTN account so that you “watch” certain users. Looking around at the forum you soon learn which users give the most valuable answers.
Start following the OBIEE related blogs. At this moment there are about 10 your really need to watch. (Check my blog list).
Must read material: