Contents


IBM Business Analytics Proven Practices

Cognos BI - Dynamic Cube Security Case Studies

Product(s): IBM Cognos Cube Designer; Area of Interest: Reporting

Comments

Content series:

This content is part # of # in the series: IBM Business Analytics Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Business Analytics Proven Practices

Stay tuned for additional content in this series.

Purpose of Document

This document will expand on Chapter 7 - Dimensional security of the IBM Cognos Dynamic Cube Redbook and show some examples of how the various combinations of dimensional security filters can affect the view of data your users can see, and the resulting aggregates created.

Applicability

This document was created with IBM Cognos BI 10.2.1.

Assumptions

It is assumed that the reader is familiar with the basic concepts of Dynamic Cube modelling and has read the IBM Cognos Dynamic Cube Redbook.

This document uses the great_outdoors_dynamiccube sample model as the base model which will be modified to include the security filters and views demonstrated in this document. It is assumed that the IBM Cognos BI samples have already been installed and configured along with the appropriate data sources.

Overview

Dynamic Cube dimensional security views can have a diverse effect on the data and aggregations a user can see depending on how they are configured. This document will take a look at six different security scenarios all surrounding the order method Email and products Firefly 4 and Firefly 2. Various implementations of the two security filters created for each dimension respectively.

Creating security filters

A security filter defines whether a user is granted or denied access to one or more members in a hierarchy. Open the great_outdoors_dynamiccube model in Dynamic Cube Designer, and ensure you have connectivity to the appropriate data source. We will be using sets of members from the data source to build the security filters and will require access to the data source.

  1. Expand the Model object, then the Order method dimension. You should see the Levels folder and the Order method hierarchy.
    Figure 1. Project Explorer view with Model and Order method dimension expanded
    Figure 1 - Project Explorer view with Model and Order method dimension expanded
    Figure 1 - Project Explorer view with Model and Order method dimension expanded
  2. Right-click and select the Order method hierarchy and select Open Editor... or double-click the Order method hierarchy. This will bring up the properties window. Select the Security tab.
    Figure 2. Order method hierarchy property pane
    Figure 2 - Order method hierarchy property pane
    Figure 2 - Order method hierarchy property pane
  3. Click the Add Role based Security Filter button to create a new security filter for the Order method hierarchy.
    Figure 3. Order method hierarchy property with New Role Security Filter object selected
    Figure 3 Order method hierarchy property with New Role Security Filter object selected
    Figure 3 Order method hierarchy property with New Role Security Filter object selected
  4. Right click on the New Role Security Filter object and select Rename. Change the name of the object to order method.
  5. Next select Grant Members, Descendants and Ancestors from the Scope dropdown box.
  6. Finally, select Edit from the definition dialog, expand the Order method hierarchy, then the Members folder. Expand the All root member, drag and drop E-mail into the express dialog box and click the OK button. The security filter should appear as shown in Figure 4.
    Figure 4. Security filter definition for order method
    Figure 4 - Security filter definition for order method
    Figure 4 - Security filter definition for order method
  7. Repeat steps 1 through 6 using the Product dimension and hierarchy. The security filter name should be product and the definition is,
    set(Firefly 4;Firefly 2)

    Figure 5 shows how the product filter should appear when completed.
    Figure 5. Completed security filter definition for product
    Figure 5 - Completed security filter definition for product
    Figure 5 - Completed security filter definition for product

Creating the security views

Security views are made up of one or more security filters. Combining security filters allows a modeller to create multiple views of the same cube without having to remove levels or members. For this document we will create seven security views to demonstrate how the various views affect what members a user sees, and the aggregates that are produced. Table 1 names each view and provides a brief definition.

