Data modeling best practices - Part 1 - in Power BI and Analysis Services
Вставка
- Опубліковано 10 лип 2024
- Patrick looks at a few data modeling best practices in Power BI and Analysis Services. These are things we see customers doing on a regular basis and you should be aware of it.
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 #DataModeling #StarSchema - Наука та технологія
I have recently started expanding my knowledge with PBI and your channel has amazing information, examples and tips. I appreciate your work very much! Thank you for your efforts!
Dude, your videos are incredibly helpful. I've learned a ton from you over the past few weeks!
Thanks for that Patrick. I have been using star schema and some good data modeling practices and I have to say: "This is the way". Not only performance is better: design, end user understanding maintenance is much much easier as well. The good practices with Power BI make us Super Power Workers! Thanks again!
Great video. Smart. Great pace. You very quickly setup the various problem statements, explained the options, and demo'd. You're a great instructor. Thank you!
Great topic you have chosen to talk about it here.
AWESOME video Patrick!
Now I will be waiting for the Part 2.
Congratulations!
Awesome, Patrick, you make this stuff fun - thank you!
Wow, very good information in here about optimizing tables. Thanks for the post as always, Patrick!
Greetings from Finland. I find your videos really beneficial in getting better in the fundamentals when working with Power BI. Earlier I was working on a project with Cognos where the databases were already ready and I could only work in the UI and then give people with access to backend tasks on what to do there. That was pretty terrible as we were always facing performance issues to the point where individual KPIs were just failing randomly (timeouts). Efficient data modelling is really (REALLY) important when we want people to actually use the tools we create.
You guys are by far the best at generating good quality content.....from inside the Cube! 👍🤟 Keep these video coming!! Thanks
u r one of the best teacher I have been through (few among above 1500 teachers world wide)
Excellent video. Very well explained. Thanks. I would like to see more videos like these around the topid BI. Thanks once again.
Great video! Thank you for the very easy to digest examples
Always enjoy your videos -thank you 🙏
This is so helpful. Can't wait for the other parts to come out.
Thanks for watching and stay tuned it will be release soon!
Love it! I’ve leveraged this process for all sorts of reports, everything from property and date tables to organize my various sources of data. Thanks for the deeper dive .
Great to hear! Thanks for watching 👊
Great video, thanks for putting this together.
Very good ... Looking forward to part 2!
Adam's dataset video actually worked for my reports. Thank you both 😀😀😀😀
Woot! That is awesome to hear. 👊
New sub! Awesome vid and down to earth presentation with great humour and knowledge of the topic!
awesome tips patrick, love it!
Patrick, as always, you know what I think, AWESOME video. Cheers!
Thank you Nelson! And thanks for watching. 👊
Thanks for your videos. I always enjoy them. I understand star schemas. Been using them for decades. What causes my hangups are filtering directions in the model. Hopefully you have a video discussing that topic.
Excellent done ! thank you.
Thanks Patrick! This was extremely helpful and easy to understand!
Most welcome Kim! 👊
You are a Rockstar. Really Informative ,Great Job ..Keep it up and going
Thanks for waiting that was a great video
Also, great channel. Cheers to you and Adam!
Your report is fast as long your data model is concise.
Great explanation.
Ah you're a life saver. Subscribed!
This is pure gold.
omg I love the way he spoke out wiiiiide table. Give you a heart :)
Great video, thank you
Your videos are really helpful. Can you please do a video on using multiple fact tables with different granularities.
Did a Part 2 ever emerge?
Loved this one
Great video! Thanks
Great stuff again Patrick.......Jim from Oz
Awsome advice!! many thanks Patrick... Hello from Colombia, Sourh America...
Most welcome! Thanks for watching! all the way in South America 👊🙏
Thanks for that Patrick. I have been using star schema and some good data modeling practices and I have to say: "This is the way". Not only performance is better, design is easier, understanding is easier and the maintenance is much much easier. The good practices with Power BI make us Power
Bruno Cippitelli Yes! Just makes life easier.
Great video Patrick as always, is it more performant to have a relationship, or merging the columns in Power Query?
Thanks Patrick! Awesome advice.. I am great a fan of both of you guys. Just a question that, Is it a good practice to use SQL DB Views instead of DB Tables?
Good content Patrick.
"I wrote a video" - that's some badass PowerBi skills, mate :D
😂😂
Great channel! Cheers from Rio de Janeiro, Brazil!
Appreciate that Vinicius! Hope you are doing well down there 👊
Thanks for the video
There is another drawback to wide tables I think you could have mentioned. When a context transition occurs, the entire table being iterated is brought into memory, uncompressed. For wide tables with lots of rows, this will ravage memory even more.
My experience with the star Schema is mixed especially with independent product tables where it limits your ability analyse to a certain extent. Cross tables was one of them
Thanks!
Wow super demonstration..
great channel! and thank you for what you do! i have a question. some time when i put some value in my table it s shaow me just the first value from my database and i can't choose any form of calculation (sum, average and co...) how can i do to change this? thank you agan
Would love to see more videos like this that I can share with my team.
In particular I've been running into issues where we have a general policy that all transformations/data modeling are to be done on the back end, with I think the intention being that the logic can be accessed by other sources if needed and not just locked inside Power BI.
At the same time though, it just seems like this approach cripples what Power BI is capable of and leads to so many other issues. Especially when I see things like essentially multiple flat-file like views being pulled into a model, with relationships just being created in a really ambiguous way that has caused all kinds of problems that I inevitably end up needing to fix by creating a dimensional model.
Would love to see a video going more in depth on concepts like when is it right to do a calculated column in SQL vs. having a measure in DAX, or elaborating on why its better to have a flexible dimensional model instead of applying all your filtering logic etc. to a view and then using that to drive a single report page.
I've created really clean, robust dimensional models in the past so this sort of thing is infuriating to see, but at the same time I can't seem to break this misconception with people that creating a dimensional model takes too long compared to making a SQL view, or that having a pre-joined view in SQL isn't really a good replacement for a solid data model.
Hey, Patrick! First of all congrats for your tips. So... could you put here the link where you talk about “Avoid many-to-many relationship”? I didn’t find it. Thanks, bro
Great Video. at 7:30 are we creating lookup tables from large table in Power BI itself. Is there any other video on how to do it.
Awesome tips. Where could I see the part 2?
Hi Patrick, Great Video! But where is part 2 of this video as the title suggests? Thanks!
NO WAY YOU CAN HAVE TWO DATA TABLE DISPOSITIONSSS, my mind just exploded ugh Patrick you're and angel sent from MS Heaven
Hi Patrick, Do you know if there's a way to export the data model to Visio Pro ? I work a lot with the modeling tool but I find that's very limited. I'd like to customize the background color, assign different colors to different objects, etc. Thanks !!
Hello, I'm new to Power BI. I'm used to using Power Pivot in Excel and joining tables through SQLServer to create queries (sometimes many many tables). Occasionally creating relationships to other queries within the back end. I was just curious if you guys had any recommendations on when to join tables through relationships or when to merge tables in Query Editor? Coming from Excel, I thought the best practices were to merge tables with joins, but soon found out that wasn't optimal in many cases. So any tips? Thank you!
Hey Patrick i like your videos they are really helpfull for a newbies like me. I have a question whether i can use direct SQL tables in to power BI or do i need to create any views or Sql query or SSAS cubes first? . What happens if i use direct SQl tables into power bi in import mode? SQl tables size is relatively small
Great vid question - How do you deal with data that contains different types of markers please i.e. ".." for missing data, "..." for suppressed data, "." for data from a different time period?
Hello, may i know if the tables you used in video are from Analysis services ssas?
This is 100% basics right here. Its all review for me but Im watching to support this great channel and to simply keep this fresh in my brain. STAR schema is a must in most cases.
Love it! And love the support! 👊 Sometimes going back to basics is a good thing.
@@GuyInACube I'm just now getting into PBI after years in SQL. So designing the back end is my specialty. But the same rules apply in a lot of situations because the concepts are the same.
Many times I have to remind someone "do we need the information in two places or can we table these out and relate them?"
I am looking for job.. Is there any vaccines, please
Great Patrick +1
Thanks for the great tips.
Currently, I have the following requirement for dashboard to show monthly comparison for the whole year, as well as, year to year comparison, with ability to drill down to transactions.
6 excel source reports each month, from different sources. These reports need to be consolidated after complex transformation and additional classifications from user mappings.
My initial thought was to make it into one giant dataset for user convenience.
After watching your video, it seems I need to redo from scratch and build the star schema, with additional mappings. Reason is there is no standard key fields from the different sources. Imagine you have 3 sales reports but for the same products, each report uses a different product code.
Would this be the right approach?
Yooooo! wassup! If you have SSAS capabilities, one should use this option for creating the model and then connecting to that source from Power BI. I find this to be a more efficient way to work with data coming from disparate sources.
When things get too complex, it is always good to return to the basics. Good simple video with powerful information.
Nice videos. Question that is driving me nuts: When I unselect all options in a filter in Power BI, the visualization shows as if all options were selected. It should logically show nothing. Thoughts?
awesome
Any tips on books or other resources for data modeling? I commonly find times where the data is set up in a way where creating summary tables using Power query would be necessary, or cases where I need to look for instances where a date in one table is less than or greater than a date in another table.
I had the same problem last week. Having two fact tables were i couldn’t use a slicer for a field in both tables correctly. The slicer would filter one table but not the other.
Very good video
Hey, I need some help.. Is there a way we can 1. Create Calculated columns based on 2 different tables. 2 Create a data preview from the model I create in Visual Studio. I would eventually use LIVE connection due to data volume size in Power BI so the modelling part needs to be finalized in Analysis services only as we hit limitations in Power BI. Thank you!
Hi Patrick,
Need to understand how you are not getting an inactive relationship when you are dealing with multiple fact tables and multiple conformed tables? Is there any setting that you are making in power query editor etc?
Cheers mahn!
Awesome!! I love watching your vids instead of going through formal Power BI training because I understand SQL and data analytics very well and want to jump right into using this tool which is new to me. Power BI is incredibly powerful and it's actually easy enough to get started in, but as things get complex it's not always intuitive. So thank you for all the tips, info and best practices! :) I do have a question though, and maybe you have already made a video on this but I'm new to the channel so here I go! Can you talk about the difference between star schemas, Merge with a JOIN, and simply setting up a relationship between 2 tables via the Manage Relationships button (which is maybe the same thing as star schema but I didn't realize that?) ? And when it is best to use which? I find myself wanting to use Merge b/c it's the most like writing in SQL, but I want to use more tools within Power BI, and let it do the work for me!
Yes, I also want more relationship/join/star schema videos. But even more beginner than you've done.
you are best ...!
greetings from guatemala
Appreciate that Francisco! Thanks for watching. 👊
Dang...where's the Power Bi 101 video! I just finished a Power BI course and it was great theory and cool to see all the tool can do, but I'm lost at where to begin! I need some basic report building exercises to show me step by step what to do. Any such resources?
Do you have any tutorials with follow along for beginners?
I have a question not related to the content in this tutorial. The "fill
map" visuals available in the PowerBI market place do not have a
"conditional format" feature that can allow users to define "data
colors". This function was previously available in one of the earlier
versions of PowerBi but when i go to "Data color" options within the
fill map visual there is no longer provision for "conditional
formatting". The choropleths were also able to automatically adjust the
color schemes using filters in that version of PowerBI. Is it possible
for you to demonstrate how one could utilize the currently available
choropleths to achieve the same(Conditional formatting of Data Color for
fill map visual, which can respond to filters).It would also be
wonderful to see how to upload and use shape files(.shp)
When is Part 2 coming?
I guess it is that: Where to create your columns in Power BI | Data Modeling Best Practices
ua-cam.com/video/ZSUCmi6h5SY/v-deo.html
Do you guys have a video on how to correctly handle Many to Many?
Great video Patrick. So, I do have a question though: is creating conditional columns in the query editor better than creating custom columns in the data model? Does that make real difference in terms of performance?
I do have a few reports in which I used to create custom columns, but I am seriously thinking in replacing them directly in the query editor.
Thanks for all you do!
Conditional column in query editor would help. Bonus if query folding is enabled
nice Patrick, Here multiple Fact tables are available . How can we handle the reports and dax queries..
Hello Patrick - Thanks for the videos. Learnt a lot through your videos and your unique teaching style :) I have a situation and I would like your inputs on this. I have a Fact Table where I have 3 different dates like Order Date, Shipped Date and Delivered Date. I have a custom Date Dimension Table that I created in my Data Model using the CALENDARAUTO() Dax Function. Now I need to use that Date Dimension Table as Role Playing Dimension because for some queries, I need to leverage the join between Order Date Date, for some Shipped Date Date and for some Delivered Date Date. However, while building the relationship I can only have one Active relationship between the Fact Table and the Date Dimension Table. I could think of 2 different possibilities based on whatever knowledge I have gained.
Number 1: I know that I have an option to create 3 versions of each measure using the USERELATIONSHIP Dax Function to force which relationship to be used while calculating the measure. But as I mentioned that it would mean that I need to create 3 version of each measure e.g. Sales by Order Date, Sales by Shipped Date, Sales by Delivered Date etc.
Number 2: Otherwise I think I have to have 3 separate Date Dimension Tables like Order Date Dimension, Shipped Date Dimension and Delivered Date Dimension.
Can you please suggest what should be the right and appropriate way to solve this model design situation? Looking forward to your help.
I think 3 tables would be simpler
Good day sir.
I would like to ask on how to include a dimension on a hierarchical design (recursion) to a fact?
Kindly set an example and provide explanation if possible.
Thank you.
Is Power BI views something similar to perpective in Analysis Service?
Hi Patrick, my RMS creates a 24-digit primary key. Power BI displays these in scientific notation, which, of course, creates a ton of duplicates. We have tried to change the data type without success. We are forced to make the data type Text, which of course is a pig when it comes to compression. Is there a way we can have Power BI display the full 24-digit primary key?
How can i narrower my table which have a column with comma separated string values for almost like 20?? And i really have to work with those values..
Really need the help. TIA
Can you tell me if we can do Power BI Adhoc Analytics
I have an important question! What is the difference between data modeling (like star schema) and database design/ database schema? How do we design a database so that it flows nicely to a data model? Can’t the database schema already just be star schema so that they are one ans the same??? But how can we expect users to update data in star schema form when everything is so fragmented and it uses integer IDs everywhere?
Thanks!!!!
Imagine there is no dw but numerous summarised tables built with numerous dimension tables in rdm would the best way to tackle this be building denormalised table structure into a star schema model ? Our company has numerous models with different levels of summarisation - aggregation in the same model would you create lowest level of data and summarise using measures to aggregate to different levels using dimension relationships ?
Hi,
i am working in corporate company. We are using powerbi on premise version. I want to build a data model to use in my dashboards. Which tools do I need to build datamodel in on premise version?
Thanks
How can I work with unstructured data, what process should I use to make structure data
Nice video. The demonstration of the data model seems to me like its Snow Flake. I know Snow flake is a variant of the star schema but varies at one critical point that Star Schema is allowed to be denormalized (which is opposite to what you are recommending in terms of narrowing the table) as it has to contain the attributes of the dimension whereas Snowflake is strictly normalized. Could you please clarify how your example defies the variation and conforms to Star schema?
@Patrick I have a scenario where I got 3 Million rows distinct Products linked to a Sales Table. However in the Sales only about 1 Million Products have been sold so far. I really dont need the unsold 1 Million products to be loaded into my model. Is there a way Power BI can delete unused records in a relationship? I could use a Sub Query on my Product to check if it exists in Sales, but I feel it will be expensive as my Sales data increases. Any suggestions?
Great video as usual Patrick! At 6:50 you mentioned a previous video on how to create narrow tables starting form a wide one.
I haven't been able to find it; can you please provide the url?
Thanks for watching. Here is the link ua-cam.com/video/vjBprojOCzU/v-deo.html to the video.
@@GuyInACube Great! Thanks a lot a very interesting video, especially from a trainee perspective.
Sometimes I have some hard time with people that do not understand why we talk about modelling, normalization and relationship.
Is there a part 2 for this video? Couldn't find it 😢
hey patrick pls do a vedio on how to avoid bidirectional relationship and manu to many relationship
Love that Blockbuster shirt 😂
Gotta keep it alive :) 👊
how did you create multiple tables ?
How many Columns in a Table you would consider it being a wide table?
Patrick:
How about to get the links for what you talk about here:
1) Star Schema Overview (you talk about in 6:39)
2) "Flat tables and create data model" (you talk about in 6:48)
This will be great to have it!
I will be waiting for your answer.
Best regards!
Agree! Here is the link to 1) Star Schema Overview -- docs.microsoft.com/en-us/power-bi/guidance/star-schema