Jump to content


Photo

Many to many relationship


  • Please log in to reply
9 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 23 June 2016 - 06:22 PM

I would like to create an OLAP pivot table between the three tables in the WB attached. The Game WS has a one to one relationship with both the pitching and the play by play WS's and I have been able to create 2 relationships between them.

The relationship between the pitching and the play by play WS's is a little harder to discern. One pitcher can be involved with many plays and vise versa. But if I were to group the relief pitchers of each team together, which I will want to do, then I believe you would have a many to many relationship.

Excel has told me that a one to one relationship between the pitching and play by play would create a relationship with no unique column in either table: I tried 

 

     Play by Play  (Pitching ID)     with    Pitching   (Pitching ID)

 

Can this be set up as three one to one relationships?

Am I correct that grouping the relief pitchers would create a many to many?

Do the ID columns in the play by play need to be reformatted?

 

As always I appreciate your help.

 

JPK1066

 

 

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 24 June 2016 - 10:56 AM

Hi JPK1066,

 

I've taken a look at your worksheet and it doesn't look like this is a case for a many to many relationship, although I could be wrong about that.

 

From what I can tell, the structure is as follows:

 

One Game has many Pitchers

One Pitcher has many Plays

 

In other words, there is a one to many relationship between Game and Pitcher and a one to many relationship between Pitcher and Play.

 

It can also be said that one Game has many Plays, so if you weren't interested in pitchers you could create a simple relationship modeling "One Game has many Plays", but you should not add that kind of direct relationship between Game and Play if you're already modeling an indirect relationship via Game > Pitcher  > Play, as you'd then have two different routes to get the same information which is likely to give you inconsistent results.

 

You can create this data model using the skills taught in the video lesson: Expert Skills Lesson 6-2 Create a simple data model.

 

It might seem like there's a many-to-many relationship when you're modeling from the starting point of Pitchers, because one Pitcher has many Games and one Game has many Plays, which are also related to Pitchers, but this isn't the case. The same data model should work regardless of whether you are extracting Games, Pitchers or Plays.

 

You can see a more in-depth explanation of many-to-many relationships in the video lesson: Expert Skills Lesson 6-9 Understand many-to-many relationships.

 

I have attached a copy of your workbook with the data model created and a couple of example OLAP pivot tables showing average strikes by Game and Pitcher.

 

Attached File  BaseballWS6-23.xlsx   682.92KB   81 downloads

BaseballWS6-23.png

 

I hope this helps, but please feel free to reply if this wasn't what you were looking for.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 24 June 2016 - 03:24 PM

Once again I thank you for your reply but I've taken one more step.

I've taken the same WB with the same relationships I previously established.

I inserted an OLAP table

 

Started a filter with the @bat field being the Mets (NYM) coming from the Play by Play WS

 

Then placed the Kansas City relievers in the rows column and Excel correctly filtered only the KC relief pitchers

 

I placed the NPC metric into the columns which correctly displays the headings for all the counts that happened prior to putting the ball in play -2,-1,0,1,3

 

I also placed the NPC metric in the values window to display the count of that metric for each of the KC relievers which were correct

 

But when I used the Innings Pitched field from the Pitchers WS it brought back the sum of 49.9 which is incorrect, and with the drill down I left the column highlighted on Sheet 4 to display that those 4 pitchers for KC only pitched 3 innings. Notice on Sheet 1 that Excel is advising that a relationship between Pitching and Play by Play may be needed.

 

I can't figure out what is the next step with the relationships?

 

Thank you,

JPK1066

 

 



#4 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 24 June 2016 - 03:26 PM

Here is the attachment 

It didn't go through on the prior send

 

JPK1066

Attached Files



#5 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 25 June 2016 - 03:30 PM

Hi JPK1066,

 

I've taken a look at your workbook and I can see that you have created a relationship directly from the Game table to the PlayByPlay table and another relationship directly from the Game table to the Pitching table. Here's a diagram of the current data model (created in Access):

 

BaseballDiagram1.png

 

This means that Games can be related to both Plays and Pitchers, but there's no relationship between Pitchers and Plays so Excel is unable to connect Pitchers and Plays. You will need to add a relationship linking Pitcher and PlayByPlay on the Pitching_ID field - you can see this done in my previously attached example.

 

Ideally you should also remove the direct relationship between Game and PlayByPlay, as this allows two different routes to get the same data. The ideal data model joins Game to Pitcher and then Pitcher to PlayByPlay. It might seem like this means Game and PlayByPlay won't be joined, but the data model is clever enough to follow the route from Game to PlayByPlay via Pitcher, even if you never use any of the fields from the Pitcher table.

 

Here's a diagram of the new data model:

 

BaseballDiagram2.png

 

This solves the issue with the data model, but still doesn't produce the result you're looking for because the pivot table doesn't seem to be set up correctly (or the data isn't structured according to your requirements).

 

