locked
Data based on User's access in powerpivot in Excel RRS feed

  • Question

  • Hello

    Is there any way to restrict data access to Users in powerpivot on Sharepoint 2010?

    For e.g. If User has access to North America data, then when User log into sharepoint 2010 and go to powerpivot library and open document, he or she can see only data from North America?

    Thanks

    Thursday, November 8, 2012 7:33 PM

Answers

  • PowerPivot doesn't support Security, that's why I was asking if you were using SQL Server Data Tools in Visual Studio, or just PowerPivot. You could setup security in SharePoint but that wouldn't be so nice, since you'd have to setup a workbook for each type of User, and then set which users can open a particular workbook.

    If you already have the data modeled in PowerPivot you can import that model into an Analysis Services server, and add the security inside the server. Check out this blog I have on security in the AS server.

    http://blogs.msdn.com/b/analysisservices/archive/2012/08/10/row-level-security-in-analysis-services-2012.aspx

    • Proposed as answer by Elvis Long Monday, November 12, 2012 10:20 AM
    • Marked as answer by Elvis Long Monday, November 19, 2012 7:38 AM
    Thursday, November 8, 2012 10:30 PM

All replies

  • Is your data on a Analysis Services Server, or PP? AS servers support row level security in tabular mode so you can say that certain users only see certain parts of the data. Though any PP models based on this would have the data for the user that created them. You could use a user that sees no data though, and have the user refresh the pivot table on launch of Excel.
    Thursday, November 8, 2012 8:36 PM
  • Data is coming from relational table to powerpivot. I already have security table in the database, where it describes - which user has what access..

    So how to take that logic into powerpivot?

    Thanks

    Thursday, November 8, 2012 8:46 PM
  • PowerPivot doesn't support Security, that's why I was asking if you were using SQL Server Data Tools in Visual Studio, or just PowerPivot. You could setup security in SharePoint but that wouldn't be so nice, since you'd have to setup a workbook for each type of User, and then set which users can open a particular workbook.

    If you already have the data modeled in PowerPivot you can import that model into an Analysis Services server, and add the security inside the server. Check out this blog I have on security in the AS server.

    http://blogs.msdn.com/b/analysisservices/archive/2012/08/10/row-level-security-in-analysis-services-2012.aspx

    • Proposed as answer by Elvis Long Monday, November 12, 2012 10:20 AM
    • Marked as answer by Elvis Long Monday, November 19, 2012 7:38 AM
    Thursday, November 8, 2012 10:30 PM