Table 1. Table 1 - Security Views Definition
Security View NameSecurity View Definition
Admin ViewAllows administrators to see all members from all hierarchies
Order MethodAllows a user to see all members from all hierarchies but Order method, which is filtered using the order method security filter
ProductsAllows a user to see all members from all hierarchies but Products, which is filtered using the product security filter
Email and Firefly onlyOnly allows a user to see the Order method and Products hierarchies, which are filtered by the corresponding security filters
Email and Firefly combinedAllows a user to see all members from all hierarchies but Order method (which is filtered using the order method security filter) and Products (which is filtered using the product security filter)
FireflyOnly allows a user to see the Products hierarchy, which is filtered using the product security filter
EmailOnly allows a user to see the Order method hierarchy , which is filtered using the ‘order method’ filter
  1. Right-click and select the gosldw_sales cube and select Open Editor... or double-click the gosldw_sales cube to bring up the properties window. Select the Security tab.
    Figure 6. gosldw_sales cube security properties
    Figure 6 - gosldw_sales cube security properties
    Figure 6 - gosldw_sales cube security properties
  2. Select the Add Security View button to add a new view for the cube. Right-click on the New Security View object in the Security Views pane and select Rename. Type Admin View for the new name.
    Figure 7. gosldw_sales with the security view named Admin View
    Figure 7 - gosldw_sales with the security view named Admin View
    Figure 7 - gosldw_sales with the security view named Admin View
  3. Select the security view Admin View to highlight it and select the Add Secured Member button to add security filters for this view, using Table 1 for reference on how to build each security view. For the Admin view expand each hierarchy and select the check box corresponding to the All Members Granted security filter as shown in Figure 8. When done click the OK button.
    Figure 8. Add Security Filters dialog box for the Admin View security view
    Figure 8 – Add Security Filters dialog box for the Admin View security view
    Figure 8 – Add Security Filters dialog box for the Admin View security view
  4. Your Security tab should now appear as shown in Figure 9.
    Figure 9. Security View Tab View with Admin View Definition
    Figure 9 - Security View Tab View with Admin View Definition
    Figure 9 - Security View Tab View with Admin View Definition
  5. Repeat steps 1 through 3 to create the Order Method security view. In Step 3 the All Members Granted security filter should be chosen for all hierarchies except the Order Method hierarchy where the order method security filter should be chosen. When done, your finished security view should look like Figure 10, which shows the security filters used for the Order Method security view.
    Figure 10. Definition for the Order Method security view
    Figure 10 - Definition for the Order Method security view
    Figure 10 - Definition for the Order Method security view
  6. Repeat steps 1 through 3 to create the Products security view. In Step 3 the All Members Granted security filter should be chosen for all hierarchies except the Products hierarchy where the product security filter should be chosen. When finished, your security view should look like the one shown in Figure 11.
    Figure 11. Definition for the Products security view
    Figure 11 - Definition for the Products security view
    Figure 11 - Definition for the Products security view
  7. Repeat steps 1 through 3 to create the Email and Firefly only security view. In Step 3 the only hierarchies which you need to add Secured Views for are Order method and Products. For these hierarchies the All Members Granted security filter does not need to be selected, only the order method and product security filters for the corresponding hierarchies. When done, your finished security view should look like the one shown in Figure 12.
    Figure 12. The definition for the Email and Firefly only security view
    Figure 12 – The definition for the Email and Firefly only security view
    Figure 12 – The definition for the Email and Firefly only security view
  8. Repeat steps 1 through 3 to create the Email and firefly combined security view. In this security view the All Members Granted security filters are used for all hierarchies except Order method and Products, where they use the order method and product security filters accordingly. When done, your finished security view should look like the one shown in Figure 13.
    Figure 13. Definition of the Email and firefly combined security view1
    Figure 13 - Definition of the Email and firefly combined security view
    Figure 13 - Definition of the Email and firefly combined security view
  9. Repeat steps 1 through 3 to create the Firefly security view. For this security view the only hierarchy defined is Products, which uses the product security filter, the All Members Granted security filter is not required. When done, your finished security view should look like the one in Figure 14.
    Figure 14. Definition of the Firefly security view
    Figure 14 - Definition of the Firefly security view
    Figure 14 - Definition of the Firefly security view
  10. Repeat steps 1 through 3 to create the email security view. For this security view the only hierarchy defined is Order method, which uses the order method security filter, the All Members Granted security filter is not required. When done, your finished security view should look like the one shown in Figure 15.
    Figure 15. Definition of the email security view
    Figure 15 – Definition of the email security view
    Figure 15 – Definition of the email security view
  11. Once all the security views have been created, publish the gosldw_sales cube to your server.

In the next section we will assign the security views to users within the LDAP authentication source and examine the effect of each security view on the same report.

Creating the report and assigning the security views

In order to see the effects of each security view on the members and aggregates, a single report needs to be created. This report will not change, thus demonstrating how each view changes the user’s view of the members and aggregates returned to satisfy the query.

  1. Open Analysis Studio against the gosldw_sales cube and select Blank Analysis when prompted (Figure 16).
    Figure 16. Analysis Studio opened against gosldw_sales dynamic cube
    Figure 16 - Analysis Studio opened against gosldw_sales dynamic cube
    Figure 16 - Analysis Studio opened against gosldw_sales dynamic cube
  2. Drag the Products hierarchy onto the rows and the Order method hierarchy onto the columns. Expand the Measures dimension and drag Quantity onto the crosstab. The report should look like the one shown in Figure 17 when completed. Save the report as Security View and exit Analysis Studio.
    Figure 17. Security View report built in Analysis Studio
    Figure 17 - Security View report built in Analysis Studio
    Figure 17 - Security View report built in Analysis Studio

Now that the report has been created, all that is left to do is assign the security views to a series of users in order to demonstrate the effect the views have on the data. For this document a group of users were created so that there is a different user for each security view. Table 2 illustrates the security view each user is assigned to.

Table 2. Table 2 LDAP users and their associated Security View
User NameSecurity View
AdminAdmin View
emailonlyEmail
combuserEmail and firefly combined
bothonlyuserEmail and Firefly only
fireflyonlyFirefly
omuserOrder Method
produserProducts
  1. Log into IBM Cognos Administration as a user with sufficient privileges, select the Configuration tab, and ensure the Data Source Connection section is selected. Next select the gosldw_sales cube data source and finally the model object. You should see a list of the security views that were created and published in the cube earlier in the document (Figure 18).
    Figure 18. The IBM Cognos Administration view of the gosldw_sales cube
    Figure 18 – The IBM Cognos Administration view of the gosldw_sales cube
    Figure 18 – The IBM Cognos Administration view of the gosldw_sales cube
  2. Click the Set Properties icon on the Admin View security view and ensure the Override the access permissions acquired from the parent entry checkbox is checked. Add the user Admin Person and set the Read, Execute and Traverse permissions. Next, enable the checkbox beside the Directory Administrators group and select Remove to remove it from the list of users or groups who can access the Admin View security view (Figure 19).
    Figure 19. User permissions for the Admin View security view
    Figure 19 – User permissions for the Admin View security view
    Figure 19 – User permissions for the Admin View security view
  3. Repeat Step 4 for the other users following the associations detailed in Table 2 above.

Report outputs with security views

Now that we have the security views defined and associated to a set of users, it’s time to see how each security view affects results a user will see. Below you will see each report output along with an explanation of why the security view constrained the data in the way it did.

Admin user report output

The first report output, shown in Figure 20, was run by the Admin user - this user uses the Admin View when running the report. The Admin View allows the user to access all levels and members of every hierarchy. As a result the Admin user can see the full scope of members and aggregates for the cube and as such, the report appears as if no security was enabled.

Figure 20. Report output for Admin user
Figure 20 - Report output for Admin user
Figure 20 - Report output for Admin user

Results for securing Order method hierarchy only

The output below in Figure 21 is what a user would see if they were solely granted access to the email security view or the Order Method security view. The reason the report works and they are able to see all the products is that, by default, a user has access to all objects in a dimension unless it’s secured using a security view. The Order Method security view grants a user access to all other hierarchies, and only restricts the Order Method hierarchy to the member E-mail. The email security view restricts a user to only see the member E-mail however in order to access the cube, they are granted access to all other hierarchies because no security definition exists for them.

Notice that the totals for each product do not match with what the value is for the member E-mail and the overall totals for the All member and Lanterns do not match. The reason this happens is that in defining our security we set the scope to Grant Members, Descendants and Ancestors. As a result, while the user was restricted to only see the member E-mail for the Order method level, the All level was granted access. Rather than spend time aggregating all the members that exist at the Order method child level, it is much faster for the query to return the aggregate for the All level. In addition, since the user has access to the All level, the query returns the aggregate value for the level rather than totalling the values for the only member they can see. Using the lantern named Firefly Lite as an example, it is returning the total quantity for Firefly Lite for all order methods.

