| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Datawarehousing help
Whether you start designing a full blown DW or start with
some DM's really depends on a number of things:
Kimball et all suggest you start with projects that can be completed in 90 days, and assemble your DW piece meal.
If you're starting from scratch, you may have to work backwards on it to give your users some experience ( as well as your self ).
I have had the good fortune of working on 2 DW's with very experienced folks, and learned a heck of a lot in the process.
The best pointers I can give at the moment are to buy Kimball's books, and find a good consultant that really knows how to design DW.
Jared
dmeng_at_focal.com
Sent by: root_at_fatcity.com
05/06/2002 02:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Datawarehousing help
Hi Jared -
I am a little confused about this -
1. "A DW may be a true 'warehouse' of enterprise data from which
DM may be built."
"This is a full blown DW architecture though, and you may only
wish to start with some DM to get your feet wet, or maybe that's
all that is actually needed."
So DM first or DW first? The first statement seems to suggest DW, but the
second seems to suggest DM.
2. "A DW may in fact very much resemble an OLTP database, with
a temporal component thrown in to track changes to data over time."
So DW is not star-schema but DM is? Can you elaborate a little on how DW
resemble an OLTP.
I am very interested in data warehousing and please let me know if you
have
any good pointers.
TIA
Dennis Meng
Database Administrator
Focal Communications Corp.
Jared.Still_at_r
adisys.com To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity. Subject: RE: Datawarehousing
help
com
05/06/02
01:23 PM
Please
respond to
ORACLE-L
A DW is not simply a collection of data marts.
A DW may be a true 'warehouse' of enterprise data from which DM may be built.
Extracts go to the DW, DW is used to build DM.
A DW may in fact very much resemble an OLTP database, with a temporal component thrown in to track changes to data over time.
Users are not (generally) allowed acces to the DW.
This is a full blown DW architecture though, and you may only wish to start with some DM to get your feet wet, or maybe that's all that is actually needed.
Jared
Rachel_Carmichael_at_Sonymusic.com
Sent by: root_at_fatcity.com
05/06/2002 06:53 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Datawarehousing help
Dennis,
Forgetting about normalization won't be a problem, I've always been more
practical than "by the book". As for amounts of data being collected, I
can see
them wanting data aggregated hourly.
I greatly doubt the tech people will allow adhoc queries, they seem to "do
things right" here. What will happen is that they will be contacted by
marketing
with an "I need this new report NOW" request, but tech will generate it.
But
*my* problem is that the data warehouse will supposedly be only a small
part of
what I'm responsible for, I don't think they understand the scope of what
they
are asking for, as yet. They will, I'll make sure of it.
Right now, as this is a new internal group, I'm still collecting
information on
which databases I will be responsible for. Then I just have to remember
that
when I set deadliines, I am prone to underestimation. :)
Rachel
|--------+----------------------->
| | |
| | |
| | DWILLIAMS_at_lif|
| | etouch.com |
| | |
| | 05/03/2002 |
| | 08:48 PM |
| | Please |
| | respond to |
| | ORACLE-L |
| | |
|--------+-----------------------> >----------------------------------------------------| | | | To: ORACLE-L_at_fatcity.com | | cc: (bcc: Rachel Carmichael) | | Subject: RE: Datawarehousing help | >----------------------------------------------------|
Rachel - I always find it helpful to understand something if I know the
origins. I worked with SAS several years ago. At that time it was a
statistical analysis package. A scientist or engineer could load a set of
test data into it and perform various arithmetic and statistical analyses.
Today most of that can be done with Oracle or MS Excel. My point is that I
would expect it to be heavily biased toward mathematical capabilities.
Like
Data Mining, which is all statistics. Learn what that term means.
To learn Data Warehousing, I would encourage you to just do
some
"Googling" and find good tutorials. An excellent newslist is dwlist.
Instructions:
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.
The magazine http://www.intelligententerprise.com/ has some excellent information. I would search for "Ralph Kimball". He is one of the leading figures in the DW arena. Look for some of his earliest columns on the magazine site. He also answers questions on dwlist from time to time.
The main change you need yourself is to forget normalization. DBAs that
can't get past that point don't last long in the DW field. In the early
days
the DW people would patiently explain the reasons to a DBA, but today
there
are enough DBAs that have made the leap that a hard-headed normalization
bigot just isn't tolerated. It is much easier to just ask for a
replacement
DBA.
The reason normalization isn't adhered to in DW is that users
will
be creating their own queries and they can't understand 10-table joins
with
outer joins, etc. A DW is usually loaded and then queried. Our DW is
loaded
each weekend and then queried all week. So a DW is deliberately
denormalized
and contains redundant data for ease of use.
OLTP databases have no concept of "time". A DW is all about
time. To
reconstruct what the situation is at various points of time, the DW has
loads of historical data. For example, marketing people need to be able to
reconstruct the amount of business they did with a customer over a period
of
time last year and compare it with the same period this year.
So between denormalization and tons of detailed historical
data, DWs
are normally BIG! Fortunately they are usually read-only.
For Oracle, you want Enterprise Edition with the partitioning option. And study Oracle Materialized Views.
In schema, a DW is usually a central fact table and 4-6
dimension
tables. Less than 4 dimensions and you don't need a DW. More than 6 and
marketing people can't understand the model. Normally the fact table is
much
larger than the others, but not always. One of Wal-Mart's dimension tables
is each person in the U.S. Just size each of those tables, and you've got
your size. Growth is easy to predict. Ralph Kimball warns that often
people
will get the grain wrong. They will size it for data summarized at the
weekly level, then after it is built they will realize that isn't going to
cut it and need a daily level. You must start almost from scratch and get
7
times the disk capacity. That is the fun side of being a DW DBA. Your
cynical instincts will still serve you well, just get them away from
normalization and worry about getting the grain right.
Okay, I've rambled along here too long. Hope that gets you off
on
the right foot.
-----Original Message-----
[mailto:Rachel_Carmichael_at_Sonymusic.com]
Sent: Friday, May 03, 2002 5:08 PM
To: Multiple recipients of list ORACLE-L
Okay, my background is OLTP, but we are looking at a data warehousing
project
here....
any and all help appreciated! Specifically:
help?
Rachel
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Rachel_Carmichael_at_Sonymusic.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: INET: Rachel_Carmichael_at_Sonymusic.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: INET: dmeng_at_focal.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).Received on Mon May 06 2002 - 17:30:54 CDT
 |
 |