Preemptively know available JOIN paths #10029
Description
Is your feature request related to a problem? Please describe.
I am trying to create a sort of DIY Reporting Center for my clients at the company I work for. Basically, a version of Cube Playground where they do not have access to editing their credentials, and instead have to input their creds they got somewhere else.
For non-technical users, it would be nice to have an UI like the Playground, however, one UX thing the users miss is that they can add whatever tables in the model, and they only get feedback on whether or not the selected dimensions/measures make sense and JOIN together properly after adding them to the query. This is why in the Playground we can pick tables and columns that do not JOIN properly, and only after selecting them we get notified that:
It would be nice, for the users, to instead block off prematurely any and all tables they cannot reach (JOIN path not allowed), allowing us to "gray out" their picklist options in our interface. This way, they wouldn't even be able to select any columns from dim_courses if they have already selected dim_tenants (and there is no valid JOIN path available), as we could disable the selection on the frontend.
Describe the solution you'd like
This sounds like a graph problem. And one that would be rather interesting to work on. I thought of having some sort of crawler within Cube itself that would read and parse ALL the Cube .yaml definition files (or JSON), and traverse it as a graph taking note of what tables are reachable from where.
Since that is static for every Cube deployment (only changes if you add/remove Cubes and JOINs between Cubes), this can be precompiled during Cube startup, and should remain static.
Then, an endpoint such as cubejs-api/v1/join-paths could return this static graph as some object/JSON that can be interpreted, and would basically serve as a map that allows us to preemptively check which tables are available to be reached from the tables I currently have in my query.
If precompiling is not an option, at least letting us pass a query parameter to that cubejs-api/v1/join-paths endpoint which could return a list of tables it can reach, and ones it can't would already be enough.
Describe alternatives you've considered
The Playground does not solve this issue in the way it is proposed. The way it is handled in there is that AFTER you pick a table, it triggers a request to cubejs-api/v1/dry-run to check if the path is valid or not, and based on that it displays an error message or not.
A very rough alternative would be to try triggering tons of requests to cubejs-api/v1/dry-run for each available table (that you can fetch with cubejs-api/v1/meta). This will trigger hundreds of requests to Cube which is not ideal.
Additional context
I don't have time to work on this at the moment and in the near future. I could try to pick this up as a challenge when I get some time off. I'm creating this feature request so that I come back to this later if it's not worked on.
I will try to edit with more information and diagramas once I get some time to think about the problem further.