0

So we're having a rather heated debate at work as to what would be the best practice going forward while refactoring our reporting system. Most of us have some strong opinions one way or the other, but we are all also mostly failing at articulating what we feel would be the best approach. I'm mostly looking for some input to see what approach would result in the best solution overall for us.

Our current system is relatively straightforward, but it's comprised of quite a bit of spaghetti-code. We have roughly 10-15 dashboards/reports with about 20 metrics on them each. Some of these metrics are shared across reports, but a lot are unique to each report. As such, there are some report filters that are common on most/all reports, and other filters that are specific to a given report.

In our controllers, we have different objects that accept the report parameters, based on what filters are present. So, for example, we have an object called QueryParams, this object contains filters for StartDate, EndDate, Company, and Division. On another dashboard, we have a different parameter object that accepts all of the aforementioned parameters, but also includes Provider and Gender. And on another dashboard, we may have almost all of the same parameters as before, but instead they don't need the start/end date parameters.

This has lead to our parameter objects not really being used passed our WebUI project. Once we get to the logic and repository layers, our method signatures are simply looking for all the filters. So rather than GetVisitCounts(QueryParams) we have `GetVisitCounts(StartDate, EndDate, Company, Division). This isn't too bad, except for the fact that these filters get updated at a non-trivial frequency. When it comes time to add some new filters to a report, developers find themselves going through each layer of the project and each method to update the signature and underlying logic.

Once you get to the repository layer, most of the parameters are handled the same way. Some SQL is written and where clauses are generated based on the parameters passed in to the method. This SQL is pretty much entirely handwritten in every single method. In some cases the where clauses can be dynamically generated as the same tables are being used with the same aliases, but it's rough to make this call for all methods as there are a lot of one-offs.

Now we find ourselves needing to add 3-4 filters to all of our dashboards, and this requires us to go through and update everything. There are some proposed solutions for simplifying everything, but none of them seem super ideal. One such idea is to create a master QueryParamsobject that houses every possible filter across every possible report. This object would just be a super class that acts like a data store. It'd simplify the method signatures, but actual implementation of the repository methods would still require everything to be generated by hand.

A second approach was to use QueryParams as a base class, and built subclasses that add additional parameters as we go along. Using this approach we could build some sort of adapter that attempts to serialize the parameter object into a SQL where clause. However, this is not ideal either as there are many one-off cases where the tables are following non-standard joins, or we're only using a couple of the parameters for some reason and we can't include the parameters in the where clause.

I'm open to any potential solutions that could simplify our current approach, even if a near full rewrite is necessary.

JD Davis
  • 1,367
  • 1
  • 15
  • 23
  • 2
    Possible duplicate of [I've inherited 200K lines of spaghetti code -- what now?](https://softwareengineering.stackexchange.com/questions/155488/ive-inherited-200k-lines-of-spaghetti-code-what-now) – Greg Burghardt Jun 14 '18 at 16:50
  • 6
    @GregBurghardt: not even close. This is a restricted, focussed problem from someone knowing the code base. That other questions is about exactly the opposite. – Doc Brown Jun 14 '18 at 18:33
  • JD, I guess most of those filters can be handled by adding an addtional `where` clause (or an additional AND condition to the existing where clauses) to the SQLs? – Doc Brown Jun 14 '18 at 18:39
  • @DocBrown that is correct. 99% of the filters are simply an additional `AND` condition. – JD Davis Jun 14 '18 at 18:43
  • Why don't you just pass around a WHERE clause that contains the conditions you want? – Robert Harvey Jun 15 '18 at 14:49

2 Answers2

1

Stop writing reports in code and off load everything to a data warehouse with its own tools.

Reports often change, are hard to maintain and become obsolete quickly. Unless you have a highly technical report with unchanging requirements its pointless to treat it like code.

Business reports tend to be 'investigative'. People want to find something out or justify a decision. That means fiddling with the fields and parameters. Even static reports tend to be exported to excel and fiddled with before they are used.

Ewan
  • 70,664
  • 5
  • 76
  • 161
  • Unfortunately, while we are doing our reports against a data warehouse, our analytics department is adamant about us in-housing our own reports. They currently use Tableau, but over the last couple of years they've been having us write our own reports using ADO.net, shaping that data, and then displaying it using third party charting tools. – JD Davis Jun 15 '18 at 13:38
0

I guess the best you can do immediately is to provide a reusable component which can generate the additional SQL clause from some dynamic filter criteria. You will still have to call that component at every location where a SELECT query happens, and combine the clause with the existing SQLs. That is nothing you can do easily in a generic fashion.

How to design the component exactly is nothing we can tell you right here, that is something you have to find out for your context. Implementing some proof-of-concepts or some prototypes is probably a good idea to find out what works best.

Moreover, I would recommend not to intermix your filter problem with the problem of having too many different, manually written SQLs in your code. The latter is something you cannot solve quickly in a week (assuming a codebase of a reasonable size). That is something you can only achieve like eating an elephant: one bite at a time. Introduce better layers, refactor to more clean and SOLID code, use code generators and/or maybe an ORM, use code reviews and and make sure your whole team is on-board with this. Then the code base will become more evolvable over the years.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565