Can we use the "SSAS Security Role" feature in a Power Pivot Data Source Connection pointing to an OLAP Cube
In one of our BI applications, we have used the SSAS Security Role aspect to implement dynamic dimensional security on the OLAP Cube so that users accessing it via MS Excel are allowed to see only what they as assigned to see. We use the following in the Excel Connection String (where the Role Name is "Role"), to faciliate this:
Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<Cube Name>;Data Source=<Server Name>;Extended Properties="Role=Role";MDX Compatibility=1;Roles=Role;Safety Options=2;MDX Missing Member Mode=Error
Can we use something like this in PowerPivot when the data source for PowerPivot is an OLAP Cube? We couldnt figure out how to set the connection string like the above within the PowerPivot Data Source connection settings. It just allows "Windows Authentication".
Our intention is to build Reports in PowerView which uses a Data Model built within PowerPivot and the data source for PowerPivot is an OLAP Cube. We are doing this because we are not on the SQL 2012 environment yet - so have to rely on PowerPivot to be able to leverage PowerView. So when a user opens the PowerView report, they should be able to see only what they are supposed to see which will be driven by the Role attribute in the connection string all the way to the OLAP Cube.
Please let us know if anyone has worked on a similar scenario.
This isn't directly related to your question, I know, but I feel like I should point out that what you are doing with SSAS is not dynamic security and it isn't very secure. Surely, if one of your Excel users edited their connection string they would be able to see data they should not have access to?
Dynamic security in SSAS is something different - it involves using either the Username() function or (when users cannot connect directly to the cube themselves, but only through some other application that handles authentication itself) the CustomData() function in the role definition, so that a single role returns different permissions for different users. It doesn't require using the Roles connection string property.
In any case, you can't apply any sort of security to the data in a Power Pivot model - you can either see everything or nothing - so you won't be able to use this approach with Power Pivot unfortunately.
Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK