Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-20
9
Medium Priority
?
118 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 27

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 27

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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 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 2000 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 27

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

926 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