Securing your Cube Data

Restricting access to certain cell values of the cube for users is referred to as cell security. For example, in the case of confidential information like employee salaries, you can allow your employees to browse information about other employees such as number of years in the company, title, phone number, address, and login information, but restrict salary information. Because you want the information viewable by the person's manager, you need to control access at the cell value level rather than for whole dimension members.

Similar to dimension security, Analysis Services allows you to specify permission to cells using the roles. Access to cell values in a cube is restricted through an MDX expression that can be defined similar to dimension security. The MDX expression needs to evaluate to true or false. You can specify read and write permissions for cells in a cube. When a query is sent to the Analysis Services instance, the cells that are part of that query result are evaluated and returned. Whenever a cell is being evaluated, Analysis Services checks the permissions set for the cell. If the permission is set, it evaluates the condition to see if the user has access to the cell. If the user is allowed to view the cell, that cell value would be returned as part of the result. If the user does not have access to that specific cell, an appropriate message will be returned to the user.

Scenario using Cell Security

Business Scenario definition: You are the director of the company and you want to take a satisfaction survey or poll of your employees. Employees can only view results of the survey they have filled in. However, managers can view the aggregated results of the poll results if and only if they have more than two direct reports. Managers cannot see individual responses of their direct reports because this is a confidential survey. As an administrator you need to implement a UDM in Analysis Services 2005 so that you give appropriate security restrictions to the users to see the results.

You will use the same Dimension and Cell security relational database. This database contains tables that have the questions of the poll and the results from the employees. The following steps show how to create the right UDM and then apply cell security restrictions for the employees.

  1. Create a data source to the relational database Dimension and Cell security relational data source.

  2. Create a data source view that includes all the tables from the data source. Remove the security table from the DSV because this is primarily used only for defining dynamic dimension security. Create the joins between the tables as shown in Figure 19-26. Browse the tables Poll Questions, Poll Options, and Results so that you get a good understanding of the scenario. You will notice there are 25 Poll questions with responses from each employee stored in the Results table. Create a calculated column in the employee table so that you have a column called Full name — this will contain a string concatenation of Firstname and LastName. Enter the name for the calculation as Name and enter [First Name] + [Last Name] in the expression.

    image from book
    Figure 19-26

  3. Run the intellicube wizard to create the cube. You will now have the UDM as shown in Figure 19-27.

    image from book
    Figure 19-27

  4. Delete the count measures created by the intellicube wizard for both the measure groups because they are not needed for analysis in this scenario. The response measure contains a value corresponding to response whether the employee agrees or disagrees to the question. Make the AggregationFunction (a property of the measure) for the response measure as Count instead of Sum since we want to analyze how many users agreed or disagreed to the poll questions.

  5. Open the Employee dimension editor and rename the key attribute from Employee to Employee id. Then rename the parent attribute hierarchy as Employee so that you can browse the employee hierarchy. Make the named column for the key attribute of the employee dimension point to the named column Name so that when you browse the parent-child hierarchy you see the names of the employees instead of the ids.

  6. In this business example, managers are also involved in the survey. You need a way to distinguish the responses of the manager against the responses of the aggregated results of the direct reports. In order to distinguish the results, set the MembersWithDataCaption property of the parent attribute to "(* data)" as shown in Figure 19-28.

    image from book
    Figure 19-28

  7. Having created the UDM, you now need to define security restrictions for the cells as per the business requirement. Similar to dimension security, create a new role in the database, add all the employees to the membership collection, and provide read access to the cube and the dimensions in the database. Click the Cell Data tab. Click the drop-down list box for the Cube and select the cube Dimension and Cell Security as shown in Figure 19-29.

    image from book
    Figure 19-29

    The three panes shown in Figure 19-29 help you define the MDX expression for securing the cells. The MDX expression specified here needs to evaluate to either true or false, and this expression gets evaluated for each cell. You need to be careful in specifying the right MDX expression so that you secure the cell values correctly. You can define read permission, read contingent permission, and read/write permission to the cells. The read and read/write permissions are intuitive as to what the behavior is. If the MDX expression evaluates to true, either read or read/write access for that specific cell is provided to the current user accessing the cell. If an expression is specified for read-contingent permission then the cells specified as viewable by the MDX expression are viewable under two conditions. If those cells are not derived from other cells then they are viewable based on the MDX expression. If those cells are derived from other cells in the cube then those cells are viewable only if that cell and all the cells from which it is derived from are viewable.

  8. Following is the MDX expression that needs to be entered in the Enable read permissions pane to meet the business requirement of allowing employees to view their individual responses and managers to see the aggregated response of the poll questions whenever they have more than two direct reports. In the dimension security section, we mentioned that we will demonstrate a complex MDX expression to retrieve the employee name based on the login. The following MDX expression is a generic MDX expression and does not depend on the login name to be the users name.

   iif (     count (intersect (     descendants (     iif ( hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent.datamember is     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0),     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent,     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0))     ).item (0)     , employee.employee.currentmember)) > 2   // Condition Check   ,     (count (employee.employee.currentmember.children) > 2     and     count (intersect (     descendants (     iif ( hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent.datamember is     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0),     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent,     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0))     ).item (0)     , employee.employee.currentmember))       > 0 ) or    (strcomp ( ("login"),username) =0),     // Value 1     ( count (intersect (     descendants (     iif ( hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent.datamember is     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0),     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent,     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0))     ).item (0)     , employee.employee.currentmember))       > 0 ) or (strcomp ( ("login"),username)    =0)     ) // Value 2 

The above MDX expression can be broken up into three different parts for easier understanding. First, the MDX expression checks if the current user logged in is a manager. If the user is a manager with more than two direct reports, then value 1 expression is evaluated. If the user is a regular employee, then value 2 expression is evaluated. This is done using the IIF statement. The following MDX expression is used to identify if the current user is a manager with more than two direct reports:

    count (intersect (     descendants (    // Check if current employee is a manager     iif ( hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent.datamember is     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0),    // End of check if current employee is manager     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")),    post).item (0).item (0).parent,     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0))     ).item (0)     , employee.employee.currentmember)) > 2 

The Username function is used to retrieve the current user's login. With the help of StrToMember function and appropriate string concatenation the corresponding member in Login hierarchy is identified. The MDX function Exists is used to identify the intersection of the Employee hierarchy with the member in the login hierarchy for the current user. The result of the Exists function is a set that will contain the employee's name and all his parents. In order to retrieve the employee's name, we use the Hierarchize MDX function with the parameter Post so all the members in the set are ordered in a hierarchical order so that the employee name is the first item in the set. We then retrieve the first item of the set using .ITEM (0).ITEM (0) to retrieve the employee's name. In a parent-child hierarchy, if a member is a parent and also has data values (manager having sales quotas), the same employee name is used to represent the real member as the one that will have the aggregated values. However, these employee names will be at different levels in the parent-child hierarchy, which helps in distinguishing its own data value from the aggregated value for that member. This is shown in Figure 19-30 for the employee Rob Brown.

image from book
Figure 19-30

To check if the current member is a manager or not, we use the .parent.datamember function and compare it against another MDX expression that just gives the employee name. This MDX expression for evaluating if the current user is a manager is enclosed within comments in the above MDX expression. Based on the evaluation, the correct employee name is identified and we check if the employee has more than two direct reports using the MDX function Descendants.

Once a member has been identified as a manger having more than two direct reports, the IIF function chooses the following expression as the expression for evaluation:

     (count (employee.employee.currentmember.children) > 2 // Check 1       and       count (intersect (       descendants (       iif ( hierarchize (exists ([Employee].[Employee].members,       strtomember ("[Employee].[login].[login].&["+username+"]")),     post).item (0).item (0).parent.datamember is       hierarchize (exists ([Employee].[Employee].members,       strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0),       hierarchize (exists ([Employee].[Employee].members,       strtomember ("[Employee].[login].[login].&["+username+"]")),     post).item (0).item (0).parent,     hierarchize (exists ([Employee].[Employee].members,     strtomember ("[Employee].[login].[login].&["+username+"]")), post).item (0).item (0))     ).item (0)     , employee.employee.currentmember))         > 0 ) // Check 2     or     (strcomp ( ("login"),username) =0)     // Check 3 

In this expression two checks are performed to give access to the cells for the employee. The first conditional check (Check 1 AND Check 2) is for providing access to the aggregated cell for the managers, which involves checking if the employee corresponding to the current cell has more than two direct reports followed by the second condition (Check 3), which is an OR condition to allow access to the cells of the employee themselves. The second check (Check 3) is a simple check to match the employee with the login name because login is a member property for the employee attribute. The first condition has two conditional checks, Check 1 and Check 2, which are combined by a logical AND. By default parent members can see the cell values of their descendants (Check 2). In order to make sure individual cell values are not seen by managers, rather just the aggregated cell values can be seen, the additional conditional check (Check 1) is done in the preceding expression.

If the first argument of the IIF function evaluates to false, the result of the third argument of the IIF function will be the result of the function. The third argument is basically the MDX expression to allow regular employees or managers with less than or equal to two direct reports to see their individual responses of the Poll.

You may have grasped the entire MDX cell security expression that solves the business problem by now. You can verify the results of your expression using the cube browser and choosing a specific user or by sending queries from SQL Server Management Studio as a specific user. Launch SQL Server Management Studio using the Run As command with the user Rob. Send the following query to an instance of Analysis Services:

     SELECT [Measures].[Response] ON 0,     {DESCENDANTS ([Employee].[Employee].[Level 02].&[1])}*     [Poll Options].[Value].MEMBERS ON 1     FROM [Dimension And Cell Security] 

You will see results for the query with certain cell values showing #N/A, as you can see in Figure 19-31.

image from book
Figure 19-31

If you click the cell with #N/A you will see a message that the cell has been secured, as shown in Figure 19-32. You have now successfully solved the business problem of securing the poll results so that managers can only see the aggregated results if they have more than two direct reports.

image from book
Figure 19-32

Writing an MDX expression like the one shown in this example is not trivial. Even an expert MDX developer is bound to make some mistakes. We recommend you execute sections of your MDX expression as MDX queries or create MDX expressions as calculated members (especially in the cases where the MDX expression contains .currentmember), and then ensure the results of the MDX expressions are correct. Once you have validated your MDX expressions, you will be able to successfully define cell security and verify that cell security is applied correctly.

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176 © 2008-2017.
If you may any questions please contact us: