Generating reports from large datasets

Question

I'm working on an CRM type application(.NET, SQL Server) that needs to generate reports from large datasets, millions of database rows in a dozen different tables with a lot of aggregation and logic. The reports are currently generated by long, complicated stored procedures with lots of joins, temp tables and logic. These reports have to follow a third party specification, and the way they are currently written often produce incorrect data. There are also performance issues as the reports take about 10 times longer to run than they should. I'm faced with either re-writing the stored procedures or finding another way of generating the reports. I have two main requirements, speed and accuracy.

SQL seems like the best choice for speed. The data is relational and it already exists in a database. The downside of SQL is that the reports are very complex and having it all in one/several giant queries makes it hard to test different pieces of the logic.

Doing the calculations in C# seems like a better choice for testability/accuracy, but I don't think it would perform very well due to the memory requirements and the slow nature of procedural code.

What makes me lean towards a non SQL solution is that sometimes our customers think the reports are wrong when it's really their own bad data. Customers will often expect a person record to show up on a report, but according to the rules of the report that record should not be included based on the values of certain columns. Because the logic is so complex and our reports are written so badly we usually can't tell customers that their data is bad and why until we spend a day or two tracing that record through the logic of the report query. It would be nice if we had a rules engine that we could run on any given record to see exactly what data columns are causing it to be filtered out of the report. I'd rather not implement the report logic twice, once in the report query and again in a validation engine.

What is the best choice here? Are there rules engines that can operate on large datasets? I don't know much about big data, but I've heard about map/reduce and Hadoop. Would something like that help? What about a functional language like F#? Any other options?

Answer 1

I deal with a very similar situation where we take a large amount of relational data, filter and scrub it down to a smaller subset, and then create the highest level aggregates we can to output data that is both fast and "accurate". Being accurate is only as good as what goes in. Unless you filter out and identify the bad data going in, the chances of bad data coming out are pretty high.

We also use SQL as the heavy lifter and this is where we run large data / aggrgate builds with stored procedures weekly or nightly. This makes it easier to return fast reports via our web portal or email subscription service.

There is a lot of things you can do while still using SQL and not completely up rooting everything.

Suggestions While Using SQL:

1. Identify data subsets that can be pulled for a particular client (Filter the Data Well)
2. Make sure your indexes are optimized and configured correctly (one bad index can ruin everything)
3. Take advantage of your version of SQL and the machine hardware (make sure to use sql parallelism and try running multiple modules at the same time to increase throughput)
4. If all else fails, make sure that any query hitting a table that is being written too uses a with(nolock)
5. If you have to redo the process, modularize each calculation required to the highest aggregate required and store this information separately so that you can pull in each of the required aggregates to produce the final results
6. Think in steps and try to create a low-level aggregate that can be used to create the next level and so on.

SQL server is fast and nowadays can perform a lot of the functions you would normally try in a standalone application. So unless you have very little hardware running SQL and there is no room for expansion, I would just use SQL for the calculations. The biggest benefit to a standalone application doing the calculations is that it will be better to maintain code wise and you can potentially run multithreaded processes on one or more machines. You also do not need to get more SQL licenses.

I can definitely say that if your data is already relational, putting it into non-relational database structure will just make things harder. Another option besides scaling out your SQL instances is to scale out with another relational database. Postgres is extremely powerful and can compete with MSSQL when it comes to processing speed. My company has started converting over from SQL and we have been spreading some of the processing power across different compute nodes using Postgres-XL.