Complex question; looking for a general direction...
I need to provide a data validation tool that will give users the ability to select a category of data, then expose a table for export that contains one row per event. The validation tables are not related and should not become associated with one another.
For example, consider two kind of unrelated metrics: 1) customer purchases and 2) operating expenses
A separate dashboard provides kpi indicators like July 2019: 155 purchases and $25,000 cost of labor. This is loaded into a table of aggregates by metric and category and displayed on one screen with many other such metrics. Slice and dice is by time dimensions or geography.
A user looks at the screen and need to have the source data to dig into the 155 purchases and do a deeper analysis of that activity.
goes to my new dashboard and selects purchases and get's a list of sales transactions. Exports to excel or maybe creates their own sheet in qlik
Next that user wants to understand the basis of the $25k labor. Selects labor and gets a table of daily timesheet entries.
This does not fit a single associative data model. The aggregate version was acheived through a link table approach to generalize the metrics into values that could be summed and filtered. This was aggregated in a datamart due to performance issues (the underlying data is many millions of rows over time)
At that point, the decision was made to separate the "drill downs" to some future app to be developed. The future is now.
Am I correct to think really the only sensible approach is to have separate apps for each data source? So we would have a sales transactions app and a separate app for labor charges? And somehow link to these through a front end app that helps navigate the user to the domain specific app?