You seem to be trying to sum the IP value from the Pitching table grouped into columns by the NPC value from the PlayByPlay table, but this makes no sense as there is a One to Many relationship between Pitching and PlayByPlay. In other words, one Pitcher will always have a single IP value and potentially multiple NPC values. This means the IP value from the Pitching table is repeated as many times as there are related PlayByPlay records, resulting in the totals you've seen.

 

I have attached a copy of your workbook with the revised data model, but you can see that the IP value is still the same for each Play. Unfortunately I'm not very familiar with baseball terms so I'm having some trouble understanding exactly what information you're trying to extract, but hopefully this is of some help and let me know if you're still having trouble getting what you need.

 

Attached File  BaseballWS6-24.xlsx   768.47KB   74 downloads

BaseballWS6-24.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#6 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 27 June 2016 - 03:25 PM

I've been able to isolate the IP (Innings Pitched) on the fourth row of the Rows Field

On sheet one I've also highlighted an area where the NPC's counts are being repeated for each row that is brought into the pivot table. Is there any way that I can only have the NPC data displayed once instead of the repeat?

 

On the game sheet I wanted to create a third OLAP table and I only created a standard pivot table and then I was unable to remove it.How did I make this mistake? 

 

Can you tell me how to remove the single pivot table on the Game Sheet without deleting the Game Sheet itself, and is there any reason why I couldn't have many OLAP tables in one WB with the same three WS's that I presently have? 

 

Thank You

 

JPK1066

Attached Files



#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 29 June 2016 - 11:26 AM

Hi JPK1066,

 

I suspect that the reason you're getting unusual answers for NPC is because you're using Count instead of Sum. Count will simply return the number of records that were found, not taking into account the actual numbers involved, while Sum will return a total of the numbers found. Switching to Sum of NPC might return the values you need.

 

You can see more about this in the video lesson: Expert Skills Lesson 5-16 Display multiple summations within a single pivot table.

 

You most likely created the standard pivot table by forgetting to select Use this workbook's data model when you created it.

 

It's quite simple to remove the pivot table - simply delete all of the rows that contain the pivot table (in this case rows 7-25 on the Game worksheet and it will be deleted.

 

You can see more about deleting rows in the video lesson: Expert Skills Lesson 3-1 Insert and delete rows and columns.

 

I hope these are the answers you were looking for, but as always feel free to reply if you need anything further.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#8 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 29 June 2016 - 04:35 PM

Once again I thank you for your help.

As it turns out the count of NPC for each type of at bat is what I'm looking for, but this leads to a bigger question. 

The attached WB has multiple OLAP tables all with the relationship structure that you suggested;

    Pitching     Game ID

    Game        Game ID

 

    Play by Play      Pitching ID

    Pitching             Pitching ID

If you look at the last two PT's....PTFirst5Inning ....and    PTPlayDescription   you will see in the Pivot Table Fields Pane that Excel is not including the Game WS in my OLAP table. It is including the Game WS in the other three PT's which all have the same relationships. How can this be?

I've actually gotten closer to getting the formatting and output that I originally desired but I believe that there are some errors in field design, ID columns. Before I begin trying to build this out would you recommend re designing the ID columns and or fields? Where should I start?

 

JPK1066



#9 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 83 posts

Posted 29 June 2016 - 04:38 PM

Attached File  BaseballWB6-28.xlsx   774.09KB   73 downloads

 

Can't get the attachment to append



#10 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 29 June 2016 - 07:50 PM

Hi JPK1066,

 

I've taken a look at your pivot tables and I can see what you mean but I'm not quite sure why this has happened. My only guess is that perhaps the pivot tables were created before the relationship to the Game table was created. In any case, the simple solution is to create a new pivot table linked to the data model and then use the same settings - I've tried this and can confirm that all of the tables appear as they should.

 

Regarding extending this further, there's one important feature that you might want to bear in mind. This is the Get & Transform tool, accessible from the Ribbon under Data > Get & Transform.

 

OLAP pivot tables do a great job of creating summaries of relational data, but they're still pivot tables, and pivot tables are only designed to create summaries. This means you couldn't use a pivot table to, for example, return a list of Plays for a certain pitcher. You could easily get a count of plays, or a sum of the number of pitches, but not the full record (at least, not in a very usable format).

 

Get & Transform allows you to carry out actual queries on relational data and return full sets of records, as well as many other things. You might find it extremely useful for a lot of the reports you're trying to create.

 

There's a lot more to know about Get & Transform than I could cover here, but it's covered extensively in our Excel 2016 Expert Skills book, which is available as both a book and e-book from our books page.

 

Using a true database product such as Microsoft Access might also be a possibility to bear in mind. Excel's relational data features have improved greatly in recent versions, but still don't offer as much flexibility as a true relational database will. You might find that you can do everything you need with Excel, but it's something to bear in mind if you need to go further.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users