Re: Data Warehouse updates on Informix

Ines, I will assume you have Informix Metacube (since you didn't post that information), usually you need to update the fact tables, because they contain transactional information. If you have aggregate tables, you should consider to implement scripts that will perform incremental aggregation (Metacube doest it automatically)., You will have on the other hand the base tables, which will make up your dimension tables. I think here is the main problem , because sometimes DW Designers, do not take the time to write the proper scripts in order to have them load only the last changes, and rebuild the dimension tables based on the new information. (Example). These are three base tables. [Customer] [Country] [Region] Based on these tables, we build [CUSTOMER_DIM] ,which is a dimension containing informatoin about (CustomerCode,CountryCode,RegionCode). Usually whenever a customer information has changed, say for example, he./she moves to another country, the script will have to perform the following operations: 1)Assign the Customer the new Country 2)Update the information in the dimension table 2.1 update the row 2.2 reconstruct the information (Usually the Aggregation Levels). Write an application that connects to both databases (The Repository and the DW), and let this program check for changes then insert /update the information in the DW. In the DW side you can have Triggers/Stored Procedures to achieve the explanation shown above for base tables/Dimensions, Aggregates (For this, Informix Metacube has the Incremental Aggregation Feature, so you do not have to worry about it). Regards, PS: We have implemented five DW solutions using Informix Metacube, if you have any questions, do not hesitate to contact me. Mario Estrada --------------------------------------------Reply Separator--------------------------- ----- Original Message ----- From: In's Franco <Ines.Franco@ua.es> To: <informix-list@iiug.org> Sent: Martes 9 de Marzo de 1999 10:49 AM Subject: Data Warehouse updates on Informix > We would like to know if there is a better method to update the >tables, etc. than to load all the relational database into the >datawarehouse frequently (we do once a week) . We would know if there is > >some way to load only the last changes in the relational database. > > > >