none
Deleting data content from PowerPivot data model

Answers

All replies

  • Hi David,

    Thanks for your post.

    I don't think that we can delete all of content data in the PowerPivot model. If we need to delete all of content data, we must delete the corresponding columns and that's doesn't make sense for data analysis. The security of PowerPivot data model is based on Excel file security level, it isn't support to implement cell data or columns security.

    In your case, we can consider create a Tabular data model for business analysis which support dynamic security functionality. Please take a look at the following article:
    SQL Server 2012 Tabular Models vs. PowerPivot: http://salvoz.com/blog/2012/01/15/sql-server-2012-tabular-models-vs-powerpivot/
    Implement Dynamic Security by Using Row Filters: http://msdn.microsoft.com/en-us/library/hh479759(v=sql.120).aspx
    Securing the Tabular BI Semantic Model: http://msdn.microsoft.com/en-us/library/jj127437.aspx

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, September 13, 2013 5:35 AM
    Moderator
  • Thank you for the reply.   I can understand why you'd be encouraging me to move forward into the full-fledged back-end solution with SQL Analysis Services.  (My software development background is already in Analysis Services and I'm investigating the PowerPivot alternative to see what level of problems it can solve.  In SSAS we simply have a command "Unprocess" that clears data out of the data model.)

    Notice that you only focused on the security aspect (first of four points).  But this really isn't just about security.  And in my case, security is already addressed in the data resource (ie. the data feed).  The only thing left to do is clear out the content data and give the workbook a fresh start.  Then the workbook can be distributed to new users as a sample/template.

    Are you saying there is some internal/technical reason why the data can't be unprocessed from the data model?  (You said deleting one forces you to delete the other).  I was hoping I might find an "unprocess" command in the new VB API for Excel 2013 data models (even if it isn't exposed in the UI).  But I'm not finding it there either...

    Monday, September 16, 2013 1:27 PM
  • Any ideas for David's question about the data model?

    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:05 PM
    Owner
  • David,

    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, November 01, 2013 5:24 PM
    Owner
  • So, it may not be an issue for him anymore but it is for me. This is something that the PowerPivot team needs to solve. It is not user friendly and selecting rows, then clicking delete (which only serves to delete the columns) is not only confusing, it isn't how Excel users are used to working.

    Jeff Levinson | ALM MVP
    Author, "Software Testing with Visual Studio 2010"

    Friday, November 08, 2013 3:32 PM
  • So, it may not be an issue for him anymore but it is for me. This is something that the PowerPivot team needs to solve. It is not user friendly and selecting rows, then clicking delete (which only serves to delete the columns) is not only confusing, it isn't how Excel users are used to working.

    Jeff Levinson | ALM MVP
    Author, "Software Testing with Visual Studio 2010"

    Great. Please open a Connect bug if you haven't yet.

    Thanks!


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

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

    Monday, November 11, 2013 4:29 PM
    Owner