none
Linked Fact Tables - Split columns

    Question

  • The powerpivot base queries split the fact table X columns in to 2 seperate facts

    R_Id | Field a..h | Table 1

    R_Id| Field  i...p |  Table 2

    now when i link them in powerpivot with R_Id the pivot table comes all wrong. R_Id is unique across in each table / query

    The MDX indicates slicers aren't applied on Measures in Table 2 and the R_Ids dont seemed to be linked in the first place as i get duplicate records in pivot table with distinct values for measure in Table 2

    what might i be doing wrong?

    Thursday, March 03, 2011 5:48 PM

Answers

  • Look at creating a third table in PowerPivot with just your IDs, as a IDdim (dimension) table (think Select distinct R_ID from ...) .  Then link both of your fact tables to this dim.  We had a similar issue joining up actuals to a budget and this solved it for us. 

    The key is when you bring in your ID to the pivot in excel, use the R_ID from the dim table, NOT from either fact table.

     


    Thanks, Barbara
    Thursday, March 10, 2011 6:45 PM

All replies

  • Hi geo360

    have you tried reversing the direction of the relationship?




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, March 03, 2011 8:55 PM
    Answerer
  • just tried that.. didn't help.:(

     

    The issues of duplicate rows  in pivot table remains

    Friday, March 04, 2011 8:43 AM
  • Could you post a same sample of what your data looks like?  In both tables? 


    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Friday, March 04, 2011 3:00 PM
    Answerer
  • at home right now. so cant paste a screenshot. This might give you an idea and the best i can do. sorry  :(

    general structure of pp spreadsheet

    <Slicers>

    <Flattened Pivot Table>

     

     

    whats happening
    
    |------Table 1--------------|------------------Table 2 ----------------------------------| 
    
    R_Id | R_Name | R_Area | R_Address | R_Telephone | R_Email     | R_Website 
    
    1      |     xyz    |  China   |   abc         |  123             |  S@s.com  | www.s.com
    
    1      |     xyz    |  China   |   mno        |  234             |  y@y.com  | www.y.com
    
    1      |     xyz    |  China   |   sdf        |  789             |  z@z.com  | www.z.com
    
    2      |     pqr    |  India    |   abc        |  123             |  s@s.com  | www.s.com
    
    2      |     pqr    |  India    |   mno        |  234             |  y@y.com  | www.y.com
    
    2      |     pqr    |  India    |   sdf        |  789             |  z@z.com  | www.z.com
    
    3      |     ghi    |  Japan    |   abc        |  123             |  S@s.com  | www.s.com
    
    3      |     ghi    |  Japan    |   mno        |  234             |  y@y.com  | www.y.com
    
    3      |     ghi    |  Japan    |   sdf        |  789             |  z@z.com  | www.z.com
    
    
    
    whats expected
    |------Table 1--------------|------------------Table 2 ----------------------------------| 
    
    R_Id | R_Name | R_Area | R_Address | R_Telephone | R_Email     | R_Website 
    
    1      |     xyz    |  China   |   abc         |  123             |  S@s.com  | www.s.com
    
    2      |     pqr    |  India    |   mno        |  234             |  y@y.com  | www.y.com
    
    3      |     ghi    |  Japan    |   sdf        |  789             |  z@z.com  | www.z.com
    
    


    I'm trying to link vertically partitioned fact on R_Id.  R_Id is unique in Table 1 and 2. It should be an 1-1 mapping.
    whats seems to be happening is Table 2 records are showing up unlinked to Table 1's R_id. 
    Friday, March 04, 2011 8:44 PM
  • Look at creating a third table in PowerPivot with just your IDs, as a IDdim (dimension) table (think Select distinct R_ID from ...) .  Then link both of your fact tables to this dim.  We had a similar issue joining up actuals to a budget and this solved it for us. 

    The key is when you bring in your ID to the pivot in excel, use the R_ID from the dim table, NOT from either fact table.

     


    Thanks, Barbara
    Thursday, March 10, 2011 6:45 PM