Changing LDAP settings in an OBIEE RPD with UDML

A chap called Kevin posted a comment on a previous posting of mine asking

did you ever come across anything that could be used to change the LDAP server settings from a command line (admintool.exe, UDML, or otherwise)?

I did a quick play around with some UDML and it appears that you can.

Set up the initial LDAP server definition in the RPD

First I added a dummy LDAP server to samplesales.rpd:

A LDAP server added to samplesales.rpd

Then save and close the RPD.

Export the RPD to UDML format, and isolate the LDAP server UDML definition

Next open up a command prompt and run the following, which will export the UDML for the whole RPD:

c:\oraclebi\server\bin\NQUDMLGen.exe -U Administrator -P Administrator -R c:\oraclebi\server\repository\samplesales.rpd -O c:\scratch\udml.txt

Running the export of UDML

Open up the generated UDML in your favourite text editor. In the above example, it will have been written to c:\scratch\udml.txt.

Do a search for the name of your LDAP server, and you should hopefully find a line like this:

DECLARE LDAP SERVER "My LDAP server" AS "My LDAP server" UPGRADE ID 80295

What you do now is remove all the rest of the RPD UDML, so cut from the beginning of the file up to the DECLARE LDAP SERVER, through to the next DECLARE statement. You should end up with something like this:

Example UDML for the LDAP server definition

Make the required LDAP server change in the UDML

On a copy of the UDML extracted above, make the required changes to the LDAP server definition.
For this example, let’s imagine we’re moving the RPD to use a pre-production LDAP server.
In a copy of the original udml.txt file, now called ldap_preprod.udml, I’ve simply amended the HOST NAME field:

HOST NAME 'ldap.preprod.server.com'

Save the changed file (ldap_preprod.udml in my example).

Apply the LDAP server change to the RPD

Back at the command line, and this time NQUDMLExec

c:\OracleBI\server\Bin\nQUDMLExec.exe -U Administrator -P Administrator -I c:\scratch\ldap_preprod.udml -B c:\OracleBI\server\Repository\samplesales.rpd -O c:\OracleBI\server\Repository\samplesales.preprod.rpd

This applies the UDML in the file specified by “-I” (c:\scratch\ldap_preprod.udml) to be applied to “-B” base repository file (c:\OracleBI\server\Repository\samplesales.rpd) and write the output to “-O”, a new repository file (c:\OracleBI\server\Repository\samplesales.preprod.rpd).

Open up the new RPD in Administration Tool and check the results of your handiwork:

LDAP settings showing the change made in the UDML file

Further reading

UDML in OBIEE is nothing new, and there are some very good articles to read if you want to understand more about it:

• Andreas Nobbmann: Scripting OBIEE – Is UDML and XML all you need ?
  • This is Andreas’ presentation from the RittmanMead BI Forum 2009 – and Andreas is speaking again this year. More information here: http://www.rittmanmead.com/biforum2011/
• Dylan Wan: UDML in Oracle BI Server
• Mark Rittman: Scripting Entries in the Oracle BI Repository
• Venkat: UDML to automate repository Updates – Migration of Repositories from Development to Test/Production Environment
• Daan Bakboord: UDML – Scripting
• Andreas Nobbmann: Finding everything in your OBIEE repository – parsing your UDML

Footnote

All this can be done on Unix too, just make sure you have set your OBIEE environment first with sa-init.sh (or sa-init64.sh) before calling nqudmlgen / nqudmlexec

Whether Windows or Unix, make sure you work on a copy of your RPD, because you might corrupt it otherwise. I’m pretty sure some UDML hacking is unsupported, so use this at your own risk. And did I mention, work on a copy of your files and take backups.

From a note that I wrote last year it looks like UDML is on its way out and an XML-based version on its way in for OBIEE 11g.

The code snippets assume that you have OBIEE installed to c:\OracleBI – amend the path as necessary if you have it elsewhere. You’ll always find NQUDMLGen & NQUDMLExec in <wherever you installed OracleBI>/server/Bin (or Bin64).

Instrumenting OBIEE for tracing Oracle DB calls

