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:

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.

Click-and-Drag

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:

Create Relationships

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.

Blog Preview

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!

This post is the second 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 first blog post in order to get the proper context:

Blog One: How I learned PowerPivot from taking football stats

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.  While somewhat kludgy, it did the job reasonably well.   But it was lacking the ability to look deeper into the stats to find nuggets of insight (aka Analytics).  So…PowerPivot to the rescue!

As noted in the previous blog, I began to recognize that this mass of stats was actually a rich data set that was ripe for deeper analysis.  In order to do deep analysis, I needed to ‘pop the hood’ (or ‘bonnet’ for my international audience) and prepare the data to be used in PowerPivot.  What is PowerPivot, you ask?  I could write an entire blog on this, but Rob Collie already did in his blog entry called What is PowerPivot?.  For now, I’ll simply say that PowerPivot is the engine that powers analytics within Excel.

1. Before I go further – it’s important to note that the company I work for (Magenic) provides access to Microsoft Office Professional Plus, which includes Excel 2013.  This version allows me to use PowerPivot (and Power View, which will be discussed in a future blog). In other words, if you need these features, make sure you have the proper version of Excel 2013.

Step 1: Enable PowerPivot Add-In (this article describes the process better than I can)

Step 2: Convert the Data Range to a Table

It’s a good practice to convert the data range that will be analyzed into a Table, with the added benefits of easier sorting, filtering and formatting data.  Note: this is different that simply renaming the Tab.  To convert the range into a table:

• Click in the data range
• Choose Insert from the ribbon, then click Table:

• Confirm the data range, and make sure that the “My table has headers” checkbox is checked.

• Change the table name from the default name ‘Table1’. It’s not only best practice; it saves a step when the table gets added to the Data Model as shown below:

Step 3: Add to Data Model

• Once the Table was created, add it to the PowerPivot Data Model.  To do that, choose PowerPivot from the ribbon, then click Add to Data Model:

• After clicking the Manage button within the PowerPivot tab of the Excel ribbon (see the above image), here’s the view of the table within PowerPivot:

Repeto (Latin for Repeat)

I repeated the process for a Roster, Plays, and Teams table.  When I originally set up the Excel spreadsheet with separate tabs to capture the data, I didn’t have the foresight to know that it was step one of creating a dimensional model.  At the time, simply capturing data and putting related data together in a separate tab made little sense to me.  So, in a way, I was lucky.  But the lesson I can pass on to those reading this blog who may not be data analysts is to organize your data so it is readily ‘consumable’ by PowerPivot.  I’m certainly not a data analyst, but here are some tips I can pass along:

• Think ahead on how your data will be used.  In my case, I knew that the coach wanted to see Rushing Yards by Player, Tackles by Player, Rushing and Passing Yards by Play, Points per Game, etc.  There is a pattern: X by Y.  In data modeling terms, this is referred to as Fact by Dimension.  In my case, the Play_Data table became my Fact table, and the Roster, Plays, and Teams tables became my Dimension tables.  I’ve seen my share of SWS (Super Wide Spreadsheets), where every imaginable piece of data is stuffed into a massive number of columns.  Not only does it make it difficult to print, this approach makes it difficult to use with PowerPivot.  Keep what is being measured in one table, and what it is being measured by in separate tables.

• Clean data is good data.  While Excel accommodates a bit of sloppiness when it comes to data, PowerPivot and tools like Pivot Tables and Power View do not.  For example, “Offsides” and “Off Sides” may be the same penalty, but not to PowerPivot.  “12 yrds” and “12” may convey the same information, but one is a text entry and the other is a number.  In my case, I inputted my fair share of phantom player numbers and play numbers as well as a large number of misspelled penalties.

• Renaming is Key!  The names that you assign to columns and tables will be used extensively in PowerPivot, and it’s important to know up front that PowerPivot doesn’t “auto-fix” formulas when you later rename a column or table.  Save yourself time and headaches in the future by thinking it through (even a little bit helps).  I tip my hat to Rob Collie for that tip, which is found in his book DAX Formulas for PowerPivot – The Excel Pro’s Guide to Mastering DAX.  A great read for those who want to take their PowerPivot skills to the next level.

Bonus Tip

There is one more tip I’ll share that I’ve found to be pretty handy: the Sort by Column feature.  In the screenshots above, did you notice the “10 – Newport” data within the Game column?  Previous to the version of PowerPivot that comes with Excel 2013, I had to add a number to the text so that the data would sort properly.  This was terribly annoying, especially for everyday things like sorting by Month or Day.  So, without further ado, here is the solution:

• I removed the appended game numbers in my Play_Data table, and in my Teams table in Excel (not PowerPivot) I added a GameNumber column, as seen below:

• I updated PowerPivot to refresh the Play_Data data and to bring in the GameNumber column and data into the data model:

• I opened the PowerPivot window:

• I selected the Team column and clicked on the Sort by Column button on the ribbon.  In the dialog box, I set the Team column to be sorted by the GameNumber column and clicked OK.
• Now, when I create a Pivot Table or Power View graph, the games display in the order played:

Blog Preview

In the next blog of this series I’ll show you how to create relationships within the Data Model.