Using Custom Data and Custom Attributes to Filter SSAS Data
This applies to: Managed Dashboards, Managed Reports
By connecting to SSAS (SQL Server Analysis Services) using roles impersonation, you can automatically filter data according to the Symphony user who is logged on. However, this approach can become difficult to manage when there are a lot of roles.
Another option is to use the Custom Data setting of SSAS data connectors in conjunction with custom attributes to secure access to the data. This method requires only one role on the SSAS side.
This is an advanced topic for Symphony administrators.
Note: See the article on using a security hierarchy to filter SSAS data by user for an alternative that doesn't require configuration within the database or script.
The following walkthrough uses the Adventure Works DW sample database for illustration.
Set Up a Role in SSAS
Launch SQL Server Management Studio and connect to Analysis Services.
Create a new role (Role A) as described in Using SSAS roles impersonation but with the following differences:
- In the Role Properties dialog, click Dimension Data on the left.
- Set the Dimension drop-down to the Customer dimension.
- Set the Attribute Hierarchy drop-down to Country.
- Click the Advanced tab and enter the following MDX in the Allowed member set box.
STRTOMEMBER('[Customer].[Country].&['+CUSTOMDATA()+']')
This MDX is variable because of the CUSTOMDATA()
function which will return a custom attribute value that has been assigned to a Symphony user. This is the key to how the data will be filtered.
Create a Custom Attribute
In SymphonyAdministration, click Account Service to expand its items and choose Custom Attributes, then select Add New to add a new custom attribute.
In the Add Custom Attribute dialog, make the Name of the custom attribute Geography,and check that the Currently Selected Data Type is String.
Custom attributes are also applicable in a tenant environment:
If you are a system administrator and your environment includes multiple tenants, decide which tenant you'd like to apply the custom attribute to, and add them by selecting Select tenant. This opens a list of available tenants. Select a tenant, then Save the selection.
If you are an admin or other user working as a tenant admin or other tenant member, this custom attribute is automatically applied to the your current tenant.
Save the custom attribute. You'll see a message indicating the custom attribute has been saved, and it will be listed in the Custom Attributes dialog.
Set Up a User Group and Account
Create a new group named Role A. This name must match that of the SSAS role exactly.
Then, create a user account named User A and add as a member of the groupRole A.
Next, assign a custom attribute to the user.
Assign Custom Attributes to a User
Open the Accounts work area by navigating to Profile > Administration > Account Service > Accounts. A list of users opens for your environment, or for the tenant you are working in.
Select User A and Edit in the Contextual menu. The 'User A' account details work area opens.
Scroll to down to Custom attributes and select it to open a View custom attributes work area.
-
Select Add custom attribute to open the Select custom attributes dialog. This lists available custom attributes you can assign to this user.
Select the checkbox for the Geography custom attribute, then Save. An Edit Custom Attribute dialog opens.
Enter the value for this custom attribute (or multiple, if mufti-select is applicable to this attribute. In our example, France. Select Save to save this attribute.
Create a New Data Connector
From the main menu, create a new data connector using the SSAS provider:
- Set Windows Impersonation to Specified and enter the domain credentials that were added to the SSAS Membership pages when setting up Role A.
- Set the Database Name to the name of the SSAS database.
- Set the Impersonation drop-down to Roles.
- Expand the Miscellaneous section and enter the following script in the Custom Data box. This script retrieves the user's Geography custom attribute value and returns it to the CUSTOMDATA() function on the SSAS (MDX) side.
Create a New Dashboard
Create a new dashboard by dragging the Internet Sales Amount measure and the Country level from the newly created data connector to the dashboard canvas.
Check In your dashboard so other users can view it.
View the Dashboard
Log out of Symphony and log on as the restricted User A.
Open the dashboard for viewing and observe that the chart shows data for France only.
You can apply a similar process to make use of the Cell Security functionality in SSAS; however, you will have to disable certain MDX optimizations. To do so, locate the relevant measure from your cube in the Explore panel, right-click on it, select Slicer As Subquery, and select Disable.
For more information, see:
Comments
0 comments
Please sign in to leave a comment.