Handling MULTIPLE fact tables in Power BI
ฝัง
- เผยแพร่เมื่อ 5 มิ.ย. 2024
- You've got multiple fact tables in your Power BI dataset. And, dimension tables for each. Struggling to slice and dice across both? Patrick shows you how to deal with this mess.
Understand star schema and the importance for Power BI
docs.microsoft.com/power-bi/g...
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #DataModel #GuyInACube - วิทยาศาสตร์และเทคโนโลยี
I like how you use the Description property to list tables you can group by! Putting that one in my pocket!
Guy in a Cube has to be one of the best channels on YT!!!! Everything makes sense, Adam and Patrick have welcoming and down to earth styles, and I finish each video feeling good that I've learnt something new I can apply straight away. And the videography, illustration/animations are clear and comprehensible. Doing my DA-100 exam tonight - this has been the best resource I could have found to help me piece everything together. Thanks do much.
Thank you for creating informative, concise, and entertaining videos! I did not think it was possible to make a funny yet informative video about data modeling in Power BI, but you have raised my standards for what a good technical video should be. Also, your real-world examples are very helpful for people just starting their careers like myself. I used to make those consolidated fact tables that you mentioned, but now I know better.
However if you model it this way you are restricted in the Filter directions in case you want to filter one fact table to the other if it's specifically required. Only one flow from the fact to another can be set on 'Both' to avoid circular refrences
THANK YOU, THANK YOU, THANK YOU! I’m just getting to grips with Power BI, but every time I google an issue I get only Dax related solutions - and I don’t want to go down that path right now. I was thinking the solution to my issue would be complicated, but you made it so clear and easy that I’ve fixed it in 5 minutes - having wasted a day searching for the answer on other forums. I cannot thank you enough. I really love your presentation style too - so fun to listen to and easy to follow.
I had been dealing with a broken report for almost two weeks and this literally saved my job!!!! Thank you
Hey Patrick, you just described the exact journey I took to come to the same conclusion when I was first learning to use Power Bi. I love that I am now doing it right. You’re a legend.
Thank you very much Patrick. It is very helpful with the difficulties I just have now. This solves presenting data in reports but I always have difficulty with measures using both fact tables
Great video, BUT, i think you should record a 1h+ long video about this topic, i know you already have 3+ other videos on modeling. Please, do a huge, complete one :)
Anyone else watching this go "phew" when you realise you did it the way he recommends....I certainly did lol
I was like 'yay, I'm doing it the right way' then like 'crap, I thought there was going to be a much better way of doing it' lol
This was like day 3 of my Power BI journey way back when...with 18 fact tables :-D
Hahaha same!
Yup. I was secretly hoping there was a better way. My report consumers still don't use the consolidated fact tables properly
@@stuartmccloud307 Same! I have to write complex Dax to join 8 facts into a single measure
Not only was this helpful, it was super engaging and easy to follow. Y'all rock - thank you!
Thank you very much for this video Patrick, I would like you to explain the slowly changing dimensions with an example in another video
I love how correctly you've described a Fact-Constellation pattern without mentioning its name once!
-To all, this guy very well knows what he's talking about!
@6:30...I'm a data warehouse developer so thanks for the plug. Power BI is great but BI visualization is only as good as the model/schema behind it
Another best practice is to create multiple layouts in the Model view so that you can create the star (or snowflake) schema for each fact table in the data model. I have a model with 12 fact tables and the “All tables” view can get overwhelming. This makes relating fact to dimension tables much more manageable. Hiding fact tables is also a good practice, so that only dimension tables can be used to slice and dice the data.
You can hide fact tables if you have factless fact tables, but if they contain calculaions, you need them.
How does multiple layouts affect relationships? Is there any indepth tutorial on this
they doesn't affect any relationship. they are just partial views of the single data model. and you still see the complete data model on 'All tables' page.
The group by description in the model is a great idea. Great stuff!
Very thorough step-by-step how-to video. Thank you!
must admit, I use your phrases sometimes to really explain the point to my peers and it works :) thanks Patrick
Hi Patrick, thanks for the quick video with good explaination.
I have comment regards to handling multiple fact tables.
Instead of building circular data model, I think we can have link table which will contain the common dimension columns through which the relationships to other tables dimension tables can be done
I really struggled with this until now - Thank you!
Had same issue while using salesforce as source as needed different date filter for 2 reports, so used both method
1. Consolidated fact
2. Duplicated it
3. Had common date dimension but joining key was different for both fact
It worked but we don't have much flexibility while using salesforce as source.
Patrick it would be helpful if we can have your view while using salesforce as source like kind of video on do's n don'ts.
A lot of good and sound advice here! Even as an experienced professional, it's good to see other people explaining it with other (better!) words & ideas. The final trick of using the description field / tooltip feature to let people know how to use the fields properly is a clever yet simple workaround.
I particularly the way you explain the correlation of unrelated factors (great name by the way!). In fact, I might reuse it to explain it to me people. As for the "consolidating tables" that's another great explanation with an example. I see being done all the time!
Another great video from you guys! Keep up the great work 👍
Brilliant! I love the way you explain it so logically.
Patrick, this is awesome. Old Qlikview guy and dealing with multiple facts is kind of a PITA. I was curious to confirm that me linking a single calendar to multiple facts via a common date id was acceptable. My numbers looked good, but hearing you confirm my model is certainly helpful. Really like your approach to get to the point and make it short and sweet. Good work and thank you.
@7:32...I love these features especially the first time I saw them in the original SSAS / MDX cube designer tools back in 2006ish
Another helpful hint on top of the description is to hide the fields in the fact table that map to a dimension table like dates/products/customers/etc. That way the report writer only uses the fields from the dimensions that filter out both fact tables. Made that mistake too many times, even as the model creator and report writer in one.
Can u please give exapmles for this from Patrick's model (maybe from minutes 7:20 in the video) ? thank you!
@@MrTC-rv3jo The Product table is related to Internet Sales and Reseller sales on the Product Field. They are suggesting that you hide the product field in Internet Sales and Reseller Sales so that those fields don't get accidentally selected instead of Product in the Products table.
As you can see, Patrick has already hidden all fields on the fact tables other than the measures.
Brilliant! I knew using conformed dimensions was an option just had to be sure! Thank you
Respectfully, this is a really basic scenario and hard to get it wrong. I've been struggling with a model that has 5 fact tables, all connected to 6 different dimension tables PLUS calendar PLUS time period selector. So each fact table ends up having 6 relationships and, big surprise, most of them inactive and don't filter by the dims as expected. When I try to activate the relationship, Power BI tells me I need to deactivate some other relationship first because otherwise it would be ambiguous. "An Active relationship already exists between tables" Deactivate the existing relationship first". I ended up with a big redesign in the data warehouse, retiring some of the fact tables and squishing others together to eliminate the need for the dims, but it hardly seems like best practice.....
Contact me, I can probably help you to fix your problem if you're still having it. Just to give a small tip: sometimes you need two relationships between a dim and a fact table, you can use the "userelationship" in DAX to active the non-active relantionship for certain measures.
I have the same problem.
@@andrefreitas1572 yeah that is how we ended up solving the problem!
Another plus of using the conformed dimensions is that you can do a direct query on the fact tables and import/refresh on the dimensions.
The consolidated table in this example isn't that bad really. In fact it's the best option as the 'group by' hints will be missed by the end users.
You just need a default value for the internet sales lines that reads 'Internet Sales' in the Employee column. Now you don't have to rely on end-users checking and actually noticing the hover-over on each measure. Also, you can just make separate measure columns in your table that only holds the sales value for each sales type. The size penalty is not that big, and it saves you loads of filtering in DAX.
After all, sales are sales. Just take some time to consolidate/structure the delivered columns, and the resulting model is much more useable for the end-user. Anything that the model doesn't enforce or facilitate is something they need to remember. And they don't.
Just what I needed, good timing!
I love these real life scenarios, this is exactly what i face now. I don't know how you two do this mind reading, but keep it up!
Just implemented this approach on my most recent dashboard. Actually ended up with 3 fact tables and they each connected to the respective dimension tables. Works like a champ!
Hi Chris,
Does your model beside shared dimensions, include at least one non shared dimension?
If yes, did you create a slicer on that non shared dimension? if yes, does you slicer filter a second fact?
similar to what Patrick has in his enhanced model Dim Employee to filter Internet Sales for instance ?
Thanks for your feedback!
Thanks! Been trying to find a resource like this for ages as the data I work with is very messy and I seem to always have multiple fact tables. Looks like I have some unlearning to do!
In other news, I'm looking for more resources on linking to Salesforce Objects, could you do a video on this or direct me to some good resources?
Brilliant! I will attempt to apply it. Many thanks.
Yes, have used conformed dimensions. Thank you 👍
Guilty of building the consolidated fact table. Had to jump through all the hoops mentioned. Lesson learned! Great video, thank you.
Me too. Last week I thought my consolidated fact table was the answer to my problem....., apparently not! Thanks Patrick!
Hi! Nice video as usual :) I love the solution to keep tables as they are in the initial data warehouse. But once you start building measures like ARPU on top of both reseller and internet sales, isn't the appending solution better and easier to work with?
Why do Marco and Alberto at SQLBI, THE world experts on DAX, seem to never offer much guidance at all about Multiple Fact Tables across their entire body of work? Kimball also doesn't offer much guidance... Is there such a thing as too many fact tables for example? I'm thinking of separating mine into 1 fact table for data sources that need to be refreshed quarterly, 1 fact table for data sources that need to be refreshed monthly, 1 fact table for data sources that need to be refreshed weekly... That way I can avoid unnecessary refreshes and speed up refresh time of the data model.
Great, I just started creating a new model when I ran into this exact problem. I had already started creating a consolidated fact table but after watching this video I then remodelled it and now all the DAX is much less complicated.
However, in some use cases consolidating facts might still be the better alternative, especially in financial reporting or when a clean star schema is needed.
YES consolidate facts where appropriate. My models have 4-7 FACT tables to mash up whatever.......its all about mashing
Need to try this as we have a similar problem for sales forecasting. We have a target table which has regions, segments, dates, and products, but then separate product and deal tables holding different levels of details. We need to show progress to target by regions, segment, and products all separately requiring multiple relationships. Hopefully some region, product, and segment bridge tables like this help solve these challenges.
Great video Patrick and this is the kind of stuff which goes around so often. The key issue here is the tussle between Power BI and data warehouse team where sometimes the DW team is slow in implementing. In a lot of cases we even end up making the dimension table inside Power BI by duplicating, appending and removing duplicates in Power Query and then creating relationships with fact tables. This eventually slows down the model but at least gives a result. Any suggestion on how to make dimension tables inside Power BI effectively for a worse case scenario which follows the star schema design as showcased in the video?
Thank Patrick... its also solve my problem when I am want compare target sales (column Date, value target, product category) data from excel with actual sales in power BI. Tk
And here I am mind blown that you can create different layouts in the relationship screen and drag and drop the fields from the side! Need to look into that some more as I didn't realise it was a thing and I don't dare open up my relationship page anymore, it's just too scary to look at!
yes hella great. Also remember you can right click on a table and 'add related tables' to automatically... add the related tables to your current view :)
Love this as a best practice... I've been stuck on creating my own keys for my model and populating those keys in the necessary tables, but i would have certainly made a few of these mistakes
Great 👍 idea 💡 Patrick.. for handle multiple fact tables.. thanks 😊
Hey Patrick, thanks alot for such a informative videos I had learned alot. I just have a quick question what if my multiple fact tables as same dimensions columns but if I related them together it creates a many to many relationship. To avoid that is it best practices to create a single dimension table and related them as one to many relationship ?
Very helpful video! Thank you. Tgus subject is very important, could you having more about
Thanks again!
Are there any performance implications of using 2+ fact tables with common dim tables instead of combining them in power query into one bigger table? Assuming no granularity issues and simple SUMX statements in measures where you are combining them together? My thoughts are around whether it is more efficient for the engine to read one large fact table or 2+ smaller ones (maybe concurrently?), in addition to possibly having a shorter refresh time due to less transformations going on in the back end.
BRO! Thank you for this. Was beating my head against a wall until I watched this. Won’t even go into details. Just thank you dude!
awesome man..
Please mark more videos on how to approach data model creation
Great video. Simple and to the point. But this is a very common scenario.😀
Exactly what I need. Thanks!
Great subject because I’ve been stacking two fact tables. However, your solution for me needed you to slow down at the end and show what measures to use to distinguish the two types of sales.
Took me a while to get used to the shared Date tables, and understanding how table relationships worked in general.
The consolidated fact tables is the best practice performance wise. As long as the data is similar in his structure it is best to consolidate. Then make different partitions for refresh (for the different data source\type) .
you should address the fact that fact tables often have different level of granularity which makes thinks much harder I think
Agreed, I have that exact issue I'm working through right now.
I either have to take the fact table down to a level of granularity that I was trying to avoid or build a bridging table or complex DAX.
I'd love to see this video re-done with a much more complex model. (not taking away from the fact, it is very useful info for those starting out with data modelling)
@@KNP-BI I handle different granularities by using ISFILTERED, SUMMARIZECOLUMNS and TREATAS. You can use these to only show measures at the appropriate level.
@@richardcroft3676 Any performance issues regarding your approach so far? Thanks
@@DanielWeikert Not when using live connect to AAS with facts of 50m+ rows. If same performance applies on PBI models don’t know
a trick you could do is create a measure in a fact table and put in in the filter of your visualization to connect it to the other dimensions
great that someone finally discussed conforming dims...however i also have situations where i need multiple dims.....ship_cal, order_cal , invoice_cal....the reason is at details grains i can pull in specfic full name attributes....SHIP MONTH, ORDER MONTH for column and rows and can add them both to visuals. All that and still have conforming dims where appropriate.
I see no dev or speed perf for using USE RELATIONSHIP in EVERY single measure
Thank you very much for the helpful video, Patrick! I have a question re shared date calendar. What happens if I need two different date filters on a report page? One filtering 1st fact, another filtering 2nd fact table independently, but in a single visual.
Omg I'm a novice in power bi and spent my whole Friday doing different tricks to solve this problem and actually ended with this solution. Glad go know it's a effective way to do it. Funny how i get this video after struggling haha, but i still appreciate it
The visual effects are the video more funny and helps to understand the point. Nice improvement !
Thanks for the video! How about if we need to multiply values in two columns of unrelated tables? cant relate them because dont have unique values that match. how do we handle this using Dax? Thanks!
Beautiful video..Patrick
This is good! Thank you so much
Hi Patrick thanks for the video. I usually use a consolidated fact table to get to a dimension table. Let me explain; we don’t have a DW and I build reports based on downloaded data from our software. So I get out patient report and in patient reports. I use 1 calendar table but cuz I don’t have a list of doctors, I usually consolidate the two facts then extract a unique list of doctors and their corresponding specialties to use to slice my consolidated fact tables. In this situation is there some recommendations to make my model faster? Thanks
Great video! I am doing this model but, how do you link the dates? My date column on both tables have hour and minutes also. I need to create an specific key?
That’s the same as I think a proper way to write DAX and reduce refresh time even though my organization recommends single fact table. Thanks for sharing such a logic that proves my assumptions right.
To me this issue is emblematic of the classic Inmon vs Kimball design approach. I can never remember which one is which, but starting with the optimized data mart that then populates the data warehouse rather than the other way around seems the more idealized approach. So in this example there would be a "sales data mart" structured in a way that a single fact table would have the relevant foreign keys pointing out to the dimensional tables that handle the differences in the how various channel sales are processed. This would, in theory at least, eliminate the need for the various tips & tricks to mimic a "conformed" design, such that the PBI report developer would simply need to pull in the various related tables and let PBI do its thing with "detect relationships". Sort of related: There's a SQL joke a DBA told me many moons ago that the complexity of a SQL statement required to get the data you need is proportionate to how bad your database design is. Funniest joke I ever heard!
Useful. Thank you.
Thanks this is great content! Thank you.
Problem number 1, never made that mistake. Problem 2, I did that one A LOT! I've learned from these great videos!
To be honest, our DWH Is developed in a way that dimension that aren’t applicable get a specific key, so we can use 1 consolidated table that won’t give blanks as long as they are on the same fact subject. So we have a consolidated table for Retail, E-Com and Marketplace sales.
In addition to describing the users, what dimensions they can use with a measure I really like to also create some dax rules, that return blank or an error, if the measure is used with a non-suitable dimension. ISFILTERED is a good candidate for example.
Sounds like a really good playlist "GuyInACube reads and applies designs from The Data Warehouse Toolkit" to their data models in PowerBI (maybe using PowerQuery)."
Great video.
Doubt: I have 2 fact tables as well. I until getting split the data columns to others dimensions but my columns (id product and name product (e.g.)) aren’t uniques/distincts. How can I headling with that?
Thank you. I have a more complex case that I'd like to clarify, Patrick. What if there are three fact tables within a composite model sourced from three separate PowerBI datasets in direct query mode, each with substantial data and an incremental refresh setup? How should the relationships between these three fact tables be managed, considering each has its own DateTable? My proposed solution was to designate one of the fact tables as the primary pillar and integrate the date columns from the other two fact tables into the DateTable of the primary fact table.
Do you have a video with best practices on something like Accounts Payable modeling? For instance, a typical AP database will contain a header table for the invoices, then a detail table for the invoice lines, and then another table for payments made against those invoice lines, and yet another table for retainage released against those invoice lines. I can't seem to find any guidance on the best way to build a model with tables like this. Thank you!
Brilliant Video! Love it!
Very useful video , thank you !
Great thanks Patrick for shading light on this multi-facts with shared & non shared dimensions PBI model.
On your enhanced model, you have an Employee dimension that filters only Reseller Sales fact as this is expressed by a one to many relationship
My concern is how to cascade this to the Internet Sales fact, in other words how to make the employee filter both facts in a consistent way knowing there is no employee candidate field in the Internet sales fact table.
I am not sure but I heard the TREATAS can resolve this bug. How? It could be by adding the TREATAS within a CALCULATE.
Power BI has a powerful DAX engine and I believe this can be resolved by an advanced DAX formula.
Thanks again for sharing tips!!!
You ever figure out your question about how to cascade the employee relationship over to the other fact table? I have that issue with a model I am currently working on.
Exactly what I'm looking for! Anything more about this? Hi Patrick, are you here? How to add sales up from two fact tables into one matrix column? Patrick you here?😂❤❤❤
Thank you!
Confirmed Dimensions/ Shared dimensions/ common Dimensions are always best to use. Even if there is no proper data warehouse built. At least try creating calendar dimension with in power BI using min and Max from the required date fields to have a common dimension and handle the measures accordingly.
It’s better to always avoid French toast 😀 from customers @5:03
Hey guys! thank you so much for this info!! but, what if I'd like to know which products were purchased in both internet and retail channel? I mean, I'd like the list of products that were in both channels and all its attributes, say, in a table? I built the model just the way you told us to, but can't figure out how to perform this requirement. If I build a dimension "Internet" and "retail", and relate to a field in each fact table, then it becomes ambiguous with the calendar table. Thks in advance!!
hi is there a way to represent 2 table with different data in a clustered bar chart individually base on selected table. Basically if i select table1 result would show in clustered chart if i sleect table 2 it would show result in clustered bar chart
Thank you for this amazing video
Hi Patrick, If we have two fact tables which contains date of purchase and occurance date then this approach will work ?
What do you recommend when I have a lot of date columns that I want to use with a date table?
Helpful!
Thank you! 🙏
Quick follow up: how about if we have 10 teams sales and 10 tables, and we need to report "total sales"? (The Dim Table/ relationship are good, like what Patrick said) Do we drag them into visual one by one, or we just make a dax measure: Total sales = Sales1+Sales2+Sales3.... and use the dax
Thank you Patrick as always! How do you create those beautiful animations in your video?
Maintain separate team 👍🏿
Doesn’t Ralph Kimball recommend to “Sort Merge” the two or more fact tables (Full Outer Join) using the conformed dimension keys across both tables?
Essentially doing as you showed in the second ‘bad’ method?
More of a question than anything else. You guys are great and have helped me out of many sticky PBI situations!
I also heard of that, Union or append is pretty common it seems and I would like to know more to
I think this is the best case solution when you have 2 tables that hold the exact same data just in different grains (Header and detail tables) but not when the data is similar, but ultimately different. I think Patrick was just pointing out that it makes little sense to append tables if there will be blank values for half of the data for the reasons he mentioned and I agree.
I’m experimenting with using visual level filters with measures to remove blank values.
Hey Patrick!
I have fact tables with different date granularity so some of the facts are yearly and other are quarterly. user want to create a report where they can have quarter slicer but when they choose let say 2023-Q1 then the facts that don't have quarterly data show blanks whereas they expect it to show 2022 year end data for these facts. Is there any modeling solution to this problem?
What if you wanted to "union" the tables. For my use case, I want to do a "distinct count". So if you wanted to do a distinct count of retailer and internet market sales (my use case is user names), would that be slow performance and how would I do that in dax?
Hi, I tried to join 1 consolidated dimension(month) with 2 fact tables as you did. The relationship is showing as dotted line and one of the fact join is not active. Can you help me resolve that?
Hello Patrick! I take my data from our live sql server. And my model has multiple fact tables. I can join together some of them but still I have at least 2 fact tables from the live db and 1 from dwh that I want in the same model. How can I make my schema? Connect each fact table only with the dim tables or use a table in the middle and connect each fact table with it and the middle table to the dim tables?
Hi Thank you for the great video! I have a question. I Have two fact table and I have two FYTD sales from each table and want to put it into a single graph but they seems to be not connected. I have a same data structure as the 7:24 of the video. Can someone help me? (If my question itself is too vague please let me know..)
Hi there, I hope you are doing fine. In my current project, I am using Power BI. I have data for daily phone calls, daily texts, daily contracts sent, daily offers sent, daily contracts signed, etc. I am recording these data against Regions and Agents. So, I have three dim tables (date, agent lookup, and state lookup) and many narrow fact tables. Now, when I'm trying to connect my date table with these fact tables, it lets me create 1-to-1 relationships, and sometimes 1-to-many relationships, even sometimes I see they selected filter directions to 'both.' My question is what will be the relationship cardinality to connect these fact tables of daily measures to the date table?