I will send you a WS that has small pieces of two WS's that contain large amounts of data. I have tried to create a relationship between the 2 WS's but I continually bring up the 'Create Relationships' warning.

I believe that the Game ID Columns in the 2 sheets should work for the Primary and Foreign keys while the pitching ID columns should work for the second part of the relationship but unfortunately that's not the case.

Should I have the ability to create an OLAP table that gives me complete functionality and if not what would I need to do to get to that threshold.

Respectfully,

JPK1066

Attached Files

The reason you are not able to create a relationship that uses the GameID field is because the GameID is repeated multiple times in both tables. To create a relationship, the GameID must be unique in one of the two tables. Typically the 'Game' table would contain one record for each game, each of which would have a unique GameID. This would then be joined to a GameID in other tables (such as Pitching) in order to retrieve multiple records that are relevant to each game.

I will send you another sample WS that has a sample of a third WS "Game" It only has one index column marked Game ID. Each entry into that column is unique and relevant to columns in both the Pitching WS as well as the Play by Play WS.

Your explanation would imply that the Game WS could be set up in one Olap table with the Pitching and a separate Olap table with the Play by Play WS. But you cannot work the Pitching and Play by Play WS's together as neither has a unique column. The index columns in the Pitching and Play by Play columns would have to be reformatted, or a fourth column added, to allow one relational Pivot Table for all three WS's.

These two lines imply that the 2016 Data Model has different and more powerful properties than the OLAP table, including the retrieval of individual records. I've seen tutorials that allude to them as Power Pivot and Power Query.

"In addition, it's important to remember that pivot tables have a specific purpose: to summarize data. They are not intended to be used as query tools to return individual records.

However, Excel 2016 introduces the Data Model and Get & Transform tools that allow you to query your data. You might want to investigate these as opposed to OLAP pivot tables."

I've taken a look at your example and it looks to me like each Game contains many Pitching records and each Pitching record contains many Play records. This means it's quite straightforward to create relationships from Game to Pitching and from Pitching to Play.

I do notice that there's an additional GameID field in the Play table that would allow a direct relationship to be added between Game and Play. This technically violates the rules of database design, because there are two different ways of reaching the same data. I wouldn't recommend creating a relationship directly between Game and Play, as it would be likely to cause inaccurate results.

With the relationships established, you can easily create OLAP pivot tables that extract data from multiple tables.

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.