locked
2 questions regarding published power pivot models administration RRS feed

  • Question

  • hi eb

    1) power pivot developers keep getting the enoying msg "a schedule cannot be enabled for workbook..."

    when trying to set a data refresh schedule for a published power pivot model (in sharepoint power pivot gallery)

    until i give them admin permissions on the sharepoint's ssas\powerpivot instance.

    is this a mandatory requirement?

    Should all self service power pivot publishers become administrators on the sharepoint's ssas\powerpivot instance??

    2)

    I was told by a senior MS architect that data persmissions (roles) are not supported on sharepoint power pivot models

    but i do see in those models a Roles folder so i m confused can i or can i not manage security roles over there??

    TIA!

    Rea

    Thursday, April 24, 2014 1:22 PM

Answers

  • I see. The user shouldn't need full Admin privileges a in SharePoint. Contributor privileges or higher should be enough.

    On the Roles, I am now confused as to whether you are talking about standalone PowerPivot workbooks on SharePoint or SSAS models frontended on SharePoint. You can use roles and row level security if it is SSAS but not standalone workbooks.

    For a workaround, you might try URL String Filters as described here: http://www.powerpivotpro.com/2012/05/drill-across-in-powerpivot-live-demo/

    Maybe you could come up with a workaround using this and the USERNAME() function. Never tried it myself but it may work if the model and final report are configured properly. In addition to query string filters, This really wouldn't be secure since a user can modify URL parameters. Sharepoint also has a Current User Filter so maybe that's your option. Again, never tried it myself.


    Wednesday, April 30, 2014 11:37 AM
    Answerer

All replies

  • Any ideas for Rea's questions?

    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!

    Tuesday, April 29, 2014 12:06 AM
  • 1.  If I understand you problem correctly, you should be able to configure a default refresh account that is used by PowerPivot every time it connects to the data source regardless of user.  This default account needs to be set up by an Admin and then you have to make sure that it has permissions to the data source.

    2. PowerPivot hosted on SharePoint has the file level security of SharePoint.  That means you can control who can view or edit a PowerPivot file stored on SharePoint.  What PowerPivot does not have is row level security based roles.  So if someone has permissions to a PowerPivot workbook they have permissions to all the data it contains.  In SSAS Tabular, you can create role based permissions on individual rows of the tables in facts/dimensions.  In that case, if your data model contains worldwide data, and a user only has permissions to US related data then they will only see that data from whatever analytics tool they are using.

    Tuesday, April 29, 2014 3:14 PM
    Answerer
  • Thanks

    The schedulling problem appears much earlier - on user's first attempt to define a schedule...

    not on attempting to execute one that had been defined.

    So if user is not admin on the sharepoint's ssas\powerpivot instnce he does not get the option to define schedules at all...

    About data roles limitation in sharepoint's powerpivot models - i understand your unswer.

    Still it is very confusing that the roles folder does appear in models published over there...

    By the way - do you have some work around to implementing differential data filters

    in powerpivot models instead of data roles??

    say a dynamic custom filter with a case formula that sets a value to that filter acording to current user??

    Thanks again folks!

    Rea

    Wednesday, April 30, 2014 7:13 AM
  • I see. The user shouldn't need full Admin privileges a in SharePoint. Contributor privileges or higher should be enough.

    On the Roles, I am now confused as to whether you are talking about standalone PowerPivot workbooks on SharePoint or SSAS models frontended on SharePoint. You can use roles and row level security if it is SSAS but not standalone workbooks.

    For a workaround, you might try URL String Filters as described here: http://www.powerpivotpro.com/2012/05/drill-across-in-powerpivot-live-demo/

    Maybe you could come up with a workaround using this and the USERNAME() function. Never tried it myself but it may work if the model and final report are configured properly. In addition to query string filters, This really wouldn't be secure since a user can modify URL parameters. Sharepoint also has a Current User Filter so maybe that's your option. Again, never tried it myself.


    Wednesday, April 30, 2014 11:37 AM
    Answerer