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.
1 | SELECT 'Dev' FROM DUAL |
1 | SELECT 'Prod' FROM DUAL |
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.
Posts
