I think we are in a pretty standard situation. We have built a fairly complex data model with approximately 100 tables and many relationships to store the data related to this complex business problem (intranet system with 100’s of users). There is of course a UIPath and since the application has been built we have gone down the traditional path of offering some standard reports and then even a CSV download of data (based on those standard reports). After lots of usage of the system the user-base wants more and more complex reports to the point where it doesn’t make sense to create these “one-offs”.
The obvious next step (at least in my mind) is to put the creation of the reports in the hand of the business user. This is either something like Crystal Reports, SQL Server Reporting Services or BI tools like Tableau. Having not had much experience with many of these tools I am at a loss. Moreover, I struggle with the issue of the complex database tables and believing that the user community can understand the complex relationships of the tables in addition to opening up our schema with all of its database-only computational fields that should never be seen by the user.
I believe I have a few choices here (in terms of data architecture and in terms of direction in general) and they include:
Simplifying the schema to the business user via an API or more simplified “view” of the database tables - which is better for the business user but requires upfront work and maintainability.
Letting the business user see all of the tables - which obviously makes the maintainability easy, but will require documentation, training, explanations, etc.
Other BI choices (which frankly I’m not familiar with) - OLAP cube, etc.
I’m looking for some feedback on experience in this area and suggested direction in terms of data architecture and/or product usage.