BI Consultant van 2008

Altijd goed voor je ego, genomineerd worden voor BI consultant 2008 voor Nederlandstallige BI gemeenschap. zie http://www.bidutch.nl/. Omringt door grote OBIEE namen als Stijn Gabriels en Daan Bakboord. Dus als je BI nieuws volgt in het Nederlands, breng je stem uit!

Always great for the ego, being nominated for BI consultant 2008 by the Dutchspeaking BI community. see: http://www.bidutch.nl/. Surrounded by great OBIEE names like Stijn Gabriels and Daan Bakboord. So if you follow BI news in Dutch, cast your vote!

Till Next Time

OBIEE Keep a clean desk

When developing a repository the screen can be pretty crowded:

Since you are usually only working in one area at a time, why don't "switch off" the others to get more working space:

Till Next Time

OBIEE Creating Bookmark and or Prompted URL

By default the Create Bookmark Link is enabled in the dashboard page options menu:

This can be control in the instanceconfig.xml file:

<Dashboard>
  <EnableBookmarkURL>True</EnableBookmarkURL>
</Dashboard> 

Setting this to false gives:

When selecting Create Bookmark Link you get an URL giving back the same prompt settings.

An other option is creating a Prompted URL. This stores the value of the used prompts in the URL (Like goUrl) and allows you to edit the values.

This functionality is enabled in the instanceconfig.xml file:

<Dashboard>
   <EnablePromptedURL>True</EnablePromptedURL>
</Dashboard>

The URL will look something like:

http://vmobiee:9704/analytics/saw.dll?Dashboard&_scid=sccbLaQP0rs&PortalPath=%2Fshared%2FSample%20Sales%2F_portal%2F01%20Ranks%20%26%20Toppers&Page=11%20Multi%20Dim%20TopNs&Action=Navigate&col1=1&val1="7"&col2=%22D0%20Time%22.%22T05%20Per%20Name%20Year%22&val2="2007"&col3=%22D4%20Product%22.%22P04%20%20Brand%22&val3="Brand%203"

Before sending the URL to somebody you can "edit" the prompt values somewhat. If you want to this programmatically use the goUrl functionality.

You can set the "lifespan" of the bookmark with the <BookmarkExpirationDays> tags:

<Dashboard>
    <EnableBookmarkURL>True</EnableBookmarkURL>
    <EnablePromptedURL>True</EnablePromptedURL>
    <BookmarkExpirationDays>30</BookmarkExpirationDays>
</Dashboard>

Till Next Time

OBIEE Google Chrome redux

Google released there first non-beta version of Chrome. Tried it again, it now works fine with OBIEE Dashboards, didn't have any freezes. Still doesn't work right in answer if you have a metadatadictionary configured.

IExplorer:

Google Chrome:

Decided to unistall it for the time being.........

This is an reaction for this article: http://obiee101.blogspot.com/2008/09/obiee-google-chrome.html

Till Next Time

OBIEE Automating the Metadata dictionary repository updates

