{"id":6135,"date":"2022-09-27T12:55:09","date_gmt":"2022-09-27T12:55:09","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=6135"},"modified":"2023-12-30T23:00:55","modified_gmt":"2023-12-30T23:00:55","slug":"maximise-data-performance-with-data-modelling-in-power-bi","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/maximise-data-performance-with-data-modelling-in-power-bi\/","title":{"rendered":"Maximise Data Performance with Data Modelling in Power BI"},"content":{"rendered":"

Today, I’m talking with Martin, one of STL<\/a>‘s outstanding Power BI<\/a> trainers, about the importance of Data Modelling<\/a>. I’m asking Martin how I can maximise my data’s performance with data modelling in Power BI.<\/p>\n

\"Maximise<\/a><\/p>\n

Hi Martin, thanks for taking the time to have a chat with me. My Power BI Reports often take ages to update when new data is added at source. I need to structure my data so that it takes up less memory and updates more quickly. I have a few questions for you:<\/p>\n

What is a Data Model and why is it so useful?<\/h5>\n

Martin:<\/strong> A Data Model, in simple terms, is a system of datasets or tables with specific fields in common with each other. These are connected using something called \u2018Relationships\u2019. It is these relationships that help bring the disparate tables together as though they were a single entity. So why have a data model if you could just have one single file that contains all the data? The reason is that this single file (or flat file) uses up more memory and slows down performance because data is needlessly duplicated across multiple rows. However, a data model reduces the amount of duplication and will, in practice, help you become more productive.<\/p>\n

That doesn\u2019t sound too hard. How does it work?<\/h5>\n

Martin:<\/strong> Let\u2019s say you had Customer data relating to their contact and order details. In the table below is a \u2018Customer\u2019 table where Customer ID appears once. Each row contains a unique record of customer details.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

The 2nd<\/sup> table below contains many orders for each customer, so the Customer ID appears many times e.g., Customer ID 1014.<\/p>\n

\"\"<\/a><\/p>\n

When these two tables are imported into Power BI desktop from Excel, the common field which is \u2018Customer ID\u2019 is used to create a \u20181 to Many\u2019 relationship. This means that, for example, you can create a report visual to show a breakdown of Customer orders by Region even though the Order and Customer fields are in separate tables.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/h5>\n
Without creating a data model, what is the alternative?<\/h5>\n

Martin:<\/strong> The standard practice would be to:<\/p>\n

    \n
  1. Use the VLOOKUP formula in Excel to \u2018look up\u2019 each Customer ID in the Orders table<\/li>\n
  2. Find a match in the Customers table<\/li>\n
  3. Then return the corresponding \u2018Region\u2019 as a separate column back in the Orders table (see below)<\/li>\n
  4. Once all the data is in a single table, create a pivot table to summarise \u2018Orders per Region<\/li>\n<\/ol>\n

    \"\"<\/a><\/p>\n

    Note that \u2018Northeast\u2019 is duplicated 4 times. If each customer placed an average of 1000 orders and there were 100 customers, then the customers\u2019 region would be repeated by a huge amount (100,000 times more). This inevitably slows down the performance and affects productivity. In contrast, the data model in Power BI Desktop would only refer to the \u2018Regional data\u2019 100 times \u2013 once for each customer in the Customer table \u2013 and is therefore more efficient and quicker in processing the data.<\/p>\n

    Any final thoughts, Martin?<\/h5>\n

    Martin:<\/strong> Using data models in your Power BI reports can dramatically reduce the amount of duplication and therefore help to maximise your data\u2019s performance.<\/p>\n

    To learn more about Data Modelling and how to apply it to your data, STL runs a 2-day Power BI Modelling, Visualisation and Publishing course. Please click on the link below for the course outline:<\/p>\n

    https:\/\/www.stl-training.co.uk\/syl\/205\/microsoft-business-intelligence-advanced-training-courses.html<\/a><\/p>\n

    To see how data modelling can be applied, please click the following article:<\/p>\n

    https:\/\/www.forbes.com\/sites\/anniebrown\/2021\/08\/24\/can-ai-data-modeling-prevent-climate-catastrophe\/?sh=1cde32423baf<\/a><\/p>\n

    Thanks so much, Martin, for explaining how to maximise data performance with Data Modelling in Power BI. I will explore this some more and start creating my own data models from now on!<\/p>\n","protected":false},"excerpt":{"rendered":"

    Today, I’m talking with Martin, one of STL‘s outstanding Power BI trainers, about the importance of Data Modelling. I’m asking Martin how I can maximise my data’s performance with data modelling in Power BI. Hi Martin, thanks for taking the time to have a chat with me. My Power BI Reports often take ages to […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[637,23,8,650],"tags":[],"class_list":["post-6135","post","type-post","status-publish","format-standard","hentry","category-application","category-data-visualisation","category-microsoft","category-power-bi"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=6135"}],"version-history":[{"count":7,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6135\/revisions"}],"predecessor-version":[{"id":6150,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/6135\/revisions\/6150"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=6135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=6135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=6135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}