Lets look at a pretty interesting requirement today. I had a call yesterday with one user who basically wanted to know whether there is any way in BI EE Where he can reuse the existing Metadata that he has created for some other data source. He also mentioned that this data source would also contain the same set of tables. So, basically the user had the same set of tables in different databases(only the data would change but the data structure would be constant). He wanted to know of a way wherein he could dynamically vary the data source name instead of recreating the metadata for the other data source. Also, he wanted the data source to be listed as a drop down in the dashboard prompt so that the reports would fetch dynamic data from different data sources depending on what is chosen in the dashboard prompt. Lets look at an approach to achieve this requirement. In our example, we shall be using the EMP table of the SCOTT schema in 2 different databases. Both these databases can be accessed using the TNSNAMES entry ORCL and ORCL1 respectively. For the sake of difference lets delete one row from the EMP table of the SCOTT schema in one database.
Now, lets import the EMP table from any one of the SCOTT schema and design the Business Model and Presentation Layers. Ensure that you are disabling cache for the EMP table.
Now, the next step is to create a seperate Data Source and a connection pool which would just be used for initializing Init Blocks.
The next step is to create a Session Init Block which will be populating a Variable called DSN using Row-Wise Initialization. This is just for Initialization purposes. In our case, we are initializing DSN variable to ORCL (the base tnsnames entry).
Now, go to the connection pool of the data source containing the EMP table. Make the data source name within the connection pool to point to the DSN variable using VALUEOF(NQ_SESSION.DSN).
Once this is done, go to answers and create a simple Dashboard prompt on Ename. In the Results on this prompt, use the following SQL. Also, make it to set a presentation variable called Test. Also, ensure that this has a default value of ORCL.
SELECT CASE WHEN 1=0 THEN Employee.EName ELSE ‘ORCL’ END FROM “SELECT BM” UNION ALL SELECT CASE WHEN 1=0 THEN Employee.EName ELSE ‘ORCL1′ END FROM “SELECT BM”
Now, create a simple report containing only ENAME as the column. Go to the Advanced Tab of this report and in the Prefix section, enter the below command
SET VARIABLE DSN=’@{Test}’;
Now save this report and go to dashboards. Include the prompt and the report that we created above. Now you can notice that as you change the DSN in the dashboard prompt the data would also change (SCOTT ename would be missing in one report).
Posts
