BI Tool - where the "contract" is a hindrance

I live in a world where many of my users have learned SQL (of varying skill levels) and have readonly access to the company data warehouse. In my old desktop days, we had a number of “data mining” tools where a user could write SQL and plug it into a grid within the application. The grid supported sorting, filtering and row grouping, and this became a popular way for our users to dig around and find insights within our data. (There are modern BI tools like Tableau or PowerBI that are great at this type of thing, but my user base gives a collective “meh” every time I bring them up).

I’m wondering how an app like this fits in with the “contract first” world of ServiceStack. In this instance, there is no contract - the user is going to generate the data, and that data will end up in a grid that intelligently renders itself based on the dataset.

I have built a proof of concept service where the Response DTO declares a dynamic named Data, and I fill that with the results of the SQL (which serializes to JSON perfectly). Is that the best practice for implementing an app like this one?

(A slightly different use case might be that the developers will still write the SQL (to make sure it’s fast), and store the queries in the database and give them names. The users could pick the queries from a menu to populate their data mining grid. The benefit to this use case is that new services don’t have to be written for every new result set).

It doesn’t really fit with code contracts if the end user tools are just going to ignore any contracts and only use the JSON Output instead, you could just as easily ignore creating any types and just returning results in an untyped collection, e.g:

object Any(MyRequest request) => Db.Select<Dictionary<string,object>>(sql);

I wouldn’t use dynamic in Response DTOs, it’s still creates an untyped black hole in Service Contracts that wont be able to support many of ServiceStack features inc. many Add ServiceStack Reference languages.

Personally I’d continue to use types in all my APIs since it allows for better defined and more reusable services.

Oh I have over 500 typed services in this project and have no plan on discontinuing their use. It’s just this special ad-hoc query use case I’m making sure I solve correctly. Thank you.

1 Like