Combining data from several databases

Hello

I'm to familiar with Analysis Services. If my question is not related to this forum, please redirect me to somewhere else.

I have a number of databases of the same structure (for instance 5 databases). I would like to create some reports using data from all of them. For instance if there is a table called Customer I would like to get a report with a list of Customers with the addition of one column that contains the source database.

I'm also concerned about performance. I would prefer if the data of the original databases where copied somewhere else, since they are OLTP databases and it would be good not to be affected in terms of performance by the whole procedure.

Can you please give me some options on how to implement a solution?

Hello!

I would recommend you to build a data warehouse.

With SQL Server 2005 you have all tools you need for that.

SSIS(Integration Services) let you pump and transform data from the five source databases.

When you have a data warehouse finished you can build an Analysis Services(SSAS2005) cube on top of that or query the data warehouse tables directly with reporting services(SSRS2005).

All these poducts, the databas engine, SSIS2005, SSAS2005 and SSRS2005 are included in the same license.

HTH

Thomas Ivarsson