Securing Your Dimension Data

Often in business you have to restrict data access from certain sets of users. You might have to restrict whole members of a dimension or just cell values. Restricting access to members of a dimension to users is called dimension security. Restricting access to cell values from users is called cell security. You learn more about securing dimension members in this section, followed by restricting access to cell values in the following section with the help of a business scenario.

Dimension security helps you to restrict access to members of a dimension for your Analysis Services database users based on your business needs. For example, you can have a dimension account that could have members such as accounts payable, accounts receivable, and materials inventory for your company. You might want to restrict user access such that certain users can see only the account types that they are authorized to work with. For example, the personnel working in the accounts payable department should only be able to see the members under accounts payable and should not be able to see all the accounts under accounts receivable or materials inventory. Here is another example: If your company is selling products in various cities, you might want to restrict access to sales employees so that they can only see the data for which they are responsible on a city-by-city basis.

Analysis Services provides security restrictions on objects using an object called "role" as seen in Chapter 9. You can define roles in your database and then restrict permissions to certain members or cells based on those roles. There are several techniques to model security based on the user, and you learn those techniques in this section. A user or a group of users is typically part of a specific role, and all the users in a role will have the same level of security. A user can be part of one or more roles. An Analysis Services instance identifies a user based on the Windows login credentials. When a user connects to an Analysis Services instance, the server iterates through various roles within the server to determine the roles the user is part of. Based on the list of roles a user belongs to, Analysis Services establishes appropriate security restrictions specified in those roles. If a user is part of multiple roles, Analysis Services provides access to a union of all the roles the user is part of. The important thing to know about this union is that if two roles give contradicting indications for user access of some object, access will be allowed. So, this is not a process that tends to upset users. If you have a group of users whose security constraints keep changing dynamically, you do have design alternatives by which you can specify security dynamically. That this is called dynamic security should come as no surprise. You will see the use of dynamic security in the following business scenarios; you will also see the various approaches of securing dimensions that have been mentioned.

Scenario using Dimension Security

Business Problem definition: You are the data warehouse designer for the sales team in your company. You have sales representatives in certain states in the U.S. and each sales representative is responsible for sales within that state. The sales representatives report to regional managers who might also be responsible for sales in a state, and the regional managers report to the U.S. sales manager. The sales representatives can see the sales information of their state. The managers can see the sales information specific to them, as well as the data of the sales representatives reporting to them. You need to design a sales cube so that all the preceding security restrictions are applied to the users when they browse the cube.

We have generated data specifically for this scenario so that it will help you understand the various design techniques that can be applied. This data set contains a list of employees in a company along with several months' worth of sales data; there is also a geography table that contains a list of states. Follow these instructions to restore the relational database from which you will create a cube:

1. Copy the file DimensionandCellSecurity.bak to the back up folder of your Microsoft SQL Server.

2. Connect to the relational SQL Server in SQL Server Management Studio. Right-click the databases folder and select Restore. You will see the SQL Server Restore dialog as shown in Figure 19-3.

3. Select the "From device" option. Click on the … button and specify the entire path to the relational database back up. You will now see the various databases within the back up file listed below "Select the backup sets to restore."

4. Select the database "DimensionandCellSecurity-Full Database Backup" to restore as shown in Figure 19-3. Select the "To database" drop-down box and select DimensionAndCellSecurity as shown in Figure 19-3. Click the Options page in the Restore Database dialog and make sure the path for restoring the back up for the database and log files are specified correctly as shown in Figure 19-4. Once you have verified this, click the OK button. The database will be successfully restored on your machine.

   
   Figure 19-3

   
   Figure 19-4

5. In order to demonstrate the dimension and cell security, you need users on a domain. To keep it simple you will create local users on your current machine. Run the batch file runuserscript.bat that is provided in the Chapter 19 samples. You will now see that 15 users are added to your machine.

6. Some of the recommended solutions would need to detect the username along with domain name. The Employee table within the DimensionAndCellSecurity database has two columns called employee login and manager login. You have already created the login names for the users in the Employee table in step 5. You need to update the domain name in these columns to your machine name. To get the machine name of your system, open a command prompt and type hostname. Open the employee table by right-clicking it in SQL Server Management Studio and selecting Open Table. You will now have all the rows of the Employee table, as shown in Figure 19-5. Update the login, manager login, and access rights columns by replacing domain with your machine name (You can see that some of the login and manager logins for a few rows are updated with domain name as sivah04.)

   You are now ready to create a cube and restrict users to view sales information only for the states for which they are allowed to see the information.

   
   Figure 19-5

7. You need to build a cube from the relational data source. Figure 19-6 shows the UDM of the data warehouse. There is one measure group called Sales that contains the Sales information. The dimensions are Employee, Location, and Time. You can create the UDM yourself using the autobuild option of the cube wizard and select the Sales, Employee, Location, and Time tables from the data source. Alternatively you can use the Analysis Services project DimensionSecurity provided with Chapter 19. The manager-employee relationship in the employee dimension is modeled as a parent-child hierarchy. All the remaining attributes in the employee dimension are only used as member properties for the employee and the corresponding attribute hierarchies are made invisible.

Figure 19-6

Once you have created your UDM, the next step is to define security to restrict the data being seen from the users based on their location. The roles object in Analysis Services allows you to restrict data access based on the login of a user. The roles object contains a collection called membership which you learned in Chapter 12. You can add a user or a group of users to this membership collection. The security restrictions applied in this role will be applied to all the users in the membership collection. In this business problem you need to limit access to the sales representatives so that they can only see the sales information relevant to their state or their direct reports. You will learn several solutions to restrict the dimension member access along with their merits and de-merits.

Restricting a user to see only certain members of the dimension Location automatically restricts the user from seeing the sales information for that location. Location is a dimension and applying security or restrictions to users to certain members of a dimension is therefore called dimension security. If a user is part of more than one role, Analysis Services restricts the user to just a union of the roles the user is member of. For example, if a user is a member of Role1 where you have restricted the users to see location New York, and the user is also a member of Role2 where you have restricted the users of Role2 to location New Jersey, the user can see both these locations when he connects to Analysis Services. If Role1 had security restrictions for a user that does not allow you to see the dimension member New York and Role2 had security restrictions the same user in a way you are able to see the member New York, then the Analysis Services allows the user to access and retrieve the dimension member New York.

Now you will see the various design techniques concerning role definition and what the trade-offs are of those design techniques. Some of the techniques mentioned below are from the dynamic security presentations by Dave Wickert, Program Manager, Microsoft Corporation. These design techniques have been modified for Analysis Services 2005.

The User-Role Approach

One approach is to restrict location access by defining the list of locations a user can see. In order to do this you need to create a role for each user and define the restrictions so that user can only have access to members of specific states. In the example below you will create roles for the users David and Robert. The following instructions show how to solve the problem definition by creating roles for each user:

1. To create a new role for the Sales UDM, right-click the Roles folder and select New Role. A new role is created with the name Role and you will now see the roles designer. Right-click on the Role.role in solution explorer and rename it to David.role. You will be prompted with a dialog box asking you if the object name needs to be changed. Click the Yes option. The roles designer has several views, as shown in Figure 19-7. In the General view you can define administrative tasks on the cube, such as process permissions or permission to read definition of the objects in the database. You can also give full control at the database level, which means the users have full control to edit objects within this database.

   
   Figure 19-7

2. Click the Membership tab in the roles designer. Click the Add button to add a user to this role, and add the user David. You already created a user account for David earlier in this section. David's login account will be <machinename>\David, where machine name is the name of the machine you are working on. When you click the Add button you will see a dialog where you can enter the domainname\loginname. Enter your machine name followed by "\David" in this dialog and then click OK. You will now see that the user David has been added to the role Role as shown in Figure 19-8.

   
   Figure 19-8

3. The next step is to provide access to the cubes and dimensions in the database. Click the Cubes view. You will now see the list of cubes within the database. You can see your UDM Dimension Security. From the drop-down list box under Access, select Read access to the UDM as shown in Figure 19-9. By selecting this access type you have allowed users in this role the ability to read the data in the cube. In addition to providing access to the cube, you can also provide access to the users to drill-through to detail data or to process the cube in this pane. Leave the Local Cube/ Drillthrough Access option to None.

   
   Figure 19-9

