Solved

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

Posted on 2014-10-20
9
111 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 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

830 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