Utilizing Help Files in OBIEE

Hi all, my name is Helena Bennett and I am currently working in the BI space as a Business Analyst, I came across help files recently and thought I would put together a “how to”….

What are They?
In OBIEE help files are .htm files which are used to help users to better understand report content. They can assist with user training of new reports as they can describe any logic contained in the report for example filters which isn’t obvious from looking at the report. They can also describe the attributes and measures displayed on the report and any other report specific information.

How do you create them?
To create a help file you need to do the following:
1. Create a .htm file which contains the content of your help file
2. Save it to the following location: …\app\res
3. Point to the help file location from the report
Click on the edit button in the Title section of the report

Then enter the path and name of your help file:

How to you access them?
When the report is displayed there will be a ‘?’ in the title as shown below

Clicking on this will display the help file for that report

Give them a go – users will love them!

Oracle Data Integrator (ODI) web services (SOAP client via ODIInvokewebservice) and the OBIEE web services API.

As outlined in a previous post ODI has a built in SOAP client via the ODIInvokewebservice tool. One of its limitations is that it doesn’t run on Java 6.

Another limitation is that it doesn’t like SOAP headers. So if you are used to SOAP headers you find in tools such as soapUI you are out of luck for the moment. Note 1143755.1 explains that an enhancement request has been logged to include this in a later release, but as of release 10GR3 it is not supported.

So what use is the ODIInvokewebservice? You could, e.g. query the Amazon product advertising API or the ebay equivalent. In this post I’ll show you how you can query the OBIEE web services API. I am currently working on a project to extract the group, user, privilege etc. information from the OBIEE presentation catalog. This is useful to automate the documentation of group hierarchies, inherited privileges etc. I’ll keep you posted on the progress.

The OBIEE web services API does not like Single Sign On (SSO). If you are using IIS as your application server you need to create a second virtual directory and disable integrated security.

Once this is done we are ready to go. Create a new package in ODI and add an ODIInvokewebservice tool. On the General tab click on the Advanced… button. This will bring up the ODI SOAP client.

In the URL field type in the path to the OBIEE WSDL file: http://”server_name”/analyticsSOAP/saw.dll?wsdl. This should point to the new virtual directory in the step above. Next click the connect to WSDL icon.

This will return all of the services and methods that are available from the OBIEE web service API. The web service we are interested in is the SAWSessionServiceSOAP and in particular the logon method. Each request to the OBIEE web services API needs to be authenticated and the logon method returns a sessionID for us.

Populate name and password with username and password of an OBIEE account with SOAP privileges and click the Invoke web service icon

This will return the sessionID that we can use in other requests to the OBIEE API later on.

Click on OK to return to the previous screen. On the General tab you can then define a response file that ODI writes the returned XML to from your web service call.

This will produce an XML file in the specified location similar to the one below:

<?xml version=”1.0″ encoding=”UTF-8″?>
<ns1:logonResult xmlns:ns1=”com.siebel.analytics.web/soap/v5″>
<ns1:sessionID xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:type=”xsd:string”>5lll90nu19bbhi49u57h8vdott63gcs5j19g9vazOr07UFe9W00</ns1:sessionID>
</ns1:logonResult>

We can then extract and load the sessionID via an ODI Interface into a database or temporarily store in the hsqldb odi memory engine.

There is one issue with this, however. The ODI XML parser doesn’t like xsi attributes such as xsi:type. So before we can reverse engineer the logon.xml from above we need to get rid of any occurrence of xsi:type in the logon.xml file. I have written an ODI procedure in Jython that does exactly that. As a parameter it takes the file path to the logon.xml and replaces any occurrence of xsi:. Of course, if you are on Linux you should use sed & awk to do this as performance is better.

s = open(”<%=odiRef.getOption(”FILE_PATH”)%>”).read()
s = s.replace(’xsi:’, ”)
f = open(”<%=odiRef.getOption(”FILE_PATH”)%>”, ‘w’)
f.write(s)
f.close()

Now we are ready to reverse engineer the XML and load the sessionID into a relational database. These steps are well documented so I refer you to the “Oracle by Example Series: Oracle Data Integrator” site or to Craig Stewart’s ODI video tutorials.

If you want to master scripting in ODI get the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

What has ODI encryption got to do with clearing the OBIEE cache???

As part of a data mart refresh I am using nqcmd to clear the cache for the corresponding subject area in OBIEE. Thanks to John Minkjan for his post on properly documenting how the OBIEE cache can be cleared.

Basically I am executing nqcmd -d AnalyticsWeb -u Hans -p Zipfel -s d:\obiee\scripts\Purge_XXX_Cache.txt in an ODI procedure with technology set to Operating System.

