Join Ron Davis for an in-depth discussion in this video Exploring the Power Pivot data model, part of Excel Power Pivot DAX in Depth.
- Okay, let's explore the data model and the interfaces that we can use within the data model. So I'm going to open this Data Model Start and this is different workbook than we've been working with so far. I've got more tables that we have inserted into the Power Pivot data model. So I'm going to go to the Power Pivot tab and within the Power Pivot tab, before I go over into the model, I wanna show you something. I'm gonna click on this Calculated Field and I'll go New Calculated Field in here.
It's going to be a little slow there on the start. The reason it was a little slow is it has to decompress the PivotTable and go ahead and open it. Now you see I have this little dialogue box where I can come through and create a calculated field. Again a calculated field used to be called a metric. I would suggest that you still refer to it with your business users or if you are a business user as a metric as that term is well understood in business.
So here we have the table that we're going to associate this with and you can pull this down and you see here a list of the tables that are in the data model. And then you would give it a name right here, whatever you wanted to name it, doesn't much matter. And I would suggest putting a description down in here for whoever it is that's going to come and follow you through so they can try to understand what it is you actually did. And if you notice we have the equal sign down here. Remember I said the equal sign kicks off in DAX what's actually going to happen down there.
It's kind of like the OnStart function. I also have this Check Formula and we can check formula and it's saying something's wrong because we haven't done anything. And if I hit my Function Insert key then we can come through and insert all of the functions that we have which we'll cancel out of. So this is a handy little way of creating calculated fields or metrics within DAX and I happen to like it myself, but we'll cancel out of it.
And if I had some calculated fields, which I don't, and I went Manage Calculated Fields, whether I created them here or I created them in the Power Pivot data model they would show up. So let's just cancel outta that and now I'm going to go into Manage. Now if you notice, it loaded very fast. It loaded very fast because we have already loaded it when we clicked that Create a Calculated Field. Remember our calculated field again, you'll see it in the data model right down in here.
This is called the Calculation Area and I've got it in every table right there. If I come up here and click Calculation Area, she's gone and it doesn't show and then again Calculation Area, it comes back. So let's examine what we have here. If we come across the top, these are fairly obvious. This gives me the ability to copy and paste. Now over here I can get my external data and From a Database and then I would go from SQL or wherever it is I'm bringing that information in.
Now From Data Services, if I pull that down, you can see the data services that we have available. And if I select From Other Sources, this actually shows me everything that I can bring in. By the way, beyond the scope of this course is this Windows Azure Marketplace for getting external data which is something I highly suggest you explore in your role as a business analyst. There's a number of free ones you can download and take a look at.
One of the, I think, interesting ones relates to weather and another one relates just to map coordinates. And we'll cancel outta that. And of course you could see any existing conditions. I brought these tables in from SQL and you can see I've created a number of connections into SQL Server. Coming across here, if I go PivotTable and pull that down, if I click PivotTable then the data model is going to switch over to Excel and actually create a PivotTable for me.
Here are my formatting. This is fairly interesting so let's go through and let's move over to the Products Table and I'll select this and down here in Formatting, you see there was the Text Type and if I pull down my selection option there are my data types that we talked about earlier. You notice the one that's missing is Table because Table is a unique data type that we use within functions. And I can format this information if I pull down Format.
See the only available is Text because we can identify this as text whereas if I pick an integer and pull that down, then I can come through and apply some kind of formatting that I want. Coming across we have Sort and Filters, so indeed, if I wanted to, let's pick an easy one. Let's go to the Categories table and we can Sort and Filter right here too and if I pull this down and I can say Sort A to Z, now we have a different sort order than we had this Category ID and if I pull this down I can also then clear my filters and go back to the default which is sorted by the primary keys that came in.
Find is obvious; this allows me to come in and do a find. Now down here you see this AutoSum. This is going to go through and as soon as it did it came in and created a AutoSum. You notice it through the error, it allowed me to create an AutoSum, but it threw the error because we can't sum a text field. So if we switch over to Orders and I take something that's interesting here. Let's see what this is, wait, however, we're gonna get a popup.
It's Customer ID, that's not gonna do us any good. And that's my Employee ID. Order Details will be more interesting. Here's my Discount so if I take this down here and again we'll create the metric and I go Average. There's my average of and let's expand that out and see what that is. There's my average discount so there I've created my AutoSum.
Now here's my Data View, if I switch over to Diagram View, which is a view I happen to like. Let's switch over to Diagram View and take a look at that. Now we're in Diagram View and up here I can click in and she'll come through and show me all the tables that are in this screen. By the way, you notice it's highlighted. That's because we've actually selected that relationship. Remember a data model is a grouping of tables that are related together. Without the relationship, there is no data model.
So the data model takes our tables and we relate these tables one over into another. Data models also have within them these calculated fields which are metrics and we can create a hierarchy and KPIs. If I select a different relationship, it's going to show me here that I have the relationship between Product ID and Product ID and that'll work all the way through as you do different relationships. Your Order ID is related to Order ID, et cetera.
This Show Hidden, what that is is I can choose to take a table and hide it from the data model and then that wouldn't, obviously, it would be hidden. The reason I might do that is I can share these workbooks and it's not a security implementation. It's to focus the attention of whoever it is that's actually coming through and using this. If we go over to the Design tab, now again I can come in here and if I go to this Calculation Options, I can switch to Manual Calculation Mode so if I was coming in and creating say a number of calculated columns, you saw how slow they might be in the creation, then I could switch over to Manual Calculation and just do them all at once.
I can Create Relationships, select the table and relate it over into the other or I can Manage Relationships which shows me the existing relationships in the table. Finally, on the Advanced, I can Show Implicit Calculated Fields. There's Implicit and Explicit Calculated Fields and we really want to head towards an Explicit Calculated Metric which is one that you're going to come in and actually code out yourself.
One last thing, this is kind of interesting. If you want to get back over into Excel, there's this little link right here which will switch you over to Workbook and then you're in. And there's a quick tour of the data model. Now I'm going to close this and when I do I'm gonna get this Save The Changes. I'm not going to save them. If you are working through this yourself and you're using the downloaded Data Model Start and you make some changes, which I encourage, go ahead and do that, then save it with a different name.
- Exploring DAX syntax and data types
- Using functions
- Understanding evaluation contexts
- Using CALCULATE to change evaluation context
- Working with dates
- Utilizing many-to-many relationships
- Implementing banding