I am looking to find the a tool that lets end users with no database knowledge and SQL experience the capability to create reports that provide both raw data and aggregate information.
Requirements are: Oracle database - this is graph like with events(unique table structures), people and locations linked via a single central table. Each event can have multiple child tables.
We need to be able to create a range of logic models on top of this covering specific events and roles that people play, as well as models running between different events.
We need to fully support out joins in the logic models - we have a lot of gaps in child objects and a lot of occasions when we need to see these gaps alongside the data that is present.
Query parameters need to support and/or at multiple levels via an easy to use interface.
~80% of reports will need to contain record level data as this is the most common use of a reporting tool for the organization. Further work is carried out in Excel most of the time, however this is slowly changing but culture is like on oil tanker on this.
This should really be fully web based - both for the creation of the models and running of reports. The database and application are hosted off site and we have a large number of report creators who would need to access via browsers.
It should also support SQL for a small number of power users.
We do already have MS SSRS running against an MI warehouse and OLAP cubes (via SSAS), however these are very poorly designed (I suspect by someone who had never delivered OLAP before) and do not allow the record level reporting. I am inclined towards the SQL Server Report Models but as they are de-supported I am not totally convinced on sticking down this path.
As ever an open source/low cost solution would be a good consideration....