August 12, 2008 at 9:34 pm
Comments posted to this topic are about the item Excel with Stored Procedures
August 12, 2008 at 10:07 pm
I think using Excel as the interface for selects from SQL Server is so much better than programming a dinky new interface. I'll save this article as a guide if I want to make it possible for the Excel user to enter a parameter, so thank you.
My usual practice is to make the first sheet in an Excel workbook the "refresh data" sheet -- no parameters-- just using the enabled "refresh data" button built into Excel which runs "select * from viewName". A second sheet in the workbook presents and comments on the query (view) that selects the data -- this is rudimentary data dictionary for data owners; documents the database permission that the "refresh data" requires; and states a "sunset date" for support of the view. As the DBA, I keep a record of the distribution of these "refesh data" sheets and the scheme of database permissions.
The great thing about the use of Excel with SQL Server is the Excel's default behavior of putting the column headers from the (non-parameterized, select *) query in row 1, and keeping the "freeze panes" property set on the sheet, so row 1 is always in view as the header. So far, I have had no performance problems -- because I maintain the indexes and statistics on the tables from which the view selects -- and I observe an informal limit of about 20000 records to be returned by a view selected from Excel. I've arrived at this limit from listening to data owners' comments on the usefulness of the "reports" more so than having to admin query performance. Some of the views have where clauses using date functions with getdate() to select only recently created or updated records.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
August 13, 2008 at 12:04 am
Hello,
I just wanted to note that--at least in Excel 2007--you can use the call syntax to call stored with parameters from Excel.
{Call ?,?,?,?,... ?} works just fine.
You're warned that the query can't be displayed graphically and that you can't use parameters--but it still works. You're prompted for parameters, you enter them, then you return to your worksheet and you're able to specify the cells (or hard coded values) that feed the ?s in your Call statement.
August 13, 2008 at 5:26 am
Was hoping for a lot more in this article, I think this subject touches on one of the main reasons we use SQLServer ie to just get data into Excel like user interfaces - with flexible reporting functions.
As you mature this idea ( i.e. MS Office tools needing database/stored proc access, which BTW has been ongoing for probably 10years+) you eventually starting talking about Analysis Services/Sharepoint & Live Services and hence I think stored procs are not the way forward because much *bespoke* coding [function of client AND server side] is what it eventually results in...
[ In fact may I be so contentious as to say that SQLServer DBA jobs only exist today to facilitate things like Excel data sourcing ]
...or perhaps you folk think that the SSAS technology is still immature and want to resort to these outdated modes of thinking?
Not having a dig at anyone just want to encourage the move forward rather than backward.
August 13, 2008 at 5:26 am
I have been using this for about 10 years for management reports but I don't allow the users to create their own queries because most users are not technical enough to create a query. I create the query and then I have the users refresh the query and it works well. My spreadsheet have multiple worksheets and they use the refresh all feature to update all the supporting spreadsheets. The queries look for the parameters on the main worksheet sometimes in hidden fields. Using this method the user is only prompted once for each parameter. I also use a DNS less connection so they don't have to setup an ODBC driver and I use a generic user on the SQL end. I limit what they can see by creating views that only the generic user can use.:)
August 13, 2008 at 5:39 am
I too have used this technique for many years as a way of quickly exposing the outputs of SPs for reporting - usually reporting that sits outside of the more structured MIS system.
One thing to note is that you might start to see lots of 'new' errors in the logs, along the lines of spid xx DBCC TRACEON 208, SPID XX
These can be suppressed by using trace flag 2505 - more info here : http://support.microsoft.com/kb/243352
Kev
August 13, 2008 at 5:54 am
I've got to keep 200 DB servers up and running with typical specs of 16 CPU 24GB RAM replicating across 3 sites.
Even at my most cynical (and I can be more cynical than most) I wouldn't describe my job as facilitating Excel.
I agree that Analysis Services should be utilised more. If anyone knows how to integrate cubes with reporting services I would be very interested.
The problem with ad-hoc queries is do you dare let someone run an ad-hoc query on a multi terrabyte database albeit a reporting server. Great fun fielding angry customers complaints that they can't generate their reports because someone ran a query with a dodgy join and non-existent of crazy WHERE clause in it. The last time I saw a reference to that much OR was in King Solomons Mines.
August 13, 2008 at 5:55 am
Thanks for the information but I do not use stored procedures and I'm wondering why you feel this is necessary. I base the spreadsheet on specific views and then I allow parameters which I code into the view such as a date field, I use [Enter Date] in the criteria field. This eliminates problems with SQL Server.
August 13, 2008 at 6:07 am
David,
If you're using reporting services and users have web access to run reports then reports can easily be integrated into Excel (not sure about Excel 2000) using a web query.
This removes the need for stored procedures and gives the advantage of formatting coming over with the data.
As far as integrating SSAS with SSRS then this can be done, although my experience with this runs as far as building a proof of concept for a client.
The disadvantage of SSAS though is that for smaller systems the Analysis services adds an extra layer of complexity and a requirement of technical knowledge that not all companies (especially the smaller ones) need or want.
August 13, 2008 at 7:00 am
Continuing with my satanic advocacy and to test my thesis further..."how many of your servers are for 'producing reports' versus "act as prime records for key OLTP processing' ?"
...perhaps the fairest way to answer the question is in terms of 'online data storage' to the nearest gigabyte.
Also I believe many technologists [aka developers] really don't want to spend their lives building reports that half competent users should really be able to do for themselves and are nearly always (similar to the VB macro universe) just slight variations on a couple of basic themes.
{ Obiviously I appreciate that most of us earn our money impressing ourselves by parameterising these 2 or 3 very basic patterns so ...er ...well 🙂 }
Finally we really need to get over this self-enforced threat about 'rogue joins', tis a bit like 'the tail wagging the dog' - I'm not denying the issue exists but I dont believe in this day and age that we need to resort to it or advertise it.
Now that v. large databases have been around for a decade plus and there exists a major ongoing demand for MIS/reporting services then we, as technologists, need to focus on solving this problem 'efficiently' - and I mean 'efficiently' in a truly global computing paradigm sense and not local to our day to day lives.
I find it remarkable that a technologist can have luddite tendencies...
{note this subject just interests me, am keen to see discourse on it and I repeat that I really do not mean to offend anyone with my ramblings so apologies if I have :unsure:}
August 13, 2008 at 7:07 am
I use Excel 2003 with stored procedures without involving VBA. The trick is to manually edit the .dqy file that MSQuery generates, or more accurately, just manually create the file and leave out MSQuery altogether. Its just plain text so Notepad works nicely. I personally use UltraEdit.
The format you want is:
XLODBC
1
DSN=myDSN;DATABASE=myDatabase;Trusted_Connection=Yes
EXEC dbo.myStoredProc ?, ?
prompt1prompt2
The first 2
lines are standard.
Line 3 is basic connection string info.
Line 4 is your EXEC statement with parameters listed as '?'s
Line 5 is a tab-separated list of parameter names, or prompts for Excel
Save the file as myStoredProc.dqy. In Excel, use Import External Data > Import Data, and browse for your new file. Then hit the Parameters button and you'll see your parameter prompts. I usually set them to update on change of a cell value.
I was always frustrated with MSQuery limitations, now I have no need for it. This made things much easier for me and I have built some very nice user interfaces with zero VBA.
August 13, 2008 at 7:16 am
The people responsible for architecting the "solutions" for a business should be about the business' data primarily and about the tools/technology secondarily. (I was a statistical data analyst before I became a database programmer.) Use the minimum technology to report the data to the data owners. In many situations, this means Excel is better than adequate and the business has no need for the expense of SSAS or Sharepoint technology or technologists-- even though technology vendors have a need to sell their wares.
The managers who are my customers for Excel "reports" are old hands at "Data - Filter - AutoFilter" and now they use the same Excel they used to grow the business to filter the result sets delivered to Excel by the views.
As the SQL Server DBA **and SQL Server sysadmin** I spend my time on data quality, data integrity, data security-- and on fixing "architecture" devised by network and Windows admins who seem to have some real blind spots about business requirements.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
August 13, 2008 at 7:32 am
I have a management background and in my later years learned computers using Access. I started with Access 1.0 the first version Microsoft ever realized and today as a result I build great interfaces using very little code but I use all the bells and whistles that Microsoft provides in this product so I go at things a little different than a DBA and Programmer. That said, I progressed to SQL Server using version 7 and now I use version 2005 and I build databases, tables, multiple step SSIS packages and still use very little code. The VBA example above written by a programmer I'm sure works very well but all I do is the following in the external data query I click on criteria and add the field used for the prompt such as date and in the value I enter a prompt such as [Enter Date]. This works just as well as the VBA code.
August 13, 2008 at 8:51 am
I love the way the technical spec was written and finished first and then they came to the DBA to ask how/if it can be done. Who else has been there / done that?
August 13, 2008 at 9:25 am
magarity kerns (8/13/2008)
I love the way the technical spec was written and finished first and then they came to the DBA to ask how/if it can be done. Who else has been there / done that?
been there multiple times, only done it once
every other time i've told them how it should be done 😉
Still there could be some very valid technical reasons why it had to be done with stored procedures.
And Excel is the defacto tool for data manipulation outside of the database team
Viewing 15 posts - 1 through 15 (of 120 total)
You must be logged in to reply to this topic. Login to reply