Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

Jan Karel Pieterse — Wed, 30 Nov 2005 13:06:13 GMT

Hi David,

Interesting as Excel Services may be, I suspect the subject is not the most popular one around. Your last three posts have attracted no comments at all.

I'm certainly not saying Excel services shouldn't be written about. But I am under the impression that your writing for the wrong audience here.

Nevertheless: Great blog, keep it up.

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

David Gainer — Wed, 30 Nov 2005 21:08:07 GMT

Hi Jan Karel,

My goal for this blog is to cover everything we are doing with Excel client and server in v12, so I am going to be pretty thorough for all feature areas. I promise there will be lots more posts that stir up comments before we are all done :-)

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

Nigel Harper — Wed, 30 Nov 2005 21:32:56 GMT

David,

I have just got back to your blog after a 3 week break and finished wading my way through all your new entries and readers' comments. Wow! Lots to absorb and get through.

I'm sure that this lot on Web Services is being read but that it is very new to most of us and so we have not got anything to say. Rest assured by the time you start up a blog for the next version of Excel you will no doubt get a load more responses on this topic.

Thanks again,
Nigel

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

Harlan Grove — Thu, 01 Dec 2005 03:49:25 GMT

A question I posed under a previous topic seems to need to be asked again. Will Excel services provide any control over recalculation, i.e., anything akin to Excel's Application.Calculation property and its Application.Calculate and Application.CalculateFull methods? If not, do SetCellA1 calls trigger recalculation, and if so, is recalculation interupted upon subsequent SetCellA1 calls before the workbooks fully recalcs? Or is recalculation deferred until the first GetCellA1 call? Do workbooks need to be saved to servers with Calculation set to Automatic?

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

David Gainer — Thu, 01 Dec 2005 08:22:43 GMT

Hi Harlan,

1. Workbooks can be saved with calculation set to either automatic or manual.
2. If it is automatic, then SetCellA1 (and any of the other setting calls) will trigger a recalc.
3. If it is manual, we have 2 methods to trigger a recalc: Calculate and CalculateWorkbook. The former enables to calculate a range or the entire workbook, and works as Range.Calculate. The latter calculates the entire workbook, and takes an argument to control the calculation – essentially enabling the two operations that are equivalent to Excel’s Application.Calculate and Application.CalculateFull.
4. Subsequent calls with Excel Web Services are not possible while a call is still being processed. So if SetCellA1 did not return yet (e.g. because the workbook is still being calculated) and you call it again, you will get an exception. The way to interrupt the previous request is to call CancelRequest (the only method that can, actually, be called during the course of a previous request); if the cancel is successful, you can issue the next request. Note that CancelRequest may or may not succeed – the server is not always able to stop a request (e.g. if it is hanging on an external data query).

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

Harlan Grove — Fri, 02 Dec 2005 00:00:03 GMT

David Gainer...
...
|3. If it is manual, we have 2 methods to
|trigger a recalc: . . .

Good.

|4. Subsequent calls with Excel Web Services
|are not possible while a call is still being
|processed. So if SetCellA1 did not return yet
|(e.g. because the workbook is still being
calculated) and you call it again, you will get
|an exception. . . .

Not good. Any way to query whether a request has been completed? If each SetCellA1 call would trigger recalc if the workbook were saved with Automatic calculation, and the next SetCellA1 call couldn't be issued successfully until that recalculation ends, then either there'd need to be some means of querying the state or all SetCellA1 calls would need to be wrapped in exception handlers wrapped in loops.

Any way to turn off automatic calculation from Excel services? If not, wouldn't it be best to treat all workbooks as saved with calculation set to manual? Maybe I'm being overly naive, but if the first dozen requests posted to Excel Services were SetCellA1 calls, there'd be nothing gained by recalculating after each request. On the other hand, it'd be a good thing if the first GetCellA1 call automatically triggerred recalculation even without an explicit calculate request.

Dealing with automatic calculation in large models will likely be enough to convinve most of your potential customers that Excel Services wasn't a good idea. On the other hand, dealing with manual recalc but forgetting to issue an explicit recalc request before issuing GetCellA1 requests would also have unpleasant results which might lead potential customers to the same conclusion. You might want to poll the willing MPVs for their calculation war stories. There are serious trade-offs involved.

