Re: Creating an Oracle Reporting Server

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:bcp0jm$kse$1_at_ctb-nnrp2.saix.net...
> Joseph C. Henning wrote:
>
> > I have a question on the number of ways you can replicate a production
> > database to a reporting server (data warehouse). Can this be done by
> > hot backups? Can this be done on a transaction basis? Just looking
> > for different ideas on how to do this task. Any help would be great.
>
> One of the options not yet mentioned is replication.
>
> But one thing to keep in mind though is that OLTP designs by nature do not
> lend themselves well to Data Warehouse style queries and reporting. Thus
by
> simply taking a physical snapshot of OLTP data may not meet your Warehouse
> performance or reporting requirements.
>
> And I would guess that these (performance and reporting requirements) are
> the prime criteria for wanting to move your OLTP data to a reporting
> platform for reporting purposes.
>
> If further manipulation is needed (e.g. creating stats/reporting/summary
> tables), then it may be easier to do that rather via a dblink. I.e. using
> the driving_site hint, run the aggregation query via a dblink from the
> reporting server against the production server. Storing the results in a
> stats table on the reporting server.
>
> There are various ways to skin this cat. The trick is of course to do it
> with getting as little blood as possible on your hands.. ;-)
>

My 2c: Billy's approach makes sense to me. Here is a (slight) variation:

Of course every situation is different, and as ever it depends, but my gut feeling is that (assuming the OLTP Prod site is a remote one), many times a good option is:

1. Simple one-to-one read-only fast replication from OLTP to the data mart. then
2. Stored procedures in the data mart to build/update the denormalised reporting schema.

Regards,
Paul Received on Wed Jun 18 2003 - 14:23:12 CDT