Jump to content


Photo

OLAP Tables

Avoiding Create Relationship

  • Please log in to reply
3 replies to this topic

#1 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 04 February 2018 - 10:02 PM

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



#2 Mike

Mike

    Advanced Member

  • Administrators
  • 189 posts

Posted 06 February 2018 - 04:17 PM

Hi JPK,

 

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.

 

This is explained in depth in: Expert Skills Lesson 6-1 Understand primary and foreign keys.

 

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.

 

You can find everything you should need to know to work with the data model in: Expert Skills Session 11: Create Get & Transform queries.


Mike Smart is the author of ten world best-selling Excel books. The books are available in printed form for for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Mike has also recorded over 850 video lessons (that you can watch online) for Excel 2007, Excel 2010 and Excel 2013.
Mike Smart is also part of the team that answers questions posted on the ExcelCentral.com forums.


#3 JPK1066

JPK1066

    Advanced Member

  • Members
  • PipPipPip
  • 101 posts
  • LocationHilton Head SC

Posted 06 February 2018 - 08:49 PM

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."

 

 Are these the tools you are talking aboutAttached File  Game Sheet Pitching and Play by Play example 2-6.xlsx   24.84KB   48 downloads?

 

Respectfully,

 

JKP1066



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 11 February 2018 - 10:35 PM

Hi JPK,

 

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.

 

I have attached a copy of your workbook with the relationships added:

 

Attached File  Pitching.xlsx   783.74KB   41 downloads

Pitching.png

 

You can see more about creating relationships in: Expert Skills Session 9: Data Modeling, OLAP and Business Intelligence.

If you want to create Get & Transform queries, you can see how to do this in: Expert Skills Session 11: Create Get & Transform queries.


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