locked
Relationships need to be recreated after source data updated RRS feed

  • Question

  • I have a very simple Powerpivot report based on data in a CSV file. I have a relationship to another simple table, joining dates.

    When the contents of the CSV file is changed, same structure, just more rows added, then relationship is broken. I have to delete it and re-add again.

    The relationship still exists, and it does not show any error, however all reports blank until I delete & re-add the relationship.

     

    Thursday, May 5, 2011 8:57 AM

Answers

  •  

    I found the reason why the joins were failing after data is added to one of the tables.

    In my "Fact" source, I have a column containing a date & time value

    e.g:

    FullDate, Amount
    21/5/2011 14:35, 50
    21/5/2011 17:51, 43
    21/5/2011 23:45, 20
    22/5/2011 09:16, 58

    I created a CalculatedColumn to simplify the FullDate to just a date, without time, as follows: DATE(YEAR([FullDate]),MONTH([FullDate]),DAY([FullDate])) , then joined this column to my Date table. 

    This caused the issue with the relationships when I added data.

    If I simplified the date BEFORE loading into Powerpivot, i.e. in the source Excel Sheet, then the issue does not occur.

     

    Wednesday, June 1, 2011 9:05 AM

All replies

  • No responses? 

    This issue occurs on all Powerpivot cubes I have. Is this normal behaviour, that the relationships are invalidated when the contents of any table changes?

     

     

    Friday, May 20, 2011 1:09 PM
  • Hi Benzane

    I performed some tests with an identical scenario but the unique identifiers were of type integer. There were no problems when removing or adding data. But the trouble started when some ID's were duplicated. The problem persisted even when I removed and created again the relationship and added the expressions =RELATED(tabl[col]). One of the columns was still returning errors.

    When I replaced the function RELATED by expression =CALCULATE(VALUES('other table'[col]),FILTER('other table','other table'[col with ID]='current table'[col with ID])) no suprisingly no errors occured. 

    By my experience the expression with CALCULATE(...) is slower but more stable then the expression RELATED() which is pending on a relationship.    

     


    Eddy N.
    Monday, May 23, 2011 7:32 AM
  • Hi Eddy,

    I too am having the same issue as Benzane, however my source data is from a number of different Excel spreadsheets.

    When I refresh the table, and more rows are added in PowerPivot, I have to delete and then re-create the relationships.

    If I do not do this, my PowerPivot pivot tables and pivot charts do not show the correct data.

     

    Regards,

    Armen

    Wednesday, June 1, 2011 12:35 AM
  •  

    I found the reason why the joins were failing after data is added to one of the tables.

    In my "Fact" source, I have a column containing a date & time value

    e.g:

    FullDate, Amount
    21/5/2011 14:35, 50
    21/5/2011 17:51, 43
    21/5/2011 23:45, 20
    22/5/2011 09:16, 58

    I created a CalculatedColumn to simplify the FullDate to just a date, without time, as follows: DATE(YEAR([FullDate]),MONTH([FullDate]),DAY([FullDate])) , then joined this column to my Date table. 

    This caused the issue with the relationships when I added data.

    If I simplified the date BEFORE loading into Powerpivot, i.e. in the source Excel Sheet, then the issue does not occur.

     

    Wednesday, June 1, 2011 9:05 AM
  • I encountered the same thing. Joining on a calculated column shouldn't be a problem should it? It's a common requirement.
    Wednesday, July 6, 2011 8:30 PM