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.
My Databases
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.
The Universe
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.
The Magic!
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:
@prompt(‘Select DB:’,’K’,{‘DB1′,’DB2’},mono,constrained)
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:
@prompt(‘Select DB:’,’K’,{‘DB1′,’DB2’},mono,constrained).dbo.Customers
Save the Data Foundation.
The Results
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.
http://trustedbi.com/images/blog/BO40/dynamic/ddc_replacementtable.png
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.
Extra Credit
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
Troubleshooting
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.
«Good BI»
Hi David,
this is a gr8 post and we’ve done something similar with derived tables in BO6.5.
In BI4.0 SP5 when i use prompt type “K’, i get invalid prompt definition error in IDT! any thoughts on this? Our data source in Oracle.
That’s strange. Did you update both the client AND the server to SP5? I would open a ticket with support and find out the minimum patch level for SP5.
Hi David
I posted the original “Ideas Place” suggestion, after trying to engage with SAP here in Australia for many, many months (actually turned in to years), to make sure the Business View functionality got in to the Universes. Eventually James Rapp helped me get it to Ideas Place (he’s in the US), with still nothing from my Australian colleagues. I’m a bit surprised James never got back to me on this though. In any case, your solution works for me using BI4 Service Pack 5 Fix Patch 4 against SQL2008 using OLEDB. Now all I have to do is get ODBC working in 32 bit for our legacy PICK database on Windows 2008 R2 and I think we may have a solution.
I am also mighty impressed by what the UDT and UNX’s will let us do.
Thanks so much for this post. I was stuck on the multi-connection DF and was not seeing the possibility with prompts, although I am a bit green with Universes – so that’s my excuse.
Regards
Glenn
Thanks Glenn for the feedback. I’m glad you found it helpful… I have to say it took me quite a while and multiple calls to product management to get it sorted out. Glad it’s working for you!
Hi David
Thanks. CR Enterprise returns “The LOV Value Column: Caption has a different value type than the parameter settings. This LOV is not supported.” Let me know if it’s a quirk of your solution or my ignorance with UNX’s? I suspect I have not set something up right.
Glenn
David,
Can the @prompt table name for the dynamic data connection be used in BusinessObjects XI 3.1? I have the table named as in your example (although it has quotes around it) and in info view it does ask me to enter the database name (database options are greyed out and start on the second line), but I get: A database error occured. The database error text is :[Microsoft][ODBC SQL Driver][SQL Server] Statements(s) could not be prepared..(WIS 10901). I also get the same results if I try to look at a column value in the universe designer on the business objects server. Does the ODBC’s for the databases have to be setup on the BOE server before this will work or is it even possible in our version of business objects?
Thanks for any advise you can provide.
Karen
Yes – as far as I know. I’ve done very similar things in XI 3.1 with dynamic table names, etc. The core things that have to be done is the same, but the screens look a little different.
The issue might be with the quote marks that Microsoft is requiring. Does your table contain a space or a special character? I would test it first with a very simple example and make sure you can get the basics working first.
Also, note that the BEST way to do this is to not prompt the user if you can. You can create an overloads for each user/group and hard-code a table name so no prompting is necessary.
My table is called Customer (same name in all databases). I’m stuck under your heading of: The Magic! You added the prompt and the necessary table qualifier: .dbo.customer – If I right click on my table and select the Rename Table option to type the @prompt and the qualifier into the Table name input box, when I try to type in the .dbo, my dot will not enter into the Table name. The only way I’ve been able to get the dots in-between the Owner and the qualifier is to enter both values into the Owner and qualifier text boxes on the Rename Table input box (and your screen shot says these need to blank for setup). And…in this case, in order to get it to display in the correct order (@prompt.dbo.tablename) I have to put the @prompt into the qualifier text box. Then the table name will display right but I still get the ODBC error message.
So, I’ve been trying different things but can’t seem to get past this issue. Unfortunately upgrading is not a possibility at this time. I do thank you for your time and quick response to my post.
David,
Thank u for giving a solution which makes things simple. The first magic! is the one which i have to use. Here comes the problem..when i run for multiple objects in the report level, it is prompting for each and every object. is there any solution to overcome this.
For example there are schemas for different country. so, when i selected the dbo.usa schema, the prompts are coming for each and every objects in that schema. Can you please help me on this.
David,
I am trying the syntax “@prompt(‘Select Data:’,’K’,’DB VersionDB Name’,mono,constrained).dbo.tablename”. I have a derived table that holds the list of values of database names. But I am unable it to reference in the @Prompt as you suggested.
I tried giving:
@prompt(‘Select Data:’,’K’,’DerivedTable.column_Name’,mono,constrained).dbo.tablename. But getting error. Can you please tell me where I am going wrong?
Hello,
Thanks for this article. I have one question. I migrate my universe from XI R2 to BI4.1 but the @prompt with the K doesn’t seem to work. I have still the quote surrounding the table name in the from part of the query. Have you have any idea what is the problem?
Thanks in advance.
Regards,
Benoit
This tutorial was wonderful! I used this with the @prompt in 4.0. We then migrated to 4.1 and that didn’t seem to work anymore. I changed it to use the table mapping technique and it worked like a charm (I think it’s also easier to manage this way).
Thanks!!
Is there a way to do this though the SDK avoiding the security groups ? I have a situation where there would be MANY different DBs which are created through a self service portal – it would be easier to change the DB on the fly –
Is this possible ?
HI David,
It is helpful document.
I have similar type of implementation, I am little confused to configure the this steps.
Could you please suggest below scenario, how to resolve.
I have 6 Databases,maintained in Single server, Each database needs to dynamically connect and replicate data to report level. All DB schema tables are same , Only DB names are changed. For example ( Austell/Batavia/Boloingbrook/Cranbury/Milpitas/Rancho).
We have idetified 2 types of users
1) Report access users- these are responsible for any one of the DB( Ex Austell) We have created common connection and Individual DB connection pointing into only ( Austell-DB) We are replacing common connection by Replacement option at security profile
2) Business Group : These users are required to check the reports against of each report users. For these users I need to prompt the DB connection Dynamically.
Could you please suggest what is best way to achieve this solution
Regards
Prakash V
I know this post is older, but i am still hoping for assistance. We have physically partitioned tables in SQL (aka SalesHistory – Sales from 12/31/2015 and prior, and SalesCurrent – Sales from 1/1/2016 to today) with exactly the same fields.
I would like to use a Date Prompt in Universe and translate that input into the table name – thereby dynamically changing the table name in the query.
For instance If user inputs 12/15/2015 then the table name = ‘SalesHistory’ and if the user inputs 1/15/2016 then ‘SalesCurrent’.
We need this universe table switch to be transparent to the user current reporting mechanism (webi).
Hi David,
The best thing I can think of is instead of adding @prompt(‘Select DB:’,’K’,{‘DB1′,’DB2’},mono,constrained) before each table name, add it to the Table/View Qualifier and keep Owner as it is.
This way table name in the universe structure will be untouched and carry same name as defined in DB and there will not be an error of Incorrect Syntax
Regards,
Vinesh
Thanks David.
We have done all the steps however our requirement includes rectricting the data connection to specific group.
i.e. group 1 should only see DB1 and group 2 ‘DB2’.
Is it possible to achive?