Hello Everyone,
I am sorry if this is posted anywhere else. I did look for it but didn’t find anything.
I need to find the universes that have a table (say Location) from all the gozillion universes we have. Is there a better way that i can find the universes that have Location table other than manually checking each and every universe.
Appreciate your help.
Thanks,
Zen.
zenmasterbo (BOB member since 2006-02-15)
Hi,
An utility is available for documenting a universe.
BOB Downloads, try to search here many utililities are available here.
Ther is one utility called Universe Documenter available at this Universe documentar
Just read the information and try to implement it.
Omkar Paranjpe 
(BOB member since 2006-02-13)
Hi Zen,
Run the following SQL against your BO repository tables:
SELECT uni.uni_longname AS universe_name,
uni.uni_filename AS universe_file_name,
tab.tab_name AS table_name
FROM unv_table tab,
unv_universe uni
WHERE tab.universe_id = uni.universe_id
AND tab.tab_name LIKE UPPER ('%date%')And don’t forget to change the condition for the table name.
Marek Chladny 
(BOB member since 2003-11-27)
Thanks Marek. I will try this and if it works i need to get you a beer. Yeahman!
zenmasterbo (BOB member since 2006-02-15)
Marek,
I work in XI R2 SP1 version and i was not able to find unv_table in the repository. Any advice???
our database: oracle 9.2
Thanks again,
Zen.
zenmasterbo (BOB member since 2006-02-15)
Hmm, you should have mentioned your BO version before. The BO repository in BO XI is completely different from the previous BO versions.
The solutions I see is either the using of Universe Documenter mentioned by omkar_paranjpe in his post above or using this utility that documents the universe too.
Marek Chladny (BOB member since 2003-11-27)
Hi Marek/Omkar,
Thanks for all your help so far. Universe documentor is good for documenting universes (another thing i got from this board today). My question is how can this help me to solve my issue of finding all the universes that has a table called “Location”? If i have to document all the universes then this is definitely not the solution i am looking for. It would be same as opening each and every universe and look for that table in it.
Please help.
Thanks very much,
Zen.
zenmasterbo (BOB member since 2006-02-15)
Quite true.
What I can suggest is to write some macro that would automate this. Although I dont know very well the Designer SDK I am sure that a macro could be written that opens in a loop each universe stored in a folder and writes a log with the names of the used tables or the table you need to find.
Have a look at the VBA code of the utility I sent you the link and if you know VBA you should be able to utilize it for a macro I’ve just described. Sorry, but I cant you offer a better help.
Marek Chladny (BOB member since 2003-11-27)
Hi,
When the universe documentar stores the values in EXCEL, just use the loader to put it into the DB table.
Say if you have 10 universes, then there will be 10 tables. So write a stored procedure to get the tables from all teh tables and build a new table consisting of every table in how many universes.
Look this is just a thought, may be very hard to implement if you have too many universes say 50+ in case when it will be very hard to maintain 50 tables for each universe+1 final table.
Another thought is that you can write a stored procedure to read the excel files that this Add in will create.
These ideas may sound weird may not be achieveble, but the fact is that now a days i am sitting at client’s place so i am also getting a habit of giving such ideas like them 
Omkar Paranjpe (BOB member since 2006-02-13)
Hi Omkar,
Thanks for your ideas though I was not able to understand them properly. They are way over my head and unless you can explain to me in more depth i will not be able to implement them. But i will defnitely try. I would like to start with your first idea. What is loader anyway. Thanks dude.
Zen.
zenmasterbo (BOB member since 2006-02-15)
Hi,
That is why i said I have started passing comments liek teh clients :D,
I said that assume that you have 10 uiverses
YOu will have to pass the table name as the input parameter for this procedure and the output parameter will be either the message or the stores values in the table.
So if you adapt this approach then you will have to build the table such as total number of universes present in your BI system + 1 finaltableto store the values.
Hope this helps you, do revert back incase if you need any more help.
Omkar Paranjpe (BOB member since 2006-02-13)
Zen, omkar_paranjpe,
Why all this hassle with excel sheets, loaders, stored procedures… 
If one knows Designer SDK (and it can be learnt a little from the VBA code of the 2 utilities mentioned above) then one can write a macro that would examine all universes and would log all necessary information (at least the universe name and the names of the tables used in the universe) directly into the DB table. Then only a simple SQL is necessary to get the list of all universes that contain a particular table.
Marek Chladny (BOB member since 2003-11-27)
Hi Marek,
If it is working then all cheers
I had written some VBA codes (although very few codes and not the complex one) and never tried for the SDK 
. So gave him a lengthy process.
If it works fine then all cheers again.
Can you also post some SDK code if possible?
Omkar Paranjpe (BOB member since 2006-02-13)
Hi Marek / Omkar,
I am working on tweaking the macro that is currently able to read one universe and list all the tables to scan through all the universes and pick the table i want. This sounds simple procedure and decided to go with this for now. But i am a beginner in writing VB code I wull let you guys know if i get any success. Meanwhile thanks again for helping me so much.
Regards,
Zen.
zenmasterbo (BOB member since 2006-02-15)
Good luck Zen.
I learnt VBA this way too. I took the codes, VBA samples… and tried to change/tweak them in a way I needed. So it is definitely a good way to go to learn VBA 
Marek Chladny (BOB member since 2003-11-27)
Finally got some success. Need to do more testing. Tweaked the Universe Documentor macro (by Dwayne). This new macro scans all the universes saved in a folder on your computer and brings all the tables (seperated by universe name) in one excel sheet. From this excel sheet I was able to find which universe is using particular table. It is good to have a VB expert at your work place. Thanks a lot guys for your support.
Zen.
zenmasterbo (BOB member since 2006-02-15)
If you submit your macro to Bob’s Uploads, along with documentation in the Announcement: Please read before uploading any submissions – this would be great for others to share.
A moderator will have to review your submission, and then move it to BOB’s Downloads.
Anita Craig (BOB member since 2002-06-17)
Anita,
I sure will after i finish my thorough testing. Thanks to Marek and Omkar dudes
Regards,
Zen.
zenmasterbo (BOB member since 2006-02-15)
Hi Zen,
Did you ever post this up to Bobs Download? I am looking for a solution for this exact scenario but the Universe Documentor I have does not display this tab.
Thanks for your help.
di652 (BOB member since 2005-02-24)
So does anyone know the whereabouts of this new Universe Documentor that lists all the tables within the repository.
Looking to get this information from our 4.0 repository. I know how to look for it in earlier version of BO but not this one…anyone.
Help Please
di652 (BOB member since 2005-02-24)