Use Multiple Connections in a Single Universe

I’m not sure why, but this is an old trick that I’ve used that has come up twice in recent weeks for different deployments I work on. Everybody should know by now that a universe can only point to a single connection for any single query without federation in place. However, a fairly simple override can allow differing users to utilize a different connection each when they run a query.

Let’s take a scenario where you are using one SAP BusinessObjects instance. Within that reporting architecture, you have a base of users that have data on regional databases dbnorth, dbsouth, dbeast, and dbwest. When a user logs in and runs a report against the universe, they have to run against their assign database connection. You wouldn’t clone the universe four times….would you? (The answer is no)

In this example, we’ll use eFashion and see if we can’t make this happen. First, start by establishing your groups. I created a set of groups for each of my regional databases.

With the groups and users assigned, use Designer to create a connection for each database you need to query against. Ensure that the groups have adequate privileges to their respective connections.

Next we are ready to start working with Access Restrictions in Designer to set up the overrides so that when a user in each group logs in, the connection is overridden. Start by hitting the Manage Access Restrictions menu selection.

Begin by adding a new access restriction. Name it so that it is consistent and can identify which connection override it enforces. Set it so that it selects the appropriate regional connection for each group.

With a regional override connection, all that remains is to enforce the access restriction on each group. The final result is a set of groups that automagically toggle between their respective connections when logged in and running a query.

So no, this is not a solution to run against more than one database concurrently. Look into federation for more integrated reporting. However, you should now be able to get users into the right data pretty easily and securely.

Editorial note: Be sure to use care when migrating this with the Import Wizard through dev, test, and prod. I’ve seen the Import Wizard not like the initial move. I’ve not tested with LifeCycle Manager, but one can hope it handles the dependencies better.