The course is part of this learning path
Power BI has changed the business intelligence (BI) landscape forever, enabling BI professionals and regular Excel users alike to work with big data and build beautiful and insightful dashboards. Integral to the Power BI platform is the data model.
This course discusses fundamental concepts of data modeling design within Power BI. We explore Microsoft's recommended best practices as well as common data modeling complications and the solutions to them. This course will prepare learners to develop data models strategically to achieve the best performance.
- Identify the primary components of data models and describe flat schemas and star schemas
- Diagnose common Power BI data modeling issues within models and recommend appropriate solutions to them
- Identify common challenges in Power BI data models, implement smart solutions, and avoid common mistakes
- Business professionals whose job requires them to design and build data models in Power BI
- Anyone preparing to take the Microsoft DA-100 exam
To get the most out of this course, you should be familiar with preparing data using Power BI.
So we notice this hierarchy. See the year, containing months, containing days, but this wasn't created by us. This was created automatically by Power BI because it detected a data type that almost always gets segmented into successive levels of granularity, dates. Dates are almost always grouped by day, month, quarter, and year. So Power BI has been programmed to do that automatically, but some of these default behaviors can conflict with real-world practices.
First of all, if our fiscal calendar doesn't start on January 1st, we should just totally disable this automatic behavior, because none of those date hierarchies will be helpful to us. This can be done in file, options, and settings, options, current file group, data load, time intelligence section, and clear the auto date time option. I'll just select OK. And in the fields pane, let's notice that the date hierarchies are no longer available.
Now that we know how to turn off a programmed column hierarchy, let's talk about how to manage organic hierarchies that appear within the data. We have one table for all company employees. Each employee's manager is shown here as well, but each manager is also an employee. So this is gonna get a little weird.
Let's look at Aaron. Aaron and his whole team, they report to their manager, Abram. That creates a many-to-one or children-to-parent data relationship inside this table. But Abram is himself, also an employee. So he also shows up in the employee column and he and his fellow store managers report to a regional manager named Abbie, another children-to-parent relationship, and Abbie and her fellow regionals report to a director and so on.
Because all levels of this hierarchy are tucked inside a single column, we won't be able to create an effective hierarchy unless we know how to flatten out parent-child hierarchies. The process of flattening out a parent-child hierarchy involves adding columns and using some awesome DAX that can identify and display the path from CEO all the way down to store employee. There are two DAX functions needed for this purpose and while we will demonstrate them at the moment, you shouldn't feel pressured to understand them completely at this time. The important takeaway is that it's possible to flatten these out.
Let's start by inserting a new column on the modeling tab and we'll input full path as the column name equals path, open parenths, and we're gonna tell it where to find the children in this path first. So that's on this same employee-manager table in the full name column. Type in a comma and then it's gonna want to know where the parents are. That's on the same table in the manager column. Close out that parenths and execute. Great, the resulting column shows the full pathway from CEO down to employee.
Now we just need to extract each level into its own column using the path item data analysis expression. Let's create a column for each one and then enter the path item function. So the first one let's make it CEO and that function is gonna be path item, open parenths, name of the table, and then that full path column that we just created. And we want it to take that very first level, close that, execute.
Cool, all right, let's do regional managers next. Equals path item, open parenths, the name of our table and then this time we want it to take level two. Close parenths, execute. Awesome, let's keep going. Now that we have flattened out this 1D column into a proper dimensional column structure, we will be able to run useful analytics and even design the proper Power BI hierarchies to allow users to drill down into this type of data.
Chelsea Dohemann is a Senior Technical Trainer and Microsoft Certified Master with almost a decade of experience in technology training. She has taught an array of applications from Microsoft products including Office 365 web apps, Microsoft Office Suite, Power BI, VBA for Excel, and SharePoint to Adobe Acrobat Pro and Creative Cloud. Being a persistent learner herself, Chelsea is acutely in-tune with the challenges of learning. She presents her topics in plain language, with real-world examples, reducing complex concepts down to their simple parts.