OBIEE: Dynamic Variables

Apparently this is a 3 part series, Part I, OBIEE: Text File as Data Source and Part II, OBIEE: Create Repository Init Block.

Finally, to the ultimate goal, dynamic session variables. Ideally, these would be repository variables, but since I'm using the row-wise variety, that is not possible (as of 10.1.3.4). Once a day, or some other relatively long time frame would be sufficient.

Anyway, there's an occasion where dynamically creating variables might be needed or necessary.

To recap the problem

Due to certain restrictions in our environment, the tnsnames entries in our separate environments (Dev/QA/Stage/Prod) are exactly the same. I would prefer to have one entry in each environment that points to that environments database, but alas, I cannot. What this does is forces us to change the password in the connection pools when migrating. I know it's not ideal, but it is what it is.

The proof of concept I am embarking on will allow us to lookup the tnsname entry and the associated password for that environment, theoretically, requiring no changes between environments.

In that respect, it was decided that we would have a single service account. From that service account, we could look up the values we need for each environment. Here's the other fun problem...what comes first? The chicken or the egg? This service account password will be stored in a simple text file that will be locked down by the system administrators. Don't ask what happens when the password changes either. At this point, it's just a proof of concept. So be gentle. If you have an established way to manage passwords...please share.

A few people did chime in, Pete Scott suggesting using the a command line utility detailed here. Christian chimed in that I should use it at my own risk and rnm1978 said this problem has not been truly solved yet.

The whole issue of migration OBIEE deployments through the environments is a bit of a pig's ear with no foolproof method that I've seen

I need to look up that phrase as I'm certain it's peculiar to my brethren across the pond.

Finally, the meat of the post. Here's my table:

OBI@TESTING>@desc obi_variables
 Name                           Null?    Type
 ------------------------------ -------- -------------
 VARIABLE_NAME                  NOT NULL VARCHAR2(50)
 VARIABLE_VALUE                 NOT NULL VARCHAR2(250)
 VARIABLE_TYPE_CODE             NOT NULL VARCHAR2(30)

I'll throw a few records in there for fun (and to obviously demonstrate).

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'TNS_TESTING',
    'TESTING',
    'TNSNAME' );

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'PW_TESTING',
    'TESTING',
    'PASSWORD' );

INSERT INTO obi_variables
  ( variable_name,
    variable_value,
    variable_type_code )
VALUES
  ( 'URL_TESTING',
    'http://localhost:7777/pls/apex/f?p=101:1',
    'URL' );

TYPE       VARIABLE_NAM VARIABLE_VALUE
---------- ------------ ------------------------------------------
PASSWORD   PW_TESTING   TESTING
URL        URL_TESTING  http://localhost:7777/pls/apex/f?p=101:1
TNSNAME    TNS_TESTING  TESTING

For demonstration purposes, I am going to create a new database in the Physical layer and add a connection pool called obi variables pool. From the previous post, I'll be using the values that I brought in through the text file. The only difference is that I added username...just in case. To sum that up, I'm bringing in the TNSNAME entry, the USERNAME and the PASSWORD.

Your connection pool should look like this:



The password column is the same format as the others, VALUEOF( INIT_PASSWORD )

You'll have to re-enter that when you save.

Now I'll be creating an init block using this connection pool. I won't drag you through that entire process again (your welcome). This is a Session Init Block as opposed to the Repository Init Block in the previous example.

The only difference is that I will be using a row-wise variable. I'll get to that in a second.

After selecting the obi variable pool connection pool, entering the following SQL statement into the area provided:

SELECT variable_name, variable_value
FROM obi_variables

.In the Edit Data Target area, select the row-wise initialization variable. Select OK.

Test it and voila! You now have dynamic variables in OBIEE.