Re: How to treat several identical database as one.

David Williams <djw@smooth1.demon.co.uk> wrote: : Our Informix-4GL based system will soon be used at several sites in :the same organisation i.e. several machines each with a copy of the :application and a database. We want to run a PC-based reporting tool :that can report across all the databases. : Our first idea was to create a new database with views of the form : create view job as : select * from server1@machine1:job : union : select * from server2@machine2:job : union : select * from server3@machine3:job : i.e. create a 'pseudo-table' which appears as all the data from all : the job tables on the three servers. : Unfortunately views do not support unions. Any ideas???? : We don't want to replicate the data just make all the similar tables : appear as one table so the PC-base reporting tool (GQL) can be used : by 'normal' users? i.e. non-techines. As I see it you have only one real solution to this problem - set up another database as a datawarehouse. Even if you could have used the union above (possibly directly supported by your reportwriter) any where clauses (that your users are more than likely to need) would become rather complex. Sometimes they would probably exclude all data from one database, and that table should not be part of the union at all. We have also found that only the most expert users can even begin to do their own reporting against an even moderatly complex OLTP database. If you set up a datawarehouse you can usually simplify the database to such an extent that it becomes easy for the users to do their own reporting. The best solution is probably a start schema based database as explained by Ralph Kimball in his latest book. The datawarehouse may incompass more data than that, but what the users see might be only the start schema. Nils.Myklebust@ccmail.telemax.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company