Filtering a workbook and managing the security

Question

Hi,

I need to know to how organize a workbook that filters by a Business Unit data. I'd like to obtain this scenario: a SharePoint user that belongs to a certain Business Unit (bu) when open my PowerPivot workbook must see only data of you bu. Is it possible? Do I need to create as many workbooks as many business units?

Any suggests to me to solve this issue, please?

Thanks

Answer 1

Hi psacorca,

Dynamical security is not supported on PowerPivot, there is no role concept to powerpivot, we usually create a public unattended account for the whole powerpivot. Thanks for your understanding.

However here I still would recommend you to submit a feedback to Microsoft Connect at this link at this link https://connect.microsoft.com/SQLServer/Feedback This connect site is a connection point between you and Microsoft. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

Regards,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Challen, thanks for your reply.

Filtering by a business unit or department could be a very interesting and useful feature. An user that belongs to a department do not must see the data related to another department. So a (bad, very bad!) workaround is to create as many workbooks as many business units/departments: if I have 10 business units/departments, I need to create 10 different workbooks! ...mhhh

Thanks 

Answer 3

Hi Pscorca,

Current version of PowerPivot doesn't support security, it's only access yes or no. In SQL denali PowerPivot CTP3, we can achieve that users only see data relevant to them. This normally corresponds to the role he or she has within the company. It is important to know that security is one of the features which can not be set up within the Excel PowerPivot addin but only within Visual Studio 2010 (BIDS). Microsoft feels like this is a typical feature that BI developers would create, not end users.  You can click on the “role manager” icon in Visual Studio 2010, then open the “Role manager” window in which you can define a “Role"

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Hi Challen, thanks for your reply.

Do you say that in the new version of PowerPivot, for SQL Server 2011, is the security manageable? Do you confirm?

Moreover, it is clear that the security must be implement from a technician, but how can I implement it for PowerPivot inside VSTO 2010? PowerPivot is an add-in for Excel.

Thanks

Answer 5

In details, when an employee, belonging to a certain business unit, accesses to the workbook containing a filter or a slicer for business unit, he must see only data related to the proper business unit.

With the new version of PowerPivot does this mechanism implement? Thanks

Answer 6

Denali release gives you two new options for implementing security requirements:

1. Denali introduces the username() function in DAX which will give you the option of creating dynamic security expressions.

http://msdn.microsoft.com/en-us/library/hh230954(v=SQL.110).aspx

http://smithicus.wordpress.com/2011/07/18/dynamic-security-in-sql-denali-powerpivot-ctp3/

2. In Denali you will have the option to migrate your powerpivot model to a tabular SSAS instance.

Which will give you more enterprise features at the cost of  losing Sharepoint integration. On a tabular instance you will be able to implement roles and set “row-level security”. http://msdn.microsoft.com/en-us/library/hh213165(v=SQL.110).aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hi Challen, thanks for your reply. It is a good start, but I need to wait the Denali RTM release and try it. Now I cannot do any actions.

Thanks