Best Practices: Ad Hoc Analysis Front end for Sql Server 2005

[Mark Olbert]

I've read quite a few posts about the lack of Access design support for Sql Server 2005, but have a few questions.

My understanding is that no current version of Access supports designing objects in SqlSever 2005 (as an aside, if this is true it
is a cock-up of truly monumental proportions by Microsoft, both in terms of the basic problem -- incompatibility -- and the stunning
lack of coordination between the Access and Sql Server marketing teams -- the SqlServer team should've flagged the incompatibility
as a major "breaker". And please don't give me the spin that "it's compatible, just not on the design side" -- compatible is
compatible, not sort of half compatible -- can you imagine how people would react if someone told them the back end to Excel
[assuming it had one] had changed and that they could look at their spreadsheets, and recalculate them, but not make any changes or
create any new ones?).

Not being able to design objects in an SqlServer 2005 database means I can't do ad hoc analyses, which require an ability to make,
view and tweak queries. So what does Microsoft recommend as the current best practice for ad hoc analyses using SqlServer 2005
databases?

I am quite conversant with .NET, C# and Visual Studio 2005, and somewhat familiar with SqlServer Business Intelligence Development
Studio and SqlServer Management Studio. From what I can see, none of those tools is a good ad hoc analysis platform (which is why I
kept using Access for SqlServer 2000 databases in the first place). You can do anything in .NET and C#, but writing programs to
answer ad hoc questions is more than a little ridiculous. Simlarly, you can do anything you want in Management Studio (and
Development Studio, which is essentially VS 2005 with some customizations), but it's not a good ad hoc analysis platform (for one
thing, unless I'm using Management Studio wrong, writing and executing queries is much less straightforward, not to mention slower,
than in Access using ADPs).

So how do I do my ad hoc analyses prior to 2007 when Office 2007 comes out?

- Mark

 

[David Portas]

I've read quite a few posts about the lack of Access design support for
Sql Server 2005, but have a few questions.

My understanding is that no current version of Access supports designing
objects in SqlSever 2005 (as an aside, if this is true it
is a cock-up of truly monumental proportions by Microsoft, both in terms
of the basic problem -- incompatibility -- and the stunning
lack of coordination between the Access and Sql Server marketing teams --
the SqlServer team should've flagged the incompatibility
as a major "breaker". And please don't give me the spin that "it's
compatible, just not on the design side" -- compatible is
compatible, not sort of half compatible -- can you imagine how people
would react if someone told them the back end to Excel
[assuming it had one] had changed and that they could look at their
spreadsheets, and recalculate them, but not make any changes or
create any new ones?).

I doubt that this was due to any accident or lack of communication.
Indications from Microsoft have been that they don't plan on ADP support for
SQL Server 2005. Draw your own conclusions.

Not being able to design objects in an SqlServer 2005 database means I
can't do ad hoc analyses, which require an ability to make,
view and tweak queries. So what does Microsoft recommend as the current
best practice for ad hoc analyses using SqlServer 2005
databases?

You can use pass-through queries in Access or MS Query in Excel or you can
use SQL Server Management Studio. That covers basic ad-hoc querying.
Typically, creating and modifying database objects (rather than just writing
queries) is a task for developers and DBAs so that's supported by the
development tools, not by software aimed at business users.

Analytics and ad-hoc reporting for end users has not been Microsoft's
strongest offering. Other vendors have dominated that market on the SQL
Server platform. That looks set to change now that Microsoft has acquired
ProClarity, so check out:
http://www.proclarity.com
http://www.microsoft.com/office/bi/default.mspx

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[David Portas]

(for one
thing, unless I'm using Management Studio wrong, writing and executing
queries is much less straightforward, not to mention slower,
than in Access using ADPs).

As you say, maybe you are using Management Studio wrongly.

If you know SQL then Management Studio is the best way. If you don't know
SQL and don't want to learn it then get one of the end-user tools I already
mentioned.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Mark Olbert]

I doubt that this was due to any accident or lack of communication.

Indications from Microsoft have been that they don't plan on ADP support for
SQL Server 2005. Draw your own conclusions.

If it was deliberate, that's even worse. I hate it when vendors eliminate functionality.

- Mark

 

[Mark Olbert]

As you say, maybe you are using Management Studio wrongly.

If you know SQL then Management Studio is the best way. If you don't know
SQL and don't want to learn it then get one of the end-user tools I already
mentioned.

I do quite a bit of work in SQL, so that's not the problem. Management Studio is a clunky way to do ad hoc analyses. Importing data
is a pain (and, speaking of removing functionality, there's some useful importing capability in SqlServer2000 enterprise manager
which is gone in Management Studio), and the cycle of writing queries, using them, tweaking them, repeat (which happens quite a bit
doing ad hoc work) is nowhere near as smooth as in Access.

But if you're telling me Management Studio is the best there is right now, fine. That just means the best is quite a bit worse than
what I used to have.

