locked
One-to-one Issue in PowerPivot RRS feed

  • General discussion

  • Disclaimer: I am a PowerPivot and general database novice, so please forgive me for any incorrect use of terms or general lack of understanding.<o:p></o:p>

    I am currently using PowerPivot to create a comprehensive HR Metrics Dashboard at my company.  I would like to break up data pulled from my HRIS system into different tables based on how sensitive the data is (employee job info, salary data, performance data, etc.).  I am linking these with an employee ID as a primary key in all three tables, and I have some other related tables linked with foreign keys, as shown below.<o:p></o:p>

    I'm finding that when I'm running pivot tables on some data, like Salary by Job Family it works fine, but when I try to run Performance Score Numbers by Job family, it returns the same total sum values for each row.  <o:p></o:p>

    I would really appreciate help on figuring out how to keep these tables separate and still be able to run data through them as if they were one big table.  <o:p></o:p>

    Thank you in advance,<o:p></o:p>

    Dan Tip


    Monday, June 17, 2013 2:24 PM

All replies

  • For the filtering to work the way you describe, you need your relationship from PerformanceReviewScore to EmployeeData to be the opposite direction from what it currently is.  PerformanceReviewScore needs to be the "many" side, and EmployeeData needs to be the Lookup.  That way filters on GlobalJobLibrary naturally flow to EmployeeData and then to PerformanceReviewScore where the values are being aggregated from.

    Let me know if that helps.

    Also, Rob Collie recently did a couple posts on PowerPivotPro.com on this topic if you're interested.

    http://www.powerpivotpro.com/2013/06/the-dreaded-one-to-one-relationship-or-why-boaring-movies-are-best/


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Monday, June 17, 2013 3:47 PM
    Answerer
  • Thanks for the response, Brent!  

    So, I reversed the direction of the relationship from PerformanceReviewScore to EmployeeData, and now I can run Performance Scores by Job Family, but I can't run Salary by Performance Scores.  I would really like to do both without creating a lot of duplicate tables.  

    I haven't gotten a chance to peruse the posts you recommended yet though, so I'll go through those now.

    Monday, June 17, 2013 7:34 PM
  • So, after reading the posts, it seems that it does not address the issue of trying to go back and forth through one-to-one relationships.  Does anyone have any recommendations on how I can solve this issue?  Should I just move everything to Access?

    Thanks,

    Tuesday, June 18, 2013 6:55 PM
  • Is this still an issue?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, October 25, 2013 8:22 PM