Based on everything that has come up so far we seem confident that we have 3 ways to access OFN data that we wish to support:
- In App
- Business Intelligence
- API
From the great tech spike work that @Matt-Yorkley we have lots of valuable insights. In particular it seems clear that we need to follow all three routes in order to satisfy our report requirements and that no one of these will be the one that rules reports totally.
So in order to create a Roadmap for Reports we need to understand the priorities for each of the routes.
In App
Looking at the summary of requirements it seems that we have a few ways forward for In App reports:
- Focus on refactoring existing reports one by one, improving consistency and fixing bugs as we go.
- Start the refactor with a new report to solve the pressing enterprise fees and tax issues. This will serve as a trial for the reports refactor and we can bring other reports in as we go.
- Start the refactor by enabling a ‘mega-report’ that solves most reports needs and forms the basis of the reports refactor by rendering other reports obsolete. The mega-report could replace most reports if it enabled:
- Querying orders with and without line items
- Includes all the key search fields from reports - order cycles, distributor, shipping/payment methods, date range
- Sort by Distributor, Producer and Shopper
- Totals and aggregation
- Hide and display a million potential fields
It is likely that an approach of developing a new report will be lower risk as we can leave the existing reports alone while we do it and experiment with a new reports approach.
EDIT - Note that In App reports can be available via the API in the refactored form that Matt has identified with no extra work.
EDIT - To be clear the priority reports problem to solve is Tax… though the questions remain about the scope and product strategy of this report.
So questions:
- If we go for a new report what is the scope of it?
- What are our priorities and scope for the first iteration of updates for In App reports?
- Is there a design task to bring consistency to report field conventions and naming that should be done before a new report?
- Or would it be better to design a new report and then clean up the old reports afterwards?
Business Intelligence
It is clear that we have a number of large users that need bespoke reports to solve their requirements. We’ll never be able to satisfy all our users specific needs when it comes to reports and BI so using an external system built for this is a great solution. Go Metabase!
With not much work we can set up metabase for super admin access on the same DB that the app uses. This is fine for some cases can gives superadmin more of the power they need but it does not solve all the problems.
Ideally we want users to be able to access all the power themselves. But this means recreating the OFN permissions logic in Metabase and that logic is complex. There are infrastructure challenges inherent as well - see below.
So the questions here:
- Where does the Metabase roll out sit in the reports priorities?
- Does it make sense to create super admin only Metabase for each instance as they need it? Eg France, Aus, Katuma all have their own Metabase on their app DB.
- Would it be safer to create duplicate DBs for all instances using Metabase?
- Are there bigger gains if we instead focus on aggregating duplicate databases so we don’t have to duplicate work in Metabase?
- Or is it a higher priority to get the OFN permissions system into Metabase to enable users to access?
- And how does this sit alongside the other reports priorities in our Roadmap?
API
From the investigations is have become clear that just ‘putting reports on the API’ posed problems with performance, both in terms of our json rendering (its slow) and in the complexity of aggregation. (EDIT - In reports refactoring Matt has shown that it is technically possible to put all reports on the API efficiently). If we need to make an end point for every report then the actual data gains found might not be all that worthwhile.
The primary data extraction needs via API are Orders with and without line items. The API already has endpoint for these, imperfectly. The great work done here suggests that almost all the search parameters we could dream of will filter correctly. However do have a bit of a mess in the responses - in particular adjustments, fees and taxes are a mess. We know that we need Bye Bye Spree before we can refactor the adjustments code.
There is much to be done to understand the data structure that we want for our json outputs. Fortunately the API is already out of Spree (hooray!) so we can being this work as we wish.
Questions:
- Where does the API work sit in our priorities?
- Is there a design process that should be starting earlier to understand our real needs from the API?
Infrastructure
As data extraction and reports put a high load on servers there is much talk of replica databases. One proposal has been to have all instances join together their data on a single replica DB. This offers huge potential advantages when it comes to integrations, allowing all instances to benefit from Zapier integration, Metabase reports and other API integrations without reinventing or needing their own tech team.
So, questions:
- So from a product perspective is this a direction we want to go?
- Do we want to prioritise this before other work?
- What else do we need to know from a technical perspective that will help to position this work in the priorities?
Shout out to @Matt-Yorkley for the absolutely awesome technical investigations that have helped us understand where to go with reports!
Seems to me that we are now at a place in which we want to create a Product Roadmap for the reports work so that we can understand the priorities within and between the three streams and start to plan the work.
This is all intended to be food for thought for the reports meeting on Tuesday 23rd June. See y’all there.
performance challenge)
