Saturday, October 17, 2009

Switching Report Data Source Name(DSN) or User Name from OBIEE Dashboards/Answers

One of My Customer had a requirement to come up with a design which would allow the OBIEE Admistrator to switch report Data source Name(DSN) from the Answers . Let us say currently a report is coming from a DSN = ORCL now if the Administrator would like to change the source to a new DSN = ORCL2 the administrator should be able to do so from the OBIEE Answers. To give you a brief background behind this requirement , When we went live all the realtime(near realtime to be precise) reports were pointed to a data source which was planned to be refreshed nightly .Therefore we wanted to be able to come up with a design where in we could easily point a report to the live production instance for selected reports if the Business users could justify the need for it to be realtime.

We will first see what I am trying to achieve and then explain you the steps .
The Below report is being sourced from a Data Source Name(DSN)= 'ORCL' .

Now if you would like to source the same report from a different Data Source let's Say 'ORCL2' we can do so by adding just one Prefix statment by going to the Reports>Advance tab.

After applying the above statment the Report now sources from a DSN=ORCL2 as you can see below .

Let us now look into the steps to achieve this
1. I created three Different Data Sources and ran the script to create two tables Department and Employees.You can download the script and the RPD from the link below.
ORCL>DWH: Data Source(Database)=ORCL,Schema(User)=DWH
ORCL>DWH2:Data Source(Database)=ORCL,Schema(User)=DWH2
ORCL2>DWH:Data Source(Database)=ORCL2,Schema(User)=DWH

Make sure that you keep the Same Password for all the three different schemas above.
2. As you can see in the RPD I just imported the two Tables established the Join in the Physical Layer and Dragged/Dropped the Folder all the way to the Presentation Layer.Then I set the Aggregation as Sum For Employees>Salary.
3.We then need to create a Session Initialization Block as below with two variables.

4.Variable OLAP_DSN . Make sure that 'Enable any user to set the value' is checked.

5.Variable OLAP_USER.

6.Now let us Modify the Connection Pool to use the Session Variables.

7. Let us now create three Reports.

Go to the Advanced Tab>Prefix and add the below script

Go to the Advanced Tab>Prefix and add the below script

7.As you can see below the reports are coming from three different Data Sources in a Single Dashboard Page.

8. The Administrator can now Navigate to Administration>Manage Privileges>Answers:Access Advanced Tab and grant privileges to the group who is allowed to add the script in the Reports Advanced Tab.


  1. Hi Shiv,

    I've taken the liberty of expanding upon your post and taking it a step further:


  2. Hey Shiv,
    thx for ur post.It was helpful to me in solving a problem in connection pool.


  3. Hey Shiv, The RPD and Script which you have share are not accessable when I click download. Can you please upload

  4. Hey Shiv. THanks for this post. I have a question for which I cant seem to find an answer. So, is it possible to change the connection pool from one eBS instance to another and keep the same predefined reports in OBIAPPS? I have a connection pool looking at one eBS instance (demo) and would like to change to another eBS instance in order to get different set of data showing up in the reports. How can this be done? CHeers

  5. Hi Shiv,

    Thanks for this post. It so useful. You have plenty in you to deliver to BI world.