5 Identical Databases into 1 Consolidation Database 1

[Readymixer]

I need to do some consolidation reporting. My company has an app that is split across five SQL Server 2000 databases; one per geographic division. The 5 databases are identical with respect to architecture; the only difference is the data they contain. How can I consolidate the 5 databases into 1 for company-wide reporting?

 

[MDXer]

Easiest way would be a data warehouse. If you build it in a Kimball type star schema you should get better enterprise reporting as well.

in the data warehouse you would extract the tables that make up the fact and dimensional data to a staging area. In the staging are you would cleanse the data and key it using surogate keys to your dimensions. The final step would be to populate the fact tables.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Krickles]

Do you need every single table or can you isolate the reporting information to X number of tables? If just some of the tables, then try a data mart instead of a data warehouse. For example, if the database contains 50 tables, but you really only use 20 for reporting, just consolidate the 20. The best way I can think to do this is by recreating the tables (empty) into a sixth database...you can call it [tt][ApplicationName]_reporting[/tt].

I would choose the table which has the majority of the information and add one column to it to identify which database had the original information. For example, if you had three tables (customer, order, product) with customer being the primary (since all orders should be tied to a customer), you could do the following:

Original customer table:
[tt]
Cust_id
Cust_name
Cust_number
Cust_address1[/tt]

Consolidated customer table:
[tt][blue]
Database_id[/blue]
Cust_id
Cust_name
Cust_number
Cust_address1[/tt]

The database should have ID keys for you to reference when linking the consolidated tables. To automate the consolidation, use MSSQL DTS. Start by writing the queries to collect the information, put the queries into DTS packages, then schedule the packages. One word of caution: if consolidating among multiple servers make sure they are linked servers.

Hope this helps.

Regards,


Krickles | 1.6180

 

[MDXer]

The typical problem of starting with a data mart is that once people realise how much easier it is to query from a central datasource they start demanding more. It's probably best that if a dm or dw approach was taken that the people who use the system be invovled in defining the scope of the project to ensure that what is delivered is will fit their needs.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Readymixer]

I am now starting to study and understand DTS. Once I understand more I will try the 'key tables' approach. That is a very interesting way to look at my challenge. Thanks Krickles!

MDXer, I like your thoughts on including key folks in the design of the DW.