May 10, 2012 at 12:41 am
A common request at every place I've worked is to give users a way to get a defined data set or sets (ie, queries usually written by developers) in an on demand manner and have that data be provided to them in excel format. These often need to be developed and made available very rapidly. In principle there are many ways of doing this. In practise most of them have some problem or other that makes them undesirable from the point of view of either the user or the IT department. Here's a very brief summary of what I consider to be the most common solutions, and a few uncommon solutions, broken down by category, with what I see to be the main concerns. I'm curious which mechanism you use, whether your like it, or whether you have a mechism not on this list:
SSMS/TSQL BASED SOLUTIONS
OPTION: run query in ssms on demand, copy grid results to excel, email excel file to user
ISSUES: annoying waste of developer time, requires developer availability
OPTION: output to excel direct from tsql using opendatasource
ISSUES: usually not possible due to lack of 64 bit jet driver
OPTION: output to text file direct form tsql using opendatasource
ISSUES: prone to special character problems, like tabs, commas, newlines etc embedded in large text coulumns such as memos, notes, etc. Excel produces an error when attempting to open text files given an .xlsx extension (xls gives a warning but does work)
COMMAND LINE BASE SOLUTIONS
OPTION: output to text file using bcp/sqlcmd
ISSUES: prone to special character problems, like tabs, commas, newlines etc embedded in large text coulumns such as memos, notes, etc. Excel produces an error when attempting to open text files given an .xlsx extension (xls gives a warning but does work)
OPTION: output to text file using bcp and a format file, give the text file an xls/xlsx extension
ISSUES: Excel produces an error when attempting to open text files given an .xlsx extension (xls gives a warning but does work)
EXCEL BASED SOLUTIONS
OPTION: use query engine in excel to pull data from server
ISSUES: requires development of a lot of "front ends" (different excel files), no centrally managed code base
SSRS BASED SOLUTIONS
OPTION: create SSRS report, have user execute report on demand and export data from report to excel
ISSUES: additinal development layer (SSRS), poor performance
SSIS BASED SOLUTIONS
OPTION: embed query in ssis package, package outputs to excel (and possibly emails to user), pacakge executed on demand via dba or dtexecui by developer (if permitted)
ISSUES: cost/benefit ratio not good (large amount of development and yet still requires dev/dba time and availability), results in a lot of ssis packages to manage
OPTION: embed query in ssis package, deploy packge to server, provide a friendly GUI giving the end user the ability to configure run the packgae via some wrapper mechanism
ISSUES: as above
OPTION: same as above but with generic ssis package using expression configuratons to run multiple different export schemas
ISSUES: development headache in creating a persistent SSIS package that can connect to multiple destinations
OPTION: same as above but with programatically-generated and configured SSIS package (using EzAPI) running as a service on the SQL server, service API used by client GUI
ISSUES: complex development
ARCHITECTURAL SOLUTIONS (included for completeness)
OPTION: Provide an environment where users can build their own queries against a recent copy of of the database
ISSUES: Requies additional infrastructure
May 10, 2012 at 6:50 pm
The very last option is one of the best options IMHO. Even though it requires extra infrastructure, it will give the users a recent copy of production where they can "play" without fear especially if you use San "snapshots" to build the environment because you'll be able to restore the environment in minutes at any time of the day. Shoot. Once that's done, hold classes to teach the users how to write their own queries.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2012 at 9:41 pm
These are what I would use depending upon the user requirements
(1) If the users need to have full control over when to run the job, I would use SSRS and create a simple data dump report and give them permissions to create their own subscriptions. This way they would be able to choose Excel as the export format and also choose email addresses. (minimal development time, requires some end user training/skills)
If the users do not need to control then I would suggest using
(2a) SSIS and automate the entire process, Excel export and email file to users. (minimal development time, requires no user training)
or use
(2b) SSRS and schedule the jobs pre-configuring the start date, end date, run time and the email addresses. (minimal development time, requires no user training)
3) If the users want a point and click solution, probably a .NET solution would be best. (moderate development time, minimal user training)
May 11, 2012 at 1:17 am
Jeff Moden (5/10/2012)
The very last option is one of the best options IMHO.
I definitely agree, but of course the usual problem is resourcing. The business may want 100 extracts eventually, and providing their own data source would almost certainly be the most efficient over the long term. But businesses rarely think that way. Four or five unrelated people want their spreadsheet ASAP, and management isn't prepared to put the resources in up front, after all 4 extracts isn't really much work right? :hehe:
May 11, 2012 at 8:34 am
True ernough but if it were only 4 or 5 people who wanted a single spreadsheet, it wouldn't really be a problem, would it? The real problem is that there are many business users who want a lot of different things or you wouldn't have had a complaint to begin with. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2012 at 9:55 pm
Jeff Moden (5/11/2012)
True ernough but if it were only 4 or 5 people who wanted a single spreadsheet, it wouldn't really be a problem, would it? The real problem is that there are many business users who want a lot of different things or you wouldn't have had a complaint to begin with. 😉
Yes exactly... and at any given time the business only sees those 4 or 5 requests, and therefore does not see any problem. But tomorrow, or next week, there'll be 4 or 5 more, and then 4 of 5 more, and then 4 or 5 move... Being a "business as usual" kind of task it doesn't jump on the radar of management as much as "real" project tasks, even though, in the end, it might take twice as long as some of those projects to do all of the requests individually.
May 12, 2012 at 11:14 am
So, keep a log of the requests and how long they took to resolve. After 6 months or a year, calculate a comparison between handling the ad hoc requests and the infrastrucure for the users to handle their own request. If it all works out, present it to management with the ROI figures. Don't forget to include that it will also server as a "hot row level" backup for when people screw things up in production.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2012 at 5:07 am
You can find my solution to this here, http://tinyurl.com/3h929cq
I was inspired by Glenn Berry's script and excel spreadsheet downloads, and thought "Surely we can script this?" It turns out you can. I know Powershell is the new King, but long live VBScript! 😉
As an aside, and without wishing to hijack the thread, I stumbled on this post while looking for http://www.allmhuran.com/portal/, which appears to be down. Is this likely to be resolved soon?
HTH
Dave J
May 22, 2012 at 8:31 pm
Jeff, In a perfect world where I wasn't already working lots of overtime and surviving almost entirely on a neurochemical dependency on stress, spending the time to put together some pretty management proposal to describe or prove something that seems obvious and trivial to me would be the ideal solution - except for my general lack of interest in writing pretty management proposals for obvious and trivial things. You're preaching to the converted on the subject. Of course, I'd also be the one who would have to plan and build it, and, in my role as DBA plus senior programmer + general problem solver for both the business and the development team, I'm currently well over 100% allocated on production support alone. But I'd do it if I was given the infrastructure.
As it is we have project managers, management panels, task allocation and reporting, and so on, designed to figure out things like ROI. I provide them with information from the front lines and put in suggestions as to thing they should be focusing on while doing all of that stuff. What they actually decide... <shrug>
In any case, we're off topic now. My ideal solution would be something like this:
Application presents users with a list of available exports (list predicated on authorization to run)
User selects export, provides any parameters, and hits the go button. User is prompted for an existing file locaton and data is exported to that file (not tab/comma separated, to avoid embedded special char/newlines etc)
Because of the requirement of export direct to excel without a CSV/TXT intermediary, the technical implementation options are limited. Any "export to text" functionality (sqlcmd, bcp, etc) are eliminated, leaving office automation with low level nested loop style write-per-cell, or export in xml (xslx) format, or export via programmatic SSIS Of the three, I like programmatic SSIS the best.
May 23, 2012 at 6:07 am
allmhuran (5/22/2012)
EXCEL BASED SOLUTIONSOPTION: use query engine in excel to pull data from server
ISSUES: requires development of a lot of "front ends" (different excel files), no centrally managed code base
This is the method we currently use. Fortunately or unfortunately, our users know just enough SQL to take existing queries that we provide and modify them to get the data they're looking for. Limits the number of IT requests, but we don't really have a good idea of what data is being pulled from the system and for what purpose.
We're upgrading one of the systems that the users query the most and as part of that upgrade we're changing strategies for user queries. In the new methodology, we're going to have a separate database that provides the interface between the application database and what the users can query from. Right now I have a server side trace running to gather the queries that are being pulled from Excel.
allmhuran (5/22/2012)
Application presents users with a list of available exports (list predicated on authorization to run)User selects export, provides any parameters, and hits the go button. User is prompted for an existing file locaton and data is exported to that file (not tab/comma separated, to avoid embedded special char/newlines etc)
This seems like a pretty good idea and very similar to a reporting tool we have for another third party system. It would have to be tweaked a bit in order to work for us, but I'm going to keep it in mind as we go through the upgrade.
May 23, 2012 at 6:57 am
I've got different solutions for different users.
I have a co-worker in one department who's competent enough with T-SQL queries to give her read-only access to the tables she needs to access. Read Committed Snapshot Isolation and the main DML being frequent single-row inserts, makes this a viable solution for that department.
I have a weekly SQL Agent job that runs 6 different SSIS packages that send data to various other departments in Excel format via e-mail. Requires dev time when requirements change, but that's about once a quarter at most, and usually only takes a few minutes each times. Otherwise, unattended and works well enough.
I get periodic ad hoc requests that can't be automated or outsourced to the originator, and I usually just query-copy-paste from SSMS to Excel. For a few with larger datasets (where the clipboard bogs down), I use the SSMS Import/Export wizard. Any ad hoc query I run, I save the query to a scripts folder on a file share my department has access to. That way, if the request comes in again later, I don't have to re-write code. And if the request becomes frequent, I can add to the automated versions.
I'm working on getting SSRS and even SSAS up and running for some of the more complex requests, but it's not there yet and not planned for a few more months, after other projects are done.
If you have frequent requests that are the same queries, but SSRS is problematic, try setting up a daily export to a common fileshare, using SSIS and SQL Agnet. Run it during off-hours. Anyone who wants can grab a copy of the Excel file with that day's data, and you don't have to run it on-demand or e-mail it to a new person or anything. Most people will be perfectly happy with that kind of solution the vast majority of the time.
Either timestamp the file name, or overwrite the existing file. Ask end users which they prefer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 8:11 am
I haven't looked into it, but can't you include a data modeler and report builder with SSRS? So there are some reports that are standard, some that you will have to do yourself, and others that can be created on the fly by the users. I find SSRS to be the easiest way to get reports to my internal users. It is already built and security is easy to manage.
Jared
CE - Microsoft
May 23, 2012 at 9:32 am
Weird double-post removed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 9:33 am
SQLKnowItAll (5/23/2012)
I haven't looked into it, but can't you include a data modeler and report builder with SSRS? So there are some reports that are standard, some that you will have to do yourself, and others that can be created on the fly by the users. I find SSRS to be the easiest way to get reports to my internal users. It is already built and security is easy to manage.
Yep. It can do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 23, 2012 at 6:39 pm
GSquared (5/23/2012)
I've got different solutions for different users.
Yes indeedy, your rundown mimics very closely the various options currently used where I work, and indeed at pretty much everywhere I've ever worked 🙂
For the "copy from SSMS paste to excel" - I avoid the out of memory exceptions by copying in batches of (n) columns at a time.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply