March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi all,
I have one table which contains SALES and PURCHASES about sales. In the same table i have a unique CONTRACT_NR (Vertically) and this is Related between Sales and Purchases
For those sales and Purchases i have several Categories and Sub-Categories. I also have SUPPLIERS which could be CUSTOMERS and vice-versa but i can have a unique code for each one since i have a column that indicates what it is (always look the e.g below).
First critical question is should i seperate this table into 2 Fact tables?
The Requirement is to create visuals which will be interactive between each other. So imagine a table only with sales and a table only for Purchases and when i click to a Sale in the other table i need to see the Related Purchases
and vice-versa. For example if i click to Sale 1 i want to see the Purchases 1-001 and 1-002, and if i click to Purchase 1-001 in the other table i need to see Sale 1.
So the second critical question if i seperate these tables into 2 Fact how i will meet this requirement?
CONTRACT_NR | TYPE | PRODUCT_CATEGORY | SUB_CATEGORY | C_S_TYPE | CUSTOMER_CODE | SUPPLIER_CODE |
1 | SALE | 1 | 2 | CUSTOMER | 100 | NULL |
1-001 | PURCHASE | 1 | 2 | SUPPLIER | NULL | 200 |
1002 | PURCHASE | 1 | 3 | SUPPLIER | NULL | 300 |
2 | SALE | 2 | 4 | CUSTOMER | 500 | 100 |
2-001 | PURCHASE | 2 | 2 | SUPPLIER | NULL | 700 |
I read to connect the Fact tables through some dimension like product and Calendar but in my case this is not working becaues if i can't do the cross filter direction between the DIMENSION table and the 2 Facts "BOTH" since i have trouble with granularity. So the visuals are interactive again.
Thanks.
Solved! Go to Solution.
What i did i create as a bridge dimension table in which i have in the same row the Sales and Purchase and 2 Fact tables FT_SALES and FT_PURCHASE, in the Purchase table i have also the SALES_NR since it's a shortcut of the Purchase_nr. Then i linked the FT_SALES with bridge table with one to many relationship and the Bridge table with FT_PURCHASE with many to many through the SALES_NR. That way i have my tables interactive and when i want to link a different table to both i use the TREATAS function that you suggested me in a different post ! @lbendlin
This depends on the business questions you are trying to answer. Usually a clean snowflake schema with separate fact tables is better, but there are cases where you need to use a different data model to answer the questions.
What i did i create as a bridge dimension table in which i have in the same row the Sales and Purchase and 2 Fact tables FT_SALES and FT_PURCHASE, in the Purchase table i have also the SALES_NR since it's a shortcut of the Purchase_nr. Then i linked the FT_SALES with bridge table with one to many relationship and the Bridge table with FT_PURCHASE with many to many through the SALES_NR. That way i have my tables interactive and when i want to link a different table to both i use the TREATAS function that you suggested me in a different post ! @lbendlin
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |