Using UserName to Control Data Access and Default Member in SSAS 2K5 (Carrie Williams)

Written by Carrie Williams on 19 May 2007. Posted in Security

Summary: How to use UserName to limit access to data in SSAS and set the Default Member. We will modify the Adventure Works SSAS cube to demonstrate the use of UserName.

Contents

Introduction

Definition of UserName Function

Overview of the Employee Dimension Table

Overview of the Employee SSAS Dimension

Modifying the Employee SSAS Dimension

Add Dimension Level Security to a Role

Results Demonstrated in ProClarity (How to Test)

Conclusion

Introduction

This paper will discuss how to use the UserName function to restrict data access and control the default member in SSAS 2K5. To most effectively demonstrate the topic being addressed we will refer to the Adventure Works cube and discuss the modifications necessary. The Adventure Works cube is provided in the installation package of SQL Server 2K5.

Definition of UserName Function

Figure 1. Definition of UserName function from Books Online (BOL)

As the definition indicates, we will use the UserName function to retrieve the domain-name\user-name of the user currently accessing the cube. The result is used in the MDX expression for the default member of the Employees hierarchy and for dimension level security for the Employee dimension in our test security role. We will demonstrate these results in ProClarity Desktop Professional. Please note that this solution is not limited in its use to the ProClarity Desktop Professional; any tool that can be used to access an AS cube, including other ProClarity products, will also demonstrate the same results.

Overview of the Employee Dimension Table

The Adventure Works employee dimension table is designed to contain a parent-child hierarchy along with an Employee Department hierarchy and a large variety of employee related attributes (Figure 2). Most

Figure 2. DimEmployee in Object Explorer in SQL Server Management Studio

of the fields are attributes, with ParentEmployeeKey and EmployeeKey being the parent and child keys, respectively. The additional attribute field also important for this implementation is the LoginID, and we will demonstrate its importance. (Leave the Management Studio open as is for later.)

Overview of the Employee SSAS Dimension

Access the Employee dimension in the Business Intelligence Development Studio by opening up the Adventure Works cube, browsing the Solution Explorer and double-clicking on the Employee dimension (Figure 3).

Figure 3. Solution Explorer window - Adventure Works SSAS database.

Notice the existing attributes and user-defined hierarchies; there are two, Employees and Employee Department, however Employees is a parent-child hierarchy and does not appear in the Hierarchy section, but is denoted by the self-referencing symbol (Figure 4).

Figure 4. Employee Dimension.

Modify the Employee SSAS Dimension

In the existing Employee dimension we will make the following changes: 1) add the Login ID attribute – it will be used in conjunction with the UserName function, and 2) add an MDX expression for the DefaultMember property of the Employees attribute (parent attribute).

First, add the Login ID attribute to the dimension by dragging the LoginId column from the data source view (in Employee dimension tab) and dropping it under the last attribute in the Attributes panel. Open the properties window for the Login ID attribute by right-clicking on the attribute and selecting Properties. Because this attribute is not interesting for analysis or reporting set the AttributeHierarchyEnable property to false. You can leave the rest of the properties as they are.

Figure 5. Login ID dimension attribute properties window.

Next we’ll set the Employees DefaultMember property in the dimension attribute properties window. Again, open the Properties window by right-click on the Employees attribute and choosing Properties. Find

the DefaultMember property and click on the ellipsis to open the MDX Builder. Type in the MDX shown below in Figure 6. There are essentially three important parts of this MDX expression that we will explain. (1) The Filter function is used to filter out members from the Employees attribute (parameter one) that do not satisfy the criteria identified in the second parameter, and returns the members as a set {}. (2) The filter criteria (second parameter) uses the Login ID attribute and the UserName function to identify members whose Login ID is equal to the return value of the UserName function. (3) The Filter function in this expression should always return one member (employee member that is currently viewing the cube) as a set and therefore we will use the Item function with 0 as the parameter to retrieve the member from the set. In English this MDX expression reads “Set the default member of the Employees hierarchy to the member currently accessing the cube”. (Note: Be sure to save your changes.)

Figure 6. – The MDX Builder window for the DefaultMember property of Employees attribute.

Add Dimension Level Security to a Role

Now we will add the dimension level security for the employees dimension attribute to a new role. This dimension level security is used to restrict access to measure values associate with the currently logged in employee and that employee’s subordinates.

Before we get started on the new role it is first best to add a new user to the local users of the computer being used to test the results of our implementation. To do this we’ll need to access the Computer Management window (Figure 7). The method used most often to access this window is to right-click on the My Computer icon and choose Manage. Expand Local Users and Groups, right-click on the Users folder and choose New User. Give this new user a name and a password and select only “Password never expires” (this is just a test role; you can delete it at any time following your testing). Click OK to create the new user and then close the Computer Management window.

Figure 7. Computer Management window

Now we can begin creating the security role. Right-click on the Roles folder in the Solution Explorer panel (see Figure 3 again) and choose New Role. On the General tab of the new role select Read definition (Figure 8).

Figure 8. General tab of new role. Note - Your role will initially be called New until you rename it.

On the Membership tab, click the Add button and enter the name of the user previously created. You’ll need to add the user in the format: computer-name\user-name (Figure 9).

