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

Combine aspnetdb with the applications database

I have tried to find all the threads related to this topic. At this point I would like to find out what the best practices are for doing this.

I plan to use Membership, Roles, and the login controls to handle my authentication and permissions issues. I want to use the same database for my application data as for Application Services, using a standard SQL Server.

So far, the easiest way to set this up that I can think of is to:

1. Use aspnet_regsql.exe to create the Application Services Database schema

2. Edit the "LocalSqlServer" entry in <connectionStrings> to point to the above database

3. Add the tables, views, stored procedures, etc., that I need for my application into the database created in step 1

Is there any more configuration I need to do to get Membership and Roles working? When I did the Walkthrough, step 2 seemed to be the only requirement for getting Membership and Roles to work.

Will I run into any trouble using LocalSqlServer for my application data updates?

I would be interested in any other approaches to setting up a combined database, including opinions why and why not to do it.

There should be no other problem if you've configrue the application to connect to the right database using login which has proper permissions on that database. You cna take a look at Scott's article if you haven't read it yet:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Column Conflict error

Hi;

I am getting this error while I am trying to delete a record from "MediaTypes" table
Media Types table and DVBTestCOutputs table have related through MediaID (cascade on update only not delete)

MediaTypes DVBTestCOutputs
---- -------
MediaID int ....................
MType char(10) ....................
MediaType int

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DVBTestCOutputs_MediaTypes1'. The conflict occurred in database 'Test', table 'DVBTestCOutputs', column 'MediaType'.

can you help me please I do not want the related record deleted also from DVBTestCOutputs table so I didn't choose the cascade on delete checkbox is this the problem??If there is a PK-FK constraint, then you are not allowed to have child records that have no parent record. You will need to update the value of the ParentID in the child table to another ParentID, before deleting the parent record.

Otherwise, you need to remove your constraint.

Cheers
Ken