Solved

Relations in Power Pivot is not the same as in the underlying Access database

Posted on 2014-10-20
9
112 Views
Last Modified: 2014-10-21
Hi Experts,

I have an Excel report, that is based on a powerpivot, that has the data from an underlying Access database.

In that database, I have a one to many relation from my products tabel to my sales database. But when I go into my PowerPivot, this relation is turned around, so I have a one to many relation from my sales database to my products tabel.

How can that happen and how can I secure, that the relation from products to sales data is kept?

best regards

Jørgen
0
Comment
Question by:Jorgen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40391611
Relationships are lost when imported into PowerPivot.

you can expose ForeignKey properties from your Data Services, that way you can rebuild the relationship in PowerPivot.


http://office.microsoft.com/en-ca/access-help/copy-primary-key-column-properties-to-a-foreign-key-column-adp-HP003083979.aspx
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40391629
So if I understand you correctly. thén I should try to create a primary key in my products table, and then show that as a foreign key in the data table.

It should be said, that the two tables are imports from an external dataprovider, but the datatype should be the same.

I did not set a primary key in the products table, but I did drag a one to many relation from the products table to the sales data, and I thought that would generate a ForeignKey property. Did I misunderstand that?

That was the way it was build from the start, and therefore I am in doubt, if that will change anything. Please clarify how my relation in the PowerPivot will be affected.

regards

Jørgen
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40391650
i am curious to know what method you use to import the access data into the powerpivot? is it via Table Import Wizard
or Custom Query?
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 4

Author Comment

by:Jorgen
ID: 40391655
Table Import Wizard - should I try the custom query
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40391660
I am running a Danish version of PowerPivot. I can see, that I use the Table Import Wizard - but I do not find anything that could be translated to custom Query, So please be a Little more specific if I need to use that one.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40391715
May I ask what makes you think you have a one to many going from the many table? If it's the direction of the arrows, don't let them confuse you - they point to the 'one' table (the lookup table, effectively).
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40391729
OK that was actually a very good information to get. I will check to see if I get the totalfigures anyway, and hopefully that will solve my confusion
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40391755
Jorgen,

not sure if i still understood what could be the problem with your data.

but i have quickly created a dummy  data to simulate the scenario

see the attached dummy Access database that has three tables that has three primary keys and two foreign keys and see the relationship.  try to import that to powerpivot and then you would see that relationship would be the same as original as  it appears in access.  i have also attached the imported powerpivot file.

if you would like to explore on how to create forgien key in access database then see the video in the youtube link given below.

hope it helps.


http://www.youtube.com/watch?v=Tst-fFcYxn0
Database1.accdb
Powerpivot.xlsb
0
 
LVL 4

Author Comment

by:Jorgen
ID: 40391766
Hi JimJam

For some reason, I do not get access to import the database from my customer. I will try to get back to you tonight, when I can access from my own machine.

regards

Jørgen
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question