Datawarehouse Problem

[klewis10367]

I have an ERP Product (Microsoft Great Plains) running on SQL 2000. I have 4 database's that reflect different entities within our company. The schema for these databases is exactly the same. We want to consolidate the data for these databses into one reporting (real time)database. At this point what we are doing is creating Union views for all of the tables we need to report off of and adding a DBase field to the view to decipher the companies. The problem is that the views are very slow and sometimes timeout. We can not use the SQl replication because it needs to add a field to the source tables, which is not supported. Does anyone know of a software or have an Idea that would allow me to create a new database out of the 4 databases and within each table created add the DBase field. I am using triggers and a nightly dts package to accomplish this right now, but this is not exactly real time.

 

[johnherman]

First, I would contact your Microsoft representative. I would explain your circumstances and ask if they would consider providing the ability for Great Plains to use replication. This might take up to two releases to happen even if they like your idea, but they might be able to give you some sort of workaround.

There are other data replication/ETL products which can consolidate data, but that's what you're asking. Data Mirror might work. You could also consider hardware mirroring with OLTP against one set and analysis against another (keeping the view idea).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[klewis10367]

Thanks for your input. I think i will give Microsoft a call.