In a previous article: (http://obiee101.blogspot.com/2008/12/obiee-metadata-dictionary.html) I showed you how to setup the OBIEE Metadata dictionary. In practice people tend to forget to update this dictionary when the repository is brought to production and it tens to get outdated over a period of time.

This can be easily solved by automating the creation of the metadata dictionary.

step 1: Create a command file

Create a small command file called: nQMetaDataExport.txt (or which ever name you like)

Add the following text:

Open samplesales.rpd Administrator AdministratorPassword

Hide

GenerateMetadataDictionary {\MetadataDictionaryFolder}

Close

Exit

step 2: Create a scheduler command

Kick of:

{OracleBI}\server\Bin>admintool /command {YournQCommandDirectory}:/nQMetaDataExport.txt

every day/week/month

WOW: The command line switch of the admintool is not supported by Oracle.

Till Next Time

OBIEE MetaData Dictionary

The metadata dictionary is a very powerful support package within OBIEE. Sadly it is not commonly used at this moment. This is mainly caused by the fact that the functionality is very poorly documented by Oracle.

This article describes the configuration and implementation of the OBIEE metadata dictionary both on an OC4J and an IIS web services. (Other web services can be easily configured based on of these systems)

Prerequisites

The account used for implementing this functionality must have full access to:

• 
  
  The source repositories
  
  
  
• 
  
  The Presentation server.
  
  
  
• 
  
  The Web server.
  
  
  

Generating the metadata dictionary

Open the repository in Offline mode:

Go to tools utilities:

Select Generate Metadata Dictionary:

Select a destination directory:

On successful finish you will get:

If you are replacing you might get this:

Navigate to your {OBIEE MetaData Dictionary}\ {repository_name}

Open NameIndex in a browser to check the readability.

Open TreeIndex in a browser to check the readability.

OC4J Prerequisites

First we have to tell the presentation server where to find the dictionary. This is configured in the INSTANCECONFIG.XML file. Usually this file is found in {OracleBIDate}\web\config.

Open the file and add:

<SubjectAreaMetadata>

<DictionaryURLPrefix>/analytics/dictionary/</DictionaryURLPrefix>

</SubjectAreaMetadata>

between the <WebConfig> tags.

WOW: Some OBIEE config tags and the contents are CaSe SeNsItIvE!

OC4J web server

Create the “dictionary” directory in the analytics application folder:

Usually {OracleBI}\oc4j_bi\j2ee\home\application\analytics\analytic. (it’s the folder where your SAW.DLL is located).

Copy the content of your dictionary export folder to this folder:

Restart the presentation server

Log in as administrator in answer, select the subject area:

Click on the dictionary symbol:

ISS Prerequisites

Create a new virtual directory in your IIS manager

Add the virtual directory to your INSTANCECONFIG.XML. Usually this file is found in {OracleBIDate}\web\config.

<SubjectAreaMetadata>

<DictionaryURLPrefix>/analyticsDictionary/</DictionaryURLPrefix>

</SubjectAreaMetadata>

WOW: Some OBIEE config tags and the contents are CaSe SeNsItIvE!

Restart the presentation server

Log in as administrator in answer, select the subject area:

Click on the dictionary symbol:

Granting access to the metadata dictionary

Log in as administrator

Go Settings > Administration

Select manage privileges:

Select access to Metadata Dictionary

By default this is set to everyone.

Till Next Time

This article was orignally written for the Ciber knowledge blog: http://knowledge.ciber.nl/weblog/

EDIT: This Feature is availible from 10.3.4

OBIEE LOV prompts

The classical way of making a LOV prompt is creating SQL in the form:

SELECT case 1=0 then TABLE.COLUMN else "YES" end from BM_NAME union all SELECT case 1=0 then TABLE.COLUMN else "NO" end from BM_NAME

This always produces costly SQL since OBIEE will execute this as a SELECT DISTINT against the reference table.

Much better is to add a small LOV table to repository with it's own business model. I created a small XLS spreadsheet with a couple of LOV's:

Next I imported it into the repository and create the dim version of the LOV:

Made a very simple business model for it:

And of course a presentation layer:

Now you can create each LOV prompt as a direct SQL request:

SELECT LOV.LOV_VALUE FROM BM_LOV WHERE LOV.LOV_TYPE = 'BOOLEAN' ORDER BY LOV.LOV_SORT

The result can be stored in a presentation or a repository variable for late processing:

Till Next Time

OBIEE bypassing the presentation / web cache

In addition to settings mentioned in this article:  http://obiee101.blogspot.com/2008/11/obiee-presentation-services-cache.html. You sometimes you want to bypass the presentation / cache for development purposes. Or more often when you get weird write back behaviour. Add this to the instanceconfig file:

<CacheMaxExpireMinutes>-1</CacheMaxExpireMinutes>

<CacheMinExpireMinutes>-1</CacheMinExpireMinutes>

<CacheMinUserExpireMinutes>-1</CacheMinUserExpireMinutes>

Till Next Time

OBI Forum Live Vergeet je niet in te schrijven

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 First / Last day previous week

First day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY,-6, (TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)))

Last day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)

Till Next Time

OBIEE First / Last of month

OBIEE doesn't have a built in function for the first and or last of a month. This can be easily achieved by the follwing functions:

First of month:

TIMESTAMPADD(SQL_TSI_DAY, ( DAYOFMONTH(CURRENT_DATE) * -1) + 1, CURRENT_DATE)

Last of month:

TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , TIMESTAMPADD(SQL_TSI_MONTH, 1, CURRENT_DATE))

Till Next Time

OBIEE Hidding a section

Sometime you need to hide a section on a dashboard (temporarily)

Go to the "Format Section" part of your dashboard section:

Open the "Custom CSS Style Options

"

Enable the "Use Custom CSS Style " enter display:none

Till Next Time

OBIEE Breadcrum trail

Sunil did another great one!

http://sranka.wordpress.com/2008/12/07/how-to-implement-breadcrumb-in-obiee/

Till Next Time

OBIEE BI Publisher Login Errors

Good tip from Kevin:
http://oraclebi.soundvoid.net/2008/12/bi-publisher-login-errors/

Till Next Time

OBIEE using timeline graphs in pivot views

Consider the following data:

If we put this in a timeline graph we get:

OBIEE Missing BI-Office add - in

Erik did a good one: http://bidwcz.blogspot.com/2008/12/chybjc-bi-office-add-in-me-se-to-stt-i.html But since most of you (including me :) ) don't speak or read Czech here is a translated summery:

If you a "missing" the  OBIEE office plug in after you did an install and think it should be there, try this:

In your MS Office application grotto Help> About MS Office> Disabled Items >

Press Disabled Items  and check if the OBIEE Office add in is the list.

Till Next Time

OBIEE BI-Publisher Templates

Found this one on the NET:

http://www.oracle.com/technology/products/xml-publisher/demoshelf/Shelf.html

 

Till Next Time

OBIEE 1K Status

Not only is it our first birthday today, but we also passed the 1K pageview per day border for the first time. Another proof that OBIEE is alive and kicking!

:-) Till Next Time

OBIEE101 First Birthday

Yes, OBIEE101 has become 1 today! Thanks everybody for the On- and Off-line input!

Till Next Time

OBIEE Handling SCF data / PIT Reporting

Due to more and more compliance rules a lot of DWH's these days have there data stored based on the SCF principal (Slowly Chancing Fact). A typical example of SCF data is the state of an order:

If you want to do PIT reporting (Point In Time) you need to use the SCF columns:

One possible solution track is using creating a SCF_IS_PIT column in the repository based on a repository variable.

First let's create a PIT_DATE session variable:

Next create the column:

Our data will now look like this (Current date = 03-DEC-2008"):

If we add a filter to our report :

we only get "CURRENT" data:

For most users this is the information they are looking for. For those who need to go "back in time" with the same report, you simple add a prompt to the dashboard allowing them to set the PIT date:

Till Next Time