re: Excel Services part 10: Data Connection Libraries, or connecting to databases made easy, manageable, and secure

David Gainer — Tue, 06 Dec 2005 09:08:27 GMT

Hello Harlan,

>Not good. Any way to query whether a request
>has been completed? ....

The normal way of using a web service such as this, through the proxy class that Visual Studio generates, is synchronous. The call does not return until its processing is complete. So the issue that you raise goes away. Only code that tries to generate and send SOAP calls directly, and chooses to do so asynchronously, could hit that problem. Since we believe that most developers will use the Visual Studio framework, we did not add the ability to query for the request state, but we appreciate your feedback.

>Any way to turn off automatic calculation
>from Excel services? If not ...

No. Turning off recalc for all workbooks may be suitable for some cases, and not for others. As an example, if the application is designed with multiple iterations of set-calculate-get (each iteration in the loop both sets parameters and gets calculated results, and calculation is not very heavy), then an extra Calculate call each iteration could be a significant performance hit – an extra roundtrip to the server. So we did not want to force manual recalc for all custom applications. Ideally, it would be good to be able to know how the workbook was saved and to be able to change the setting – great feedback. This is just one example why an Excel-like object model for Excel Services is a desirable feature, which we hope to pursue at some point in the future.

>Dealing with automatic calculation in large
>models will likely be enough to convinve most
>of your potential customers that Excel
>Services wasn't a good idea

Again, thanks for the feedback, especially the implied conclusion that it is not clear cut, and really depends on the scenario. The tradeoffs are exactly why we did not wish to force one way or the other – both are possible, but require careful application design. If an application presents the issue of expensive calcs for each call to SetCellA1, there are a few workarounds. They are based on the assumption that you are in control of both the workbooks and the code.
1. Save the workbook with manual recalc, and call Calculate explicitly only once after the Set calls are done.
2. Organize the workbook parameters in a single range, and call SetRangeA1 once, instead of multiple calls to SetCellA1. This way, not only calculation happens only once, but you also save most of the roundtrips to the server – a performance gain.

Tech Talk PT » Blog Archive » BI in Office 2007 Resources

Tech Talk PT » Blog Archive » BI in Office 2007 Resources — Fri, 28 Apr 2006 12:03:43 GMT

PingBack from http://techtalkpt.wordpress.com/2006/04/28/bi-in-office-2007-resources/

Excel 2007 charting UX design

Alex Barnett blog — Fri, 14 Jul 2006 13:36:27 GMT

Sander Viegers is a user experience (UX) designer in the Office Design Group who contributed to Excel...

Управление внешними источниками в InfoPath 2007

Заметки с полей — Sun, 20 Aug 2006 23:40:55 GMT

При публикации форм InfoPath 2003 возникало несколько проблем. В частности, одной из проблем было то,...

Using Parameters In Dashboards

Microsoft Excel 2007 (nee Excel 12) — Mon, 25 Sep 2006 22:01:47 GMT

Today we have a guest post from Dan Parish, who is a program manager on the Excel Services team.&nbsp;...

Calling Web Services From InfoPath Web Forms

Mark Bower — Wed, 06 Dec 2006 01:55:53 GMT

Today I needed to get an InfoPath Forms Services web-form to make a call out to a web service. I was

Calling Web Services From InfoPath Web Forms

Mark Bower — Wed, 06 Dec 2006 01:56:55 GMT

Today I needed to get an InfoPath Forms Services web-form to make a call out to a web service. I was

Excel Services et le monde du Dev, give me 5: Connectivité

The Mit's Blog — Tue, 09 Oct 2007 15:48:56 GMT

De retour dans une de mes couches logicielles préférées de MOSS : Excel Services Il est juste de reconnaitre

Microsoft Excel Excel Services part 10 Data Connection Libraries or | fix my credit

Wed, 17 Jun 2009 05:06:15 GMT

PingBack from http://fixmycrediteasily.info/story.php?id=4175

Microsoft Excel Excel Services part 10 Data Connection Libraries or | adirondack chairs

Fri, 19 Jun 2009 12:10:38 GMT

PingBack from http://adirondackchairshub.info/story.php?id=1737