Oracle Business Intelligence OBIEE 101: July 2009

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:

http://greatobi.wordpress.com/2010/01/07/can-you-just-turn-answers-on/ {Jeff McQuigg}
http://greatobi.wordpress.com/2009/08/24/using-report-specs-for-requirements/ {Jeff McQuigg}
http://exoticexpedition.wordpress.com/2010/01/15/6-guiding-principles-of-obiee-dashboard-design/ {Deepanshu Mehta}
http://it.toolbox.com/blogs/eye-on-obi/understanding-the-oracle-bi-server-see-the-light-31891 {Gerard Braat}
http://www.biconsultinggroup.com/obiee-tips-and-tricks/ {BICG}
http://greatobi.wordpress.com/2011/07/06/prototyping-with-obi/ {Jeff McQuigg}

If you need more practices check out the Oracle Learning Library:

http://apex.oracle.com/pls/apex/f?p=OTNCR:1:0

Till Next Time

Thursday, July 23, 2009

OBIEE my first server

Had to clean the attic at my parents, and found one my first computers. Wonder if OBIEE will run on it :-) …..

Wednesday, July 22, 2009

OBIEE Hide the saved filter view link

In this article: http://oraclebiblog.blogspot.com/2009/07/how-to-hide-saved-filter-view-link.html John Andrzejek shows a way of doing this by editing a CSS file.

I prefer editing the viewmessages.xml.

Remove the text between the kmsgViewSavedFilterAnchorText tags. (you have to do this for every languages you use).

Till Next Time

Edit: Stijn found the orignal : http://obiee101.blogspot.com/2008/08/obiee-difference-between-two-rows.html

OBIEE get the previous row

Thought I had it on the blog, but couldn’t find it….. Q&D way of getting a value of the previous row:

Use MSUM (MovingSum)

MSUM("F1 Revenue"."1-01 Revenue (Sum All)",2) - "F1 Revenue"."1-01 Revenue (Sum All)"

(Yeah I know there better ways :-) )

Till Next Time

Stijn found the original post on this subject:

http://obiee101.blogspot.com/2008/08/obiee-difference-between-two-rows.html

Friday, July 17, 2009

OBIEE If you had a 1000 wishes….

A colleague suggested a new open platform. What would you see changed or enhanced in the next release of OBIEE. (Yes we will be getting a lot of new stuff in 11g, but still you might want more….)

So put your wishes here!

Till Next Time!

Thursday, July 16, 2009

OBIEE OTN is Down / was Down

It's July 16th 2009 ................................

:-) Till Next Time

Note: seems to be working again...

Monday, July 13, 2009

OBIEE Enable508

While going trough the list of configuration tags: http://knowledge.ciber.nl/weblog/OBIEE/OBIEE10340CONFIGURATIONTAGS.pdf I found this

{enable508}true{/enable508}

It changes this

Into this:

Put it between the serverinstance tag of the instanceconfig.xml

Till Next Time

Note: Have a look at Stijn his comment on 508 Compliancy

Sunday, July 12, 2009

OBIEE DiagCap.vbs

Interesting if you want have to do in-depth error research:

cscript diagcap.vbs -d temp

Found in {OracleBI}\server\Bin, it collects al the interesting logs and configurations into 1 directory.

Till Next Time

Saturday, July 11, 2009

OBIEE New Sample Sales Analysis v 1.3

The RPD and WebCat can be found http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip

It has same nice statistics demos!

Till Next Time!

Friday, July 10, 2009

OBIEE Hide the page options button

I was asked on the OTN forum how to hide/remove the Page Options button on the dashboard:

A crude and simple way is to edit the dashboardtemplates.xml:

<WebMessage name="kuiPersonalizeLink">
<HTML>
<span class="minibuttonOn" style="display:none">
<a href="javascript:void(null)" onclick="return NQWPopupMenu(event,'idPersonalizationMenu',null,'top')">
 
<sawm:messageRef name="kmsgDashboardPageOptionsMenu"/>
 
<img src="fmap:Views/sortdesc.gif" border="none"/>
 
</a>
</span>
<br/>
</HTML>
</WebMessage>

Till Next Time

OBIEE Nifty New Blog

Add this one from Srinivas Malyala to your bloglist

http://tipsonobiee.blogspot.com/

Till next Time

OBIEE made it to Expert on OTN

Made it to “Expert” level on the OBIEE OTN forum today:

Thanks for the points everyone!

Till Next Time

Thursday, July 9, 2009

OBIEE perfmon (Performance Monitor)

Not many OBIEE administrators know of it’s existence:

http://localhost:9704/analytics/saw.dll?perfmon

Provides some good info on what is going on.

Till Next Time

Wednesday, July 8, 2009

OBIEE Adding custom image in Conditional Column Format

My main inspiration for this article is based on the reply of Daniel (green4X) in this OTN entry: http://forums.oracle.com/forums/thread.jspa?threadID=921926

Step 1: Locate the {OracleBI}\web\app\res\s_{StyleName}\meters folder and create a subfolder called CompagnyImages (or whatever other name you like!)

Step 2:

In the CompagnyImages Folder create a subfolder called images (or whatever other name you like!)

Step 3:

Put all the image you want to use in the images folder, they can any web recognized format (jpg, gif, png….)

Step 4:

Go back to your CompagnyImages Folder and add a index.gif picture, as far asa I know this has to be a gif (lowercase). This will be the “category” in the conditional format selector:

Step 5:

If you are using OC4J_BI sync the folder {OracleBI}\web\app\res\s_{StyleName}\meters the one in {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_{StyleName}\meters

Step 6:

Restart the presentation server and the application server (OC4J)

Step 7:

Test it by creating a simple report:

Till Next Time

OBIEE Cool CSS trick

Check out this one from fiston :

http://obieestuff.blogspot.com/2009/07/htmlcss-trick.html

Till Next Time

Tuesday, July 7, 2009

OBIEE Dashboard Default Controls

Want to remove this?

This is controlled by controlmessages.xml

{webmessage name="kmsgDashboardAlternateFormats"}

{span class="DashboardFormatLinks"}
{?xml:namespace prefix = sawm /}{sawm:param insert="1"}{/sawm:param}
{/span}

{span class="DashboardFormatLinks"}
{sawm:param insert="2"}{/sawm:param}
{/span}

{span class="DashboardFormatLinks"}
{sawm:param insert="3"}{/sawm:param}
{/span}

{/webmessage}

1 = refresh
2 = print
3 = Briefing

Just comment out it in your version controllemessages.xml in th CustomMessage folder.

Till Next Time