Hello. I have inherited a disastrous Warehousing project and need some quick fixes to at least get things tolerable.
I need to know before I try if there is a way to use tables from multiple databases residing on the same server in my universe. It has not popped out at me yet. Right now, the Fact and Dimension tables are in separate databases, but on the same server.
Currently they have views in the Fact Database that only has one table, the fact table. The name of that database is DW_E3JCFact. The views are basically a select * statement over the Dimension tables which are in a database named DW_SD (for shared dimension).
Obviously, the query speed is absolutely disastrous. I have two options. Either build an indexed view over the DW_SD tables or directly pull tables from both databases in the SQL Server. I prefer the latter.
BTW, we are using all ODBC Connections. I don’t know if there is a way to use OLEDB to further speed the thing up. I somehow feel oledb would be necessary to query separate databases.
Thanks as always.
jozeph78 (BOB member since 2003-10-22)
When using SQL Server, you can add tables from different databases on the same server. If you can access them from a single connection string, then you can do it. And SQL Server allows this. 8)
Your performance will be much better using OLEDB, and should not have any impact on your ability to use tables from seperate databases.
MichaelWelter 
(BOB member since 2002-08-08)
Do you mean a physical copy of the table? I’m no DBA by any means so if you could provide a little more of the “how to” it would be greatly appreciated.
jozeph78 (BOB member since 2003-10-22)
The connection definition connects you to the server. Then, when you open the table browser in Designer, you should see all the databases on that server, provided your account allows you to see them. You can open them up and add tables from different databases.
MichaelWelter (BOB member since 2002-08-08)
on the topic of OLE DB…
I’m working with existing production systems so i dont’ know how to set up an oledb connection with a universe, but I know how to use connection strings with VB Script, Perl and Java. I’m not sure how to set up an OLE DB connection but I’ll do some searches on the forums and else where for that. Got any pointers?
jozeph78 (BOB member since 2003-10-22)
Well my odbc connection changes default database to DW_JCFacts so I can only see the views and tables in that DB, not the entire server.
Wait…HAHAHA… Scroll down. I’ve answered my own question.
Perhaps I should be deleting this post but it’s funny to me that I didn’t even think to scroll down. I just assumes once I started to see system tables that they weren’t there. LOL.
Wow it’s amazing to have someone to work with (I have the most experience at my company with ETL/ Warehousing, and that would be 2 weeks worth 
).
Thanks Michael and THanks again BOB.
jozeph78 (BOB member since 2003-10-22)
I havent’ tested this yet either. I’m still waiting for my ETL process to load the data.
I was figuring it would simply use the fully qualified table name in the format
<db_name.owner_name.table_name>
I’ll post my findings.
jozeph78 (BOB member since 2003-10-22)