Data modeling: The basics - Power BI Tutorial

- [Narrator] Data modeling is the process of taking your organization's data and creating a model that can be used then for reporting and forecasting by the business. Sounds simple, but not necessarily. Data modeling includes many steps. First, we start with determining what data we want to load. And that includes what data source it comes from, because we can include data from more than one source. It also includes how much of the data we want to return from each source. Do we only want to include the last three years of data, for example? Do we want to summarize transactional data and just return the summary? Or, do we actually want to see each and every one of the transactions? Once we've made these choices, or even while making these choices, we will begin loading data into the model using whatever modeling tool we're using. And in this course we're using Power BI Desktop. If we have more than one table, and you almost always will have more than one table, you'll then need to define relationships between the tables. For example, you'll need to indicate the relationship between territories and the states in the territories; between customers and their orders; between purchases and vendors. With those types of relationships in place, now we're ready to be able to transform this data. We'll shape it. We'll combine some columns. Merge data from different sources. We'll also transform data. We'll change the data type, for example. We may also change the names of columns, so that their more human-readable and easier for business users to work with. And it's in this last stage here of data modeling where DAX really shines, because DAX isn't primarily about getting data, it's about how we're going to transform our data. DAX, which stands for Data Analysis Expressions, but everybody says DAX, is a formula language similar to the formula language that we have in Excel, it uses functions, and this is a good way to think about it. You think, 'If I were in Excel I would do this sort of thing.' And for many of the DAX simple formulas we'll create we'll use very similar functions. But DAX has other functions as well that are designed to deal with data models, data warehouses, and so on. We use DAX to be able to create new information from the data that we already have. For example, if we have two columns, one column for the quantity sold and another for unit price, we can use DAX to create a formula that will calculate the total price for that item. Basically, quantity times price. And, to be clear, if we had control of the data model, and if it was a good decision to do so, we could also add that column in the data model. So, sometimes, we're doing work in DAX that could be done in SQL Server, could be done in Excel, in Access, wherever our data is coming from. DAX is also used for period-to-period comparisons. This year's sales by product to last year's sales. Which are much harder to do with traditional formulas and functions. And, we'll turn to DAX when we need to create information in our model and we're not allowed to modify the data source for whatever reason. Using DAX we will build on what you already know about creating data models in Power BI Desktop.