This method has been blogged about in various places on the web. Below is my take on a recent implementation of this method with some screen shots, helpful hints and some things to lookout for.
One approach to dynamically allowing / denying access to the data contained within an Analysis Services Cube via PerformancePoint is through Custom Data. Custom Data allows Analysis Services to include the currently authenticated user name as a parameter on the custom data field in an Analysis Services connection string from PerformancePoint.
There are many objects and steps that must be implemented in order to properly configure Custom Data including Data Warehouse objects, Analysis Services Data Source Views, Measure Groups, Dimensions, Roles and PerformancePoint data connection properties. Each step is outlined in detail below.
Data Warehouse Objects
A dimension table must be created containing the Active Directory login for each user who will be accessing the data.
A bridge, or “factless” fact table, must also be created to map the users in the dimension table to the appropriate data in the corresponding dimension table. For this example, we are using Accounts.
Analysis Services Objects
Data Source View
The newly created user dimension and security fact table need to be added to the data source view. The relationships between the objects should be represented as User –> Fact Security –> Account –> Fact Table as noted in the image below:
Measure Groups
A measure group must be created based on the Fact Security table with at least one measure defined. Both the Measure Group and Measure should be hidden as those browsing the cube will not need access to this information.
Dimensions
A dimension based on the user login table is also necessary. This dimension and corresponding attributes should also be hidden.
Add the dimension to the cube and ensure that the dimension usage is represented similar to this:
Roles
Create a new role with read access to the database, data source, cube and dimensions. On the dimension data tab, under advanced, specify the dynamic MDX expression to either grant or deny access to the dimension data.
Note that CustomData() MDX function in the above string will retrieve the authenticated user name from the PerformancePoint connection string.
PerformancePoint Data Connection
In the data connection editor within Dashboard Designer, the role used to access the cube as well as the option for “Unattended Service Account and add authenticated user name in connection string” must be specified.
Verification
Start SQL Server Profiler prior to accessing the Dashboard. Once the page is accessed, in SQL Server Profiler, the unattended service account will be reflected in the NTUserName and the authenticated user login will be reflected in the Custom Data property.
So that’s that. A few things to point out are:
1. An interface and process should be created to allow appropriate users to map active directory logins to dimension attributes for which they should have access (SharePoint list is a great candidate for this).
2. A security fact table must be created for each measure group that requires restricted access.
3. The dynamic attribute security must be created for each attribute that requires restricted access.
Happy securing!
One approach to dynamically allowing / denying access to the data contained within an Analysis Services Cube via PerformancePoint is through Custom Data. Custom Data allows Analysis Services to include the currently authenticated user name as a parameter on the custom data field in an Analysis Services connection string from PerformancePoint.
There are many objects and steps that must be implemented in order to properly configure Custom Data including Data Warehouse objects, Analysis Services Data Source Views, Measure Groups, Dimensions, Roles and PerformancePoint data connection properties. Each step is outlined in detail below.
Data Warehouse Objects
A dimension table must be created containing the Active Directory login for each user who will be accessing the data.
A bridge, or “factless” fact table, must also be created to map the users in the dimension table to the appropriate data in the corresponding dimension table. For this example, we are using Accounts.
Analysis Services Objects
Data Source View
The newly created user dimension and security fact table need to be added to the data source view. The relationships between the objects should be represented as User –> Fact Security –> Account –> Fact Table as noted in the image below:
Measure Groups
A measure group must be created based on the Fact Security table with at least one measure defined. Both the Measure Group and Measure should be hidden as those browsing the cube will not need access to this information.
Dimensions
A dimension based on the user login table is also necessary. This dimension and corresponding attributes should also be hidden.
Add the dimension to the cube and ensure that the dimension usage is represented similar to this:
Roles
Create a new role with read access to the database, data source, cube and dimensions. On the dimension data tab, under advanced, specify the dynamic MDX expression to either grant or deny access to the dimension data.
Note that CustomData() MDX function in the above string will retrieve the authenticated user name from the PerformancePoint connection string.
PerformancePoint Data Connection
In the data connection editor within Dashboard Designer, the role used to access the cube as well as the option for “Unattended Service Account and add authenticated user name in connection string” must be specified.
Verification
Start SQL Server Profiler prior to accessing the Dashboard. Once the page is accessed, in SQL Server Profiler, the unattended service account will be reflected in the NTUserName and the authenticated user login will be reflected in the Custom Data property.
So that’s that. A few things to point out are:
1. An interface and process should be created to allow appropriate users to map active directory logins to dimension attributes for which they should have access (SharePoint list is a great candidate for this).
2. A security fact table must be created for each measure group that requires restricted access.
3. The dynamic attribute security must be created for each attribute that requires restricted access.
Happy securing!
Technorati Tags: Business Intelligence Consulting,Custom Data,Analysis Services,PerformancePoint,Security