Figure 9. Membership tab of new role.

On the Data Sources tab, pick Read in the Access drop-down and select Read Definition.

Figure 10. Data Sources tab of new role.

On the Cubes tab, be sure that read access is select for the Adventure Works cube.

Figure 11. Cubes tab of new role.

The next tab, Dimension Data, is where we add the dimension attribute security. First, click on the drop down box to select the Employee dimension (Figure 12). Once the dimension is selected choose the Employees dimension attribute from the Attribute Hierarchy drop-down. Type in the MDX expression for the allowed member set on the Advanced tab as shown in Figure 13. This MDX expression is similar to the DefaultMember MDX expression previously discussed. The Allowed member set requires a set returned in the MDX expression, hence it is not necessary to use the Item(0) portion of the DefaultMember MDX expression. Although only one member is returned in the set by the expression, SSAS security is designed such that you will be able to view members above the returned member in a *parent-child* hierarchy, however, the measure values for those members will be the same as the returned member. This is demonstrated later in Figure 21. Click Save and close the role. Be sure to deploy your changes to the Adventure Works database if you’re using project files rather than directly connected to the Adventure Works database.

Figure 12. Dimension Data tab – select the dimension to modify attribute member security.

Figure 13. Allowed member set MDX expression.

Results Demonstrated in ProClarity (How to Test)

To demonstrate the changes we’ve made to the Adventure Works database it’s best to use the ProClarity Desktop Professional or one of the ProClarity web clients. For our demonstration we will use ProClarity Desktop Professional and will not provide details on the navigation of the tool, but rather discuss the results and necessary steps to provide the adequate test. In order to provide the truest test we will appear to view the cube as a member of the Employees hierarchy.

Open ProClarity and select Open Cube from the ….. C. View the Employees hierarchy expanded on rows with the Sales Amount Quota on columns (Figure 14). Notice that Amy E. Alberts has 3 subordinates and there are two levels of management above her. We will choose Amy E. Alberts as our employee to test. (You may leave ProClarity Desktop Professional open.)

Figure 14. View of Employees parent-child hierarchy in ProClarity.

Now let’s return to SQL Server Management Studio and the DimEmployee dimension table. Right-click on the table and choose SELECT TO. Add the following code to the end of the SQL statement that appears in the query window (Figure 15) and run the query.

WHERE FirstName = ‘Amy’ AND LastName = ‘Alberts’

Figure 15. Find Amy Alberts using a t-sql query

Notice that the EmployeeKey value is 290; this also represents the record number. Now open the DimEmployee table by right-clicking on the table and selecting Open. Use the record number (290) to find the employee record for Amy Alberts (Figure 16). Modify the record by replacing the existing value in the LoginID column with the user name of the test user previously created (Figure 17). Close the table; we are finished in Management Studio.

Figure 16. Find Amy Alberts record in DimEmployee table using Current Position Text Box

Figure 17. Type in your new user’s credentials for Amy Alberts

Return to ProClarity Desktop Professional and select Open Cube from the File menu. On the Open Cube dialog click on the Connect button to change the current connection options. On the Connect dialog click on Advanced to show the Advanced Options and enter your test user’s credentials (Figure 18). When you click OK ProClarity will open the cube using your test user’s credentials.

Figure 18. Connect to the cube as the new user.

Arrange the hierarchies so that the Employees hierarchy is on rows and the Sales Amount Quota is on columns. Notice the new default member for the Employees hierarchy. Now you can see that because our login information matches that of Amy E. Alberts (after modifying the dimension table), Amy E. Alberts is the default member (Figure 19).

Figure 19. ProClarity Desktop Professional (Note: DefaultMember = Amy E. Alberts)

Drill down on the Amy E. Alberts member to view her subordinates’ measure values for Sales Amount Quota (Figure 20). Access to the subordinates’ measure values is allowed.

Figure 20.

In the Setup Panel select the Amy E. Alberts subordinates and the two members above Amy in the hierarchy (Figure 21). Notice that although you can view Brian and Ken their Sales Amount Quotas are exactly that of Amy’s. Parent-child dimension security is designed in this manner – you may limit where you start in the hierarchy by default (using DefaultMember), and at which level you can view the actual measure values of the members. You cannot, however, prevent a user from seeing their parent members (again, the parents’ actual measure values are not displayed though).

Figure 21. ProClarity Desktop Professional (Employee hierarchy expanded with security enforced)

Conclusion

Using the UserName function we have demonstrated an example of a very simple yet effective data access solution for the Employees (parent-child) hierarchy of the Adventure Works SSAS database. The results of our changes to the DefaultMember property for the Employees attribute and the dimension level security added to the Employees attribute can be represented nicely in ProClarity Desktop Professional. To maintain this solution it is only necessary to maintain the DimEmployee dimension table making this an ideal solution for most SSAS implementations that require this type of data access restrictions.

Using UserName to Control Data Access and Default Member in SSAS 2K5 (Carrie Williams)

Introduction

Definition of UserName Function

Overview of the Employee Dimension Table

Overview of the Employee SSAS Dimension

Modify the Employee SSAS Dimension

Add Dimension Level Security to a Role

Results Demonstrated in ProClarity (How to Test)

Conclusion

Similar Articles