locked
Working with PowerPivot after underlying data structure changes RRS feed

  • Question

  • Hi,

    in order to organize to better the work with PowerPivot when frequent changes to underlying data structure occur, any suggests? It seems that PowerPivot doen't have any capacity to detect any structure changes (fe a renamed table, a renamed columns, a data type change, etc.).

    Thanks

    Friday, December 16, 2011 9:03 AM

Answers

  • Hi Pscorca,

    It will detect the changes on data structure, but you have to click the TableProperties button to remap the relationship between source talbe and workbook in PowerPivot.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Sunday, December 25, 2011 7:36 AM
    Wednesday, December 21, 2011 1:23 AM

All replies

  • The best practice is to use views that expose data to PowerPivot, instead of getting data directly from tables. If you can define views on your data source (i.e. a SQL database), this is the best thing to do, also because there is a sort of "declaration" of exported data in your database. However, this is not useful for other data sources like Excel, text files and Data Feeds, for example.


    Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
    Sunday, December 18, 2011 9:22 AM
  • Hi Pscorca,

    Thanks for Marco's advise, to this scenario, you just need to switch to Design tab in PowerPivot windows, then click the TableProperties button to remap the relationship between source talbe and workbook in PowerPivot.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by xrrazr Thursday, December 22, 2011 12:27 AM
    Tuesday, December 20, 2011 7:23 AM
  • Hi.

    Ok for the views that allow to create a logical layer between the db and PowerPivot, but this solution functions when fe a data type is changed.

    I think to a mechanism inside PowerPivot to detect the changes on data structure.

    Thanks

    Tuesday, December 20, 2011 11:21 AM
  • Hi Pscorca,

    It will detect the changes on data structure, but you have to click the TableProperties button to remap the relationship between source talbe and workbook in PowerPivot.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Challen Fu Sunday, December 25, 2011 7:36 AM
    Wednesday, December 21, 2011 1:23 AM
  • You can add Views between the SQL tables and PowerPivot, but if you're building a database underneath it, sometimes you'll need to change the datatype anyway. You can cover this with views but then the changes in the source will become unnoticed by developers of the PowerPivot workbooks. Is that desirable?

    In my opinion something should be shown like: These are the changes in the database since last refresh. What do you want do with it? Perhaps an impact analysis on the workbook could help the user : " the Datatype change and that has impact on this DAX expression" , etcetc....

    I wonder whether PowerQuery handle this better... but then the resulting datamodel is integrated in the existing powerpivot model...


    Monday, October 12, 2015 10:00 AM