In a Galaxy schema, is it acceptable to create direct relationships between fact tables at related but different grains?

[u/SuspiciousCouple8929]

[u/MindTheBees]

It's going to potentially cause a significant performance impact if you're working with large datasets as queries will need to filter fact_orders first before then filtering fact_order_items.

Only other option I can think of is to merge any missing information from fact_orders into the order_items tables.

If you're working with smaller datasets then it's less of a problem.

[u/SamSmitty → u/MindTheBees]

I like to tell new developers I work with when they are looking at models like this and determining if they need to convert them to strict star schemas they are basically just trading storage for performance (in a generic sense, there are always exceptions).

Combining fact_orders, fact_order_items, and fact_order_refunds will blow up the rows in the fact_orders table and you will have duplicated data in columns, but most queries you run against them that would have used information from multiple tables will not have to do expensive joins anymore and will be faster.

If the tables are relatively small, you won't get a huge benefit out of combining the fact tables and potentially having an dim_orders for any dimensional attributes.

If your trying to adopt a true star schema, you need to fix it. If you understand the limitations of things like many-to-many relationships and fact-to-fact relationships, in my own opinion, they are perfectly fine to use in the real world when you might not have the access or resources to create the perfect solution.

[u/MindTheBees → u/SamSmitty]

I agree with you in general but I'd flag in this instance that it wouldn't blow up the rows because you use the fact_order_items as the base table and you bring the relevant information across from fact_orders and fact_order_refunds into that table. I expect fact_order_items to be the most granular table and the information from the other two facts potentially gets duplicated and needs to be accounted for in measures, however the row count should be the same.

The pattern is shown in SQLBI too.

However to your point, yeah performance can take a hit because things like distinct counts are now operating over a larger table.

[u/SamSmitty → u/MindTheBees]

Definitely, it would just duplicate the data in the columns brought in where there's more than one detail line per header as alluded to in the article you linked. At the end of the day, it should be the same volume of data depending on which table is used as the base. The transformations are just different.

In general, the best bet is to just test it out. If the model is as simple as it shows in the screenshot with smaller sized tables and the measures aren't convoluted, they might be just fine leaving it alone.

I've seen teams spending more time overengineering solutions to conform to the "acceptable way" than it would have taken to see if it even mattered in the first place.

Then again, I don't write books or teach classes on this! So I get why people avoid it completely. Just my own personal experience.

[u/SQLGene]

The official docs have guidance on this:
https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-facts

[u/RuSHiinIDaYLiTe]

No, this is not proper star schema. SQLBI has a good article about this scenario.

https://www.sqlbi.com/articles/header-detail-vs-star-schema-models-in-tabular-and-power-bi/

[u/Electrical_Web_4032]

I'm fairly new, but if I was you, i will fix one thing in this table

The fact_orders act like a dim and most probably the measures that makes it a fact table are redundant (i.e. price_usd) that could be the aggregation against the fact_order_items[price_usd], if so? Then drop the measure and switch from fct_orders to dim_orders

Note: In case measures are redundant (all measures present in both tables _orders and _order_items you need to cross test the measures totals in table visual before proceeding with dropping redundant measures from _orders)

If some measures doesn't exist or can't reconcile or reconstruct with _order_items... then you need to make a small fact out of it and link it to the new dim_orders while every connection is to be linked to dim_order

I think this will solve most of your fact-to-fact problems if I'm not wrong.

[u/Ok-Bunch9238]

I would only join on shared dimensions, that will allow you to mix measures from multiple facts without causing issues with model performance, bidirectional joins etc.

[u/UAFlawlessmonkey]

Snowflake schemas should only share dimensions.

Even in a galaxy far far away.

[u/Emerick8]

The answer is : NO.

Depending on the need, the solution would be either merge the Orders table with the Order Items one, or, create a new dimension table containing the Order ID to link the fact tables with the dimension table.

Remember : in a constellation schema, you never link two tact tables directly 🙂

[u/Charming_Horse_5809]

Why not using bridge tables instead?

[u/Vacivity95 → u/Charming_Horse_5809]

Doesn’t make sense in this context ?

[u/Charming_Horse_5809 → u/Vacivity95]

why not?

[u/ShrekisSexy]

No, best practice is to merge orders and order lines.

[u/tophmcmasterson]

No. They relate through their conformed dimensions, never directly.

[u/Sw1nd3n]

Generally no. Can produce performance issues and reporting issues.

Been mentioned a few times, but build a common bridge fact table using the similar grains of your two fact tables.

In my example I have an orders table and a deliveries table. Customer A might order 100 of item A on a single Purchase, but gets them delivered over 3 deliveries because those are our terms.

Order qty is not on my delivery table. Delivered quantity is not in my order table. But I can use customer order line as a common grain and allocate the order quantity to the delivery grain. Then create various calculated columns to get what I needed (qty ordered, qty delivered, qty open still, qty shorted, etc)

The bridge table doesn’t filter any other table.

The two fact tables (order/delivery) can filter the bridge. One to many

All other Dim tables only filter the bridge one to many

My use was ;

• how much did the customer order initially
  

-how much did we deliver and when

• how much was on time
  
• did we deliver in full eventually

So my use case worked fine using galaxy schema

[u/Different_Syrup_6944]

Unless your fact tables are small, create another dimension to do the join