I'm looking to build some BI Analytics into my software (NodeJS, mySQL).
Thus far the analytics for clients have been done manually with Excel and some fancy pivot tables. Clients have asked for these analytics to be built directly into the software so it stays up to date.
Thinking about this, if I tried to write SQLs to mimic what my excel pivot tables are doing, it'd be a nightmare, probably very inefficient too. So my questions are:
1) Is there a data-cube library or product that I could use? Something where I can make interesting queries on my data relatively easily. The dataset isn't very big (maybe 50megabytes of text), but it is quite complex (e.g., it'd take a skilled analyst a few days to build the pivot tables in excel from the raw data set).
2) Any guidance on what I should be thinking about when designing the architecture for data extraction? For example: Should I be extracting data from the same database that data is being inserted into, or should I have a separate database (and maybe separate server) that only serves analytics? What are the elements I should be thinking about when making this decision?
Thanks!