Stephane What type of DW is this? Is a simple transaction system or is it more complex? What I'm asking is whether the individual changes are important or if only the last value is what you want stored. Actually, there is a good, active data warehousing list that would probably give you better answers.
Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Tuesday, August 19, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Hi, In an ODS, it is feed in near real time during the day and pushes data to the DW and other systems at night. So it may need to keep several copies of the same data (several changes or the DW can be offline so the the ODS may need to keep several days). All tables have a surrogated PK and the PK from the source systems are used as alternate non-unique keys. All tables will have a DW_transfer indicator (yes/no) There are 2 designs I can immediately think of : 1. I keep all the data in the same table and I use fields like last_version_indicator (yes/no), start date, end date. This is fast since it implies inserting the new record and updating the previous record. 2. The last version for each rows are kept in a table and the historic are kept in a second table.Each table has its historic table. This implies inserting in the core table, insert the previous record in the historic table and deleting it. 3. another way ? The ODS must answer the following: All changes need to be transferred to the DW not only the last version. End-users will query the ODS (I do not know yet if queries will access only the last version or not). Views. In the case the solution1 is used. Would you used views to do the following table_t : the table table_last_version_v : view showing only the last version (with where last_version_indicator='YES'), used for queries. In both cases, would you used a view table_not_transferred_v : view showing records with the DW_transfer_indicator set to no, that view would be updated by the ETL processes or let the the programs do the where DW_transfer_indicator=no. Thanks Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
