3: Building a Data Model to support self-service reporting
Learning objectives
3: Building a Data Model to support self-service reporting
Learning objectives - Video Tutorials & Practice Problems
Video duration:
5m
Play a video:
<v ->In this lesson, we're gonna learn about how</v> to build a data model to support self-service reporting. I often like to say the data model is the center of the Power BI universe. We've seen back in the previous lesson how we use power query to gather data from many different sources, cleanse it, prepare it, and actually load it into our data model. One thing we're gonna do in this section is we're going to go ahead and build our data model out further, so we're gonna build relationships between our tables, we're gonna further enhance our data model by building some default properties into the model to make it easier for self-service consumption and then we're going to round out this lesson by talking about building DAX to further enhance our model with centralized business rules. Now, what we're going to do here is in this lesson, we're going to go through this a little slower because this is different than what a lot of people are used to in terms of a target data set. Doing data modeling has often been considered the domain of IT people for many years, but I like to think that there's a bit of a data modeling renaissance happening through tools like Power BI which are pushing the data modeling tasks back into the business users. Now don't worry, the tasks actually aren't that bad. And in fact, I would argue they're actually easier than a lot of things you've done inside of Excel. Going through and building the data model will seem like a lot of hard work and be very tedious, but that hard work will pay off because you're going to get to the point where you're developing single data models, that are going to be servicing many different ports. Unlike what typically happened in Excel where we would build a data set and a report, a data set and a report, and a data center report, we end up with a lot of data sets and a lot of reports. One of our big goals here in Power BI is to reduce the number of data sets out there. And, I truly believe that we're closer than ever as an industry towards the central version of the truth because of things like this. So, what we're going to do here is we're going to go through a couple of learning progressions. We are going to introduce what is called Dimensional Modeling and why it's important to Power BI in terms of modeling, but sometimes in our first progression of Power BI, we really just want you to get using the tool, and have users engage with the tool, and interact with your data in new and inventive ways. In progression number two is what we'll talk about here, we then like to take you a little further back to the model and teach you how to model your data dimensionally, but keeping in mind that the data model sometimes isn't what is perceived as the most important value. It's the front end work itself. So, like I mentioned with sometimes we recommend you go through a first progression where you use existing sources that you know, and trust, and have cleansed, build Power BI reporting on top of that. And then, in a second progression, look to remove those sources out of your mix and actually go back to sources, and dimensionally model your data within Power BI. We're going to show you how to do all that within this chapter here, okay so, what we're going to do is also consider how we balance between what I call the tripod, making sure your data model is set up for end-user simplicity, so it's got good table names. So, it's got good column names, it's set up for performance so that the users can do a variety of different analytics as they see fit. And, we also want to make a model that's set up for maintainability over the course of time because we always anticipate more data's going to be added into our models, we want to make sure it continuously performs, we'll talk a little bit about that, okay so, some of the learning objectives in this particular lesson are, number one, we're going to teach you how to navigate the Power BI desktop first. So, we want to get you comfortable with navigating the desktop, secondly, we're going to introduce you into dimensional modeling. 'Cause once again, this is a new concept to a lot of people and we really feel it's important to get you into the terminology, into some of the constructs, and show you how things work so you can start stepping your way towards dimensional modeling proficiency. We're going to create relationships and manage settings between those relationships. We'll talk about modifying column properties. We'll look up modifying column formatting. The last two here are items where we're actually building defaults back in the data model so that self-service users can actually simply go to their model and do drag and drop reporting without having to do a lot of configurations. They don't necessarily have to use a lot of the defaults for setting, but they're there just in case. We're also going to talk about building hierarchies which are gonna allow you to roll your data up and down. So, a classic example is your dates. So, taking days and rolling them to months, and then quarters, and then years. Or something like a product hierarchy in the example we're going to build here. We'll also then go ahead and introduce DAX. We'll build some basic DAX expressions. We're going to talk about the concept of context within DAX. We're then going to introduce time intelligence which are some of the coolest functions inside of the Power BI desktop. Then, we're going to introduce CALCULATE which may be one of the more widely used functions inside of DAX. Finally, we'll introduce quick measures and then we're going to discuss data model cleanup in preparation as you roll it out for users to start building reporting on top of, let's begin.