CrossDB Model

Hi,

I'm used to AS while designing and using OLAP-Cubes.

Now I have to build and publish several models directly on relational base. So far, no problem with the wizards, every DB got a model with attribs and roles. But the information i wanna analyze, is splitted across multiple databases, where there is no foreign key relationship designed in DB. I have to link the tables of different DBs together by hand and design (or let design :-)) a model across more than one database for analysations.

So how would you build up a single model for several databases?

Hope my problem is understandable.

Thanks in advance.

Thorsten

Hm, maybe with using views which joins the multiple tables as one fact table, and using natural keys where there are no foreign key relationships designed?
|||

Call me noob,

but is there a way to define cross-db-views?

|||noob ( ;-) )

Well, I am kinda a noob too, but can't it be done by something like this?:
SELECT * FROM
database1.dbo.table1 JOIN database2.dbo.table2 ON database1.dbo.table1.id = database2.dbo.table2.id

I don't know it exactly, but it might be worth trying :-)
(In this case, I assume you're using just one database system: SQL Server)
|||

For sure, you can join across DBs the way you wrote. But you can't save these queries as views, or?

Because views are DB related.

|||Just tried it (in a view) and it works just fine :-)