As customers migrate from the classic Business Views one feature missing from the new Information Design Tool is a straight-forward dynamic data connection option when defining connections. In fact, it’s already been mentioned in Ideas Place along with a great use case.
Dynamic Data Connections
Dynamic Data Connections allow a database connection to be determined by the answer to a user prompt. This capability is not often used but is extremely powerful for organizations that have exactly the same database schema across multiple instances.
Normally within the universe you can use table mappings to automatically point the user to the correct database, but in some cases the user must be able too select to desired database on the fly.
Once I began to look into this issue I realized that I could use the power of @prompt and table mapping to dynamically determine the name of the database to connection to. This solution works extremely well. The only restriction is that all of my databases through a single Universe connection, e.g. I can’t have my same database schema mapped across desperate databases, e.g Microsoft and Oracle.
In my example I am using MS SQL Server 2008.
I setup two databases: DB1 and DB2. Both databases contained a single table called Customers. The report consumer needs to dynamically switch between DB1 and DB2.
Defining The Connection
The first thing you need to do is define a connection. It’s very important to define the connection using connection that points to the first of my two databases. By default I don’t want to require a table owner and table qualifier to be defined. In my case, I setup the connection to be called DDC_Connection and it points to my DB1 database.
The Data Foundation
This will be where the magic happens, but will get to that later. We are going to add the Customers table to our Data Foundation. This is where you will define all your standard table joins. In our case my data foundation is called DDC_Foundation and it contains one table, Customers.
You will want to use Show Column Values and Profile Column Values to test your data foundation.
Next we will want to setup our Universe as if it were only connecting to a single database. Use the Data Foundation we’ve already defined to finish building your Universe. In my case the Universe is called, DDC_Universe.
You will want to use the Show Values and Queries capability within IDT to test your Universe and make sure everything working properly. You may also wish to test it with WebIntelligence as well.
Now that you’ve tested the Universe and it’s working with your default database, you want to make it dynamic by replacing the table names with an @prompt statement. This statement will be interpreted by the reporting engine and replaced with the returned string. In my case:
will be replaced with DB1 or DB2.
NOTE: If you are unfamiliar with the options for @prompt, you may read about them in the IDT Users Guide
I replaced the Customers table name with the @prompt command and the necessary table qualifier, .dbo.Customers
Therefore the resulting string you will be paste into the table name will be something like:
Save the Data Foundation.
Open the Universe and have a look. Everywhere you say the table name has now been replaced with the @prompt string. Below is a screenshot of the resulting Region field.
If I now right-click and choose Show Values…
You will see the @prompt be evaluated at run time and you will see a prompt.
In my case I selected DB1, so the region Georgia appeared in the list.
The only challenge with this magic is that suddenly my universe can seem unnecessarily complex and difficult to maintain. Therefore in order to keep the universe as simple as possible, we can use table mappings to only apply the prompts at run time to all my non-universe designer users.
Better Magic: Using Table Mapping
If you use table mapping, then you leave your universe in the default state such that the data foundation does not contain an @prompt.
Next, select the IDT security editor from the top menu bar.
This will allow you to define a security profile and apply it to a set of users. Typically you would want this rule to apply to all non-Universe Designer Users.
First select Users/Groups at the bottom of the screen so you can select the users you want to apply our Data Security profile to.
Select the Group of users you want to apply the table mapping to. (I selected Everyone for testing.) Next, select the universe you want to apply the table mapping to. In this example I selected DDC_Universe.unx.
Next, you will click to icon to create a new data security profile. I recommend you rename to profile to clearly indicate what it is used for, e.g. Dynamic DB Connection.
Click on the tables tab and Insert to create a new table replacement
You will be replacing your table names with the @prompt/table name combination values. The table mapping wants to put quotes around the replacement strings, therefore you must specify the Qualifier, Owner and Table separately.
Here is what it should look like after you have entered in the values.
The final step will be to activate this newly created security profile to the group and universe that you’ve selected. Simply check the box next to the security profile.
Experience The Magic
After everything has been setup, the WebIntelligence reporting engine will apply the security profile when a matching user runs a reporting using a secured universe (namely a security profile has been applied).
Here is what the user will see in WebIntelligence. They will select the universe, choose the objects they want for the query and choose run.
When the user selects Run Query, they will receive a prompt to select to database. This is because the security profile defined on this universe for this user requires the security profile be applied.
After the user selects the database, they will see the results based on the database they chose.
In this example I shared here, I hard-coded the list of databases in the @prompt selection. If you have a large number of universes, you can create a table in your our database which lists all the databases. In the case of one customer with hundreds of distinct databases, they created a column called DB Name and made it part of the universe for reporting purposes. They then referred to it in the query as:
@prompt(‘Select Data:’,’K’,’DB VersionDB Name’,mono,constrained).dbo.tablename
If you use the wrong @prompt parameter, namely ‘A’, quotes will be returned surrounding the string and this will cause the syntax to be wrong and you may see a message like this:
There were some known issues with the @prompt in earlier releases of BI 4.0.
This is a known issue fixed in 4.0 SP4 Patch 6
The Semantic Layer Amazes
I’m sorry but I have to say it. The breath and depth of the semantic layer never ceases to amaze me. This is a perfect example. When Business Objects first come up with the idea of a semantic layer, they got it right… and most of the features like table mapping have been there for years!
They say that 80% of customers only use 20% of the functionality that exists in a product. I’m sure glad the other 80% is there when I need it… and all the pieces fall into place.