This post is the third in a series about learning the built-in Business Intelligence features baked into Excel 2013. If this is your first time accessing this blog series, I encourage you to read the previous posts in order to get the proper context:Blog One: How I learned PowerPivot from taking football stats Blog Two: How I learned PowerPivot Blog Series – Data Prep
As a quick review: the first post in the series described how I used an iPad and Excel 2013 to capture stats for my son’s youth football team. The second post focused on taking raw data and prepping it to work in PowerPivot. In today’s blog, I’ll describe creating relationships within PowerPivot and create a fully-functioning data model. This data model will serve as the source for creating measures and displaying information within Pivot Tables and Power View visualizations within Excel.
A bit of review may be helpful before I proceed. Here’s is what’s been done to this point:
- Five tabs in an Excel spreadsheet, each with data related to stats captured during a game, the team roster, the teams played, penalties, and the plays themselves.
- The data ranges in each tab were converted to Tables named Play_Data, Roster, Team, Penalties and Plays
- The tables were added to the PowerPivot data model
The Data Model
Ahh, behold the The Data Model. It’s almost as if the privilege for even speaking these words were once only reserved for Data Analysts (and well deserved, BTW – these are bright people). PowerPivot reaches into this rarified air and brings this power to the masses. Well, at least those of us that have basic understanding of the underlying data and knowledge of Excel. In other words, the Power User. As I see I it, Microsoft’s strategy is to position Excel as the de facto BI tool and supercharge it with capabilities such as PowerPivot, Power View, and Data Explorer. Now is a great time to jump in and ride the BI wave!
With these superlatives out of the way, let’s look at the Data Model. From Excel, I clicked on the PowerPivot tab and clicked on Manage, as seen below:
This displays the ‘Data View’ view, which looks something like this:
To get to the Data Model, simply click on the Diagram icon in the ribbon:
Data Model – Before
Choosing the Diagram View displays the following data model. Note that the tables are not connected, simply floating as independent entities without any relationships (sounds kinda lonely…).
Data Model – After
Here’s Diagram View after the tables are all ‘hooked up':
Data Relationships (aka “The Hookup”)
I was fortunate that relationships between the tables were pretty straightforward, in large part because I had planned it that way. In the ‘Think ahead on how your data will be used’ section in my previous post, I had thought about how the data was going to be used by the coaches and how this process informed how the data needed to be structured into Facts and Dimensions. This is the foundational step in creating a dimensional model. For those of you that want to go a bit deeper on a structured approach for dimensional modeling from a business perspective, I encourage you to review TDWI’s Dimensional Data Modeling Primer: From Requirements to Business Analytics document and/or attend a training session.
So, let’s get started. I’ll show two common methods to relate tables together: Click-and-Drag and Create Relationships.
Assuming we’re still in the Diagram View, I first wanted to create a relationship between the Game table (aka my Fact table) and the Plays table (aka a Dimension table). To do this, I simply clicked on the Play field in the Game table and dragged it to the Play field in the Plays table. That’s all there is to it.
This view shows the active relationship:
Another common method for creating relationships is a bit more manual but does the job equally as well. Within the Design tab in PowerPivot, click on Create Relationship…
…to bring up the Create Relationship dialog box. In this example, I created a relationship between the Game table and the Team table (aka a Dimension table):
I repeated this process to create the relationships needed between the tables. In the end, I had created a honest-to-goodness star schema dimensional model. In other words, a fully-functioning data cube. In a later post I’ll talk about publishing the PowerPivot workbook into SharePoint, where the uploaded PowerPivot workbook is transformed into a data source for reports and other data visualization tools that can connect to a SQL Server Analysis Services (SSAS) database. This is significant – it wasn’t all that long ago that only Data Analysts and other technical folk had the tools, knowledge and experience to create dimensional models and data cubes. <<Reference White Paper>>
You may have noticed that in the Data Model – After screen shot that some of the connections were dotted-line versus a solid line. In PowerPivot, tables may have multiple relationships to each other but only one connection may be active at any time. A solid line connection visually indicate an Active relationship between tables, while dotted-line connections visually indicate Inactive relationships between tables. This is OK – do not fear it. In a subsequent post I’ll talk about how I used the DAX function USERELATIONSHIP in creating a measure that leverages an Inactive connection.
Now that I have a viable PowerPivot data model, I’m going to spend the next blog in the series creating a series of basic Measures and a Pivot Table or two. This is where it gets fun, so stay tuned!