OBIEE: Create Repository Init Block

My post earlier today, OBIEE: Text File as Data Source , was the first part of my overall goal.

Here's what I'm trying to do. 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.

We've pulled the text file into the RPD already. Now, I need to create 2 repository variables to hold these values. I open up the Administration tool, go to Manage then variables. You should see this:



Then follow this picture to create a new Initialization Block



Name your Initialization block init_block_test



Leave the scheduler stuff alone for now, click on Edit Data Source



First, browse for the connection pool



Select the get_local_password (yes, the name mysteriously changed from the default Connection Pool to "get_local_password," it makes sense doesn't it?).



In the Default Initialization String text box, enter:

SELECT username, password 
FROM test.txt

You can test it if you want, but I'm saving it for the final step.

Your Initialization Block Data Source should look like this



Now select Edit Data Target



Which looks like this



Select New and enter INIT_USERNAME in the Name text box and make the Default Initializer 'USERNAME' (in single quotes).



Do the same thing for the password column, INIT_PASSWORD and 'PASSWORD'

Now you are back at the main screen and the Test button (lower left hand corner) should be enabled. Select it.



One final test...let's see if we can access these in the presentation layer.



So it's a success right?

After I was done, I began to think..."You can access the repository variable from the presentation server"

Ummm...that's not good. Double checked the properties of both the Initialization Block and the Variables to see if there's a way to lock it down...and there's not. Because of this "small" little security issue, I'm not completely sold. It is a proof of concept, perhaps it should stay there.