Figure 21. Sample output for secured report with email or Order Method security views enabled
Figure 21 - Sample output for secured report with email or Order Method security views enabled
Figure 21 - Sample output for secured report with email or Order Method security views enabled

Results of securing Products hierarchy only

Much like the security views for Order method, the security views assigned to the users fireflyonly and produsers return the same output. Both security views restrict the Products hierarchy so that only the members Firefly 2 and Firefly 4 are visible. Once again, although the fireflyonly user only has access to the Firefly security view, which only defines security for the Products hierarchy, by default the user gets access to all hierarchies unless specified. The output below in Figure 22 shows the result for restricting the Products hierarchy.

Similar to the previous example, while the user only has access to the members Firefly 2 and Firefly 4, they have access to the parent member Lanterns. As a result rather than perform a sum for the members returned at the Products level, it simply requests the aggregate value for the Lanterns member - for Fax this would be a total quantity for all lanterns with an order method of Fax.

Figure 22. Sample output for security report with Firefly or Products security views enabled
Figure 22 - Sample output for security report with Firefly or Products security views enabled
Figure 22 - Sample output for security report with Firefly or Products security views enabled

Results for securing Order method and Products hierarchy together

The next two secured views to be examined are where we have secured the Order method and Products hierarchies exclusively and where the Order method and Products hierarchies are secured with access to all other hierarchies. In both cases the members returned for Order method and Products are restricted by the secured views. As a result the totals for the Lanterns and All levels are the totals for all Lanterns with an order method of E-mail and all order methods for the products Firefly 2 and Firefly 4 (Figure 23).

Figure 23. Sample output for security report with Order method and Products hierarchies secured
Figure 23 - Sample output for security report with Order method and Products hierarchies secured

Combining views

In this final example we will grant the user named combuser the read, execute and traverse permissions to both the Order Method and Products secured views. Remember that each view grants access to all hierarchies in the cube, while restricting only their respective hierarchy. Table 3 below illustrates the combined security settings that would be created when combining these two security views.

Table 3. Table 3 - Result of combining Order Method and Products security views
HierarchyProducts Security ViewOrder Method Security View
BranchAll Members GrantedAll Members Granted
Employee by RegionAll Members GrantedAll Members Granted
Order MethodAll Members GrantedE-mail
Product brandAll Members GrantedAll Members Granted
ProductsFirefly 2, Firefly 4All Members Granted
PromotionsAll Members GrantedAll Members Granted
RetailersAll Members GrantedAll Members Granted
TimeAll Members GrantedAll Members Granted
Time (close date)All Members GrantedAll Members Granted
Time (ship date)All Members GrantedAll Members Granted
Sales orderAll Members GrantedAll Members Granted

The output from running the same report (shown in Figure 24) appear that they have access to all the members for both Order Method and Products hierarchies. In effect they get a combination of the permissions, so they are able to see the measure values for all Lanterns with an order method of E-mail and they are able to see the measure values for all order methods for lanterns Firefly 2 and Firefly 4. The total values that you see for the order method Fax don’t reflect the members the user has access to and in turn the total for All order methods doesn’t match the values returned for E-mail. Again this is due to the fact that the user has access to the ancestor of the order methods or the All level as well as the ancestor of the members Firefly 2 and Firefly 4 or Lanterns and those aggregate values are returned rather than the total of the members the user does see.

Figure 24. Report output for combining Order method and Products security views
Figure 24 - Report output for combining Order method and Products security views
Figure 24 - Report output for combining Order method and Products security views

Section 7.8.1 of the IBM Cognos Dynamic Cubes Redbook describes the behavior you are seeing above with members being visible yet the measure values are not. This is due to the fact that that there are members from each security view that are not visible in the other view. The result of the tuples that should return values is ERR in the final combined security view. For more information on combined security see the IBM Cognos Dynamic Cubes Redbook.


Downloadable resources


Related topic


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=987466
ArticleTitle=IBM Business Analytics Proven Practices: Cognos BI - Dynamic Cube Security Case Studies
publish-date=10282014