Large Volume Excel Data Pulls - Avoiding ODBC

We have a requirement to provide ad-hoc access to large subsets of a system's data to users to analyse in Excel. We do not want to grant direct ODBC access. This will curb o开发者_JAVA百科ur ability to make DB layout changes without our users' processes breaking.

Web Services seem ill suited for the volume of data at stake, in the region of 100's of thousands of records.

What would you suggest as an alternative to direct ODBC access?

There is a database concept of a "view" which does exactly what you need - it allows to expose large set of data and gives you a freedom of DB schema changes as long as you take care of exposing the same data to a user.

I agree with you regarding web services - it is not only the volume of data, but also the fact getting web services to work with Excel (2007 and above) is far from trivial. Also you will lock your DB schema as much as you would with a view.

For the really, really huge number of records you can consider data warehousing - a separate db, where you provide a read only access for reporting purposes and feeding the data from your read/write database. The feed can be easily and quickly done via SSIS.

HTH