Monday, December 29, 2008

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

Wednesday, December 24, 2008

OBIEE Keep a clean desk

When developing a repository the screen can be pretty crowded:

image

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

image

image

Till Next Time

Sunday, December 21, 2008

OBIEE Creating Bookmark and or Prompted URL

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

image

This can be control in the instanceconfig.xml file:

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

Setting this to false gives:

image

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>

image

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

Friday, December 19, 2008

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:

image

Google Chrome:

image

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

Wednesday, December 17, 2008

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

clip_image002

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

Friday, December 12, 2008

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:
clip_image002
Go to tools utilities:
clip_image004
Select Generate Metadata Dictionary:
clip_image006
Select a destination directory:
clip_image008
On successful finish you will get:
clip_image010
If you are replacing you might get this:
clip_image012
Navigate to your {OBIEE MetaData Dictionary}\ {repository_name}
clip_image014
Open NameIndex in a browser to check the readability.
clip_image016
Open TreeIndex in a browser to check the readability.
clip_image018

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.
clip_image020
WOW: Some OBIEE config tags and the contents are CaSe SeNsItIvE!

OC4J web server

Create the “dictionary” directory in the analytics application folder:
clip_image022
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:
clip_image024
Restart the presentation server
clip_image026
Log in as administrator in answer, select the subject area:
clip_image029
Click on the dictionary symbol:
clip_image031

ISS Prerequisites

Create a new virtual directory in your IIS manager
clip_image033
Add the virtual directory to your INSTANCECONFIG.XML. Usually this file is found in {OracleBIDate}\web\config.
<SubjectAreaMetadata>
<DictionaryURLPrefix>/analyticsDictionary/</DictionaryURLPrefix>
</SubjectAreaMetadata>
clip_image035
WOW: Some OBIEE config tags and the contents are CaSe SeNsItIvE!
Restart the presentation server
clip_image026[1]
Log in as administrator in answer, select the subject area:
clip_image029[1]
Click on the dictionary symbol:
clip_image031[1]

Granting access to the metadata dictionary

Log in as administrator
Go Settings > Administration
clip_image037
Select manage privileges:
clip_image039
Select access to Metadata Dictionary

clip_image041
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

Thursday, December 11, 2008

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:

image

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

image

Made a very simple business model for it:

image

And of course a presentation layer:

image

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

image

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:

image

Till Next Time

Tuesday, December 9, 2008

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

Monday, December 8, 2008

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:

image

Open the "Custom CSS Style Options

image "

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

Till Next Time

Friday, December 5, 2008

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 >
image

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

Till Next Time

Thursday, December 4, 2008

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!

Birthday_candles_OBIEE101

Till Next Time

Wednesday, December 3, 2008

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:

image

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

image

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:

image

Next create the column:

image

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

image

If we add a filter to our report :

image

we only get "CURRENT" data:

image

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:

image

Till Next Time