OBIEE Repository Version Control

If you work in "professional" development street (a separate machine for development, testing, acceptance and production) you will have different version of the repository on different machines.
It’s good practice to show to the user which version of the repository they are working on.
Since OBIEE doesn’t have a (automatic) version number you have to use a work around.
Here is one:
In the repository make a new session variable init block “INIT_REP_CONTROL”.

Set the datasource to:
SELECT '0.00.01' REP_VERSION, '01-APR-2008 20:00' REP_VERSION_DATE
FROM DUAL

Add the two session variables “REP_VERSION_DATE” and “REP_VERSION”

In Presentation area add a new subject area “Repository Version”:
To the description area add the text:
The current version of the repository is: VALUEOF(NQ_SESSION.REP_VERSION).
This version was brought to production on: VALUEOF(NQ_SESSION.REP_VERSION_DATE)

Add a dummy table and column, set the permissions to your HiddenColumns groups:

REMEBER TO ALTER REP_VERSION AND REP_VERSION_DATE each time you move your repository to the next server.

Till Next Time

OBIEE Yes No Prompt

Sometimes you need a simple Yes / No prompt. If you don't have a LOV (List of Values) table in your db defined there is a simple workaround.

Create a new prompt based on any column:


Next set show to SQL results, deselect all choices:

Alter the SQL to:

SELECT case when 1=0 then TBLTRUCKS.LICENSEPLATE else 'Yes' end FROM Trucks union all SELECT case when 1=0 then TBLTRUCKS.LICENSEPLATE else 'No' end FROM Trucks

The "case when 1=0 then TBLTRUCKS.LICENSEPLATE " is only there to trick OBIEE in thinking that it's selecting a column.

Alter the column name to 'SELECTOR' (single quotes).

Set your default value, your presentation variable and set the label:

Till Next Time

OBIEE Manage the cache part 2

Seeding / Filling the cache

• Now why should you want to do that? OBIEE is not a database, so why would this be an advantage?
• If you have a "slow” connection to your source database, maybe because it's on the other side of the world.
• If you have "slow" aggregation views.
• If you can "predict" what 90% percent of your queries look like .

From your user statistics you can pick up the top 10 of your queries, Check if you can make them "dynamic" by changing fixed date into dynamic dates.
Put the following in a .txt file in your maintenance directory.

// Load cache by Query

// John Minkjan CIBER Nederland

// FileName: LoadCache.txt

// executed by cmd string:

// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\LoadCache.txt
// Query 1

SELECT TBLFUELCONSUMPTION.LICENSEPLATE, TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM Trucks WHERE TBLFUELCONSUMPTION.MEASUREDATE >= TIMESTAMPADD(SQL_TSI_YEAR, -2,NOW());

// Query 2

SELECT TBLFUELCONSUMPTION.LICENSEPLATE, TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM
Trucks WHERE TBLFUELCONSUMPTION.MEASUREDATE <= TIMESTAMPADD(SQL_TSI_YEAR, -3,NOW());

// The Queries are separated with a ;

Till Next Time

OBIEE Manage the cache part 1

Emptying/Purging the cache
One of the most powerful features of OBIEE is the way it uses it's cache. Good cache management can really boost your performance. From the system management point of view there are a couple of tips and tricks to influence the cache performance.
For a customer I made a couple of handy scripts for handling the cache.

1. Purging the whole cache.

If you have a completed database reload or want to do some performance testing with your repository you might want to purge the whole cache.
Put the following in a .txt file in your maintenance directory

// Purge complete cache
// John Minkjan Ciber Nederland
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s

c:\obiee\mscripts\purgecompletecache.txt Call SAPurgeAllCache()

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\purgecompletecache.txt

2. Purging the cache by table

If you have a major update of your dimensional tables you might want to clear the cache for just one table.
Put the following in a .txt file in your maintenance directory:

// Purge complete cache
// John Minkjan Ciber Nederland
// FileName: PurgeTableCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeTableCache.txt

Call SAPurgeCacheByTable( 'JJMORCL_SH', NULL, 'SH', 'TBLTRUCKS' );

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeTableCache.txt

WOW: the database, schema, (catalog), and tablename are the names in OBIEE not in the database.

3. Purging the cache by query

Sometimes you only want to purge only "old" data from your cache.
Put the following in a .txt. file in your maintenance directory:

// Purge cache by Query
// John Minkjan Ciber Nederland
// FileName: PurgeQueryCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeQueryCache.txt

Call SAPurgeCacheByQuery('SELECT TBLFUELCONSUMPTION.LICENSEPLATE,
TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM Trucks
WHERE TBLFUELCONSUMPTION.MEASUREDATE <= TIMESTAMPADD(SQL_TSI_YEAR, -1,NOW())'); // The "query" line must be one contiues line! You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeQueryCache.txt

WOW: the "query" is the OBIEE one not the database one!

4 Purging the cache by database

Put the following in a .txt. file in your maintenance directory:
// Purge cache by Database
// John Minkjan Ciber Nederland
// FileName: PurgeDataBaseCache.txt
// executed by cmd string:
// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s
c:\obiee\mscripts\PurgeDataBaseCache.txt

Call SAPurgeCacheByDatabase( 'JJMORCL_SH' );

// The "dbName" is the OBIEE name!

You can execute this from the commandline: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\PurgeDataBaseCache.txt

Till Next Time

OBIEE Downgrading Repository

I wrote a Q&D Command line tool to downgrade an OBIEE repository. You have to run it on the oldest OBIEE-server.
<--------------------------------------------------->
< COBIEEDG {CIBER} Obiee downgrade tool
< No warrantys what so ever!!!!!!!!!
< version 0.0.2
< 09 March 2008
< COBIEEDG Has to run on the oldest OBIEE server!!
<--------------------------------------------------->
-? Show Argument Help
-U User
-P Password
-I Source Repository
-O Output Repository
-N New Repository Number
<--------------------------------------------------->

You can download it here!

Till Next Time

OBIEE Continues Time Line

Because OBIEE graph types are not "Date/Time" aware you often face the problem of broken Date/Time Lines. If you don't want to wait for OBIEE 11G you might want to try this alternative. Our table with fuel consumption entry's doesn't have an entry for each day after filtering: Due to this fact the graphical representation is wrong:

One solution is to combine this report with a union query based on a calendar dimension:
The executed query against 10G looks now like this:

The graphical representation is no much better.

Be aware that a union query will influence the perfromance!

Till Next Time

OBIEE No Thousand separator

One of our customers wants no thousand separators visible when using the English languages settings.

To achive this you have to edit your localedefinitions.xml. You can find this probably in \OracleBi\web\config.

Restart the presentation sever.

WOW: (Word Of Warning) Do not use wordpad to edit this file but Notepad or an real XML-editor!

Till Next Time