Creating a User-Based Security Dimension
Before you start
Check if the dimension you plan to create the security dimension on has multiple hierarchies:
- If it has multiple hierarchies, they should share the same leaf level (the most granular level of the hierarchy). This ensures a relationship is automatically established for every hierarchy after you create a security dimension. To check if the hierarchies share a level, open the leaf level (indicated by a leaf icon) of each hierarchy in the dimension and check if the Query Name field is the same for each.
- If the hierarchies don't share a level, find one hierarchy's leaf level (indicated by a leaf icon). Click its ellipsis icon and select Duplicate. Duplicate it to each other hierarchy in the dimension. Then delete the old leaf levels from those other hierarchies.
Procedure
To add a user-based security dimension, you need first to do the following outside the Design Center:
- Create a security dimension table - also known as "user-to-attribute mapping" table - in the data warehouse. See also Mapping table example.
- All user IDs that run queries against the cube must be included in this table.
- You can add or remove users to the table at any time by inserting or deleting rows.
After that, do the following in the Design Center:
-
Open your project, open the cube in which you want to create the security dimension, and then open the dimension that you want to create the security dimension on. Consider the following:
- You cannot connect a security dimension to a degenerate dimension.
- If you wish to secure a degenerate dimension, connect the security dimension directly to the degenerate dimension's source dataset instead.
-
Add the user-to-attribute mapping table as a dataset to your library of datasets.
-
In the Library toolbar, click the Add New Security Dimension icon. The Create a Security Dimension window opens.
-
Accept the suggested name for the dimension or change it. The suggested name is in this format: <existing dimension> Security
-
Optionally, enter a Description for this security dimension.
-
In the Dataset field, select the dataset that you created from your mapping table.
-
In the Column Containing Attribute Filter Keys field, select the column that you are adding security to.
-
For the Lookup Rules field, choose the lookup rule:
- "None" will perform a join.
- "Use filter Key" will look up the filter keys separately, and use them in the dimension or fact query.
-
In the Column Containing IDs field, select the column that contains the user IDs.
-
In the ID Type field, select User.
-
For the Scope setting, choose the scope of queries that the security dimension should apply to.
-
By default, the Secure Totals setting is turned on. If you decide to turn it off, AtScale will behave as follows:
-
The security restriction will not be applied to:
- Subtotal measures of the secured hierarchy level or reachable attributes of higher levels.
- Queries that select secured Fact Tables (All or Fact Scope), but do not select the secured dimension.
-
The security restriction will be applied to:
- Grouping of the secured level.
- Secured level's secondary attributes.
- Attributes and nested dimensions that are reachable from hierarchy levels lower than the secured level.
-
-
Click Save. The new security dimension appears on the dimension canvas.
-
Locate the attribute in the dimensional or fact dataset that you wish to secure and drag the attribute to the security dimension to open the Create a relationship dialog.
-
Optionally change the left-hand dataset column, although if you chose the correct column in the previous step the correct column will be preselected for you.
-
If you are on a dimension canvas, accept the default option of "Many-to-One".
-
Click Save.
The new security dimension appears on the canvas with a relationship connecting the fact or dimension dataset to the security dimension on the secured attribute.