Solved

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

Posted on 2014-10-20
9
104 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
  • 5
  • 3
9 Comments
 
LVL 25

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 25

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
 
LVL 4

Author Comment

by:Jorgen
ID: 40391655
Table Import Wizard - should I try the custom query
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 25

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now