Re: Data Warehouses

Thank you both for your reply. I am glad to hear that this is feasible. I am currently in the process of reading the "Data Warehouse Toolkit" book to get a handle on the proper schema for this data. I've done quite a bit of research so far, and I'll definitely check out the oracle guide for data warehousing.

One thing that makes the data I'm working with kind of unique is that not only does each row have a unique identifier, there are a variable number of numeric items after the unique identifier per row. The numeric items can be numbered 1 through 999. So I'm thus far planning to put the numeric items and their numbered offset in the fact table with an fk relationship to the unique row identifier in a dimension table. There are other dimension tables, but those are trivial. The problem happens when each of these unique identifiers themselves can become large in volume. Like I said, there will be about 1 million of these unique identifier, and then a variable number of numeric items tied to these unique identifiers. Reports on the data will typically use some info from the other trivial dimensions tables, then variations on the unique identifier, returning aggregations of the numeric items per row.

I'm hoping I come to a similar type of data in the above referenced book, but have not so far. Have you seen something similar to this before?

Joe

"Karsten Schmidt" <groups_at_karsten-schmidt.com> wrote in message news:c6711ac4.0303200012.2da4f915_at_posting.google.com...
> "Joseph Wilson" <me_at_home.now> wrote in message
news:<LU9ea.1636$2x2.662043_at_dca1-nnrp1.news.algx.net>...
> > Does anyone have experience with large volume data warehouse? What kind
of
> > volumes have you seen that are feasible. The company I work for is
looking
> > to start a data warehouse project with data that will be coming off of
the
> > mainframe at about 1 million records a day at about 4k to 9k bytes per
> > record(4 to 9 GB per day). They want to store this in oracle for up to a
> > year to be able to trend and aggregate the data. Has anyone seen a data
> > warehouse implementation this size? Any tips or recommendations?
> >
> > Thanks,
> > Joe
>
> Hi Joe,
> this is certainly possible - I am working on a database that loads
> about 20 million records per day, much smaller though - just a couple
> of hundred bytes each. The hardware is moderate - 4 CPU HPUX box,
> actually shared by a few more Oracle instances.
>
> The thing I found is that pretty much all the report generators I've
> seen fail miserably when confronted with a billion- row table. We
> resorted to hand-written - and tuned sql.
>
> And you are most certainly looking into partitioning, and sql loader
> with direct path option. Deleting millions of rows is going to kill
> you. Drop partition is way easier.
>
> Regards Karsten
Received on Thu Mar 20 2003 - 18:28:02 CST