Archives

Tagged ‘OBIEE‘

OBIEE 11g Repository Documentation: Extracting metadata from the RPD

With OBIEE 11g there are two methods to extract metadata from the RPD.

The first one uses the admintool.exe command to generate metadata. It was already around in OBIEE 10g. However, the syntax has changed slightly. The second uses the biserverxmlgen command.

Admintool

This is an unsupported and undocumented feature. Use at your own risk.

You have to create a command file that is then invoked by admintool.exe.

OpenOffline <path to RPD> <RPD Password>
DescribeRepository <path to output file> <encoding>
Exit

Parameters

<path to RPD>: Path to your RPD, e.g. D:OracleMiddlewareinstancesinstance1bifoundationOracleBIServerComponentcoreapplication_obis1repositorymyrpd.rpd
<RPD Password>: The password to your RPD
<path to output file>: Location on file system where the RPD metadata will be output to. The extension of <path to output file> determines the type of output: (1) .csv (comma-separated values) (2) .txt (tab-separated values) (3) .xml XML
<encoding>: Unicode, ANSI, UTF-8

You can call the command file by using the following command (This has not changed from OBIEE 10g.)

admintool.exe /command <path to your command file>, e.g. admintool.exe /command C:extract_rpd.txt

biserverxmlgen

I can see three advantages of using biserverxmlgen over admintool

(1) The Admintool /command syntax is not documented or supported
(2) The output of biserverxmlgen is more comprehensive, e.g. it contains information such as the alias in the presentation layer
(3) It may be easier to extract this. I have not tried this out, but to extract some of the info from the admintool extract can be a bit of pain, as there is a recursive relationship between logical and derived logical columns. Not sure if there is an XSD for this XML. If you have any info on this let me know.

The syntax for this is:

biserverxmlgen -R D:OracleMiddlewareinstancesinstance1bifoundationOracleBIServerComponentcoreapplication_obis1repositorymyrpd.rpd -P Admin123 -O c:rpd.xml -8

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

Edit Title

Then enter the path and name of your help file:

Help URL

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

Clicking on this will display the help file for that report

Sample Help File

Give them a go – users will love them!

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.

odi_encrypt1
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

odi_encrypt2

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…).

odi_encrypt3

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)

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)

obiee_hint2

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)

obiee_hint1

This will then generate SQL as follows:

obiee_hint3

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.

obiee_hint4

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.