- Mark

 

[David Portas]

I do quite a bit of work in SQL, so that's not the problem. Management Studio is a clunky way to do ad hoc analyses. Importing data
is a pain (and, speaking of removing functionality, there's some useful importing capability in SqlServer2000 enterprise manager
which is gone in Management Studio), and the cycle of writing queries, using them, tweaking them, repeat (which happens quite a bit
doing ad hoc work) is nowhere near as smooth as in Access.

But if you're telling me Management Studio is the best there is right now, fine. That just means the best is quite a bit worse than
what I used to have.

- Mark

It sounds to me like you may be using the Query Designer feature. I
don't know anyone who uses that. I write queries in Management Studio
by clicking "New Query", "Connect" and then just type. If you are doing
that then I don't understand why you think the development cycle is
harder. It is only so if you don't know Transact-SQL.

What Import/Export functionality is missing? Click Tasks / Import Data.
You can feedback any bugs / suggestions at the feedback centre:
http://lab.msdn.microsoft.com/ProductFeedback/

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Mark Olbert]

Knowing T-SQL doesn't mean I prefer to do everything by typing . You're correct, I do use the designer quite a bit, and the feel
of it is worse than the feel of Access.

As to the import capability, you used to be able to import data from another instance of SqlServer 2000 by copying objects and data.
That's been removed from Management Studio. The suggested workaround is to use Business Intelligence Studio and create a project for
doing the same thing. That works, but it's very, very cumbersome compared to the removed functionality.

All of this leads me to the conclusion that Sql Server 2005 was either released too early (i.e., a bunch of stuff that was planned
for inclusion that got left on the cutting room floor) or that the architectural plan was rushed. Either way, I'm not a happy
camper, and it sounds like I am not alone, not by a long shot.

- Mark

 

[privatenews]

Hello Mark,

Thank you for your comments and feedback on these features and please rest
assured this are routed to the right channel. In the meantime, I also
encourage you submit via the link below

http://lab.msdn.microsoft.com/productfeedback/default.aspx


Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mark Olbert]

Peter,

Do you have any recommendations on the best tool to use for ad hoc analyses of SqlServer 2005? I'm willing to try something new if
there's a replacement for the functionality I used to get with Access.

- Mark

 

[privatenews]

Hello Mark,

Based on my scope, currently SQL 2005 managment studio shall be the best
option. You could use the feature like " display estimated exectuion plan",
"design query in editor", "include actual exectution plan", "Analyze query
in database engine tuning advisor" feature to design and tune ad hoc
queries efficiently. You may want to check Books Online for more details.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[aaron.kempf]

ADP friggin rocks for SQL 2005 don't listen to these MDB fairies

Microsoft will come out with a patch for this; people like David Portas
sit here and say 'SSMS is the best' (GAG)

since when can you copy and paste a view in SSMS?
since when can you copy and paste a sproc in SSMS?

I suggest you kick and scream and beg microsoft to come out with a
patch for SQL 2005.

if you want me to participate in a SIT-IN i'm sure that blocking 520
westbound at 4pm on a Thursday would get some attention

IM NOT MOVING UNTIL YOU RELEASE A SQL 2005 ADP PATCH!!!

 

[aaron.kempf]

strongly agree with you bud

'just because i know tsql doesn't mean i want to type it all day long'

SSMS is inferior to ADP in about a dozen ways

 

[aaron.kempf]

are you sure that we can't somehow make some sort of tool that would
make this easier?

i mean seriously here

when you make a new view; we somehow force ADP to sink all those stupid
warning messages; that doesn't seem impossible
then you get a form that says 'what do you want to save this view as'

and then you take that view defintiion and create it programmatically;
refresh the db window

it almost works

i just wish that we could call some 600lb gorilla to kick microsoft's
ass and force them to put out a tiny little patch to do this.

i mean.. these idiots didn't even let Access 2000 and SQL 2000 play
nicely together.

I use ADP every friggin day; every single thing i do is in ADP.
for my ETL; i use .NET of course; but data entry? reporting?

either ADP or Olap lol

Reporting in ADP is 1000 times better than Crystal; and data entry
forms in ADP are much much better than anything else in the whole wide
world.

-Aaron

 

[david epsom dot com dot au]

All of this leads me to the conclusion that Sql Server 2005 was either
released too early (i.e., a bunch of stuff that was planned
for inclusion that got left on the cutting room floor) or that the
architectural plan was rushed. Either way, I'm not a happy
camper, and it sounds like I am not alone, not by a long shot.

- Mark

I've read that MS is moving towards 'incremental' releases, away from the
big block buster release. If this is true, expect to see more products
released with partial functionality.

(david)

 

[aaron.kempf]

if you want me to participate in a SIT-IN i'm sure that blocking 520
westbound at 4pm on a Thursday would get some attention


IM NOT MOVING UNTIL YOU RELEASE A SQL 2005 ADP PATCH!!!