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:

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:
Before you start with anything get up to speed with dimensional modelling.

(Get familiar with the works of Ralph Kimball)
Brush up your modelling capabilities, ADAPT is a good method to start with:
Read up on your Data Warehouse /  BI basics:
Get a clean (virtual) machine and first install an Oracle database. The 10g express version is OK for starters.
Maybe you want to brush up your DB skills:
Next get the latest version of OBIEE
Download the documentation:
If you have everything installed work trough the OBE tutorials on OBIEE.
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.
Have a good look around at the OTN forums. Most questions have been asked before.
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…
If your start an 11g project brush-up your weblogic skills:
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:

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

    Thursday, July 23, 2009

    OBIEE my first server

    image

    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

    image

    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).

    image

    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)"

    image

    (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

    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:

    image

    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')">

    &nbsp;

    <sawm:messageRef name="kmsgDashboardPageOptionsMenu"/>

    &nbsp;

    <img src="fmap:Views/sortdesc.gif" border="none"/>

    &nbsp;

    </a>

    </span>

    <br/>

    </HTML>

    </WebMessage>

    image

    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:

    image

    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

    Log in as administrator:

    image

    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!)

    image

    Step 2:

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

    image

    Step 3:

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

    image

    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:

    image

    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:

    image

    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