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:

[If errors have occurred, PowerOLAP returns a message indicating where the error may be located, and a possible correction.]

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.

At this point, Fact Data Security Privileges have been assigned to User(s) and Group(s).

For the example, a named User from the US Group must access the database, specifically the Products cube, and open the Slice Yearly UnitCost and Price. The Slice will appear as follows, showing Unit Cost and Unit Price Fact Data for 2000and 2001—and they will be in read-only format (the User will not be able to write in figures in the Slice cells):

These figures could provide a reference for data entry into another Cube, which is the subject of what follows.

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:

Continue selecting the rest of the dimensions and corresponding member/s which you want to be included in the range to specify precisely the Dimension member intersection points the User(s) and/or Group(s) can have access to.

Thus, in the example, continue selecting the Year dimension with member 2002 and Region dimension with member USA.

7.   Select Year and pick the member 2002 then click on Region and pick the member USA.

When you are done with the Range Reference specifications, proceed with the rest of the security statement.

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.

At this point, Fact Data Security Privileges have been assigned to User(s) and Group(s).

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:

In the area of the database that is boxed in the Slice, the User has Write Access—note that she does not have Access to other areas (indicated by #ACCESS), which show Revenue, Margin, etc. for preceding years.

She could enter figures (having been assigned Write Privileges) to in the Detail cells—Quantity, Unit Price and Unit Cost—and by doing so, could determine all consequent figures for her budget work-up (specifically, the BudgetUSstaff). For example, by entering 1000 for Quantity, 25.99 for Unit Price and 14.89 in Unit Cost, the following figures would be shown.

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.