We migrated from bo652 to xi 3.1 and were told we would have to copy all of our reports to different report names and copy all of our universes to different universe names so that we can run the reports against different oracle instances (sids). All of our databases have the same table structures so the univers structures are identical. However the data is different due to us having 4 different production databases, 4 different test databases and 15 development databases. This might seem confusing but this is how it is. We have a global user community and 1/3 of our community wants to run reports against their data and 2/3 of the community want to look at their data. Does anyone else have this type of environment and if so how do you run the same reports against different databases (connection). In bo652 we had different userid’s that were members of a group. The universes associated with this group were changed so that the connections pointed to the specific oracle sid (i.e. pman). Now that we are restricted by a named user license we cannot have a bunch of userid_sid users since we will run out of licenses. Does anyone have any suggestions or techniques they can share? :?
Thank you for the quick response and for naming the technique. I would like to add a slight twist to my explanation. Is there anyway to have the same user log into two different databases without having to change his name? I bring this up because of licensing concerns. We have 180 current users and have been able to segregate 1/3 of them to point to their respective databases using the override technigue. However, 1/3 of the people looking at sid A also want to look at sid B. Can this be accomplished with the same userid or do they need to log in with a different user id? I ideally would like to be able to add a parameter to our reports that would request the user to say which oracle sid they wanted to look at and run the report. Is this possible? This would cut down on license usage, since it is really the same user!!
I think that if a user will have only 1 userid then you will need to have 2 universes. Each pointing to a database that a user needs (A and B).
A possible work-around, although not nice and would probably not perform well, would be using 1 universe pointing to a database A and tables from the database B would be used in the universe using a database link. So for instance, a table ABCD will be represented in the universe by a derived table of this format:
select ...columns...
from ABCD
where ...possible conditions here...
and @prompt('select database (A or B)','A',{'A','B'},mono,constrained) = 'A'
UNION ALL
select ...columns...
from ABCD@DB_link_to_databaseB
where ...possible conditions here...
and @prompt('select database (A or B)','A',{'A','B'},mono,constrained) = 'B'
As you can see, either the table from the database A or the same table from the database B is used based on the user input in the prompt. The ABCD table from the database B is used using a database link (the syntax is for an Oracle database).
You would need to replace all your tables in the universe with such derived tables.
Performance of the reports, if the database A is used, would be OK. However, if the database B is used, then everything, all data, is pulled using the DB link. So the performance could be poor.
Phew, I can see what you mean. I am surprised that BO did not consider this when creating universes. I was hoping to be able to start designing universes or reports at least that would provide some ability to be more dynamic that what we have. Thank you for your solution. I need some time to ponder my options. Sincerely, Jerry
(BOB member since 2004-01-27)
(BOB member since 2003-11-27)
Thank you for the quick response and for naming the technique. I would like to add a slight twist to my explanation. Is there anyway to have the same user log into two different databases without having to change his name? I bring this up because of licensing concerns. We have 180 current users and have been able to segregate 1/3 of them to point to their respective databases using the override technigue. However, 1/3 of the people looking at sid A also want to look at sid B. Can this be accomplished with the same userid or do they need to log in with a different user id? I ideally would like to be able to add a parameter to our reports that would request the user to say which oracle sid they wanted to look at and run the report. Is this possible? This would cut down on license usage, since it is really the same user!!