Single View creation using tables from different data bases

Venu Gopal K shared this question 1 year ago
Answered

Hi,

I have a SQL Server database with three different databases say, DB1,DB2,DB3.

DB1 contains tables say: TB1,TB2

DB2 Contains tables say: TB3,TB4

DB3 Contains tables say: TB5,TB6

Now my requirement is, i requirement to create some set of report using TB1,TB2........TB6, hence i need a single view for all the above tables to build dimensional model.

Is there any way that i can bring different tables from different databases into a single view? ( i know virtual table will support partially but this is hard coded SQL script which i don't want)

Need you help on this.

Thanks,

Venu

Comments (5)

photo
1

Hi Venu,

This is possible, but is not recommended purely for performance reasons. If you are trying to join across databases, the reccomended route is to create seperate views and join these at the report level, where the scale of the join can be better controlled.

However if this is the only route then you will want to look into composite views:

http://wiki.yellowfin.com.au/display/USER71/View+Creation#ViewCreation-CompositeView

Though again, if you can work around using these, it is highly recommended. Chances are this will cause you more problems than your current work around. Let me know your thoughts.

Regards,

Nathan

photo
1

the link related with 7.1 Version, where in 7.3 there is no concept of Composite view?

photo
1

Here is the updated link:


http://wiki.yellowfin.com.au/display/USER73Plus/View+Creation#ViewCreation-CompositeView


Though I should re-iterate. We highly recommend using advanced sub-queries at the report level instead of this. Hindsight I probably should not have mentioned these in the first place ;)

Nathan

photo
1

Thanks Nathan.


-Venu

photo
1

No problem, let me know if you have any other questions.

Regards,

Nathan

photo