I have currently installed an agent on the box for the BI server to launch nqcmd. However, I think that alternatively you can perform a custom install of the BI ODBC driver and the System Management component on the server where your ODI agent is running.

The major problem I have with the above solution is that the password for the Administrator password is sent across as clear text. Any ODI Operator module user would be able to view this. A gaping security hole. My first instinct when looking for a solution was to look for an encryption mechanism for this in OBIEE itself. Unfortunately no such thing is provided (as far as I know).

ODI to the rescue

I was rather frustated and ready to give up when I remembered that ODI has an encryption mechanism of its own.

Below are the steps I took to encrypt the password.

Right click on the procedure and select Encrypt. In the popup select Get a new encryption key

Save the key in a secure location as you will need it should you ever wish to decrypt the procedure (yes it can be easily lost…).

When you now execute the procedure only gobbledygook (what a great word) will show up in the Operator module.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

OBIEE & OBI Apps event, Dublin, Ireland, 17 September

There has been very little activity on the blog recently as I’ve been busy working on an ODI data integration project. The good thing though is that I have collected a lot of blog material.

Watch out amongst others for a post on the wobbly relationship between ODI and analytic functions in the not so distant future.

Anyway, as part of the Irish BI OUG SIG we will be running a full day event on OBIEE and OBI Apps on 17 September at the Oracle offices in Dublin. Adam Bloom from Oracle will give overview presentations on both apps. Each of these will be followed up with case studies by Una Madigan from APC by Schneider Electric. Those guys implemented OBIEE way back when it was still Siebel Analytics. I saw Una present recently at the OUG Ireland. One of the best presentations I have seen on the subject and a textbook implementation of OBIEE. Both technologists and business folks will be able to take away some lessons learnt here.

As always there will be ample opportunity for networking. The lunch alone is worthwhile going… And maybe we can squeeze in a quick pint after the event.

What are you waiting for? Go on and register.

Query hints in OBIEE

I have recently come across a post on the OTN forums on how to use hints in OBIEE. OBIEE lets you specify hints in the physical layer on a table by table basis. The problem of the user in the forum was how it is possible to hint the alias of a table in the SQL generated by Answers. This is relatively straightforward and I will show you how this can be done in a minute. However, the bigger issue around usage of hints in OBIEE is that it is extremely inflexible. In Business Objects you can use a workaround to use hints on a query by query basis. This same workaround does not work in OBIEE and use of variables did not work either. But let’s first have a look at how we can use hints with aliased tables before we have a look at why OBIEE is very inflexible when it comes to query hints.

We have the following scenario: We want to use dynamic sampling on a table either because we have stale statistics or we want to filter multiple columsn on the table. If you want to find out more about dynamic sampling have a look at Tom Kyte’s great article on dynamic sampling in Oracle magazine.

In order to find the alias that OBIEE will use for the physical table in BI Administrator go to Tools > Query Repository. For name type in the name of your table and Physical Table as type (as per screenshot below)

This will return the internal ID of this table. In the screenshot this is 3001:210. We are interested in the part after the colon. This piece is the alias that OBIEE will give this table in Answers. It will prefix it with a T.

So if we want to add a dynamic sampling hint to the products table we need to add the following to the product table’s hint field : dynamic_sampling(T210,3)

This will then generate SQL as follows:

As you can see, our hint was applied to the query.

However, the whole thing is terribly inflexible. It will add this hint to each query that you generate via answers that involves the products table.

I have tried various workarounds to make this more flexible, unfortunately without any succcess. To get this to work in Business Objects you create the equivalent of a logical column and populate it with /*+ dynamic_sampling(T210,3) */ ”. This will throw a parse error but it will still allow you to add it as a column to the equivalent of the presentation layer. When you create an ad hoc query you have to add this column as the first field to your ad hoc report. This will then generate SQL similar to this:

SELECT
   /*+ dynamic_sampling(T210,3) */ '' as col1,
   'whatever' as col2
FROM
   products T210

When you try to do the same in OBIEE it will throw a parse error and won’t allow you to proceed. I have also tried to create the hint as a variable and then reference this in the hint field of the physical table. Again without success.

When Answers generated the SQL it just took the literal value of VALUEOF(hinter) without evaluating the hinter variable and put the phrase in as the hint for the query.

If anyone has a neat solution for more flexible hinting in OBIEE please let me know.

You may also be interested in the OBIEE book Oracle BI Enterprise Edition Dashboard & Report Best Practices. This is currently the only published OBIEE book on the market.