cancel
Showing results for 
Search instead for 
Did you mean: 

Data Warehouse

SOLVED
Go to solution
Brian_274
Frequent Advisor

Data Warehouse

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.
6 REPLIES
doug mielke
Respected Contributor
Solution

Re: Data Warehouse

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
Exalted Contributor

Re: Data Warehouse

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Brian_274
Frequent Advisor

Re: Data Warehouse

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
Respected Contributor

Re: Data Warehouse

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
Frequent Advisor

Re: Data Warehouse

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
Honored Contributor

Re: Data Warehouse

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