Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Let the Excel Play Expand / Collapse
Author
Message
Posted Sunday, October 5, 2008 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
And i guess the full support of VBA (data formatting API, [UN]limited UI) would also b there with "Get External Data", And i don't think i should've taught my users to use "Get External Data" using the sqlserver(server=srvrname,pswd=gift4u) , this i had to use with 50 users only.

i'd appreciate a list of -ve impacts a few lines of VBA code lines would produce (don't include "connection string is in plain text" every responsible developer will protect the macro with a password)

i'd also appreciate the list of benefits of "Teach Get External Data" approach for non-technical MS-OFFICE only kind of users who are dying for the word "Automation" & Data on single click feature..
Post #580843
Posted Sunday, October 5, 2008 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
Didn't get your point my friend, are u in favour of the VBA approach coz.. it is difficult to individually teach n number of user how to use Get External Data or ...........
Post #580849
Posted Sunday, October 5, 2008 7:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
Don't get me wrong... I'm not bad mouthing your code or your article... I actually gave it a good mark. :D

There's only one item on a list of benefits that you asked for that I can think of...

1. Users become self supporting

Most users can "survive" in MS Excel. Most of them can write simple formulas to accomplish what they need to accomplish. "Single button automation" with VBA is a really cool thing, but most users can't even spell VBA never mind do something with it other than recording simple macros. The end result is that whenever the users want a change in the automation or want something new to be automated, they have to come to you. You become the very busy single point of failure because, as you already know, everybody likes to make it easy on themselves and your fine automation definitely makes it easy.

If you get those folks together for a "Lunch-n-learn" and show them how to both setup MS Query and use "Get External Data" through a View to do their jobs, your users will become self supporting (their pride in doing something really cool will see to that) and you can concentrate on your primary job as well as thinking of what to teach them next. If you limit what they can read to simple views, you also keep control of the queries so they don't crush the server performance wise.

About the "Lunch'n'Learns"... not everyone actually needs to show up for those, although they should all be invited. We had a huge problem with users writing some really ignorant queries where dates were concerned and they were not only taking a million years to run, the results were often incorrect because the didn't actually know the right way to use dates (and all it's abbreviated forms) as criteria. I created a nice little "How to" booklet and not only gave it to them as handouts, but published it on the company WIKI. Only about 30% of the people actually attended the course but 100% of the people learned how to do it correctly because the people who attended the course actually taught it to their peers.

You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient. And, I've found, that if you teach them, you also and very suddenly become very well known and indispensible as a source of knowledge. That helps a lot in today's job market. ;) Hmmm... so maybe there's other advantages on the list of benefits you wanted, eh?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #580853
Posted Sunday, October 5, 2008 9:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 2,904, Visits: 1,822
Does it work? Yes
Should it be used? Not if you want a robust scaleable solution.

In the environment where I work we had to buy and install a server solely to let users query data away from the live environment.

We found that our live servers had strange locking and timeout problems which were solved by replicating data to a reporting server.

However, this has simply shifted the problem elsewhere. The other issue is that we now have an unknown number of "mission critical" applications that are uncatalogued and unsupported. I am currently working on a project where we have to put a compromise solution in place to cater for such unknown mission critical apps.

There is nothing wrong with the article. It presents a solution that works.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #580863
Posted Sunday, October 5, 2008 11:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
I understand your concern Jeff, infact you made a valid point by saying that

"You can either spend time writing custom automations for all 50 users, or you can teach them to become self sufficient".

Also, i'm not advocating excel+VBA as a new full scale reporting tool, but my case was altogether different, all the reports were already running (partially) fine in our web application where users would click on the "Download to excel" and use the excel sheet thus generated..

BUT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

After this they spent another 15 min for formatting it the way they wanted,
for example whenever a large number like 7856556645452 gets exported in excel it doesn't appear as it should rather it takes the exponential form 7.85e10 also it get rounded off and bla bla bla.......

I even solved it by modifying the stored proc which fetched this data as ' ' + 7856556645452 but after a couple of days later i found that the stored procedure was doing more on the part of formatting the data than on fetching it, hence finally pestered with the daily arising new issues i took the help of VBA.

Some people have commented that code written this way is not maintainable, i agree coz any change in the data requirement would require the query in the macro to be modified but again why would you write plain text queries for what can be achieved through a stored procedure.
Post #580869
Posted Sunday, October 5, 2008 8:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
shashank (10/5/2008)
...why would you write plain text queries for what can be achieved through a stored procedure.


I never said I would. ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #580890
Posted Sunday, October 5, 2008 8:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
David.Poole (10/5/2008)
Does it work? Yes
Should it be used? Not if you want a robust scaleable solution.

In the environment where I work we had to buy and install a server solely to let users query data away from the live environment.

We found that our live servers had strange locking and timeout problems which were solved by replicating data to a reporting server.

However, this has simply shifted the problem elsewhere. The other issue is that we now have an unknown number of "mission critical" applications that are uncatalogued and unsupported. I am currently working on a project where we have to put a compromise solution in place to cater for such unknown mission critical apps.

There is nothing wrong with the article. It presents a solution that works.


I agree... nothing wrong with the article.

Not sure why you think you'd end up with uncatalogued and unsupported apps, though. The method I recommended would be done through supported views (or procs). The Excel spreadsheet is just to make the output "pretty".

I do, very much, agree that this type of reporting should probably be done through a reporting database rather than the production database for the very reasons you mentioned.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #580893
Posted Sunday, October 5, 2008 9:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
I know that, an SSChampion won't :D
Post #580898
Posted Sunday, October 5, 2008 9:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 5:38 AM
Points: 16, Visits: 93
I agree with everyone i definitely can't run in defiance of the facts by SSChamps and gurus infact this article doesn't need this much attention , but i'd say only 1 thing in the end that if the credit card number 4324244355455656 starts appearing as 4.32e10 or 43242400000000 on your final excel sheet than whatever u do won't be related anywhere to make the excel "pretty"
Post #580900
Posted Sunday, October 5, 2008 10:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
shashank (10/5/2008)
I know that, an SSChampion won't :D


Heh... OK. If you're going to throw a conversation on the floor with stuff like that, I guess this conversation is over.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #580902
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse