On-demand, secure and user access based refresh of PowerPivot Data

[Vivek D]

I have a excel dashboard based on a PowerPivot model in Excel 2013.

I want to make it such that all users have a blank dashboard template. When user clicks on a Refresh button, latest data is fetched and only data that the user has access to is fetched and the dashboard refreshes.

Is there a way to achieve that ensuring the solution is secure i.e. DB details are not directly (at least not easily) available in the workbook and a user should not be able to pass someone else's id and get their data.

Have any of you done something like this or know of a way to achieve this?

 

[Chihiro]

A question. Will the dashboard only used in local network (i.e. company domain). Or will it be used outside of network?

 

[Vivek D]

A question. Will the dashboard only used in local network (i.e. company domain). Or will it be used outside of network?

Local Network only.

 

[Chihiro]

Normally I work with Access to grant specific data use to users (VBA to allow access to specific query only). But similar principle can be applied here.

Obtain environment variable for username and use DAX measure to filter/summarize data for data linked with specific user.

To get their user name from their domain login.

Dim uName As String

uName = Environ("username")

Or with domain name:

Dim duName as String
duName = Environ("userdomain") & "\" & Environ("username")

To create user specific dashboard, I'd probably go with method in the link. Using user name obtained from above code to do filtering operation.

http://www.cimaglobal.com/Thought-l...3/Insight-July-2013/Excel-extras---July-2013/

Not sure if you can have blank initially for template.

 

[Vivek D]

The problem with this approach though is that all data does reside within the powerpivot model and it only filters post that. From a security standpoint that's a little "iffy" and an added drawback would be that a user who might ideally need/have access to 1000 records would also end up having the full say 500K records within the file.

I would ideally like it such that the report goes to the DB and fetches only the relevant data so that data that the user doesn't need and shouldn't have access to, never makes its way to the report itself.

 

[Chihiro]

For that, you need PowerQuery. PowerPivot alone can't do it.

Another method is to build query in Access as intermediary step and do your security operation there.

Or if you have option (i.e. everyone has access to MS Access), create front end program based on MS Access and build your report there.