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.
SET VARIABLE OLAP_DSN = 'ORCL2'

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.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

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.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';


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.
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.
SET VARIABLE OLAP_DSN = 'ORCL2'

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.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

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.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';


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.
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.
SET VARIABLE OLAP_DSN = 'ORCL2'

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.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

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.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';


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.