August 13, 2008 at 9:45 am
zahid.m.shafi (8/13/2008)
...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 🙂 }...
Amen.
August 13, 2008 at 10:04 am
This seems to be the proper audience for the following scenario so here it goes...
We have multiple databases that each have multiple reports for the client delivered in an Excel format.
The databases are OLTP with no plans for a Data Warehouse any time soon. Data is pulled using Stored Procedures in SSMS and then copy/paste to Excel. Then Pivot tables present this the data to the end user.
There are nearly 200 Reports that need to be updated weekly for delivery to the client. Many of those reports take multiple stored procedures that may take any where from 30seconds to 20 minutes to run. Many of the stored procedures cannot run concurrently due to contension issues, thus they are all run in serial.
Currently, the plan is to develop custom software that can Schedule the reports. Run the stored procedures and copy them into Excel automatically. A prototype has been developed.
Would that be the recommend solution or something else?
August 13, 2008 at 10:07 am
Samuel Vella (8/13/2008)
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.
Hi, This sounds like a great idea. I tried a couple of these from the simplest to the most complex, using either the Reports or the ReportServer access.
Well, If my reports takes parameters, I cannot see how to change them after the first query ran. Also, I shall select only the data table so If I edit this query later I get errors because other objects are missing, also the resulting query looks to refer to an execution ID or snapshot, not to a fresh query. Any idea how to make this a little more dynamic? bellow is an example of original query I tried;
BI Guy
August 13, 2008 at 11:08 am
I have created many queries using Excel as my tool. Instead of stored procedures I use Views because they allow parameters. create a view in SQL and in the SQL box in EXcel you would put all the fields that you will be selecting from your View and then you can paramaterize. It works great!
August 13, 2008 at 11:23 am
I do realize that this is just asking for trouble, but how do you do the same thing in Access? 😀
Steve G.
August 13, 2008 at 11:24 am
Good Article...
August 13, 2008 at 11:28 am
I sometimes use MS Access as the data source for Excel and the only difference is that you select MS Access database as your data source instead of SQL Server. I usually use a query as the source and sometimes that query's source is a SQL Server view or table.
August 13, 2008 at 11:32 am
Sorry, I wasn't clear in my previous post - how do you query SQL Server from Access using this sort of technique?
Steve G.
August 13, 2008 at 11:37 am
You can either link the SQL tables into your database or create SQL-pass through queries but since this is a discussion about using External data in an Excel spreadsheet I'm not sure that I understand your question but hopefully I did.
August 13, 2008 at 12:54 pm
I understand what you are saying about using views etc but things that don't matter a jot in small to medium databases suddenly become a very big deal in larger databases.
Our Excel people don't have to write stored procedures, they could use views and tables but some of the queries are very complex or require multi-stage processing i.e. it isn't a single query.
Wrapping it up in a stored proc is an advantage for them for several reasons
1. It then becomes a DBA problem (actually this is points 1 to a zillion as far as they are concerned - thanks guys)
2. The functionality of the query becomes visible if you use RedGate SQLDOC, Innovasys DocumentX, APEX SQL Doc etc on our data dictionary web site.
3. Readability of code for the Excel user.
4. Security. Just because it is a report doesn't mean you want EVERYONE to be able to read it or get at the data that drives it.
Although reporting servers are not strictly speaking "mission critical" they are important and if they went offline they would impact on a paying part of the business.
Access to tables/views is necessary but some form of competency test is required before users are added to a role that exposes these objects.
All it would take would be someone to write WHERE DATEDIFF(d, DateCreated,GETDATE()) >3 on a 2 billion row tables and the performance of the reporting server would suffer, particularly if they are joining several multi-billion record tables together.
Excel Web queries have been in since Office 97 so scraping a web report such as one generated by reporting services is not a problem. If someone was doing it on a regular basis then I would be looking to provide them with a more directly route.
I don't have an issue with reporting services, other than it being a bit slow.
Basically people are comfortable with Excel that is why they use it.
August 13, 2008 at 1:00 pm
I am in a very large company 20 billion in sales and I am writing queries on the performance in call centers and I don't have an issue with things being slow. Perhaps what you are doing is more complex than what I am doing. I've worked for several large companies in the past, one of which is Cisco and I have always developed this way.
August 13, 2008 at 1:50 pm
zahid.m.shafi (8/13/2008)
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 ]
You have two interesting comments, and both of them I would respectfully disagree with at least some of the time.
First, while there are definite disadvantages to, as you put it, "bespoke" coding in that it makes certain things such as troubleshooting more complicated, there are times when it is appropriate. There are cases where to obtain results you must do a combination of things, some of which are most effeciently effected at the server and some of which are most effeciently done on the client. This separation may also be important in some scenarios for security and for role separation for employees in the company. It can help divide the responsibility for the multiple parts of a large project.
As to the role of the DBA, I would certainly agree that there are some DBAs that do nothing but that. There are others that are involved in every level of the project short of interface design. From planning the data structure, to optimizing the interface with SQL, and of course then maintaining and securing the data itself.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 13, 2008 at 3:40 pm
A few points:
Some of you guys need to climb down off your high horses and remember that many of us use SQL Server in environments where getting Reporting Services and Analysis Services up and running and making them serve needs is prohibitively expensive in terms time, expertise and money. Using Excel as a reporting front end and data extraction tool can work very well for lots of organizations and has its place.
Secondly, beginning at least with Excel 2003 (maybe 200, I'm not certain) you can call a SP with parameters from the Query Tool in Excel - the syntax is a bit twisted, but it works:
{Call spname(?,?)}
Those are curly brackets around the whole expression, parens around the question marks that are placeholders for the parms. You'll get the same dialog as noted in the article and can specify cells in the spreadsheet from which to draw parameter values.
Finally, I don't know anyway RS or AS can be used to supply data to complex spreadsheet based models to that they recalculate based on current data from a SQL back end - doing it with Excel queries works very nicely, however.
-
Richard Ray
Jackson Hole Mountain Resort
Teton Village, WY, USA
August 13, 2008 at 5:47 pm
Guys, For those of you who needs to go beyond Excel 2003 65000 rows or so limit, you can feed a pivot table with SQL server data.
I provide a couple examples bellow for Pivot, Table and to retrieve some value within a vba module. Note that the ADODB provider can also be used to query an AS Cube!
VBA let you use Excel as a nice custom front end without much programming. You can have controls and buttons straight in your spreadsheet. 🙂
Sub Recompute(Source As String, P1 As String, P2 As String)
If P1 = "" Then Exit Sub
If P2 = "" Then Exit Sub
On Error GoTo ErrorHandler
Select Case Source
Case "Pivot"
Range("C15").Select
With ActiveWorkbook.PivotCaches.Item(1)
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=datamart.onsemi.com;UID=myuid;APP=Microsoft Office 2003;WSID=myuid-D4;DATABASE=SM;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = Array("exec asp_World '" & P1 & "', '" & P2 & "'")
End With
Range("A2:L2").FormulaR1C1 = "'ASP@Mix change impact for " & P1 & " To " & P2
Case "Raw Data"
Sheets("raw").Select
Range("D10").Select
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=datamart.onsemi.com;UID=myuid;APP=Microsoft Office 2003;WSID=myuid-D4;DATABASE=SM;Trusted_Connection=Yes"
.CommandType = xlCmdSql
.CommandText = Array("exec asp_World '" & P1 & "', '" & P2 & "'")
.Refresh BackgroundQuery:=False
End With
End Select
Exit Sub
ErrorHandler:
MsgBox ("An error occured, please try again. " & P1 & ", " & P2)
End Sub
Private Function adoVar() As Integer
Dim Connstr As String
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim MyCmd As String
' this function requires a reference to ADO active X libraries
MyCmd = "SELECT case when r.Status = 'Ready' then 1 else 0 end AS Status From onglobals.dbo.tb_IsReady r WHERE (r.Name = 'ASP_World_tbl') "
Connstr = "ODBC;DRIVER=SQL Server;SERVER=rocky;UID=myuid;APP=Microsoft Office 2003;WSID=myuid-D4;DATABASE=ONglobals;Trusted_Connection=Yes" _
Set cnnConnect = New ADODB.Connection
cnnConnect.Open Connstr
Set rstRecordset = New ADODB.Recordset
rstRecordset.Open Source:=MyCmd, ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, LockType:=adLockReadOnly, Options:=adCmdText
adoVar = rstRecordset.Fields(0).Value
cnnConnect.Close
Set rstRecordset = Nothing
End Function
BI Guy
August 13, 2008 at 6:11 pm
I spent five years writing ASP classic websites dumping out "reports" to the company intranet - until the day I realized that anyone using the data was copying it to excel anyway. I decided to cut out the middleman.
My customers are much happier to open a spreadsheet than a web browser. A few queries, some pivot tables, a little VBA for refreshing and setting parameters (you can't have a parameterized query feeding a pivot cache), and a desktop machine running scheduled jobs overnight creates smiling faces in the morning when they can just go get the latest report.
I use either views or stored procedures, depending on my users. The best thing about using stored procs is that the users can't get creative with where clauses.
By the way, the other wonders of using Excel are graphs, dashboards, and users not calling you asking how to use the reports!
Viewing 15 posts - 16 through 30 (of 120 total)
You must be logged in to reply to this topic. Login to reply