Business Intelligence – Oracle

Oracle BI EE – Switching between Production and Development Data Sources using same Reports and Dashboards

Posted by Venkatakrishnan J on February 27, 2009

I was recently involved in a client project wherein the client was implementing BI EE on top of an Oracle Database. Since they were on a shoe-string budget, they could not afford to have a couple of servers for BI EE Development and Production. Of course, not an ideal situation, they did have a couple of instances of the database (one for development and the other for production). These databases were populated using Informatica from an external source. Since, the client had only one instance of BI EE, they wanted to know whether it is possible to switch between production and development databases just by means of a single prompt (for administrators alone to test the data validity across the database instances). Though a very unique request, it made sense due to the frequency of development happening on the dev databases. Also, they wanted a capability wherein a set of users would be looking at a set of reports reporting against the dev database and another set of users would be reporting on the same set of reports on the production database. The below diagram provides the details on what is required.


Lets look at an approach today using which we shall achieve the above requirement. So, in effect our aim is to have a prompt across all the reports, which will switch between the development and production databases (for Administrators). For demonstration purposes, i would be using the Global Schema. Lets consider the Global Schema as the actual development database. Also, lets consider another schema called global_prod which would have the production database tables.


As you see, we have the same set of tables in both prod and dev databases. Typically any Administrator would like to run the reports on both dev and prod to determine any possible issues. So, lets start with designing the BMM layer for these 2 schemas. The idea is to create a single BMM layer which will fetch data from either prod or dev depending on the fragmentation set in the repository. To start with, lets create a simple select view in both Dev and Prod to return the actual values that we would choosing in the prompts for Dev and Prod.




Now, for each of these new sources, create a complex join with the fact tables as shown below


Now, in the BMM that we are building, add a custom dimension with a single column called Source using both the above custom select views as Logical Table Sources. Also, set the fragmentation in such a way that one LTS is chosen, when Dev is chosen in the prompt and the other LTS is chosen when Prod is chosen in the prompt as shown below.


Now, for all the dimensions and the fact tables, include both the Prod and dev sources. Ensure that fragmentation is set for each one of them to Prod or Dev depending on the LTS. For example, the fact table related Dev LTS is shown below


I have not shown the dimension build and other aspects in the RPD BMM as everything else would remain the same. Instead of one LTS for each dimension, use 2, one for prod and the other for dev. Of course, as your RPD becomes complex, adding more and more LTS would only make the RPD to grow big. That is something one would have to consider. But this approach has very good flexibility and can be implemented by anyone who wants implement a similar requirement.

Now lets log into Answers and check how this works. Create a very simple report as shown below and have the Dimension Source column as Is Prompted in your report. Also create a prompt to show the Source values of Prod and Dev. Include the report and the prompt in the dashboard. Lets choose Dev first and run the report.


If you look at the query, you would notice that Dev database is used for generating the above report.


The same would be the case for Prod as well. But one drawback is that, if we do not include this filter, we would get a union all from both the LTS as shown below.



One cannot expect every user to create a prompt like the one above in each and every report. So, in order to avoid the duplicate issue and to ensure that a group of users automatically have a Dev or Prod filter assigned to them just add a custom Security Group and add the filter as shown to each and every Logical Table in the BMM. One Security Group would have a filter on Dev and the Other on Prod. So, anytime a specific user from one of the above groups creates a report, the Dev or Prod filter is automatically passed and this will ensure that the fragmentation happens properly.



5 Responses to “Oracle BI EE – Switching between Production and Development Data Sources using same Reports and Dashboards”

  1. Kumar said

    Hello OBIEE & EPM Gurus

    just wondering if anyone of you either experienced or come across importing XBRL instance documents into either OBIEE or Hyperion HFM or Essbase….just curious, as I know OBIEE and Hyperion supports from reporting stand point of view, but I am not sure from the Data import and aggregation point of view….

    Appreciate if any comments/feedback/inputs…


  2. Alexwebmaster said

    Hello webmaster
    I would like to share with you a link to your site
    write me here

  3. Robert said

    Would it be possible to create 2 different connection pools/Groups one each to Test and Prod then grant the Connection pool pointing to the production DB to the Production Group and the same to test. Then create two different Login ID’s one with the production Group and one with the Test Group.

    If this is a viable method do you know how OBIEE functions when there are multiple connection pools

  4. Akash said

    First of all I approeciate your post.

    I have some questions related to this post.
    lets say I have a connection pool in rpd with

    wherin we dont specify the database name. when we point the rpd to differenct environment we change the static variable name’s value and password.
    In that case how do i create two connection pool in rpd.? like you have ORCL-Dev and ORCL-Prod…

    What is Dual..? while creating view..
    Do I need to create any columns in that view.?
    if we dont create then while creating BMM join with facts it was giving error.

    • Akash said

      Hi Venkat

      I got all d answer except last one..if u can answer pls..?

      Do I need to create any columns in that view.?
      if we dont create then while creating BMM join with facts it was giving error.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: