Assigning Fact Data Security Privileges
Defining Fact Data security allows Administrator to build ranges of data that will be available to selected Users or Groups, for viewing figures or for data entry. Fact Data ranges, as you will see, use similar syntax to Cube formulas.
If you are following the example, the intent is to provide read-only access to Fact Data in the Products Cube, which will enable the US Staff Group to see Unit Cost and Unit Price (in the Yearly UnitCost and Price slice, to which they were given access) for 2000 and 2001. These numbers, in our example scenario, could serve as reference for the following: We want to show how to give the same Group access to very specific areas of the Margin Cube, in order to add figures to the new BudgetUSstaff Member (of the Version dimension), for 2002.
Respectively, these two procedures are useful examples of providing Read-Only access to Fact Data; and providing limited access for Data Entry. With knowledge of these two procedures, you can provide any type of Fact Data Security parameters to users accessing a shared database.
Provide Read-Only Access to Fact Data
The way to begin the process of assigning Fact Data Privileges is through the Cubes dialog box.
1. In the PowerOLAP ribbon, select Model tab and Cubes command.
The Cubes dialog box appears.
2. Select a cube (e.g., Products) then click on the Fact Security button (boxed in the preceding figure).
The Fact Data Security Rules dialog box appears:
3. Click the Add button to begin specifying a data range.
4. Now you can <Name> the rule (e.g., by typing Access Products Fact data). Press the Enter key to set the name.
5. Click the Range Reference button, , to define the range.
The Build Range Reference dialog box appears.
6. Select a Dimension (e.g., Year), then the Members you want to include in the range (e.g., 2000 and 2001).
The dialog box appears as follows:
Tip
You can further specify a range by continuing to pick other Dimensions and specific Members. Note that Qualifiers radio buttons and a Filter text box also appear in this dialog box, to be used just as in the Formulas dialog box (see the Sections on Formulas).
7. Press the spacebar, click on the Allows button and the spacebar again. (The formula is now trailed by the word “allows”.)
8. Click the Rights button.
The Specify Data Access Security Levels dialog box appears, as in the following figure.
Notes
The Access Level drop-down at the bottom of the dialog box, boxed in the preceding figure, provides the choices Read, Write and Reserve (the Reserve privilege will be explained in the next topic, Lock Ranges of Fact Data).
9. Select a User(s) or Group (e.g., for the example, US Staff)
and select an Access Level (e.g., Read).
10. Click OK .
This retuns you to the Fact Data Security Rules dialog box.
11. End the expression by typing a semi-colon (;) at the end.
12. Click the Check button to scan the expression for errors.
Provided there are no errors, the following message box appears:
13. Click OK.
14. When the Fact Data Security Rules you want are complete,
click OK in the dialog box.
15. Click OK in the Cubes dialog box.
Provide Limited Access for Fact Data Entry
To provide limited access to a Cube (i.e., Write access to a limited range), an Administrator would proceed as follows:
1. In the PowerOLAP ribbon, select Model tab and Cubes command.
The Cubes dialog box appears.
2. Select the Cube you want to provide limited access to(e.g., Margin). Then click on the Fact Security button.
The Fact Data Security Rules dialog box appears.
3. Click the Add button.
4. Type the <Rule name> (e.g., For 2002 Budget Contributors), then press the Enter button.
5. Click the Range Reference button, , to define the range.
The Build Range Reference dialog box appears.
6. For a Dimension (e.g., Version) select the Members you want to include in the range (e.g., BudgetUSstaff) by checking the check box.
The dialog box appears as follows:
7. Select Year and pick the member 2002 then click on Region and pick the member USA.
8. Click OK.
This will return you to the Fact Data Security Rules dialog box.
9. Press the spacebar, then click the Allows button and the spacebar again. (The formula is now trailed by the word “allows”.)
10. Click the Rights button.
The Specify Data Access Security Levels dialog box appears.
11. Select a User(s) or Group (e.g., for the example, US Staff)
and select an Access Level (e.g., Write).
Notes
At this point you could even specify individual Users, and apply Fact Data Security Privileges for each (which would require a Rule for each)—so that a User could Write Fact Data only to certain Products, e.g., the Products each is responsible for.
12. Click OK (you are returned to the Fact Data Security Rules dialog box).
13. End the expression by typing a semi-colon (;) at the end.
For this example, the fact data security rule looks as follows:
14. Click the Check button to scan the expression for errors.
15. Click OK.
16. When the Fact Data Security Rules you want are complete,
click OK in the dialog box.
17. Back in the Cubes dialog box, click OK.
18. For the example, a named User from the US Group can access the database, specifically the Margins cube, and access that part of the database to which he/she has access. For example, a Slice showing BudgetUSstaff figures for USA for 2002 for a particular Product and Month (e.g, Aunt Re’s Chicken, January), as appears below:
Lock (Reserve/Commit) Fact Data Range
At times, for example when doing database maintenance or when running a report, Administrators may want to lock defined Fact Data ranges. You will recall, too, that there was an option, in the Access Level drop-down of the Specify Data Access Security Levels dialog box enabling the Administrator to grant Reserve privileges to a User.
In fact, there are several choices for an Administrator to place such restrictions on Fact Data:
Unlock |
Unlocks a locked range. You need Lock privileges to use this feature. |
Reserve |
Reserves the range for you to read and write; others can only read data in the range. You need Reserve or better privileges to use feature. |
Lock |
Locks a range. You can read it; others can not read or write data into this range. You need Lock privileges to use this feature. |
Commit |
Allows you to commit the data in that range, so that no one can change the data until it is Un-Committed. You need Commit privileges to use this feature. Only an Administrator can Un-Commit a Fact Data range. |
Notes
There is a calculation performance enhancement resulting from data that has been locked. Once locked, calculation time will decrease because the formula calculation engine does not need to “look” at locked data to recalculate and thus can skip over those ranges of data in its process.
To Reserve, Lock or Commit a range of Fact Data:
1. In the PowerOLAP ribbon, select Model tab and Cubes command.
The Cubes dialog box appears.
2. Select the Cube for which you want to reserve, lock or commit Fact Data range.
3. Click the Data Locks button.
The Data Lock dialog box appears, indicating named Fact Data Ranges:
4. Select a Range Name (e.g., For 2002 Budget contributors).
5. Click Unlock, Reserve, Lock or Commit.
The status and user name are displayed alongside the range name. In instances when status of the cube is unlocked thereno user name is displayed.
6. Click OK.
7. Back in the Cubes dialog, click OK.