OBIEE 11G RPD bypass using Private Variable Columns

Tweet

We had a requirement where the user wanted to be able to get into the tool and change his formula and also wanted the change to be reflected across all his reports. Changing the formula in each report was a lot of effort and hence not practical.

This blog talks about a 6 possible ways to handle this requirement and introduces the concept of Private Variable Columns.

Giving credit where it is due: I worked on these approaches with my colleague (Justin) who came to me with the requirement stated above.

1. Saved columns:  Saved Columns are discussed @ https://docs.oracle.com/middleware/11119/biee/BIEUG/analyses.htm#CJACHIBC
A Saved column is what its name suggests. You can put a formula in a column and save it in the catalog. You can then put this saved column in any analysis of your choice.

You get an option to "Save" a column along with the other options on the column. Saved columns are not available in 11.1.1.7

This sounds like a solution but the problem is referencing. Let's say you wanted to use this saved column to do some calculation in the column formula of some other column. You would do the following.

This will result in the following.

The problem is, if you were to change the formula for saved column from  "Presentation Table"."Column A" to "Presentation Table"."Column C" then the change wont get reflected in the formula of 'Concat Columns' column here. The formula of 'Concat Columns' will still remain "Presentation Table"."Column B"||'-'||"Presentation Table"."Column A" instead of "Presentation Table"."Column B"||'-'||"Presentation Table"."Column C"

So this feature of OBIEE 11.1.1.9 will not work for us.

2. Global variables: Global variables is a good feature and is documented @ https://docs.oracle.com/middleware/11119/biee/BIEUG/analyses.htm#CJACFHEF

We can create a Global variable by clicking on the little arrow next to 'Variable' in the column formula of any column as shown below. Global variables are not available in 11.1.1.7

This will open the following popup

You can click on the green plus sign and create a global variable. You can then use this variable in any column formula you desire.

The problem with this approach is security.
The definition of the global variable will be visible to all and my user was a little concerned about the security. He did not want any user to have an access to his formula.

Now that the 2 new 11.1.1.9 features did not work for us, we had to think of something innovative.

3.Using presentation variables: In this mechanism, we assign a value to a presentation variable using a variable prompt and then add the prompt in the Dashboard Properties. We then use the presentation variable in the column formula in an analysis. So when the value of the presentation variable is changed, the column formula picks the updated value at every run. The screenshots of this process are shared below.

The result of this is shown below.

In this approach, unlike the Global Variables, the analysis, the dashboard and the prompt can all be controlled using your catalog security.

Let us say that you had a Year column next to Column A. Your report will look like the following

What if you want the value to change in every row of your report based on the Year column?

Just replace '5' in the prompt value with the following Case Statement.
Case When "Time"."Year" = 2015 Then 5 When "Time"."Year" = 2016 Then 6 When "Time"."Year" = 2017 Then 7 Else 8 End

This will result in the following output.

Now, if a user wants to change the logic, all he has to do is the change the case statement in the variable prompt. He can change his case statement to lets say Case When "Time"."Year" = 2015 Then 99999 End. The value will automatically change everywhere the presentation variable is used.

Since we use presentation variables to implement dynamic columns whose formula can be changed on the fly and since we can control the security as well, I call these columns, Private Variable Columns.

4. Command line catalog manager: While a Private Variable Column is the most elegant method of handling this requirement, I wish to talk about another way of implementing this requirement.
The command line catalog manager approach might not be the best one in this use case, it might be quite handy to handle some of the other requirements.

We can use the catalog manager in command line mode and couple it with a shell script that reads an excel/csv file and updates the XML of the catalog where ever required. I will blog about this process in detail sometime later and will then add a link of that blog here.

5. Excel/CSV as a source: In some cases, it might be worth letting the user put his data in an excel/csv file and then use that file as a source in OBIEE. The drawback of this approach is that we will have to code to join the excel to our existing data model and the join will happen in the BI server. BI server joins might take longer to execute depending on the size of the file. Setting up the security on the file will also be a task. We will also have to make sure that the file is accessible from the BI Server.

6. External tables in Oracle: Another flavor of excel as a source is External table of Oracle. We will have to join the external table in RPD and ensure the security around the csv file used by the external table. However, since the external tables are on the database so the joins perform better than the joins on the BI server. External tables are a feature of Oracle database so this approach will not work if you are using a different database.
External tables are discussed @ https://docs.oracle.com/cd/B28359_01/server.111/b28319/et_concepts.htm#g1017623


Since we are talking about RPD ByPass, I want the mention the other known way of bypassing the RPD i.e. the Direct Database Request (DDR). Oracle talks about DDRs @ http://docs.oracle.com/cd/E25178_01/fusionapps.1111/e20835/appuir.htm#BIEUG10464
DDRs are not the best way to handle this requirement.

Till next time ..