Re: How to treat several identical database as one.

In article <50olu1$h18@nic.global-one.no>, Nils Myklebust <Nils.Myklebus t@ccmail.telemax.no> writes >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. > These are planned to be management reports giving somekind of summary info for all three sites, so sites are never excluded. >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 It's up to the user but we may well write most reports for them. >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 > The only problem is that we don't want any more hardware involved. A datawharehouse would need more disks/ CPU power and a replication agent running to keep data in sync. A datawarehouse would be nice but the users have no budget for one. Any other ideas??? -- David Williams