We have created a standard-based architecture for individual client tables that aggregate our transactional data that are optimized to use Metabase. The downside of this approach is that we have to create individual tables, individual security rights in SQL Server, added database connections in Metabase and then API-based cloned and customized dashboards in Metabase. While this provides a high level of data security cross client, it creates a much higher level of complexity in managing reports and scale.
My goal is to be able to define a reporting data architecture that is multi-tenant and that is stored in a single aggregated table and separated by a field value, for example customer_id. That would allow for each customer to be able to see only their slice of the data. With the addition of cascading filters and v37, this is now possible without displaying filter values unrelated to the customer. However, a challenge is that each one of our customers have extended attributes that are unique to that customer. The benefit of having individual reporting tables is that we can customize each data warehouse table with the needed column for that specific customer. If I used a traditional single-table approach it has the potential for the number of columns to grow exponentially and also for these columns to display in Metabase for customers that don’t use that extended attribute.
What I would like to be able to do is use something like a single JSON field to store name:value pairs for these unique extended attributes for each customer. That would allow me to have a single column in the aggregated multi-tenant report database that would store different data or extended attributes for each client.
Is there a way that you can do something like this and still leverage the native question building functionality in Metabase. So far it looks like you would have to use SQL to custom split the JSON. Since these are aggregated tables extracted from the transactional database it would also be possible to use another type/structure/brand of database so the sky is the limit. I am not super familiar with the different types of databases but if somebody could point me in the right direction I will do the research.