SharePoint, WANs and latency

--amadeus--

Hi,

I'm not sure if this is the best place for this question so Mods please feel free to move...

I am developing a series of Balanced Scorecard solutions for a global client. For lots of reasons (ease of development, replication, local control and ease of ongoing maintenance) it's being developed using MS Excel and Access.

Data is stored in a flat Access database and a spreadsheet is used as the client interface - drop downs and button in form controls trigger VBA to create dynamic SQL that retrieves data, tabulates it and charts it. It looks surprisingly slick and works well.

My issue is that for cost reasons corporate travel has been put on hold and I need to roll this out to 5 sites in Europe, 1 in China and 1 in the States. I have access to a share on the local networks but I am getting timeouts because of network latency so cannot develop or test directly on teh networks.

Sharepoint has been suggested as a solution. I haven't used it in years and never in a geographically diverse set up like this. Is it a potential solution? Does latency affect SP as well? Are there any other solutions people can think of?

Thanks

stevenmu

Is the issue that there is a seperate database/spreadsheet for each region which is stored locally and you have trouble developing against it centrally, or that you want the database/spreadsheet stored centrally and the regions have trouble accessing it?

If it's the former the obvious answer is to use some kind of remote desktop solution so that you are in effect accessing the db/spreadsheet from its local environment.

The second solution would be a bit trickier, and yes SharePoint could be a help here. There's good whitepapers from Microsoft for running SharePoint in a WAN configuration such as this, and good blogs and community info such as this, but they're probably a bit full on for your needs.

There are two relatively simple configurations that may do the trick for you.

The first is to just do a simple basic single-server install. Just install SharePoint on a server in your central location, configure it for Blob. Upload your spreadsheet/database to one or more document libraries. You'll also obviously also need to update your spreadsheet to work with the database being held in a SharePoint library.

At a basic level this means the spreadsheet and database will be sent to the client software using IIS which will enable compression and caching so should help a lot. Of course this could be done with any kind of web app to provide access to the files, but SharePoint is as quick and easy to set up for this as anything else.

It also allow for a slightly more complicated approach where you would then add web front end (WFE) servers to the farm for each region. These would be front end servers only, connected back to the central SharePoint farm. This means communication from the clients would be to a local web server, and the web servers should cache the blobs (files), so communication should be pretty quick.

Because you'd just be using the basic content management capabilities of SharePoint the free versions should do you fine (they are free to download and user licenses are included in Windows Server licenses). The current version is SharePoint Foundation Services 2010, which requires Windows Server 2008 R2, the previous version is Windows SharePoint Services (WSS) 3.0, which will be fine if 2008R2 isn't available.

It's pretty easy to download and just do the most basic install possible as a test. If it starts getting more widespread use you'd need to do some planning and a more complicated deployment, but should be only take a day or maybe two to try out and make sure it helps your issue.

stevenmu

I've just assumed btw that you're not in any way willing to rethink the work you've done and you're purely looking for a way to solve the latency issue.

But since you've mentioned SharePoint, Access/Excel isn't really a very good way to do a distributed multi-user system like this. On the other hand distributed multi-user business intelligence is one of SharePoint's core pillars. Depending on exactly what you are doing, recreating your required functionality within SharePoint properly may not take very much time at all. Just to throw out some ideas of what SharePoint can do.

SharePoint 2007 includes Microsoft PerformancePoint, which is an excellent balanced scorecard system, and SharePoint 2010 has it completely integrated.

SharePoint can act as a data store for your data, SharePoint lists can be customised and used as data tables. They can be linked to Excel spreadsheets or Access databases, which in turn can contain charts, pivot tables, reports etc.

SharePoint has Excel Services, which allows you to host Excel spreadsheets in SharePoint, users can access and view spreadsheets (or just particular parts of them such as a chart) through their browser. These spreadsheets can contain data connections to external data sources.

SharePoint can integrate with SQL Server Reporting Services allow you to host/interact with Reporting Services reports via SharePoint/browser and to launch the SQL Report Builder.

SharePoint can (via BDC/BCS) connect to and present data from other line of business systems.

There's other stuff too, but they'd be the main BI tools within SharePoint. Obviously there'd be a bit of a learning curve there, but there is also the potential for some great solutions.

--amadeus--

It's the former - separate files that I cannot access.

IT suggested something like a WebEx as a remote solution allright - but I didn't take it too seriously initially. I'd need a dedicated PC in the remote location and someone there to initiate the session and it would be dog slow. I have a horrible feeling that's what I'll end up with though! The real irony is that over a day or two they will waste the value of the air fare and hotels in dead time while I stare at a screen waiting for it to catch up...

On the other stuff yea I have heard great things about SharePoints BI tools. The current situation is that one site had a local solution that they asked to be replicated exactly in first one, then another Irish site. That went to the States and now the concept is being expanded and pushed further afield. It's a Fortune500 company and Senior VP approval has been given for this specific design - they probably would grumble if I turned round at this stage and said "wait, I have a better idea!" Once it's all installed and working I can do a demo in SPoint for them and see if they bite...

Thanks for the help, much appreciated.