I have been trying to find the right design/toolset which can help our business users. They have enormous data in excel files which they push through to various excel formulas nearly 400+ and calculations usually and mostly on a row by row basis and vlookups from other sheets. In trying to design a system for them, i want to enable them to define the business rules so that we can stick to designing and implementing the system, which will change state according to the business rules defined ? What current stack of technologies would be able to support this ?
The basic requirements to point out are like
Should able to handle millions of rows of data and process them.(Millions rows of data need not to be processed at same time it can be processed sequentially)
Convert existing excel formulas into some rules which Business user can edit and maintain (These Excel formulas are quit complex. Here formulas deal with multiple sheets and decision based on row data from multiple sheets uses VLOOKUP to MATCH and INDEX to get corresponding matching row in different sheet. )
I am planning to use Drools and Guvnor for it.. What do you all suggest? Is there any other better option? Even in Drools my major concern is if Business user will be able to create the rules as easily as he can do in Excel..
The "millions" won't be a problem for sequential processing, if there's a reasonably fast way of input and output of the data itself.
Lookups into other sheets can be transformed into sets of static facts, loaded once when the session is started - just a technicality.
The transformation of the Excel formulas: Ay, there's the rub. The Business User (BU) will not be able to transform them off the cuff. Rules aren't any more complicated than Excel formulas, but the BUs will need some formal training, ideally tailored to the subset they'll have to use. This also applies if they should use Guvnor for editing the formulas, which is just a more convenient writing tool but no silver bullet.
BTW: Excel formulas do require a certain amount of technical knowledge, even if their domain doesn't have that look and feel.