Oracle-L: RE: Design question, historic and views

From: Stephane Paquette <stephane.paquette_at_standardlife.ca>
Date: Tue, 19 Aug 2003 08:14:29 -0800
Message-ID: <F001.005CB8AD.20030819081429@fatcity.com>

It'a Bill Inmon type ;) (everything is normalized third normal form).

The DW will probably want a mix of last version/all versions. Reporting or auditing may need to view all versions.

What DW list, I used to be on 2 of them but not enough trafic.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187 stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-----Original Message-----

DENNIS WILLIAMS
Sent: Tuesday, August 19, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L

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.
dwilliams_at_lifetouch.com

-----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 :

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.
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.
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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Stephane Paquette
INET: stephane.paquette_at_standardlife.ca

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: ListGuru_at_fatcity.com (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: DWILLIAMS_at_LIFETOUCH.COM

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Stephane Paquette
INET: stephane.paquette_at_standardlife.ca

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