Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Design question, historic and views
Stephane
Have you tried this one? It averages about 5 messages a day. For help with list commands, send a message to <mailto:dwlist-request_at_datawarehousing.com> with the word "help" in the body of the message.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Tuesday, August 19, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L
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 :
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
stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>
-- 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-LReceived on Tue Aug 19 2003 - 11:49:24 CDT
(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 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: 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 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).
![]() |
![]() |