OBIEE Column headings

This post on OTN (http://forums.oracle.com/forums/thread.jspa?threadID=852758&tstart=15) triggered me to have closer look at the column headings possibilities

Lets start with a simple report (Sample Sales):

To alter the column heading go to Table View Properties:

In the Display Column & Table Headings you have five choices:

Only column headings:

As separate rows

This allows you to customize the column heading over two rows:

As Table.Column

As Table.Column (Where needed)

Doesn't seem to change anything at first glance, but works fine when you have two columns with the same name from different folders:

No column or table headings

Till Next Time

OBIEE Autorefresh no prompts

Ok this trick only works if you don't have any prompts on the dashboard page. They can live on other pages if you want to keep them.

Add a text element to your dashboard

Enter :

<META HTTP-EQUIV = "REFRESH" CONTENT = "900">

where 900 is the refresh time in seconds. Don't forget to check the Contains HTML Markup box.

Till Next Time.

Edit: Stijn pointed me to adding:

<META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE" >

To avoid caching of the browser.

OBIEE Creating a Yamazumi Graph

A Yamazumi chart is a stacked bar chart that shows the balance of cycle time workloads between a number of operators typically in an assembly line or work cell. The Yamazumi chart can be either for a single product or multi product assembly line.
Yamazumi is a Japanese word that literally means to stack up.
Toyota uses Yamazumi work balance charts to visually present the work content of a series of tasks and facilitate work balancing and the isolation and elimination of non value added work content.

1 Instructions

1.1 Minimum data

The minimum available data columns are:

• EMP_NO: identifies the employee
• TASK_START_TIME: Task Start Time
• TASK_ORDER: RCOUNT for the tasks of the employee during the period, based on the TASK_START_TIME { RCOUNT(YamazumiTable.TASK_START_TIME by YamazumiTable.EMP_NO) }
• TASK: Identifies the task
• DURATION: TASK duration in minutes

1.2 Pivot Table

The base data has to be transformed into a pivot table:

Set the aggregation rule for duration to first:

1.3 Adding a graph

Select

Chart only, Horizontal Bar, 2D Stacked, Rectangle

Set legend to None

1.4 Colouring the Task:

Go to Format Chart Data > Conditional

Select add condition for TASK

Set the colour for each TASK

1.5 Creating a legend

Set the background colour for each task:

1.6 Organize the compound view:

Till Next Time

OBIEE Brackets in the BMM

A colleague discovered this: If you are using brackets in the BMM and the column is part of a dimension and the last character is a closing bracket ')'.

 

Then when building a report the column appears 'strange' in answers, it includes the folder prefix:

It doesn't seem to influence the results, it appears to be just on of those things.....

Till next time

OBIEE rowbased calculations in a pivot table

One of the little known possibilities are the row based calculations in a pivot table. Let's start with a simple report based on the sales sample RPD:

 

From this base report we make a simple pivot table:

In the columns section click on the column properties and select "New Calculated Item"

Now can create a rowbased calculation:

You can also play with the other functions:

Till Next Time

OBIEE Impersonate a user

If you want to check how a report or dashboard runs as a different user but you haven't yet setup "ACT AS" functionality or you don't know the password of the user, you can pass the &impersonate variable trought the URL. (If you are logging in as Administrator)

Change this URL to the one shown below:

For dashboards: http://vmobiee:9704/analytics/saw.dll?Dashboard&impersonate=OBIEE_A

Directly in answers: http://vmobiee:9704/analytics/saw.dll?Answers&impersonate=OBIEE_A

where OBIEE_A is the username of the person you want to impersonate.

Till Next Time

BI Consultant van 2008 ronde 2

Samen met Niels de Ruiter (http://www.linkedin.com/pub/2/583/66a) en "de Anonieme Consultant" ben ik genomineerd voor de tweede ronde voor BI Consultant van het jaar 2008 (http://nlbi.blogspot.com/2009/01/bi-consultant-van-2008-stemronde-2.html).

Allereerst wil ik iedereen bedanken die op mij gestemt heeft en ik ga met plezier de strijd aan voor de volgende ronde.

Dus als je BI nieuws volgt in het Nederlands, breng dan nu je stem uit! Het hoeft niet op mij.........maar mag wel ;-) .

Together with Niels de Ruiter (http://www.linkedin.com/pub/2/583/66a)  and "de Anonieme Consultant" ('The Anonymous Consultant) I am nominated for the second round of BI consultant 2008(http://nlbi.blogspot.com/2009/01/bi-consultant-van-2008-stemronde-2.html).

First of all I would like to thank everybody who has voted for me in the first round and with great pleasure I'll accept the challenge for the next round.

So if you follow BI news in Dutch, cast your vote! Doesn't have be on me..... but I don't mind if you do ;-) .

Till Next Time

OBIEE Multiple connection pools

In an ideal world the OBIEE development team has his own unlimited hardware, a DBA, an ETL guy/girl, a data architect / repository builder, some reportbuilders who really know the tool, a dashboard designer with a good feeling for DTP and off course a project leader. All these functions should be full time available or at least in the same room and all the noses should be pointing in the same direction.

In the real world the hardware is "very" limited. The DBA lives on a other continent. The ETL people have different office hours. The reportbuilders work from there home offices. The dashboard designer only has a budget of two hours a week and the project manager has twelve projects at the same time.

In this article I want to show you how you as a data architect / repository builder can help the DBA. Why ? The DBA (if he is any good) controls something very important: Resources. Stuff like CPU allotment, sorting memory size, cache settings on the DB etcetera. Since resource cost money he will limit them.

Most BI-Server setups use shared connections to the database. But if we "generalize" the connection users we can see several groups:

• Reportbuilders; needs access to all tables, can transport a lot of data during development, are in the habit of "killing" query's when they "take to long"
• Answers users: create there own "new" request each time.
• Dashboard users: Only use predefined query's
• BI-publisher: Can do a lot of query's in succession.
• WriteBack users: need insert/update rights on the DB.

The help the DBA ask him for five users named: OBIEE_R (Reportbuilders), OBIEE_A (Answers_users), OBIEE_D (Dashboard users), OBIEE_B (BI-Publisher) , OBIEE_W (WriteBack Users).

In your RPD create five connection pools:

Create five Groups:

Now set the permissions for the Connection Pool:

Don't forget to disable "Everyone".

Assign the users to there groups and check in TOAD or SQL Developer if the right connections are made:

This way you can ask your DBA to assign the right resources to every group instead of an average setting for every one.

Till Next Time

OBIEE PROJECT INACCESSIBLE COLUMN

Kumar gave a great alternative for the choose statement by using the PROJECT_INACCESSIBLE_COLUMN_AS_NULL parameter

http://obieeblog.wordpress.com/2009/01/15/obiee-data-security-column-level-security/

Till Next Time

OBIEE Cache persistence

Some good investigation done on Cache persistence (by wildmight? couldn't find the author):

http://obiee-blog.info/metrics/cache-persistence/

extract:

...When a dynamic repository variable is updated, cache is automatically purged. This is designed behavior. Cache will be invalidated (i.e. purged) whenever the initialization block that populates dynamic repository variable is refreshed. The reason that refreshing a variable purges cache is that if a variable was used in a calculation, and the variable changed, then cache would have invalid data. By purging cache when a variable changes, this problem is eliminated.

Since this is the designed functionality, Change Request 12-EOHPZ3 titled ‘Repository variable refresh purges cache’ exists on our database to address a product enhancement request. The workaround is to go through the dynamic repository variables and verify that the variables are being refreshed at the correct interval. If a variable needs to be refreshed daily, there may be a need to set up a cache seeding .bat file that runs after the dynamic variable has been updated. If the cache seeding .bat file runs prior to the refresh of the dynamic variable refresh, then the cache will be lost.

Till Next Time

OBIEE Leap Year Challenge

Have a look at these tables of February 2008 and 2009: (remember 2008 was a "leap" year)

If in February 2009 we use the ago function of OBIEE:

AGO(BM_AGO_TEST.F_FACTS.F_FACT_VAL, BM_AGO_TEST.DIM_DATE_TESTDim."YEAR", 1)

On a month level we get:

which is correct. But if we do it on a day by day level we get:

Which is not quite correct since it is missing the "AGO" data for FEB 29 2008 (1232). Now you could argue that this is correct since there is no FEB 29 2009. Most accounting systems agree that in such a case the data for FEB 29 should be added to the data of FEB 28. One way of solving this is adding a Year Ago DATE_ID to your date dimensions with a double entry for FEB 28 2009:

If connect an alias of our fact table to the DT_ID_YAGO we get:

 

We see that the data for FEB 28 and FEB 29 2008 is now summarized for FEB 28 2009.

An other advance for working with fixed ago column is your time dimension is that the query costs are about 80 % percent lower then using the OBIEE AGO function. Is this example 187 versus 853 on as Oracle 10Gr2 database.

Till Next Time

OBIEE creating large / huge PDF documents

When you try to create big PDF files in OBIEE after a certain size you will get an error like:

A fatal error occurred while processing the request. The server responded with: Error while executing PDFRpcCall.processMessage com.siebel.analytics.utils.InputStreamWithLimit$ReadOverTheLimitException at com.siebel.analytics.utils.InputStreamWithLimit.incTotalBytes(InputStreamWithLimit.java:58) at ...............

Basicly it means that the FOP processor is running out of workspace. The workspace is controlled from the instanceconfig.xml JavaHost part. Instead of enlarging the default value of 1024 kb you could also consider setting this to unlimited.

<JavaHost>
  <PDF>
     <InputStreamLimitInKB>0</InputStreamLimitInKB>
   </PDF>
</JavaHost>

Till next Time

OBIEE Sample Sales

Found some good documentation from Oracle on the new Sample Sales repository in OBIEE:

http://www.oracle.com/technology/products/bi/files/oracle_bi_sample_app_content_guide_ext_part1.zip

http://www.oracle.com/technology/products/bi/files/oracle_bi_sample_app_content_guide_ext_part2.zip

Be aware that it is referencing the extended samples sales (not in the normal OBIEE download) which can be found here:

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

Till Next Time

OBIEE Selecting SKIN and STYLE from URL

Sometimes you want to redirect a user to a different SKIN and or STYLE. One method is giving the SKIN/STYLE name in the login URL.

To enable this feature you first you have to define a parameterlist in Auth\UserIdPassword part of the instanceconfig.xml:

<Auth>
    <UserIdPassword enabled="true">
        <ParamList>
            <Param name="NQ_SESSION.STYLE" source="url" nameInSource="STL"/>
            <Param name="NQ_SESSION.SKIN" source="url" nameInSource="SKN"/>
        </ParamList>
    </UserIdPassword>
</Auth>

Restart the presentation server. Now add the SKIN and STYLE to the URL: http://vmobiee:9704/analytics/saw.dll?Dashboard&SKN=siebel77&STL=siebel77

{Remember to clear your browser cache first!}

Till Next Time

OBI Forum Live Vergeet je niet in te schrijven !LAATSTE KANS!

Ze Bloggen en Loggen... Ontmoet ze nu op OBI FORUM LIVE

CIBER, InCore Solutions en Scamander zijn toonaangevende bedrijven op het gebied van Oracle’s BI Suite. John Minkjan (CIBER), Remco van Dam (InCore Solutions), Daan Bakboord (Scamander) en Gerard Braat (Oracle) zijn Nederland’s bekendste en beste (b)loggers op dit vlak. Op de nationale en internationale fora zijn ze bekende personen en leveren ze een zeer belangrijke bijdrage aan de kennisdeling.
Nú heeft u de mogelijkheid om deze 4 belangrijkste spelers persoonlijk te ontmoeten en met ze te ‘sparren’. Neem gewoon uw eigen vragen mee en ontdek de geheimen van OBI!
Op 14 januari 2009 is de eerste editie van OBI Forum Live. Een initiatief van CIBER, InCore Solutions en Scamander, in samenwerking met Oracle Nederland. OBI Forum Live zal plaatsvinden bij Oracle Nederland in De Meern.

Schrijf je nu in op http://www.obi-forumlive.nl/

OBIEE and Wiki

One of the recent trends is to create a project wiki when a project starts and keep all documentation, tips and tricks in there from day one. For our projects we always use XWIKI (http://enterprise.xwiki.org/).

It's simple to set up, you can use it within an OAS or OC4J but also standalone.

But with all Wiki's, Blogs or any other form of documentation, if it's not maintained / up to date people won't use it .....

Till Next Time

OBIEE Cache and VPD

Kumar did a great article on the behaouvior of the OBIEE cache with an virtual private database.

http://obieeblog.wordpress.com/2008/12/29/obiee-and-virtual-private-database-vpd/

Till Next Time

Have a great Year!

Till Next Time