Assigning Fact Data Security Privileges

Assigning Fact Data Security Privileges

The defining fact data security option allows you to build ranges of data for use by selected users or groups. These users or groups can then view figures and/or enter data. Fact data ranges use syntax similar to Cube Formulas.

The two procedures in this topic will demonstrate how to provide read-only access to fact data, as well as 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.

Providing read-only access to fact data

To assign Fact Data Privileges:

  1. From the Model ribbon, click Cubes to open the Cubes dialog.

cubesdialogmargincube.png

Figure 1.    Cubes Dialog - Margin Cube

  1. Select the Margin cube and click Fact Security... to open the Fact Data Security Rules dialog.

  2. Click the Add button to begin specifying a data range. A text entry appears in the list to the left - it is this text entry that you will name.

  3. Name the rule Access Margin Fact Data . Press the Enter key to set the name.

fastdatasecurityrulesdialogaccessmarginfactdata.png

Figure 2.    Fact Data Security Rules - Access Margin Fact Data

  1. To define the rule's range, click Range Reference, buttonrangereference.png to open the Build Range Reference dialog.

  2. Select the Year dimension the 2008 and 2009 Members to include in the range. Click OK to return to the Fact Data Security Rules dialog.

buildrangereferenceyear.png

Figure 3.    Build Range Reference

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, to be used just as in the Formulas dialog (refer to Creating Cube Formulas).

  1. Press the spacebar, click Allows, and then press the spacebar one more time. As shown in Fact Data Security Rules Dialog, the formula is now trailed by the word “allows”.

  2. Click the Rights button to open the Specify (Fact) Data Access Security Levels dialog.

  3. Select US Staff, set the Access Level to 'Read' and click OK. You are returned to the Fact Data Security Rules dialog where the full expression is now inserted into the dialog.

factdatasecurityrulesaccessmarginusstaff.png

Figure 4.    Fact Data Security Rules Dialog - With Completed Expression

  1. Complete the expression by entering a semi-colon (;) after Read.

  2. Click the Check button to scan the expression for errors.

  3. A prompt notifies you of a successful expression. If errors have occurred, a message indicating where the error may be located appears to offer a possible correction. In either case, click OK to dismiss the prompt.

  4. Click OK to dismiss the dialog and return to the Cubes dialog.

  5. Click OK to dismiss the Cubes dialog. Fact Data Security Privileges have been successfully assigned to User(s) and Group(s).

For the example, a named user from the US Staff group wants to access the database, specifically the Margin cube, and open the slice Products by Margin, Accounts, and Year. The slice, with data for 2008 and 2009 (2009 shown in the figure below), appears in read-only format.

read-only20082009margincubeslice.png

Figure 5.    Read-only Slice - Year 2009

Limiting access for fact data entry

To provide limited access to a cube (i.e., write-access to a limited range),proceed as follows:

  1. From the Model ribbon, click Cubes to open the Cubes dialog.

  2. Select the Products cube and then click Fact Security to open the Fact Data Security Rules dialog.

  3. Click Add.

  4. Name the rule For 2009 budget contributors and press the Enter key to set the rule name.

  5. Click Range Reference, buttonrangereference.png , to open the Build Range Reference dialog.

  6. Select the Version dimension, and then select the members you want to include in the range. For this example, select All. The dialog appears as follows:

buildrangereferenceversion.png

Figure 6.    Build Range Reference - Version Dimension

  1. Repeat this same procedure for each of the other dimensions, specifying precisely the dimension member intersection points the user(s) and/or group(s) can have access to.

  2. When you return to the Fast Data Security Rules Dialog, press the spacebar; enter and; press the spacebar again after each Range Reference specification indicated by theRange Reference button, buttonrangereference.png . Thus, in the example, for the Year dimension, specify 2009;and for Region, specify USA.

  3. When you are done with the Range Reference specifications for each dimension, press the spacebar, click the Allows button, and then press the spacebar again. (The formula is now trailed by the word “allows”.)

factdatasecurityrulesfor2009budget.png

Figure 7.    Fact Data Security Rules - For 2009 Budget

  1. Click the Rights button. The Specify (Fact) Data Access Security Levels dialog appears.

  2. Select US Staff and set the Access Level to Write.

You could even specify individual users and apply Fact Data Security Privileges for each so that they can Write Fact Data only to certain Products (such as the Products the user is responsible for). This would require a Rule for each User.

  1. Click OK to return to the Fact Data Security Rules dialog.

  2. Complete the expression by entering a semi-colon (;) at the end. Refer to Fact Data Security Rules - For_2009_Budget to view the expression we have built.

  3. Click Check to scan the expression for errors. A prompt opens to describe the outcome of the check.

  4. Click OK to save the expression.

  5. Click OK in the Cubes dialog. At this point, Fact Data Security Privileges have been assigned to User(s) and Group(s).

Restricting access to cubes

The Cube Access  function enables you to specify that certain cubes—fact data and metadata—should be inaccessible to users unless explicitly specified otherwise. You might use this function to restrict  access to sensitive information.

Combined with the capability to add users to the Operational Administrator group, it also enables you to have multiple users with administrative privileges, without providing broad access to all data. For more information, refer to Understanding Administrative Privileges .

To restrict access to specific cubes:

  1. From the Model ribbon, click Cube Access to open the Cube Access dialog.

  2. Check the boxes beside one or more cubes that you want to restrict.

cubeaccesssales.png

Figure 8.    Cube Access - Sales

  1. Click OK.

As explained at the top of the dialog, unless explicitly specified otherwise, users will not be able to access the cubes. The name of the cube won’t list in proCube functions that list cubes or any data within them, such as slices or fact data.

Locking (reserve/commit) fact data range

At certain times (for example, when performing database maintenance or when running a report), an Administrator may want to lock defined Fact Data ranges. Recall that there is an option in the Access Level drop-down of the Specify (Fact) Data Access Security Levels dialog that enables an Administrator to grant Reserve privileges to a User. In fact, there are several ways an Administrator can place such restrictions on Fact Data, as summarized here:

  • Unlock: Releases a locked range. You must first Lock privileges to use this feature.

  • Reserve: Books a range for you to read and write; others can only read data in the range. You must first un-reserve privileges to use this feature.

  • Lock: Closes off a range. Allows you to read it; others cannot read or write data into this range. You must first unlock Lock privileges to use this feature.

  • Commit: Allows you to submit the data in that range so that no one can change the data until in is un-committed. You need commit privileges to use this feature and only an administrator can un-commit a fact data range.

There is an added 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 calculation process. This represents a savings in recalculation time. For more information, refer to Persistent calculations.

To reserve, lock or commit a range of fact data:

  1. From the Model ribbon, click Cubes to open the Cubes dialog.

  2. Select an the Margin cube and click Data Locks to open the Data Lock dialog.

datalockscubemargin.png

Figure 9.    Data Locks Dialog

  1. Select the For 2009 budget contributors range name.

  2. Click Unlock, Reserve, Lock or Commit. The status and user name display alongside the range name.

  3. Click OK to accept your data lock.

Persistent calculations

Persistent Calculations enable data that has previously been calculated to remain in cache, thus increasing performance when proCube calculates the same data repeatedly. You can enable Persistent Calculations when applying data locks by using the Lock or Commit option. 

When calculating a Cube, aggregations are stored in database cache. These calculations are stored in cache until data in the Cube has been modified.  Once data in a Cube has been modified and then calculated, the aggregations are flushed from cache and recalculated.  This process occurs because the aggregations may change as a result of modifications to the data—and it occurs each time data is modified.

When a lock is placed on a data range, data within that range is frozen and cannot be modified until the lock is removed. Since the data is locked, the aggregations can be stored in cache. This makes subsequent calculations much faster.

For example, imagine that you have a Budget Cube that contains several years of data. The previous year’s data (historical data) does not change. You can create a data lock range that governs all previous years. When performing calculations on the previous year’s data, the calculation engine does not have to recompute this each time because it can simply read this data from cache, thereby increasing the product’s overall performance.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk