Solved

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

Posted on 2014-10-20
9
109 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

813 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

11 Experts available now in Live!

Get 1:1 Help Now