1

I am trying to design a solution for accessing data in Excel 2010 (and previous versions). Some high level requirements/constraints are below:

  • The data is stored in a database.
  • Users should not be able to connect to the database directly.
  • The types of data access they are likely to want will be consumed by other applications.
  • The users will want to work with the data by directly importing into worksheets and also through VBA.

From my knowledge I thought of the following

  • WCF Service
    • Can be consumed by other applications
    • Will introduce latency (if that is an issue)
    • Cannot be consumed directly in worksheet without VBA?
  • WCF Data Services
    • Can be consumed by other applications
    • Nicer model to expose (via EF) and work with (RESTful, OData)
    • Will introduce latency (if that is an issue)
    • Can be consumed by PowerPivot directly
    • Cannot be consumed directly in worksheet without VBA?
    • Only compatible with 2010 without VBA
  • Web Query
    • Requires more effort to build (will need to put together ASP.NET façade in front of database)
    • Can be consumed directly in worksheet without VBA
    • Compatible with all required versions of Excel
    • Poor mechanism for exposing data for other applications (non-Excel)

I am beginning to think there isn't a one size fits all solution does not exist and will have to create an interface for direct worksheet access and another interface for everything else, be it SOAP or REST type data service.

Does anyone have any suggestions/experience?

Thanks

Jon Archway
  • 4,852
  • 3
  • 33
  • 43

1 Answers1

0

I think you can get away with one interface and just expose two endpoints, one for SOAP and one for REST (or perhaps you don't even need the SOAP endpoint?). That would seem to be the best of both worlds to me. It sounds like it would meet your requirements (Excel/PowerPivot can consume RESTful services), and would prevent you from duplicating code.

Take a look at this SO question for an example.

Community
  • 1
  • 1
BrandonZeider
  • 8,014
  • 2
  • 23
  • 20
  • Big problem is how to consume data directly in worksheets without VBA? The only option I can currently see is providing a web query façade for doing so. Neither of the service options would give me this. – Jon Archway Jul 11 '11 at 16:27
  • Have you looked into PowerPivot? With PowerPivot, you can consume a REST service natively with just a couple clicks... – BrandonZeider Jul 11 '11 at 18:26
  • Yes I have, but the problem with this is the users still need to use 2007 for the meantime, so whatever solution is provided, it will need to work with both 2007 and 2010. Also, with PowerPivot, can the feed be interacted with in a standard worksheet and VBA in the same fashion as if it were imported directly in cells? Thanks – Jon Archway Jul 12 '11 at 08:05
  • Yes - PowerPivot at the end of the day is going to import a "local" copy of the data and make it easy to keep it in sync, so you would still have the opportunity to manipulate with VBA. – BrandonZeider Jul 12 '11 at 12:33
  • So I guess the best solution would be to combine two 1) provide web query interface via some kind of ASP.NET façade for importing data directly into cells through queryable calls 2) provide an OData service for Excel 2010 (future) and VBScript access. Both of these 'interfaces' could utilise the same underlying data access components. – Jon Archway Jul 12 '11 at 15:35