End User Ad-Hoc Reporting Tool: Microsoft SQL Server Management Studio or Microsoft Access?

Question

Our centralized IT department has suggested two primary ad hoc query tools for our general user base of approximately 200 staff members:

1. Microsoft SQL Server Management Studio 2008 (SSMS)

2. Microsoft Access 2003

Environment

• The backend database is a read-only Microsoft SQL Server 2005 database.

• The schema is 400+ tables; allowing access to the raw data for our general staff would be a disaster.

• We will be building an "abstraction layer" over the raw data for our general staff to run ad hoc queries against.

• The abstraction layer will most likely contain a number of views.

• A number of users have basic knowledge in Microsoft Access; none have used SSMS.

Which of the above tools (or alternative) would be best for a decidedly non-techie user base of approximately 200 people? What are the pros and cons of each?

Also, the IT department has suggested teaching people T-SQL so they may use SSMS. Is this reasonable?

Answer 1

Attempting to teach "non-techie" people T-SQL to query a schema with 400+ tables probably isn't going to do well, unless they are limited to querying the views only, and the views hide all the ugly complexities of various joins, grouping etc.

Our company was in a similar situation where Access was used early on, and then we switched everyone over to use T-SQL and SSMS. IMO, this is the approach you'd want to take.

Again though, the success of this will depend on the quality of your views, or better yet, reports you provide your end-users.

Randy

Answer 2

I would look more into something like Stonefieldquery.com that is designed for non developers to build reports. Not that the report writer or query builder in Access is bad, but may be too much. I think they also provide a way to centralize reports and queries where they can be shared. Multiple people are not going to be able to open a single access file and create a report (I think query building is OK.).

Most will use the drag and drop capability, but about 5-10%will come thing a need for SQL and then you can take advantage of the "teachable moment" and get them some training.

Answer 3

Cons for Access certainly would be cost; SSMS should be free assuming you're properly licensed for the SQL server.

Depending on the actual needs, some users might actually be better off with Crystal Reports (never thought I'd say that), or Reporting Services.

Answer 4

Your "abstraction layer" is the right approach to take with Access. Create an MDB with the basic views required linked into it and distribute to the users. Allow them to create new queries and reports in their own MDB as required.

Now how you are going to stop them from running a Cartesian join on tables with a million records or more I'm not quite sure.

Answer 5

you could create a series of sql server analysis cubes and have the users conenct to those using excel so that they can use excel's pivot tables.

Answer 6

How about this one? i-net Crystal-Clear 10 has a powerful ad-hoc reporting component that is made to be an easy-to-use thing for non-technical users. Your users won't have to know anything about reporting at all. They simply select the kind of report, the data et voila there is a report suiting the needs.

The data abstraction can be done easily by creating so called data-views which can be designed by e.g. your administration. There are various ways to access the ad hoc reporting GUI. We have a web GUI, a Java Applet or even a standalone Java program.

• The end users will not need any training since the GUI is highly intuitive.
• The views can easily be build by drag and drop in addition to setting datatypes, formats and so on.
• All reports (depending on security settings) can be accessed via DAV our a report repository gui.
• The server supports different security settings on a per user or per group basis.

Disclosure: Yap. I'm working for the company who built this.