Re: Creating an Oracle Reporting Server

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.. ;-)

--
Billy