I've fallen into some database development responsibilities at my company. I am not exactly an expert on databse design and thought I would pose the following design scenario.

I work for a call center that handles hundreds of thousands of calls a month. I was asked to set up an MS SQL reporting database that would drive a web application and display call statistics (among other things) for a given month. Each call record is stored in a giant oracle database that I was given read access to.

I can think of the two possible options to pull infomation from the oracle database:

1) query the oracle database through an MS SQL DTS package for the various call statistics I want each month, and store the results of the query in a table on the MS SQL server.

2) Once a month, copy ALL the call data for the previous month from the Oracle database into the MS SQL databse and run queries on the data that was copied over.


I'm not sure with method would be best, or if there is a better way of doing this.

The advantage I can think of for copying a month's worth of data is that I can more quickly query my MS SQL database instead of having to go through an ODBC connection (which is what my access is limited to for the oracle database) for each query. The disadvantage is that I am copying data that already exists and using increasing amount of space per import.

I think the monthly approach is probably better.

You might also think about designing a data warehouse to facilitate analysis if your data volumes are that large.