Is it OK to allow users to query an OLTP SQL Server database with excel?

Question

I have a SQL Server 2005 database used by several applications.

Some users wish to query the database directly from excel. I can understand this, because it is a useful tool for ad hoc queries and then getting the data in a format that's easily transmitted and manipulated by other users.

My question is: Does Excel (say 2003/2007) do its querying in a way that won't cause concurrency issues?

Or is it done in such a way that a separate datawarehouse database needs to be made to handle this scenario?

Thanks for any advice.

Answer 1

That depends on what the user is doing from within Excel. In general, you should never do reporting against an OLTP database. Instead, create nightly copy and let them query to their hearts content.

Answer 2

If your users are IT savvy enough to generate reports with Excel, that's great. It'll save you a lot of work.

To avoid concurrency issues, we made views with nolock, like:

create view vw_excelreporting_Orders
as
select col1, col2, col3
from OrderTable with (nolock)

Then we made a user for Excel that only has select rights on this view. Make sure your DBA tracks long running queries and timeouts, and this can work very well.

Answer 3

Instal SSAS (SQL Server Analysis Service). Load the cubes there from the OLTP database during off hours when there is no busy time. Let people connect to the SSAS cubes. THey are made for that.

And upgrade to 2008 on the way ;) Lots of new functions there for SSAS.

Answer 4

Excel is a great source for power users.

I have not come across concurrency issues from Excel as much as Resource issues - typically analysis queries pull large chunks of data and summarize it. This causes a huge load on my production server since it is optimized for OLTP and small chunks of data rather than OLAP with huge data volumes.

Since T-SQL proficiency and query optimization may not be a power-users' top strength, I prefer to use SQL Replication out my production database to a reports-only database where they will get the same data at a lower speed, and will not slow down production db.

http://goo.gl/Vp9n

If you OLAP loads starts getting too high, then you may consider denormalizing OLTP structures into OLAP structures and load a star schema data warehouse using an ETL process.