If you have been brought up in the Microsoft stack, post MDX, you have most likely been taught the Kimball way of modelling your data: A Star Schema
If you are coming from the Power BI world, you have most likely come across the term Semantic Models, which are also in most cases modelled using: A Star Schema
However, if you have started working with Metric Views in Databricks, the idea of a Star Schema seems a little farfetched. In fact, most would probably agree that your table resembles a One Big Table approach instead.
These to ways of doing data modelling are fundamentally different in a lot of ways. I have linked to the article below by Kurt Buhler before, but if you want the basics on Semantic Models, Star Schemas and One Big Table covered (from a Power BI perspective), it really does the job well!
Why do we want a star schema?
Now I wont get into the discussion of whether Star Schema is better than One-Big-Table in ALL cases. One-Big-Table has it merits and use cases, that is for sure. But in the case of presenting data, one thing that we are all used to rely on at this point, is the idea that we can filter multiple different measures, using the same dimension value.
That is, in simple terms:
If I decide to filter my dashboard using the country_name attribute, this filtering should work on both my Sales and my Budget figures.
A star schema model does exactly this, by connecting each of the fact tables with the dimension - allowing for easy cross-filtering.
Using a One-Big-Table approach, the above statement is actually quite hard to make happen. It requires us to either Join or Union our Sales and Budget tables - or somehow model them to fit into the same wide table. This requires a lot of data modelling that most organizations would prefer not to do. One of my main drawbacks of Metric Views so far is this exact pattern.
Is it actually possible to make a multi-fact Star Schema in Databricks?
Below we will look into the two current options:
Metric Views - which have existed for a while - and have some very limited possibilities as they are today
Dashboard Relationships - which is a new feature that just hit Public Preview that might be exactly what we are looking for
Multi-Fact Possibilities with Metric Views
The current Metric Views implementation of multi-fact relies on one of two things, both of which are really lackluster:
A Bridge Table using all the different combinations of Dimensions - basically a full outer join of all of the outcomes. This works, but it is also really messy once you get past 2 or 3 dimensions. It seems viable for small iterations and specific needs, but once you get to a large-scale implementation it simply does not make any sense. Then you are better off doing the below approach, if you really want to rely on Metric Views.
A pre-computed aggregate of each fact that are then put together through either a join or a union. Basically using CTE's to get to the granularity you want on each side and putting them together. This works as well - no doubt about it, however it kind of translates multi-fact into single-fact. If this is what you want, I suggest you change your modelling practice downstream instead. Again, this is not quite it, but it might save you a bit of trouble.
A fun note here is that if you point a Genie at two Metric Views that are computed for separate facts but which have dimensions in common, it can actually emulate a multi-fact pattern. It does so by utilizing the pre-computed aggregate approach to solve the rendering of the graph.
I have attached an example of this below - along with the code needed to render the visual
WITH actual AS (
SELECT
date_trunc('MONTH', `Invoice Month`) AS `month`,
MEASURE(`Net Invoice Value EUR`) AS `actual_sales_eur`
FROM
`ai_bi`.`metric_view`.`invoices`
WHERE
`Calendar Year Number` = 2022
GROUP BY
ALL
),
bdg AS (
SELECT
date_trunc('MONTH', `Month`) AS `month`,
MEASURE(`Total Budget`) AS `budget_eur`
FROM
`ai_bi`.`metric_view`.`budget`
WHERE
YEAR(`Month`) = 2022
GROUP BY
ALL
)
SELECT
COALESCE(a.`month`, b.`month`) AS `month`,
COALESCE(b.`budget_eur`, 0) AS `budget_eur`,
COALESCE(a.`actual_sales_eur`, 0) AS `actual_sales_eur`
FROM
actual a
FULL OUTER JOIN bdg b
ON a.`month` = b.`month`
ORDER BY
`month` ASCMulti-fact Star Schema in Databricks Dashboards
While Star Schemas might not have been the initial approach, it seems from my talks with various people across Databricks that the idea has always been there. In fact, we now have a pattern that tries to emulate this same pattern: Dashboard Relationships
Below, we will run through it, but before doing so, my recommendations in general would be to prefer the Metric Views in almost all cases. That is because the Metric Views are re-usable across different solutions and backed by code and synced to Unity Catalog, whereas the Dashboard Relationships are local resources that are only code backed, but do not have any physical representation underneath. However, I imagine (and hope!) that the Dashboard approach might be moving to Metric Views later on. Time will tell!
Dashboard Relationships (Public Preview)
The idea of a dashboard relationship is to define the semantic relations at the level of an AIBI Dashboard. This is an important note. No Unity Catalog objects are created in this process. Databricks specifically mention themselves that these are for specific use cases or for testing.
It works by creating a traversable graph of the objects, and resolving whether to use the specified relations (as joins) at runtime. Because of this, you can join fact tables through a conformed dimension, but not directly join two fact tables to each other through many-to-many joins. While it is technically possible to filter one fact table, with a value from another fact table - best practice would be to use the conform dimensions to perform the filtering instead.
The public preview should be live across AWS, but might not have hit your tenant yet if you are in Azure
Let's try it out!
Alright, lets imagine a simple dashboard where we have added two facts; Sales and Forecast and 3 dimensions; Product, Customer and Date. In that scenario, we want both facts to interact with all 3 dimensions.
For this demo I am using the SpaceParts dataset by Tabular Editor. You can find it yourself on Databricks Marketplace!
The process is this simple:
Import your datasets as usual in the Data tab.
Now go to the new Relationships and press "Get started with Relationships"
Click Add relationship in the top-right. Configure the relationship Sources, Keys and Cardinality
Click Save, and now iterate through the remaining relations one by one.
Notice that if you use Tables as your source in step 1, notice that, locally scoped Metric Views are actually created as part of your Dashboard setup. Your final setup would look something like the below image
An important note is that everything is saved in code behind the scenes, meaning that it can be versioned, edited or even copied between Dashboards...
The result of the above process would look like what is shown below, inside of your lvdash.json dashboard file. The important part to notice, if you ask me, is that this very closely resembles the join-patterns that we see in Metric Views today - which is promising for the future!
"relationshipGraphs": [
{
"sources": [
{
"name": "orders",
"datasetName": "9da378a6"
},
{
"name": "budget",
"datasetName": "6a0422e9"
},
{
"name": "customer",
"datasetName": "aa70304f"
},
{
"name": "product",
"datasetName": "ab07059f"
},
{
"name": "date",
"datasetName": "68d079ba"
}
],
"relationships": [
{
"name": "orders_customer",
"from": "orders",
"to": "customer",
"on": "`orders`.`CustomerKey` = `customer`.`CustomerKey`",
"cardinality": "CARDINALITY_MANY_TO_ONE"
},
{
"name": "orders_product",
"from": "orders",
"to": "product",
"on": "`orders`.`ProductKey` = `product`.`ProductKey`",
"cardinality": "CARDINALITY_MANY_TO_ONE"
},
{
"name": "orders_date",
"from": "orders",
"to": "date",
"on": "`orders`.`DateKey` = `date`.`DateKey`",
"cardinality": "CARDINALITY_MANY_TO_ONE"
},
{
"name": "budget_customer",
"from": "budget",
"to": "customer",
"on": "`budget`.`CustomerKey` = `customer`.`CustomerKey`",
"cardinality": "CARDINALITY_MANY_TO_ONE"
},
{
"name": "budget_product",
"from": "budget",
"to": "product",
"on": "`budget`.`ProductKey` = `product`.`ProductKey`",
"cardinality": "CARDINALITY_MANY_TO_ONE"
},
{
"name": "budget_date",
"from": "budget",
"to": "date",
"on": "`budget`.`DateKey` = `date`.`DateKey`",
"cardinality": "CARDINALITY_MANY_TO_ONE"
}
]
}
]As a result of the relation, we can now create what are called cross-dataset measures. These are also found in the Relationships tab, and can be used natively as fields in your Dashboards. They are calculated just like any other measure - but can now actually reference fields across different facts - to be res
With Dashboard Relationships and Cross-dataset measures you can now do a comparison of your Sales and Budget against each of your three dimensions - which is exactly what we want to be able to do!
Check out the official documentation below!
So, Star Schema in Databricks - are we finally there?
Honestly? Almost!
The Dashboard approach is really close to what we are looking for - however it is still scoped to a dashboard instead of being available as a governed Unity Catalog resource inside Metric Views.
Personally, I believe the idea of putting this front and center in the Dashboard experience and the fact that Databricks is talking about the ability to copy it - this should be the foundation of us getting the same type of pattern in Metric Views in the near future.
... and it sounds like from the below comment from Databricks, that I might be right.
What do you think of this new approach? Will it be helpful in your business?
Stay tuned for more news, I will make an update as soon as I see anything new on this topic!