Cary Millsap recently published a paper “Mastering Performance with Extended SQL Trace” describing how to use Oracle trace to assist with troubleshooting the performance of database queries.
As with all of Cary Millsap’s papers it is superbly written, presenting very detailed information in a clear and understandable way. (and yes I do have a DBA crush ;-))
It discusses how you can automate the tracing of specific sessions on the database, and requiring the application to be appropriately instrumented. This reminded me of a post that I made almost exactly 12 months ago here, where I explained how to pass through the username of the OBIEE user to the database. Initially I thought it would be useful simply for being able to pin a rogue query to an end-user, but reading Cary’s paper made me realise there is more potential to it.

Why would you use it in OBIEE?

Essentially, it enables you to precisely identify DB connections coming in from OBIEE. Since you can identify the connections, you can then trace them or collect additional statistics on them.

In Production, this would be useful for helping with troubleshooting. If a query is behaving badly, the responsible user can be easily identified, and through the login ID matched back to Usage Tracking data (you do collect Usage Tracking data, right?). Conversely, if a user is complaining (unlikely, I know 😉 ) of performance issues you can easily spot their queries running on the database and get a head start on identifying the problem.

As well as tracing, you can use these attributes to collect statistics (eg I/O wait time, db time, etc) for specific users or application areas. You use the DBMS_MONITOR CLIENT_ID_STAT_ENABLE procedure and then view the stats in V$CLIENT_STAT. Similar proc & V$ table exist for module-targeted statistics collecting.

Implementation

In essence, all you do is use the OBIEE Connection Scripts setting in the appropriate Connection Pool to call one or more of the PL/SQL packages. The values that you can set on the connection are as follows:

V$SESSION column	Corresponding connection command to set	Max value length
MODULE	dbms_application_info.set_module(module_name=>'[…]’,action_name =>NULL)	47
ACTION	dbms_application_info.set_module(module_name=>'[…]’,action_name=>'[…]’) or dbms_application_info.set_action(action_name=>'[…]’)	31
CLIENT_INFO	dbms_application_info.set_client_info	63
CLIENT_IDENTIFIER	dbms_session.set_identifier	63

(Ref: DBMS_APPLICATION_INFO, DBMS_SESSION)

For example, to pass through the OBIEE username and display name (NQ_SESSION.USER and NQ_SESSION.DISPLAYNAME respectively) you would use the following code:

Connection Pool settings

When you look at V$SESSION for the connection from OBIEE, it would show up something like this:

V$SESSION showing values from OBIEE connection

Instrument individual reports

What would be really cool would be if we could pass through the details of the report being executed.
A rather clunky way of doing this is by setting a custom session variable in the Logical SQL that gets sent to BI Server:

Then add a script to the connection pool to pass this value through in the database connection:

When run this then shows up on V$SESSION as:

col client_identifier for a20
col client_info for a20
SELECT PROGRAM, CLIENT_IDENTIFIER, CLIENT_INFO, MODULE, ACTION FROM V$SESSION WHERE LOWER(PROGRAM) LIKE 'nqsserver%';

You get an error if you’ve not set a value for the variable that is referenced in the connection script (in the above example, “REPORT”). So what you could do is create a dummy session variable called REPORT with a default value (eg “<unspecified report>”), which will then be used if a report doesn’t override it:

Dummy session variable

Dummy init block

Variables

It may be my misunderstanding of the subtleties of the flavours of OBIEE variables, but the behaviours seem inconsistent to me. For example, even though I am issuing a SET VARIABLE in my logical SQL, the value of the variable REPORT doesn’t change from its default (in this example ‘NONE’) when listed in the Session Manager or queried via Narrative view. It isn’t even shown if I don’t create it as a session variable in the RPD.

Unchanging session variable value

Session variables displayed in Answers

Despite this, the modified value of the variable is what gets passed through correctly in the DB connection.

SAW_SRC_PATH

This is a variable (along with QUERY_SRC_CD) that is passed automagically by Presentation Services to BI Server in the Logical SQL it executes:

Logical SQL, as seen in NQQuery.log

If this could be harnessed and manipulated (eg right-most 63 chars) then the report details of any report could be automatically included with the DB connection string. But – try as I have I can’t access the variable through VALUEOF. Anyone know how?

References

• Mastering Performance with Extended SQL Trace – Cary Millsap
• @lex’s variables reference diagram
• Gerard Nico’s excellent wiki has lots of information about OBIEE variables, amongst other things