Workload Balancing

Spammeh · 20 Mar 2006 at 12:35

I'm having problem working out a structure for a problem I'm working on. Here's a brief explanation.

A university has to work out the workload for each member of staff within each school. This is currently done using a spreadsheet using macros locally. What they university wants to do is to have each school work on their spreadsheets locally and they sync them with a centralised database. Each school within the university frequently 'borrows' lecturers from other schools to run certain modules. For the spreadsheets to be syned, this would require the user to logon via an LDAP server, and authenticating against their global username and password.

My problem is that I'm being asked to fully centralise the spreadsheets and I basically need to find a solution that would allow me to query the database and sync it (will obviously need to ensure i dont get inconsistency of data if 2 or more people are working on their spreadsheets at the same time).

At the moment all I have come up with is this:

User >>> LDAP server (auth username + pass) >>> SQL server 2003 holding spreadsheets.

I did suggest using ado.net originally (as i will using .net technologies for this project) due to the fact i thought that the load would be heavy on the server (only a max of 30 spreadsheets in the uni, with 2-3 updates per day) and the cost of upgrading for univeristy to utilise sql server 2005's new features is too great.

Is there anyone that could help me a little with this, structure wise, and what I would need to use to complete an effecient system?

UPDATE: A friend has suggested using oledb instead of ado.net for my querying to the database, do you think this could work?

ByteJuggler · 20 Mar 2006 at 13:40

I think the hard part will be dealing with concurrency/synchronization problems... You need to think through in as much detail as possible how you see things working, and try to test your ideas out as well to make sure they're feasible if possible. Also you need to check that the constraints/caveats applicable to whatever solution you come up with is acceptable to the users (I would expect there might be some.)

OLEDB is essentially a low(er) level API, on top of which ADO and ADO.Net is (or can be) layered. Unless you're really having serious performance problems due to the overheads introduced by ADO/ADO.Net (highly unlikely I would've thought in your case) I would not recommend you bypass ADO and use OLEDB directly, as you then also lose all the benefits brought by the ADO.Net object model/classes.

Are you going to be implementing this somehow all inside the excel workbooks with VBA or what?

BTW, there was no SQL server 2003, only SQL Server 2000 and SQL Server 2005, so were you perhaps referring to Office 2003 or Windows Server 2003? What exactly will you be using where?

Spammeh · 20 Mar 2006 at 14:12

If oledb would do the job, what are the big adv. of using ado or ado.net for a problem such as this? The server won't have a heavy load on it at all, as most of the work is done locally.

My big problem is solving inconsistencies of data within the spreadsheets as multiple people may work on the same spreadsheet and haven't ensured they have the most upto date version.

Essentially, all the current features within excel workbooks need to be kept, but but 'ported' over to a centralised point where it can be accessed by all.

Whoops about the typo, the server runs sql server 2000, and the excel workbooks are using office 2003 pro if that makes any difference. All macros are coded in vb6.

Spammeh · 20 Mar 2006 at 21:25

anyone else able to help with this problem? No worries if you cant

ByteJuggler · 20 Mar 2006 at 21:58

Spammeh said:
If oledb would do the job, what are the big adv. of using ado or ado.net for a problem such as this? The server won't have a heavy load on it at all, as most of the work is done locally.

To use an (exagerated) analogy to illustrate, your question is like asking "If Assembler language would do the job, what are the big adv. of using VB or C# for a problem such as this?" Hopefully the answer to that question is obvious: assembler is obviously faster/more efficient (if coded properly). But, it's also a lot more cumbersome to code, bugs creep in more easily - in many cases you have to a great deal more work to get the same effect as only a few lines of high level code, etc. etc. Which is some of the reasons why assembler is not used as a general language for problem solving and only reserved for cases where it really matters, today.

Similarly, while OleDB gives you performance benefits, it also somewhat burdens you with a lower level API and likely a slightly bigger workload to achieve results, and not providing the same level of support/features of higher level API's/class libraries.

Higher level API's like ADO or ADO.Net give you more "building blocks" which relieve you from some responsibility but in exchange for a few CPU cycles. ADO.Net has lots of support for "disconnected operations" in keeping with its distributed focus, in that respect it generally fits with the distributed disconnected model of operation that your system will (apparently) have to operate under/support. However, you can't use ADO.Net from within Excel's VBA, though I think you can use VB.Net with Excel 2003 if you have VSTO (Visual Studio Tools for Office), but I'm not familiar with that unfortunately so can't really comment.

You'll probably also (in any case) likely have some difficulty using OleDB with VB - the OleDB API is essentially a set of naked COM interfaces which uses features like pointers which doesn't easily translate into VB6/VBA space.

Here's a few links I've just dug up for you to consider:
http://msdn.microsoft.com/library/d...y/en-us/oledb/htm/oledboverview_of_ole_db.asp
http://msdn.microsoft.com/library/d.../en-us/cpguide/html/cpconoverviewofadonet.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadofundamentals.asp

Spammeh said:
My big problem is solving inconsistencies of data within the spreadsheets as multiple people may work on the same spreadsheet and haven't ensured they have the most upto date version.

Essentially, all the current features within excel workbooks need to be kept, but but 'ported' over to a centralised point where it can be accessed by all.

This sounds like potentially a rather big project... what are all the "features" that need to be preserved?

You need to decide how you want to manage the concurrency issues. I suppose one way to think about it is in terms of a locking strategy, conventionally either "pessimistic locking" or "optimistic locking". Each has benefits and drawbacks. Another question is locking granularity - what gets locked (how big is it) and for how long? E.g. do you "lock" the entire workbook or just particular sheets, cells, or what? And for how long? This will depend on how frequently workbooks "change hands" and the types of work done on them I guess.

Here's links on locking i've just dug up: http://minnow.cc.gatech.edu/squeak/2634
http://builder.com.com/5100-6388-1049842.html
http://www.agiledata.org/essays/concurrencyControl.html

Spammeh said:
Whoops about the typo, the server runs sql server 2000, and the excel workbooks are using office 2003 pro if that makes any difference. All macros are coded in vb6.

OK, so the Excel workbooks must collaborate and store their contents into the sql database? What features are in the current Excel workbooks that are Excel specific, out of interest?