From the course: Introduction to Career Skills in Data Analytics

Modeling data for Power BI

From the course: Introduction to Career Skills in Data Analytics

Modeling data for Power BI

“

- We will work with different data in different data sets or tables to do analysis and visualization. When we have multiple tables that we're working with, we'll want to model our data to get the most out of it. When you have an entity relationship diagram where the tables and relationships are showing in a model, you're actually seeing the model of the data. Now I've already connected data to my Power BI Desktop and on the right hand side, you see my fields list. I have different tables of information required for my reporting. It appears that I'm ready to go but I need to go one step further. These data sets are meant to be joined together. There are several ways to join and model data in Power Query for Power BI. When we perform merge queries, for example, we're actually establishing a join but we can also go to the modeling section and model this data from the very beginning and this allows the data to communicate through the joins, meaning if I reference an order, it knows what product and what order details are related to that order. In looking at the diagram, we see that there are some joins already established. Power BI as a convenience tries to join the data automatically, this is called auto detect and it tries to auto detect the relationships. You should always confirm that the relationships that it establishes for you are correct. Remember, it's easy to model data when you know what data is related to each other. Let's look at the orders table and the order details. These are joined together by the order ID. Also notice we have a '1' and a '*' or star symbol. This shows us the cardinality of this relationship, it's a one to many, meaning we have one order and many order details, not unlike when you place an order and buy multiple things, you have one order record and then the different line items and quantities for the products that you purchased. Let's look at the products information and the order details. These are joined by the product ID and again, it's a one to many relationship. There are other relationships when we refer to cardinality, there's one to many, many to one, one to one and many to many. One to one means that there is only one record tied to one record between the two tables. One to many and many to one, like our examples here mean that we have one record in one table that's tied to many records in another table. I do have a join that needs to exist but doesn't. Take a look at the employees, you see how there's no line to any other table, this means that the model doesn't know how the employees relate. I'll use the employee ID and drag it to employee ID, this establishes my relationship. I can go ahead and look at the properties of this relationship. I'll right click the line and go to properties. This shows me the orders table, which is the many side and the employees table, which is the one side and I see the cardinality is many to one. I'll go ahead and click OK. To manage all the relationships, I can go to manage relationships up top and work with each one of them. Okay, let's see the model at work. I'll go to report and I'll begin to build a basic visual. I'll start by just adding a table. I'll go ahead and bring in the company name from customers. I'll bring in the last name from employees. Okay, I'll collapse those so I can see. From order details, I'll actually go ahead and bring in the order ID. I'll bring in the order date hierarchy. I just want to actually show the order date so I'll right click that and just show the order date and then I'll bring in the product. I actually want to put the product in between the order ID and the order date and then I'll also bring in from order details, the unit price and the quantity and then I'll bring in my total after discount. Because I've modeled my data, I know that I have the correct company listed with the correct last name of the salesperson with the appropriate order ID and the order details for each one of their orders. Because we've modeled this data together, we can now explore the data using all the features that help us visually without having to create various merge queries to accomplish the joins.

Contents