OBIEE Optimizing NQQuery.log for debugging

When building and debugging OBIEE repository’s and reports you will often go back to the NQQuery.log. Depending on the security setting of the user the actual query send to the database will be logged here.

Because the log can grow very quickly over time it often means having to scroll to the end of the log to find the information needed.

By default the maximum size for the NQQuery.log is 10MB. This setting is found in the NQSConfig.ini under USER_LOG_FILE_SIZE:

If the log goes beyond this size OBIEE will rename it as “NQQuery.log.old” after it has finished the last request.

In a development environment you might consider setting this value to 50KB (or even as low as 10KB).
That way the log only contains the very last actions you are interested in.
Be aware that if the new log goes beyond the USER_LOG_FILE_SIZE the old “NQQuery.log.old” will be overwritten.

Till Next Time

OBIEE Controling Pivot View behavior

One of the more powerful tools in OBIEE is the pivot view. If you have large amounts of data you sometime run out of rows and or columns or even worse out of cells

You can control these values by altering the instanceconfig.xml.
This file is usually found in ..\OracleBIData\web\config
Good info can be found in the Oracle® Business Intelligence Presentation
Services Administration Guide (b31766.pdf)

If you open instanceconfig.xml in notepad you get something like these parameters


The behavior of the pivot view can be altered with the following parameters:

• [MaxVisibleColumns] Sets the maximum number of columns to be displayed in a Pivot View default{1000}


• [MaxVisiblePages] Sets the maximum number of page choices (or pages in PDF) to be displayed in a Pivot View default{1000}


• [MaxVisibleRows] Sets the maximum number of rows to be displayed in a Pivot View. Default{100000}


• [MaxVisibleSections] Sets the maximum number of sections to be displayed in a Pivot View. Default{1000}

Altered in the instance config it would look like this:

If you still run out of cells try this:

• [CubeMaxPopulatedCells] The maximum number of populated cells in the Pivot Table. If this maximum is exceeded, the user receives an error when rendering the pivot table. Default {150000 }
  


• [CubeMaxRecords ] The maximum number of records returned by a query for the Pivot Table engine to process. This roughly governs the maximum number of cells that can be populated in a pivot table (unpopulated cells in a sparse pivot table do not count). If this maximum is exceeded, the user receives an error when rendering the pivot table.

Till next Time

OBIEE Aggregate BY

OBIEE has many aggregate functions: AVG, AvgDistinct, BottomN, Count, CountDistinct, Count (*) (CountStar), First, Last, Max, Median, Min, NTile, Percentile, PeriodAgo, PeriodToDate, Rank, StdDev, Sum, SumDistinct, TopN.

Default OBIEE "groups" these functions on the lowest level. If you want it in an other "grouping" you can use the "BY" statement in your function definition.

If neccesary you can also define multiple columns (Comma seperated):

In the background OBIEE is translating this to ANSI-SQL:

WITH SAWITH0 AS (select T21.LICENSEPLATE as c1,
T21.MEASUREDATE as c2, T21.FUELCONSUMPTION as
c3, avg(T21.FUELCONSUMPTION) as
c4, count(T21.FUELCONSUMPTION) as
c8, sum(T21.FUELCONSUMPTION) as c9from
TBLFUELCONSUMPTION T21group by T21.LICENSEPLATE,
T21.MEASUREDATE, T21.FUELCONSUMPTION)

select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3
as c3, SAWITH0.c4 as c4,

sum(SAWITH0.c9) over (partition by SAWITH0.c2) / sum(SAWITH0.c8) over
(partition by SAWITH0.c2) as c5,

sum(SAWITH0.c9)
over (partition by SAWITH0.c1) / sum(SAWITH0.c8) over (partition by
SAWITH0.c1) as c6,

sum(SAWITH0.c9) over (partition
by SAWITH0.c1, SAWITH0.c2) / sum(SAWITH0.c8) over (partition by
SAWITH0.c1, SAWITH0.c2) as c7

from SAWITH0order by
c1, c2, c3

Which isn't always the most cost effective way, you might consider using the EVALUATE_AGGR function instead for very complex aggregations.



Till Next Time

OBIEE Using ORACLE stored procedures part 3

If you are using the Multi User Development (MUD) be aware that the connectionscripts aren't updated when you check in.
You have to edit them manualy in the main repository.

Till Next Time

OBIEE Using ORACLE stored procedures part 2

In the connection pool you are able to define connection scripts in 4 events:

• "Execute on connect" -> Very usefull point for security logging events
• "Execute before query" -> Ideal to fill a global temp table or to do some execution logging
• "Execute after query" -> To close you execution loging
• "Execute on dissconnect -> close your security log

Sadly in the OBIEE documentation (10.1.3.x ) there is no info about these options... I did some research (mostly trail and error) on how to incoperate variables in these scripts. The syntax for this is different then in the rest of OBIEE.

All parameters have to be enclosed in single quotes:

• ':USER', ':PASSWORD'
• 'VALUEOF(REPOSITORY_VARIABLE)'
• 'VALUEOF(NQ_SESSION.SESSION_VARIABLE)'

If you want to pass something from the dashboard put in a session variable.

The execution string is build like

BEGIN SCHEMA.PACKAGE_NAME.PROCEDURE_NAME('PARAMETER'); END;

Don't forget the semicolons ';'

Till Next Time

OBIEE Adding session variables to the report title / using a narrative view

A lot of times we find in the request for reports "please put the value of the used report variables in the title".
But when you try that you quickly run into trouble. By default you are confined to only the predefined ones:

From the OBIEE documentation:
To show your own defined variables (repository/session/presentation) in OBIEE you have to use the narrative view.
First you have to add the variable(s) to your query:

Then alter the column properties, set the column hide property.

Next open a Narrative view, set your descriptive text, the variables are referenced by there a @ and there column number (in this case 2)

Set the rows to display to 1

Reorganize your compound lay-out so that your narrative view is directly below your title

Till Next Time

Sometimes you stand corrected see: http://obiee101.blogspot.com/2008/09/obiee-title-view.html

OBIEE development street

If you are using a "development street" (different development, test, acceptance and production environment) you sometimes need to reconfigure the connection pool of your repository back and forth. On a recent project a customer of us wanted to have this automated for both convenience and security reasons.

Before I go explaining how we did two things:

1. Credit where credit is due, the original idea is from my collegue Raymond de Vries based on this entry from Mark Rittman.
2. This method isn't supported by Oracle. They have logged as a possible feature for coming realeses.

This method is based the fact that Oracle uses UDML as scripting language when updating the repository. You can check this by right clicking on the connection pool and pressing Ctrl + C. Paste the result in note pad.

In the UDML script you regonise the ID of the connection (in this case 22). What we did was storing this file 4 times as dbTst.txt, dbDev.txt, dbPrd.txt and dbAcc.txt. In each file we only changed the datasource to the apporated database.

Next we created 4 .cmd files (conToTst.cmd, conToDev.cmd, conToPrd.cmd and conToAcc.cmd). Each file looks like this:

:: conToTst.cmd

:: reconfigure OBIEE repository to TEST database

:: Author: John Minkjan Ciber

:: Raymond de Vries Ciber
:: stop the BI server

Net stop "Oracle BI Server"'

:: Backup the rpd

copy E:\OracleBI\server\Repository\trucks.rpd ..\OracleBI\server\Repository\trucks.rpd.bak

:: run the update script

:: info :>

:: nQUDMLExec [-U [userid]]

:: [-P [password]]

:: -I input_script_pathname

:: [-B base_repository_pathname]

:: -O output_repository_pathname

:: [-8]

:: -8 is for UTF-8

:: Eg 1: nQUDMLExec -I testudml.txt -O rp1.rpd:: create a new repository rp1

:: Eg 2: nQUDMLExec -U administrator -I testudml.txt -B rp1.rpd -O rp2.rpd

:: modify rp1 and write to rp2

:: Update the .rpd

E:\OracleBI\server\Bin\nqUDMLexec.exe -U Administrator -P Administrator -I E:\connect\dbTst.txt -B E:\OracleBI\server\Repository\Trucks.rpd -O E:\OracleBI\server\Repository\Trucks.rpd

:: start the BI server
Net start "Oracle BI Server"'

pause

exit

Till Next Time

OBIEE Usefull server scripts

When developing your repository you often have start and stop the BI-server between savings of the .rpd file. For these task I made a couple of .cmd scripts which I draged on the windows taskbar .

StopBi.cmd:

:: StopBI.Cmd
:: Stops the Oracle BI Server
:: Author: John Minkjan

:: http://knowledge.ciber.nl/weblog/ and http://obiee101.blogspot.com/
Net stop "Oracle BI Server"
exit

StartBi.cmd:

:: StartBI.Cmd
:: Starts the Oracle BI Server
:: Author: John Minkjan
:: http://knowledge.ciber.nl/weblog/ and http://obiee101.blogspot.com/

Net start "Oracle BI Server"
exit

and combined they give ResetBI.cmd:

:: StopBI.Cmd
:: Stops and Starts the Oracle BI Server
:: Author: John Minkjan
:: http://knowledge.ciber.nl/weblog/ and http://obiee101.blogspot.com/

Net stop "Oracle BI Server"
Net start "Oracle BI Server"
exit

Till Next Time