Best practices for star schema EDW

Hello,

First time writing in this blog and first time using Metabase.

I wanted to launch a discussion regarding the best practices to use when building a reporting layer on top of an existing star schema data warehouse.

I think the easiest way to formulate my question is to talk about what I'm used to in other BI tools.
The process usually goes as follows:

  1. Cross-functional/cross-department data warehouse:
    We create a data model for the whole organization represented by a multitude of stars where stars share dimensions to create a constellation.

  2. Focus area data marts :
    We create sub-models for particular focus areas (finance data mart, marketing data mart...etc). These models are usually created with OLAP tools (Power BI tabular engine, Microstrategy cubes...etc)

  3. Operational unchanging reports :
    We create operational reports based on the cross-department EDW model for printing and other usages.

  4. Self-Service analytical reports :
    We give access to users on both cross-department EDW model and focus areas data marts to make their own reports and slice and dice as much as they want through self-service BI tools (Excel, Power BI...etc)

Now that I explained the process, I wanted to know if there's a way to apply this with Metabase as the reporting layer. What I feel is missing here is combining "self-service slicing & dicing" and "model" part. I couldn't find the best way to do this without SQL queries.

To get close to the process mentioned, what I did is create a Question of type table that contains all the needed columns from the different tables with SQL.
This question represents a focus area data mart, and uses field filters to give the possibility to slice & dice by multiple Variables or attributes.

The idea was to reuse this "data mart" Question by users to create other questions and to create their dashboards.
The only issue here is when I try to create a second question based on this "data mart", I cannot find the variables to map filters to in a dashboard.

Am I missing something here?
Is there another way to do what I'm trying to do?
Lastly, what's the best practice to organize questions while fully exploiting the star schema and giving maximum autonomy for business users?

Thank you very much in advance for your help. Apologies for the long question.

Hi @ramiouanes
It sounds like you're looking for the upcoming Models functionality in v42: Try out the new 0.42 release
The docs are not finalized yet: https://github.com/metabase/metabase/blob/master/docs/users-guide/models.md

2 Likes

Hello @flamber,

Thank you very much for your answer.
I download the v42 release and tried the models feature.

But I still have kind of the same issue where it is not possible to use Field Filters.

As a reminder, my goal here is to use this "model" and build questions on it and give the possibility to users to build questions on top of it.

When these questions are added to a dashboard, I need to have the possibility to create filters and map them to fields in the model.

But I'm unable to create field filters in the model, I receive the following error:
image

Is there any workaround for this? I want the filters to be dynamic and set by the end user and not by the "developer" of the question/dashboard.

@ramiouanes If you just create general GUI questions and add those to a dashboard, then it's possible to connect filters. Or just let the users use GUI to create their own questions dynamically.

Anything SQL will require a "developer" to do that (or someone who understands SQL and variables).

I don't think I understand what you're looking for then.
Which other visualization tool does what you are looking for? Please provide a link to the specific feature.

Hello,

Never mind, since I'm new to Metabase, I thought it wasn't possible to connect filters to questions in a dashboard if they weren't created through Variables/Field Filters.

So I dived a little deeper in the new version and was able to do what I needed:

  1. Creating a model (Great feature that I'm sure will boost Metabase to a new level in BI & Analytics field).
  2. Create questions based on this model (with the needed expression and visualization)
  3. Create a dashboard with filters, add that question to the dashboard and connect the filters to the question.

All of this works perfectly now with this new version. This model feature and the filters enhancements (Number/text filters...etc) made me pro Metabase now :smile: .

One last question: Is this pre-release stable enough to be used in a production environment at this stage or should we wait for the official release ? When is the official release planned for?

One last suggestion: It would be great to add a model view where business users can check the different relations between the dimensions and facts for better understanding of the model.

Thank you @flamber for the help!

@ramiouanes Great.

Are you asking if a release candidate should be used in production, then my answer will always be no.
Can it be used in production, yes of course, but you have to follow all the issues and PRs, so you know exactly which problems might exists and then it's up to you to make the decision.
You can find the tentative release dates here: https://github.com/metabase/metabase/milestones

I don't understand what you mean about "model view". Which relations are you trying to see?

Amazing! Thank you for the information.

By "model view" I mean some kind of Analytical model or ERD.

Something like the following:

@ramiouanes We're working on adding more insights to the Data Model, and it might trickle down to the Models as well. Though it's difficult to cram too much information into a UI without complicating it too much.

1 Like