4. After providing access to the cube, you can provide access to the database dimensions as well as the cube dimensions in the Dimensions View, as shown in Figure 19-10. The Select Dimension Set option allows you to choose the database dimensions or the cube dimensions. The default view shows all the dimensions in the database. Similar to providing access to the cube, you can provide Read or Read/Write access to each of the dimensions. If you specify Read definition, users have the ability to send discover statements to see the metadata information associated with the dimension. For the database dimension objects you can specify administrative tasks of read definition and processing, but for the cube dimension objects you can only specify access to read or read/write. You have the option to inherit the permissions specified for database dimensions by selecting the inherit check box next to the cube dimension. You can override the permissions set for database dimensions for cube dimensions by deselecting the checkbox under inherit column and then selecting appropriate permissions from the Access column. This is helpful whenever you have a dimension that is shared across multiple cubes within the database and users of a specific role only have specific permissions to dimensions within a specific cube. Provide Read access to all three dimensions as shown in Figure 19-10.

   
   Figure 19-10

5. Click the Dimension Data tab to restrict the members that can be seen by the current role. Specifically, you are restricting access to the user David. Because David is responsible for New York City, he should only be able to see sales information pertaining to New York City. In order to specify this you need to select the dimension Location from the Dimension drop-down list. Select the attribute hierarchy City from the Attribute Hierarchy drop-down list and then select the member New York, as shown in Figure 19-11. The selection of the city New York restricts the users of the role from seeing other cities when they access the dimension Location. If you have complex business logic concerning access to members of a hierarchy, you can implement your logic using MDX expressions in the Advanced tab.

   
   Figure 19-11

   If you click the Advanced tab, you will see three sections: Allowed member set, Denied member set, and Default member, as shown in Figure 19-12. You will see that the Denied member set shows all the members of the City hierarchy, except New York, that were not selected in the Basic tab. Analysis Services interprets all members not in the Denied member set to automatically be included in the Allowed member set. That's the reason why you do not see the member New York in the Allowed member set. You can include your business logic to select the members that are to be allowed or denied for this specific role. The MDX expressions should result in a set of members of the current hierarchy in the Allowed member set and Denied member set. The result of the MDX expression specified in the default member pane should be a single member from the current hierarchy.

   Important

   An empty set for Allowed member set (shown in Figure 19-12) indicates all the members of the current role have access to the members of the current hierarchy. An empty set in the Denied member set indicates there are no restrictions applied. {} is not the same as an empty set. Having {} in the Allowed member set simply disallows the role members to see any member.

   
   Figure 19-12

6. Similar to restricting access to New York City using the Basic tab in the Dimension data, restrict access for the hierarchies Area, State, Country, and Location so that the user David can only see members relevant to New York City such as East Area, New York State, USA, and the location id 1, which represents New York City. You need to select the appropriate attribute hierarchy from the drop-down list box. You can now test the security you have defined for user David.

7. Open the Dimension Security cube and switch to the Browser pane. By default, if you select Sales and the hierarchy Geography you will be able to see the sales information for all the cities. Click on the Change User icon as shown in Figure 19-13. You will now have a dialog where you can select the role you have created. Select the role David as shown in Figure 19-14 and click OK.

   
   Figure 19-13

   
   Figure 19-14

8. Drag and drop the sales measure and the Geography hierarchy of the Location dimension into the cube browser as shown in Figure 19-15. As you can see, the user David can only see results for the member New York; his access to the sales information is restricted to that and the totals. However the Totals for the State, Area, and Country do not match the value for city New York. This is due to the fact that sales for other cities are included in the totals.

   
   Figure 19-15

9. You can define security permissions on roles so that the totals returned by Analysis Services are calculated for the visible members rather than all the members in the dimension. Open the role David and switch to the Dimension Data tab. Select the cube dimension Location and the hierarchy Area. Switch to the Advanced security definition tab and enable the check box Enable Visual Total as shown in Figure 19-16. Similarly enable the check box for the hierarchies City, Location, and State, and deploy the project to your Analysis Services instance so that new security definitions are updated on the server.

   
   Figure 19-16

   If you return to the cube browser and refresh the connection you will see that user David can see the sales information for the city New York and all the totals now match the sales of the city New York as shown in Figure 19-17. By enabling visual totals while defining dimensions security you have ensured that the role David can only see aggregated data for cities that can be accessed by users of the role David.

   
   Figure 19-17

10. You need to define security for all the users of the cube. The goal of security definition is that the regional sales managers should be able to see results for all the cities of just their direct reports while the U.S. Sales manager can see the entire set of cities. Just as you specified security on Location dimension for the role David, we recommend you repeat the process to create a role for each user and provide access to the cities that can be accessed by the user. In this way you can restrict data access to users of the cube using the dimension security feature of Analysis Services.

11. In order to restrict the user David to only see sales data relevant to him, you should restrict him from seeing other employee members in the employee dimension. The employee dimension has a parent-child hierarchy called Manager Id. Select the Employee dimension and the Attribute hierarchy Manager Id as shown in the Dimension Data tab. Select the member David Young and the members parents Joe Smith, Rob Brown, and All from the Manager Id hierarchy as shown in Figure 19-18. You have now restricted the user David to have appropriate permissions on employee dimension.

    
    Figure 19-18

12. The role David was restricted so that members of role David can only see the dimension member David and its parents under the parent-child hierarchy Manager Id. In order to make sure David truly can only see data relevant to himself, verify it once again using the cube browser. Drag and drop the EmployeeManager Id from the metadata browser to the column area of the OWC in the cube browser. You will now only see the user David. However, you can see that the users of role David can also see the totals of David's managers Joe Smith and Rob Brown as shown in Figure 19-19. This is due to the security definition unique to parent-child hierarchy. In order to make sure David only sees the sales amount sold by him, you might be thinking you can enable visual totals for the Manager Id hierarchy — though Analysis Services 2005 does not actually support enabling visual totals for parent-child hierarchies. Hence you would need to restrict access to the fact data of Joe Smith and Rob Brown using cell security which you will learn about later in this chapter.

    
    Figure 19-19

13. Each hierarchy has a default member that can be specified using the properties of the hierarchy or by using an MDX script. When you define dimension security, the default member of a hierarchy might be restricted to the users of a role. The roles designer allows you to specify the default member for hierarchies in a dimension for a specific role. In order to specify the default member for a hierarchy, you can either enter the member name in the Default member pane or use the Edit MDX button. In the Dimension Data tab of the roles designer click on the Advanced tab. Click the Edit MDX button for the Default member pane. You will now see the MDX Builder dialog as shown in Figure 19-20. Click the member you want to set as the default member for the hierarchy chosen; New York for the hierarchy State in the Location dimension. If you double-click the member name, the unique name of the member will be shown in the Expression pane. Once the MDX expression is entered in the Expression pane you can click the Check button to make sure your chosen MDX expression is correct. Click the OK button. You will see the default member expression is the roles designer. Specify the default member for the remaining hierarchies for which you have applied dimension security. Deploy the changes to your Analysis Services instance.

    
    Figure 19-20

14. To verify your default member setting, you can run SQL Server Management Studio as a specific user who is part of the role you have created. Go to Program FilesMicrosoft SQL Server 2005SQL Server Management Studio. Right-click on SQL Server Management Studio and select Run As. Enter one of the local users who is part of the role, such as <yourmachinname>\ David, and enter the password for the user as shown in Figure 19-21.

    
    Figure 19-21

15. Open the MDX query editor in SQL Server Management Studio. Select the Dimension Security database. If you send the following MDX query by substituting for dimension, hierarchy, and cube name, you should see the results for the default member you specified in the role as shown in Figure 19-22. If a user belongs to more than one role, the default member for the first role in the roles collection is chosen as the default member for the hierarchy.

    
    Figure 19-22

     SELECT <Dimension>.<Hierarchy>.Defaultmember on 0     From <CubeName> 

The user-role approach is suited to business scenarios where you have a limited set of users and their security permissions do not change frequently. Typically, when the permissions for users are static, this approach will be sufficient to suit your business need and is easy to implement and maintain.

