Well, I'm on the verge of this project that really puzzled me. The situation is, in our office, we have a department that collect data about the corporate performance indicators from all across the departments and branches.

The method they used before is, they distribute a macro Excel file, which include some worksheets with certain tables to fill by all the departments and branches. Each worksheet was referenced to certain indicator value/score. When filled out, the indicator score will be automatically filled and evaluated. So they came across with many excel files to work with.

Now, we want to move that process into an application, which takes us to this problem. The indicators and the worksheet format itself was subject to change every year. So to incorporate this very dynamic structure, we design the db to store every rows, cells, and formulas, on the database, and come up with a very complex design.

Well my question is, did we do this OK and efficient? I mean is there any suggestions to this kind of problem? We are considering our option to use Google Docs API for the spreadsheet and extract the data from it too. What do you guys think?

closed as unclear what you're asking by Tulains Córdova, GlenH7, Dan Pichelman, user40980, Ampt Feb 6 '15 at 4:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    I guess we'll need some (a lot) more details. – xlecoustillier Jan 28 '15 at 9:16
  • 2
    Without knowing what exactly changed and what types of query run on this data this is quite difficult to say (also other information like data volume etc). Maybe some kind of schema less store (nosql document store like Elasticsearch) would be easier to use. – thorsten müller Jan 28 '15 at 9:18
  • instead of working on worksheets on the excel tables, they want the departments to work on the application. So we have 5 tables to store a excel-like structure, 'table', 'row', 'column', 'cell' and 'formula' (because some cell value has to be computed from others with a formula). While an entry in the table 'cell' will store a value which is an intersection of a 'column' entry and 'row' entry. Is this efficient? Because we have doubts about the design – caesardo Jan 28 '15 at 9:34
  • 1
    What's the goal of moving to an application? Greater efficiency? Greater control? More standardization? – Walter Mitty Jan 28 '15 at 10:37
  • better integration and seamless data supply for BOD dashboard – caesardo Jan 28 '15 at 10:46

No not really.

The problem is that with every variation in the spreadsheet you end up with a different interpretation of the indicators.

So given the same set of inputs each sheet will come up with different recomendations.

Either determine what is the "correct" set of indicators and formulas to be used in the application.

  • or -

If you really want to leave well alone then excel has a perfectly good ODBC interface which would allow the application to read the original data. Alternatively the POI java library can read spreadsheet data directly. Trying to model the dynamism and general weirdness of a spreadsheet is just going to cause you problems.

Not the answer you're looking for? Browse other questions tagged or ask your own question.