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
  • Hi, I was looking for this same solution and was surprised to find pretty low amount of chatter on it. I came up with my own solution, which works for me, but is somewhat specific to the environment. Thought I'd share it for posterity though. 

    So, use case is exactly the same as Nick's. All tables are populated by views which apply row level security based on mapping users to geographies they are allowed to see. My security is wide open, so when I hit refresh I get it all. I want to be able to distribute an empty workbook that populates with allowed data for the user that hits refresh. 

    In our case, we are using ActiveDirectory identity to map users to geographies, so I took one of our testing service accounts that was lying around and gave it access to the objects (the views, in this case) a user would have access to, but did not map it to any geographies. So it sees all of the views that a user can see, but when it queries any of them, it returns 0 results. 

    When I'm ready to distribute. I close Excel, then Shift+right click to fire up Excel and choose to run Excel as different user. I use the credentials of my service account to open Excel, open the PowerPivot workbook from that session, and when I hit refresh, all my tables go empty. Save the workbook. Nice and lightweight, easy to email/upload, etc, no confidential data in workbook. 

    Friday, March 18, 2016 6:43 PM
  • Hi,

    I use the following solution for David's original problem. When I plan to delete the data from my datamodel, I add an extra WHERE clause to each of my views the datamodel is reading from. The WHERE clause should be evaluated as FALSE, thus no rows are returned.

    As I have many views and as I don't want to modify my view definitions all the time, I use a SETVAR variable that controls that I would like to get the data or not. I use SQL Server Management Studio where it should be done as follows. If I set Test = 1, no data will be returned. If I set it to 0, the views will return all data normally.

    -- Set the variable to 1 if you woud like to retrieve empty database for distribution
    -- Reuires switching to SQLCMD mode: under Query->SQLCMD
    
    :setvar Test 0
    
    GO
    CREATE VIEW dbo.MYVIEW AS
    SELECT
    -- any select definitions ...
    WHERE $(Test) <> 1

    So far it is working fine for me. Not the most convenient solution, but works. Please tell your ideas about it.

    Gabor


    Saturday, March 19, 2016 7:09 AM