Data Warehouse

[Brian_274]

Hello,
I'm starting to do some research to set up a Data Warehouse environment. What techniques and advise can anyone give me? I'll be reading all the oracle documentation on it but you know how oracle is, just because it's documented one way doesn't mean it'll work that way. The OS will be 9.2.0.4 on HP-11i. EMC SAN Hardware. We have partitioning purchased. I'm basically trying to find out any problems I should be aware of that Oracle doesn't broadcast.

Thanks.

[doug mielke · Accepted solution]

Beware the undocumented COW.
(Can of Worms)

I'm also tasked with creating a data warehouse, and was immediatly amazed that I didn't have a conceptual understanding of what I wanted.

We've put a fair amount of work into this, and still have not completed determining our objectives.

Heres one Oracle Supported free and easy method you could consider.

Archive all oracle transactions older than X days, import them to a new instance.
Purge from production instance.

Easily done, Oracle supported, but, it's difficult to add to the 'archive' warehouse.
Oracle will happily $upply the $olution to thi$ i$$ue....
And the project grows from there.
Our stratagy has been to call in a string of 3rd party warehouse vendors, and their pitches help us formulate our requirements.

The saga continues.

[Steven E. Protter]

What you need to do is dependent on the requirements.

You need lots of space and enough time to copy the data tothe warehouse on whatever schedule is established.

If its a large amount of data, you may have to consider methods such as mirror splitting to get a copy of your data.

The configuration of the database itself is different, oltp versus warehouse. Most warehouses don't involve a lot of data writes, so RAID 5 storage is more practical.

SEP

[Brian_274]

Currently we populate a separate database each month with a snapshot of the db at the time of the process. Each month is put into a different partition. Then after 12 months that data is dropped completely. They have been using a database called RedBrick that keeps up to 5 years of data. I'd like to move this over to use Oracle only. So my plan is to have a data warehouse database that contains a snapshot of the vital data for each month. Then a snapshot for each year. I think the current process takes 3 hours to populate the data from the primary to the secondary.

[doug mielke]

This sounds straightforward. Here's and idea that might make this super easy.

Since the EMC array supports snapshots, you could stop the dbase, take a snapshot on the array, then purge the old transactions.
Mount each snapshot read only somewhere, and there would be the old data. The snapshot would happen almost instantly, and require less space over a year than 12 copies of the dbase.

[Brian_274]

Doug why would the EMC way use less space over a year? I didn't plan on making 12 copies of the database, just one, and each months worth of data would be in a different schema. Or in the same schema under a different partition. Thanks to everyone else who posted responses.

[Brian Crabtree]

Brian,

One problem that you will most likely run across is, global indexes (ones that cross all partitions) will have to be rebuilt when you drop a partition. Local indexing is better, but can cause a large number of problems if queries are not written to access across the partition (ie: All statements running on the table should have the partitioned column used for local indexes), however a local index will show a marked performance hit when used as a global index.

Brian