The Access-Role Approach

In the user-role approach, you solved the business problem of restricting data access for certain employees in the company. You created a role for each employee in the company and provided appropriate restrictions. Under that design, if new employees are joining the company, the administrator of the cube needs to create a new role for every new salesperson and appropriately provide the restrictions. Similarly, if employees are leaving or changing roles, such as a salesperson promoted to sales manager, you would have to appropriately update the dimension security within the cube. There are two design alternatives from which you can choose to accommodate changes of this nature, which you will learn in this section.

You can create roles based on cities rather than the users. In this design alternative you add all the users who have the right to access specific cities to the role of a specific city. If your company had 100 employees selling into 10 different cities, you would create 10 roles and assign users to those roles — as opposed to creating 100 roles with cities assigned to each. This design leaves open the question of how to go about restricting the employee name in the employee dimension. Assuming the employee have an account of the format <domain name>\<login name> then you can restrict the employee access using an MDX expression that uses the MDX function "username" as shown below.

     FILTER (employee.employee.MEMBERS,     instr (employee.employee.CURRENTMEMBER.NAME,     right (USERNAME,len (USERNAME)- instr (USERNAME,"\")))) 

In this dimension security scenario, the logins of each employee match the first name of the employee. The employee parent-child hierarchy in the employee dimension has been modeled in a way that the employee's full name is the named column for the hierarchy. Therefore, while browsing the employee hierarchy you see the full name of the users. If you check the name in the login and match it with the appropriate employee name, you will automatically get the employee member for the user who has logged in. The preceding MDX expression completes the operation of identifying the employee member for the corresponding login using the MDX function username. The username MDX function returns the <domainname>\<loginname>. Finally, the third line in the MDX expression extracts the loginname. This loginname is used in the condition of the Filter MDX function to iterate through all the members of the employee hierarchy and extract the member(s) where the employee name contains the login name.

To extract the login name from the employee name, the VBA function "Instr" is used. The result of the MDX expression is the correct employee member name. Most companies do not have login names that match exactly to the first name or last name of the employees. In such a case you would need to form a complex MDX expression that will return the correct employee member for the Allowed set. You will see an example of a complex MDX expression in this chapter under the Securing your Cube Data section.

To test the preceding solution you can go to the cube browser and bring up the user of interest as shown in Figure 19-23. Analysis Services now impersonates the user account specified in the Other User option to access the cube data. Security restrictions are applied to the user based on the roles that user is part of. You can now browse the dimensions and cell values in the cube browser to ensure your security restrictions were applied correctly.

Figure 19-23

If your business needs are such that data access restrictions to users are reasonably static but the number of users is large as compared to the data members in the dimension, the access-role approach might be best suited for your business problem. If your business needs are such that the security restrictions of users change due to modification of roles or location, then you need a solution where you can dynamically add or remove users. We recommend the approach of creating windows user groups and adding the users to those windows groups. The windows groups will actually be added to the membership of a role rather than the users themselves. For the current scenario, you would create a windows group for each city. If the employees move from one location to another, they can easily be removed or added to the appropriate city group. In this way, you do not have to make changes to the roles in Analysis Services. This solution is feasible because Analysis Services leverages windows authentication to authenticate users, and users' permissions keep changing through windows security groups, and Analysis Services is able to handle the security restrictions dynamically. However, in this technique you still need to maintain a role for each windows group. Therefore your Analysis Services database can potentially have several roles that are equivalent to the number of members in the dimension. If you only want to have one role that provides you the ability to restrict data for all users dynamically, you have three different techniques which are explained in subsequent sections. Restricting dimension data access using one role for several users whose data access permissions change periodically is called dynamic dimension security.

The Member Property Approach

One of the ways to provide access to locations for employees is to have a column in the relational data source that contains the list of employees who have access to that location. When you need to modify user access to a location, you can either restrict them or provide access by updating the list of users who have access in the relational column. You might be wondering how this translates into defining the security in Analysis Services dynamically — when a list in the relational data source must be maintained. Actually, it is quite simple with the help of an MDX expression.

First, you need to make sure the relational column in the dimension table is added as an attribute hierarchy in the dimension. You do not necessarily need to browse this attribute, but you need to make this attribute a member property for the attribute hierarchies for which you need to apply dimension security. Create or open an existing relational table called "Location" from within SQL Server Management Studio. Just as you changed domain names in the Employee table, replace all instances of the name domain\username in the column Access to your local machine name\username. If you have created your own Analysis Services project from the relational data source, you will have the Access attribute in the Location dimension. If you are working with the sample project Dimension Security, you will notice that the column access does not exist in the DSV. Go to the DSV designer, right-click and select Refresh. You will now see the prompt that a new column has been added to the table. Click OK to add the column to the DSV. In the dimension editor for Location dimension, drag and drop the column access to the attributes pane to create an attribute hierarchy. Specify the access attribute as a member property for the remaining attribute hierarchies Area, City, State, and Country as shown in Figure 19-24. By default the access attribute is a member property of the key attribute "Location." If you do not want your users to browse this attribute, you can disable the attribute hierarchy Access by changing its AttributeHierarchyEnabled property to false.

Figure 19-24

The next step is to create the MDX expression in a single role that will define dynamic security for all the users. Once again, you need the MDX function username. Create a new role. Add all the users who need access to the cube to the membership collection of the role. Once you have provided read access to the cube and the dimensions, go to the Advanced tab of the Dimension Data pane. Select the dimension Location and hierarchy City. Enter the following MDX expression for the Allowed set:

     Filter (Location.City.City.members,     Instr (Location.City.currentmember.properties ("Access"),     USERNAME)) 

This expression filters the list of cities that can be accessed by the current user. Member property Access for each city is checked to see if the current user has access using the VBA function Instr. The expression Location.City.currentmember.properties ("Access") returns a string that contains the login names of all the users who have access to the current city. The user name function returns the string containing the login name of the current user. The Instr VBA function searches for the occurrence of the current user in the member property. If there is a match, the Instr function returns a positive number and the Filter expression uses that as an indication the condition is true, and therefore the current city can be accessed by the current user. Thus the Filter function is used to retrieve a set of cities that can be viewed by the current user. The Analysis Services project modeling the business problem using the Member property approach is provided with Chapter 19 and is called DimensionSecuritywithMemberProperties. You can test the preceding expression by deploying the current role to the Analysis Services instance and then browsing the cube using a specific user as shown in Figure 19-23.

The member property approach is one of the three dynamic security approaches recommended in this chapter. This is easy to implement and the cost of maintenance (updates of permissions to users in the relational table) is typically low because only a single column gets updated for security changes. One of the advantages of the Member Property approach over the previous approaches is that you have a single role to mainatain. However, the important trade-off in this approach is that whenever you change the security restrictions for the users, the dimension needs to be processed to reflect the changes in the database, thereby restricting the right dimension members. Based on your business requirements you can enable proactive caching on the dimension so that the dimension is processed automatically without intervention from an admin. If your dimension has a large number of members and if you need security changes to be in effect immediately, you might have performance implications because Analysis Services would have to use the dimension in ROLAP mode till the time MOLAP cache gets updated. Based on the size of the dimension members and your business need, you can choose to implement this approach.

The Security Measure Group Approach

In this approach dimension security is modeled using a fact table. A relational table will hold the access permissions of users for the dimension members. If a user has permission for a specific location, that is indicated by a row containing the username, the location, and another column containing a value 1, which indicates the user has permissions to the location. A value of 0 indicates that the user does not have permissions. Ah, something simple! Now you really want to learn this approach, right?

The fact table containing the dimension security restrictions is added as a measure group to the existing cube. The relational column that contains the value of 0 or 1 is the measure that will be used for modeling dimension security. An MDX expression using the measure from the security measure group is used to restrict the dimension members to authorized users. Follow the steps below to model the measure group approach for restricting access to users.

1. Use the Analysis Services project you used in any of the approaches discussed earlier and delete all existing roles. In the DSV designer, right-click and select Add/Remove Tables. Select Table Security and click OK. Mark the employeeid and locationid as key for the security table and make appropriate joins to the dimension and employee tables in the DSV.

2. Open the cube Dimension Security and click the Cube Structure tab. Right-click the cube name in the Measures pane and select New Measure Group. Select the security table from the DSV.

   The Analysis Services cube designer automatically adds a new measure group called security and creates two new measures, as shown in Figure 19-25. The Analysis Services tools automatically define the right relationships between the existing dimensions based on the joins specified in the DSV. If you click on the Dimension Usage tab you will see the details of the dimension types and the granularity attributes. Deploy the new cube structure to your Analysis Services instance.

   
   Figure 19-25

3. Create a new role, add all the users who need to access the Dimension Security cube, and provide read access to the cube and dimensions in the database.

4. Go to the Dimension Data tab in the roles designer and select the Advanced option.

5. Select the dimension Location and hierarchy City. Enter the following MDX expression for the Allowed set:

     FILTER (Location.city.city.MEMBERS,     (FILTER (Employee.Employee.MEMBERS,     instr (Employee.Employee.CURRENTMEMBER.NAME,     right (USERNAME,len (USERNAME)- instr (USERNAME,"\")))).ITEM (0),     measures.access)=1) 

In the above expression, the login of the current user is retrieved using the username MDX function. The inner filter expression iterates through all the members of the employee dimension and retrieves the set of members who have the name same as the login name. Because there is a one-to-one relationship between users and logins, the inner filter condition results in a set with one member. The returned set cannot be used directly to form a tuple in the conditional expression. There are several ways of forming the condition expression using the outer filter function to retrieve the list of cities accessible by the current user. In the preceding MDX expression, you can retrieve a single tuple of the inner filter function using .ITEM (0). You then have to check if the current user has access to the location. In order to do so, the outer filter function is used, which checks for a value of 1 for each tuple. The resulting set from the outer filter function is the set of cities for which the current user has access. Thus you form an MDX expression that secures the location for each user.

6. Similar to the MDX expression used in step 5, you need to form an MDX expression with the remaining attribute hierarchies Area, Location, State, and Country in the Location dimension, and set the allowed member set for each hierarchy.

7. To restrict the access to members of the employee dimension, you use the MDX expression shown below which uses the login of the user and restricts access to members in the Employee parent-child hierarchy in the Employee dimension.

        FILTER (Employee.Employee.MEMBERS,     instr (Employee.Employee.CURRENTMEMBER.NAME,     right (USERNAME,len (USERNAME)- instr (USERNAME,"\")))) 

8. Once you have completed specifying dimension security for each hierarchy, you can deploy the project to the Analysis Services instance. The Analysis Services project modeling dimension security scenario using a fact table is provided under Chapter 19 samples and is called DimensionSecuritywithSecureMG.

9. Using the cube browser's change user option you can verify the dimension security restrictions you have applied in the measure group approach.

The security measure group approach is an extension of the member property approach. Similar to member property approach, you can implement this approach fairly quickly and maintenance is also fairly low cost. You do need to process the security measure group whenever there are security changes and you need the security permissions to take effect immediately. This approach has a lower performance impact as compared to member property approach because only the specific measure group needs to be updated. If you have proactive caching set on this measure group, retrieving the data from this measure group would be fast even if you have a very large number of members in the dimension for which security has been updated. Once the security information is cached on Analysis Services, you do not have a dependency on the relational data source.

The External Function Approach

The member property approach and the secure measure group approach require appropriate dimensions and measure group processing to keep abreast of changes. You can certainly setup proactive caching on the dimension and measure group so that changes to security are immediately reflected. However, processing does involve some cost. The external function approach alleviates the problems of processing and ensures that only the most up-to-date security restrictions are applied to the users.

In the external function approach you write a UDF or a .NET stored procedure that will retrieve the list of locations the current user is authorized to access. For example, the stored procedure can return the list of cities or states or area that a specific user can access as an MDX set. This set is then defined in the Allowed member set as the dimension security restrictions for the current user. Analysis Services exposes the security permissions for .NET stored procedures which restricts the stored procedures access on specific resources such as accessing a network or creating a new file. The security permission provides an extra level of code security so that your Analysis Services is more reliable. There are no such security permissions that can be defined for COM UDFs and you need to trust the programmer has written good quality code. In addition to that you have the option of using the ADOMD server object model in your .NET stored procedure to perform custom business logic which is not available if you code a COM UDF. You still need to maintain a relational table that provides information on a user's access to locations via a column. In this example you will use the security table that was used in the measure group approach. Since the security table only contains ids of employees and location you will need to make joins to employee and location tables in the relational database to retrieve the right location members. However you can create a new table that will have the list of locations for employees based on the login information. Such a table will probably have the columns login name, city, state, country, location id and access where the column access has values 1 or 0 that indicate if the user has access or not to the specified location.

The .NET stored procedure either needs to return a string that contains the list of locations or an MDX Set of locations. The string needs to contain the unique name of the locations separated by a comma so that you can be converted to a set using StrtoSet MDX function. This function in a .NET store procedure or a UDF allows you to get an MDX set of members that need to be allowed or denied for the users of the current role. Your .NET stored procedure alternatively can return an MDX set using ADOMD server object model that can be directly used in the Allowed members or Denied member sets for dimension security restrictions. In order to create the set of members, your .NET stored procedure needs to identify the member(s) from Analysis Services based on the security restrictions defined in the relational database.

In this dimension security you will create a .NET stored procedure that returns a string containing the unique names of the locations accessible for the current user. The stored procedure will take two arguments, login and location, which are strings. For the login of the current user you can directly pass the MDX function username. The location argument will be the column name of the attribute hierarchy for which you need to retrieve the list of members accessible by the current user. What follows is the pseudo-code for translation into a function in your favorite .NET language

     Public string getAllowedSet (string login, string location)     {             1. Connect to your relational data source database             2. Form the SQL query using the login and location to retrieve                the members that can be accessed by the users             3. Iterate through the result set and form the output string so that the                members are returned in the unique name format.     } 

The stored procedure first needs to connect to the relational database and send the following query.

     select <location>     from employee, location, [security]     where employee.[login]= '<login>' and     employee.[employee id] = [security].employeeid and     location.locationid = [security].locationid 

The words within <> are the parameters passed to the stored procedure. There is a potential for SQL injection attacks with the following query. We recommend use of a parameterized SQL query, which will help you to prevent a breach of security. The stored procedure retrieves the results from the query and forms the output string, which needs to be of the following format.

     {[Location].<location>.<location>.&[<resultvalue1>],     [Location].<location>.<location>.&[<resultvalue2>],     [Location].<location>.<location>.&[<resultvalue3>],...} 

The unique name for a member is represented as [Dimension].<Hierarchy>.<Level>.&[MemberName]. For attribute hierarchies the Hierarchy name and Level name will be the same. If the key column and named column for an attribute hierarchy are the same, the member in an attribute hierarchy can be referenced as [Dimension].[AttributeHierarchyName][AttributeHierarchyName].&[MemberName]. Follow the same approach to build a string that will represent the set of members for the members of a hierarchy in the location dimension. The values resultvalue1, resultvalue2, and so on are the results from the SQL query which you need to iterate to form the output of the function. You need to add appropriate error handling to your stored procedure. Once you have compiled your stored procedure, add the stored procedure to the assembly collection of the database with the appropriate impersonation mode and Permission Set. This stored procedure will require an external access permission because it needs to access an external resource (the relational database).

Create a new role and add all the employees' logins to the membership collection. Then specify read access to the cube and dimensions. Assuming the name of the assembly that contains the getAllowedSet function is SecurityMemberSet, specify the following MDX expression for the Allowed member set.

     StrtoSet (SecurityMemberSet.SecurityMemberSet.getAllowedSet (USERNAME, "City")) 

Test the security restriction using the cube browser and change to one of the local users and verify the current user is only able to see the locations for which he has been given access.

The external function approach provides maximum flexibility in terms of design approaches. Also, you do not have the overhead of processing a measure group or dimension whenever there are changes to security restrictions. Security restrictions are always immediate because they are queried directly from the relational database each time; hence, you need to make sure the relational server is up and running all the time. Implementing this involves some amount of coding and proper error handling, but it should be worth spending the time up front to implement this type of solution.