Not sure if this is the right forum but you will let me know .
We have a datawarehouse db with data for subsidiary A and B. We have a lot of reports based on that. Now there is a new subsidiary C. For some reason ( ) a new datawarehouse db is created for this; the database is exactly the same, and a copy of the universe could be easily made (thanks to the use of aliases for every table ).
Because of the effort put in to the reports we would like to be able to switch universes on the fly. I know the user can switch the universe manually, but could this be done based on a prompt? Or perhaps a piece of VBA which kicks off before the refresh, asks a question and swithes universes?
Hmmmm, switch univers on the fly… I don’t know if it is possible but maybe you could switch the connexion of the univers using SQL because of univers and connection values are stored on the repository as ID. So, you can change this using update commande passed via a procStoc for example…
You can override connections via Supervisor, or get creative with dynamic Table names using the @Prompt (which requires that all the tables are in the same database/instance etc.).
Or maybe you could create views that UNION (or even better UNION ALL) Table1 from database A to Table1 from database B (depends on your DBMS etc.) and use the views in your original universe.
The best bet would be to convince your DW team that there should only be one data warehouse for the different subsidiaries…
{Cringes}… As always be real careful and consider every alternative before you consider manipulating the Business Objects repository directly (which is unsupported).
The key question is will any user require access to universe A, B, and C. If not, then you might want to read up on Andreas’ suggestion about switching the connection in Supervisor. It’s the perfect solution for this problem.
If your users need access to one or more of the same universes, then you might want to take a look at the Designer tip in this tips and tricks presentation I did for the NTBOUG meeting a while back. It shows how to use linked universes and alternate connections to create duplicate copies of a universe that anyone can use. Cool stuff, if I do say so myself. 8)
Maybe my situation needs some more clarification. I have 40+ report which use universe DWH. Universe DWH has a DWH connection to the Datawarehouse. Now a second Datawarehouse will be built which will be an exact copy, but with different data.
I would like to be able to re-use my report but the user must be able to specify which universe (and thus which db) to use.
Dave, I’m still very much in the dark about what linked universes could do in this situation (I read your presentation, but would have liked to have been present ). What do you mean by alternate connections? Could you sketch a setup that I could use?
Another option is to check out a product from a company called Centerboard. They have a tool which would be perfect for you. It brings togehter all these sources and lets you use 1 universe in exactly the way you want.
You’ve already received lots of good feedback on alternatives. As a “last resort” it would be also possible to use VBA to accomplish this as well. It means writing your own custom screen to prompt the user, but there is a .ChangeUniverse method that would accomplish what you are after.
I’ve tried to get it to work with linked universes. I created a new universe and linked the two (exactly the same) universes to it. But because classnames are the same the classnames of the second universe get a “2” added to the name. What do I do wrong?
I’m also trying to get it to work with VBA. With the .ChangeUniverse I can change the universe name. But I want this change done based on a prompt called “specify universe”. Can I create a BeforeRefresh and get the value of the prompt in VBA and then change the connection?
When this works my idea is to put it in a .REA and let everyone use that.
I think Dave’s hint was to help you avoid having to maintain two universes. If they are exact copies, with only the connection being different, his trick works well. Build universe1 with connection1. Now create a universe2 with connection2, and ONLY universe1 included via link (no other objects). Now you have two universes with the only difference being the connection (similar to what you have now), but your maintenance is easier.
While I haven’t done it myself, I don’t see why that wouldn’t work. You’ll need a VBA form to pop up, maybe with simple radio buttons to choose the right universe.
.
) a new datawarehouse db is created for this; the database is exactly the same, and a copy of the universe could be easily made (thanks to the use of aliases for every table 
).
(BOB member since 2004-03-02)
(BOB member since 2003-05-26)
(BOB member since 2002-06-20)
(BOB member since 2002-06-06)
). What do you mean by alternate connections? Could you sketch a setup that I could use?
,