March Repost: More Dynamics in the Physical Layer of OBIEE

In our "blast from the past" series, I am reposting today an article which I wrote 5 years ago. With version 11g not to be released for one more year, the article focuses on strategies how to add more dynamics to the physical layer of a BI repository. Please enjoy

***

During a BI project there are numerous opportunities to go down the stony road and establish redundancies where you do not really want them.

When I am in a provocative mood (which I am not very often, I assure you), I use to ask "Do you want a 'B' project or a 'BI' project?". There are so many pitfalls waiting for us to stumble into and reveal our bad (or sometimes even non-existent) design.

There are many triggers for this post, as there are many fine people blogging about OBI EE (here and here to mention just two of them, keep up the good work!) and I would also like to thank the many people who contact me with questions regarding OBI EE design and architecture.

Today I would like to examine options we have to make the physical layer definitions more dynamic. Of course we all know about aliases in the physical layer, so I will omit these.

1. Multiple Connection Pools (and permissions)

As John Minkjan points out in his post on multiple connection pools, these object types have a security permission button which allows us to define which user (or group) uses which connection pool.

This can be very convenient, if you want to "switch" the users to different connection pools. The connection pools could even point to different data sources so you can implement a simple solution to use the same set of requests/dashboards/business model against different databases (such as DEV or PROD).

In the below screenshot (click to enlarge), please note that we can set permissions for a group/user in the General tab for either a presentation layer object or connection pools (the only object type in the physical layer that has permission management options).


2. Dynamic Names

The dynamic names feature is described in several places across the Oracle BI EE and Marketing Segmentation documentation.

Simply put, it enables us to assign the value of a session variable(!) instead a static name to the following object types:

• Physical Catalog (useful for accessing different db schemas)
• Physical Table (for switching between tables)
• Alias

A real life example is the sampling factor in the OBI EE Marketing Segment Designer. Instead of issuing a lengthy count statement against the original table (with let's say 10 millions of records), you can do a count against a sampling table (with a sample of 5%, that is 500.000 records) and then multiply with the necessary factor (20 in our example) to get an extrapolated result.

Sampling factor setting in Segment Designer

Remember you can set session variables not only using Initialization Blocks but also from the presentation services using either a dashboard prompt or the SET VARIABLE operator in an advanced SQL prefix.

3. Variables

As many of you savvy readers are aware, we can use session and repository variables all over the place in the Administration Tool to make our lives easier (or more complicated?). For example you can specify a connection pools' data source as VALUEOF(OLTP_DSN) and use a repository variable named OLTP_DSN to carry the value (which is the way it is done in Oracle BI Applications).

4. More?

Do you have more ways of bringing your physical layer to dynamic life? Let me know by adding a comment to this post.

Have a nice day

@lex