Re: Datawarehouse suggestions?

Rachel,

Check out Kimballs columns. Here's on clickstreams in the data warehouse.

http://www.intelligententerprise.com/000120/webhouse.shtml

Take a look at Richard Kimballs 'The Data Webhouse Toolkit'. http://www.amazon.com/exec/obidos/ASIN/0471376809/qid=999449702/sr=8-1/ref=aps_sr_b_1_1/104-3445454-2950323

Jared

On Saturday 01 September 2001 21:25, Rachel Carmichael wrote:
> Okay, first the rant.... they want me to build a new database to hold logs
> of every time someone hits a page on our website or uses one of the
> functions on the site. Without a new server or disk, on a box that
> currently holds the OLTP database, on a disk array that is RAID 5 and that
> EMC wants to take BACK disks from. In other words, they want magic. Where
> did I leave that rabbit and hat?
>
> end rant, here's the setup:
>
> Conservative estimate says that one of the two main tables (at least for
> now) will grow to a GB in a year or two, the other will hit 500M in that
> timeframe. Rough estimates of # of rows is 273M for the first table, 70M
> for the second over 2 years. Rows are < 200 bytes at max. There will be
> several small lookup tables and the number of logging tables will increase
> over time.
>
> They are NOT talking about purging data, ever. And we are hoping that the
> volume INCREASES, not decreases or remains the same, which means the space
> estimates are no more realistic than throwing a dart at a board and picking
> a number.
>
> Oh yeah, did I mention that they want this to be a reporting database for
> external customers (read revenue stream) and want these external people to
> be able to query on any combination of columns -- so they want indexes on
> EVERY column.
>
> I will have a nightly maintenance window for loads of prior day's data. I'm
> thinking to do a shutdown, change the init.ora to tune for heavy batch,
> startup, drop indexes, load data, recreate indexes, shutdown, backup,
> startup with init.ora tuned for querying.
>
>
> question:
>
> I've always worked on either batch reporting databases or OLTP, this will
> be the first semi, sort of data warehouse for me. Advice please on how to
> build this, as I am doing this one from scratch and can plan it.
>
> I'm thinking:
>
> database will be 8.1.7, Solaris 2.7
>
> rbs and temp tablespaces as LMTs, remaining tablespaces dictionary-managed
>
> db_block_size 16K
>
> a "large table" tablespace with initial/next at 10M
> a "large index" tablespace as above
>
> a "small table" tablespace with initial/next at 16K
> a "small index" tablespace as above
>
> possibly partitioning (I have to check our licenses) on the timestamp
> field.
>
> Any suggestions, gotchas, "this is WRONG, here's how to do it" comments?
>
> Thanks
>
> Rachel
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).