There Should be a Way to Define Joins at the Business Schema Level

[IamtheLaw382]

Some of the new business schema options are really useful!  The PostgreSQL option, in particular, is fantastic!

Unfortunately, developers are somewhat limited in what we can do with these as they basically come into the business view as a flat table without any way to define joins to other tables.  The problem is that if we want to use this in conjunction with other data within a dashboard, it becomes very difficult to filter the entire dashboard as any insights using the postgreSQL business view won't be affected by the dashboard filters (or vice versa).  

I think that having some sort of an additional layer that basically allowed you to define join paths without having to edit at the physical schema level would be very beneficial and also allow for more robust security. 

[Adetweiler]

Yes! We are struggling with the concept of recreating all of our business views as flat tables!

[JoeM]

Perhaps our new Spark SQL Views are the solution for you. Check it out in action in this video. I'm sorry about the audio quality since I recorded this while traveling.

[IamtheLaw382]

Thanks Joe, I think this may be the solution.  We haven't enabled the spark views yet so it's hard for me to test.  If I'm understanding correctly, though, filtering the dashboard will filter the spark view as well provided that the spark view is dependent on the prompt in the dashboard? And this is distinct from the PostgreSQL view?

[JoeM]

Yes - effectively filtering any underlying view that a SparkSQL view leverages, will pass it through the query. The example I showed previously could be a little confusing because I was showing both the ability to filter based on underlying business views and the ability to add a presentation variable into a script. It was particularly poor since I passed a variable that acted as a filter. 

Here, I've removed the presentation variable example and shared a side-by-side comparison.

I should warn that while this capability exists, it's, in effect, a live spark query. So, instead of making a massive flattened dataset, this capability is focused on generating aggregated results. To that end, I would create a Spark SQL view to join data to create an aggregated result set that may be specific to an insight within your dashboard. If you need views to be joined across a larger number of views, perhaps consider using an MV in the schema.