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
  • Hi David,

    I had this issue today. I had a PowerPivot model connected to views that implemented Geography based security. When I refreshed the model, I would get Worldwide data, but when I distributed the file to the field, I needed it to be empty so they could refresh and pull only their market. I got really frustrated when there was no simple "Unprocess" like in SSAS. I know this solution is not elegant, and will probably not apply, but here's how I solved it.

    I backed up the database I was connected to (here's where I imagine i'll lose most people, as they won't be admins on the SQL side), and when I restored it, I truncated the tables that had the sensitive data. I then pointed PowerPivot to this empty DB, refreshed, and the data fell out. Then I updated the connection back to true production, saved, and sent the file (it doesn't automatically refresh when you update a connection, thankfully).

    I imagine there is a better way, and I would love to hear it if so!

    -Nick

    Wednesday, June 10, 2015 8:09 AM
  • Hi Nick,

    I face the same challenge and I solved it in the following way:

    1. I am using views in the database as source for the Power Pivot data model instead of tables. Anyhow, this gives me better control and an extra layer of data modelling possibilities.
    2. I created second copies of the views with an always false WHERE clause so that they return 0 rows. I give them the original view name with an "_empty" ending.
    3. When I am ready for distribution, I rename the original views and replace them with the empty ones.
    4. In Excel I ask for Refresh for the affected tables (or refresh all). The datamodel is emptied.
    5. I name the view(s) in their original form.
    6. I distribute the Excel files with the empty data model.

    I created some SQL scripts to do renaming automatically, something like this (and a similar one for naming back):

    USE MyDBName
    GO
    EXEC sp_rename 'MyView1', 'MyView1_data'
    EXEC sp_rename 'MyView1_empty', 'MyView1'
    EXEC sp_rename 'MyView2', 'MyView2_data'
    EXEC sp_rename 'MyView2_empty', 'MyView2'
    GO
    

    When your users receive the file, it is empty, but contains the correct connection data (possibly the password is not saved). They ask for Refresh All, give the password and the live data is loaded for them.

    Gabor

    Tuesday, August 18, 2015 3:47 PM