Speaker A
A good data model or semantic model is key for a well-performing Power BI report. So, therefore, I put together 10 steps that you can take to optimize your model, and we're going to cover them in just 10 minutes. So, let's dive in.
Learn 10 essential steps to optimize your Power BI data model for better performance and easier maintenance in just 10 minutes.
Ask about this video. Answers come from its transcript only — with the timestamp, so you can check them.
Generated from the transcript and can be wrong — check the timestamp.
Key Takeaways
- Optimizing your data model significantly improves Power BI report performance.
- Removing unused data and aggregating reduces model size without losing visualization fidelity.
- Custom date tables are preferable over Auto Date and Time for flexibility and space savings.
- Star schema design is more efficient and easier to maintain than flat or snowflake schemas.
- Avoid complex relationships like one-to-one and bidirectional to keep the model performant.
What the video covers
- Remove unused columns and tables to save space and improve model efficiency using tools like Bravo and Power BI Helper.
- Aggregate data to the required level of detail to reduce row count and maintain visualization accuracy.
- Turn off Auto Date and Time to avoid hidden date tables and create custom date tables for more flexibility.
- Use a star schema instead of snowflake or flat tables to improve performance and simplify DAX calculations.
- Avoid unnecessary one-to-one relationships by merging tables to reduce complexity and improve speed.
- Minimize the use of bidirectional and many-to-many relationships to prevent performance degradation.
- Normalize data carefully as excessive normalization can negatively impact query performance due to more joins.
- Use Power Query’s Group By transformation to create aggregated tables efficiently.
- Organize descriptive attributes in dimension tables for easier navigation and better model structure.
- Leverage external tools like DAX Studio to analyze hidden tables and model metrics.
Chapters
- 00:00Introduction to Data Model Optimization
- 00:51Step 1: Remove Unused Columns and Tables
- 01:42Step 2: Aggregate Data to Required Detail Level
- 03:14Step 3: Turn Off Auto Date and Time
- 04:50Step 4: Use Star Schema Instead of Snowflake or Flat Table
- 09:39Step 5: Remove Unnecessary One-to-One Relationships
- 12:37Step 6: Avoid Bidirectional and Many-to-Many Relationships
Full Transcript — Download SRT & Markdown
Speaker A
In Step number one get rid of columns and tables that you are not using so let's say you have a report that just shows the sales by state and the development over time well then many of the tables and columns that I have in
Speaker A
Step number one: get rid of columns and tables that you are not using. So, let's say you have a report that just shows the sales by state and the development over time. Well, then many of the tables and columns that I have in this big data model here, we could get rid of.
Speaker A
that is a huge amount of space that I could save in my model or we could use powerbi helper which also gives a nice little overview of the used and unused tables and columns then step number two well you also want to get rid of all of
Speaker A
How can you find these unused tables and columns? Well, for that, there are many different external tools like Bravo. You see, it shows that I'm not using 105 columns out of 131 columns. So, that is a huge amount of space that I could save in my model. Or we could use Power BI Helper, which also gives a nice little overview of the used and unused tables and columns.
Speaker A
visuals sales by state and development over time well then I could also aggregate the data to that level of detail so that means in my sales table I would not need all of the details for every single order line which in my case
Speaker A
Then, step number two: well, you also want to get rid of all of the rows that you don't need. Or let me rephrase that a little bit better: aggregate the data to the level of detail that you need for the visualizations that show on your report. So, let's say I have just these two visuals: sales by state and development over time.
Speaker A
visualizations as you can see here for the top visuals I've taken the data from the F sales table and for the bottom visuals I've taken the data from the aggregated sales table and they look exactly the same now of course the
Speaker A
Well, then I could also aggregate the data to that level of detail. So, that means in my sales table, I would not need all of the details for every single order line, which in my case would lead to 222,500 rows. But instead, we could aggregate the sales amount by the state and year-month, and that would just lead to 1,794 rows. A huge difference. It would still allow me to build the exact same visualizations.
Speaker A
create that aggregated sales table well for that you can go to Power query and there you can make use of the group by transformation where you can then set up the group buy Fields as well as the aggregation Fields then step three is to
Speaker A
As you can see here, for the top visuals, I've taken the data from the F sales table, and for the bottom visuals, I've taken the data from the aggregated sales table, and they look exactly the same.
Speaker A
automatically creates hidden date tables for each field in the model that has a date or date time data type now let me turn it on so that you can see what will happen now for each date column we have
Speaker A
Now, of course, the downside is that I take some analytical power away, meaning if I wanted to drill through to a table where I show the orders, well, that is, of course, not possible with the aggregated sales table.
Speaker A
use an external tool like DX Studio then here we can actually see those hidden dat tables that have the year the quarter the month and the Sorting columns and if I would go here to advance and view the metrix then it's
Speaker A
Now, if you're wondering how can you create that aggregated sales table, well, for that, you can go to Power Query, and there you can make use of the Group By transformation, where you can then set up the Group By fields as well as the aggregation fields.
Speaker A
gives you more flexibility in terms of what descriptive information you need about the dates like fiscal year quarter a month then step number four go for a star schema instead of a snowflake or flat table now let's say you have a flat
Speaker A
Then, step three is to make sure that Auto Date and Time is turned off. Now, what does that mean? Well, if you go to settings, then under data load, there is a checkbox Auto Date and Time. If it's turned on, then it automatically creates hidden date tables for each field in the model that has a date or date-time data type.
Speaker A
everything is nicely organized in different dimensions and fact tables for example if I need descriptive information about the employees I would simply go here to them employee and have all of the descriptive attributes for the employees now another downside of
Speaker A
Now, let me turn it on so that you can see what will happen. Now, for each date column, we have a hidden date table with the standard calendar key.
Speaker A
flat table file is 34 MBS and the other one is just 6 and a half MPS now on top of that your Dax calculations will also be much easier to write if you have a star schema and why not a snowflake
Speaker A
Now, in Power BI, you can see that from this little icon there, where we can now click on expand, and you see we have a whole date hierarchy key. But if we use an external tool like DAX Studio, then here we can actually see those hidden date tables that have the year, the quarter, the month, and the sorting columns.
Speaker A
for each City and then again we could do the same thing for the states the thing is if we go for a higher form of normalization in powerbi it has a negative impact on the performance because more joints need to happen in
Speaker A
And if I would go here to Advanced and view the metrics, then it's visible that those date tables also take quite a lot of space. So, make sure that Auto Date and Time is turned off and that you build your own custom date table like I've done over here, which also gives you more flexibility in terms of what descriptive information you need about the dates, like fiscal year, quarter, and month.
Speaker A
some points is just too much and it would be actually easier to have just one table for all of the customer related data then step number five remove any onetoone relationships you don't need them they just slow down your
Speaker A
Then, step number four: go for a star schema instead of a snowflake or flat table. Now, let's say you have a flat table, which is something that a lot of Excel users have at the beginning when you start with Power BI.
Speaker A
that comes from a different Source now the two could be connected with a onet to one relationship on custom ID but instead of this it's better to just merge the two tables into one table so that that extra relationship is not
Speaker A
Now, the first downside is that it's kind of difficult to navigate, especially if you have many columns. Now, compare this to a model where everything is nicely organized in different dimensions and fact tables.
Speaker A
on okay do a left join and then you get a column that contains all of the NASA tables with the match rols which you can then expand and then you can choose which columns you want to bring over
Speaker A
For example, if I need descriptive information about the employees, I would simply go here to the employee and have all of the descriptive attributes for the employees.
Speaker A
the filter from one dimension to flow to the other dimension because otherwise you get the following experience you might in your report have two slicers one for the customers one for the products and then when you make a
Speaker A
Now, another downside of having everything in a flat table is that a lot of the data in the table is repeated, and therefore the model is going to be bigger, and the file itself as well.
Speaker A
instead of that you could use B directional filtering right so that the filter goes from your customer table to the products table by just making that relationship in between FD sales and stock item in this case b directional
Speaker A
As you can see over here, the flat table file is 34 MB, and the other one is just 6 and a half MB.
Speaker A
items now the second downside of B directional filtering is that it could introduce some ambiguity meaning you could end up with wrong values in your report now how could this happen over here we have two fact tables which is
Speaker A
Now, on top of that, your DAX calculations will also be much easier to write if you have a star schema. And why not a snowflake schema, where you go for a higher form of normalization?
Speaker A
then from this table to FD purchases and that well could lead to different values then step number seven is to reduce the cardinality of the columns that are in your model meaning the last unique values you have for each column the
Speaker A
For example, for a customer table, we could take out the descriptive information for each city and create a separate table where we have just one line with the descriptive information for each city.
Speaker A
see I have one column called last edited with both the date as well as the time component and right next to it I split it into two columns a date column and a Time column now let's go to deck Studio
Speaker A
And then again, we could do the same thing for the states.
Speaker A
smaller than the last edited column and right next to it we have the last edited time and date which when combined are still much smaller in size than just that last addied column because the cardinality of these two colums is much
Speaker A
The thing is, if we go for a higher form of normalization in Power BI, it has a negative impact on the performance because more joins need to happen in the queries.
Speaker A
to make sure that the correct data type is assigned to each field because some data types they take more space than others for example a Tex data type takes more space than an integer whole number data type now let's take the custom ID
Speaker A
Now, another downside is the usability to navigate the data model. Now, already I have two extra tables, and it could be if I normalize all of the other dimension tables as well, that I would end up with 20, 30 tables.
Speaker A
then open up Dex Studio where we can then look at the Matrix so here in the customer table there we have the custom ID text column which is taking the most space and where's the custom ID column well let me scroll down which is all the
Speaker A
And at some point, it's just too much, and it would be actually easier to have just one table for all of the customer-related data.
Speaker A
total sales we could add a new column and then multiply the quantity times the unit price which gives us the total sales row by row and that column we can then sum up or alternatively you can also go for a measure now that measure
Speaker A
Then, step number five: remove any one-to-one relationships. You don't need them; they just slow down your model.
Speaker A
doesn't take any space in your model the column does it it needs to store all of the values in that calculated column and the measure doesn't take any space now what is important to know though is that that measure could take a little bit
Speaker A
Instead of that, merge the information of the two tables into one table.
Speaker A
consider calculated column however if that's not the case then you should prefer the measure and if we go for a column should you do it here with Dax or should you do it in power query or maybe even further Upstream now generally
Speaker A
Like over here, I have one table for all of the customer details and then a separate table with information about the credit limits, which might be data that comes from a different source.
Speaker A
models second if you have incremental refresh set up on that table well then all of the other partitions also need to be recalculated if you have a calculated column so in those cases calculated columns are also not ideal however
Speaker A
Now, the two could be connected with a one-to-one relationship on customer ID, but instead of this, it's better to just merge the two tables into one table so that that extra relationship is not necessary because that would just slow things down.
Speaker A
Upstream then of course doing it with calculated columns can be a little bit quicker than doing it in power query or even further upstream and then the last one step number 10 is to consider if you go for import mode of your tables or
Speaker A
Now, how could you do that? Well, in Power Query, you could just go to Merge Queries and then merge the customer table with the credit limit table on the customer ID and then click on OK.
Speaker A
thinking maybe over 100 million of rows or you really need the latest numbers the data needs to be Super Fresh because with direct gy it touches the data when you interact with the report with import mode there you need to set up refreshes
Speaker A
Do a left join, and then you get a column that contains all of the nested tables with the matched rows, which you can then expand, and then you can choose which columns you want to bring over: credit limit and is on credit hold, and click OK.
Speaker A
you really need direct quy so here you see I have my sales table two times this one over here is import mode so in the properties you can see the storage mode is set to import now I cannot change it
Speaker A
And that's it.
Speaker A
one is in direct query the other one is in import mode now if I go to the report view I've created two times the same visual first one at the top gets the data from the table FD sales the second
Speaker A
Then, step number six: stay away as much as possible from bidirectional and many-to-many relationships.
Speaker A
is the direct query one that needs to fetch the data from the SQL database so these are my 10 steps to optimize any powerbi data model now let me know if you have more optimization steps put them in the comment section below and if
Speaker A
Now, for example, bidirectional filters are often used by beginner Power BI users because they want the filter from one dimension to flow to the other dimension.
Speaker A
thank you for watching and see you in the next video
Topics:Power BIData Model OptimizationStar SchemaPower QueryDAX StudioAuto Date and TimeData AggregationPower BI HelperBravo ToolData Modeling Best Practices
