Enabling a Rational solution for Collaborative Lifecycle Management (CLM) user to access non-CLM product data when security is enabled

Resolving The Problem

Note: You will not encounter this issue if security is not enabled.

If you require security, you can enable security for report server and data warehouse. For more information about enabling security for your reporting server, refer to Specify Security Settings After Installation in the Cognos Administration and Security Guide. For more information about enabling security for the data warehouse, refer to Enabling security for the data warehouse.

Once you've enabled security for the report server and the data warehouse, a CLM user can access CLM product data.

If the CLM user also requires access to non-CLM product data, enable access to non-CLM projects:

1. Start Data Manager and open the catalog.
2. Under Builds and JobStreams > Others > Security create a fact build, for example ProjectUsers_default
3. Insert a Data Source selecting Rational Data Warehouse as the Database.
4. For the Query, enter the following SQL statement:
   

   SELECT P."PROJECT_ID", R."RESOURCE_ID"
FROM "{$ODS}"."PROJECT" P
JOIN "{$ODS}"."RESOURCE" R ON 1=1
WHERE PROJECT_CLASS_ID NOT IN (4, 7, 10)
AND P.ISSOFTDELETED=0 AND R.ISSOFTDELETED=0

5. In the Result columns tab, click Refresh. In the variable ODS field, enter RIODS as value and then click OK. The Data Source Properties dialog closes.
6. Double click Data Stream to open the Data Stream Properties dialog.
7. Click Auto Map and then click OK to close the dialog.
8. Right click Transformation Model and then select Mapping. Click Auto Map, then click OK to close the dialog.
9. In the Table Properties tab, insert a Relational Table Delivery. Select Rational Data Warehouse in the Connection drop down list.
10. For the Table name, enter "{$ODS}"."PROJECT_RESOURCE_LOOKUP"
11. In Columns, select PROJECT_ID, RESOURCE_ID and then select Key for each of the columns. Click OK to close the dialog.
12. Right click JobStreams > Others > Security > ProjectUsers_default and then click the Variables tab.
13. Add the new variable:
    • Name = ODS
    • Type = CHAR(10)
    • Value = RIODS
    Click OK to close the dialog.
14. Click Save.
15. Ensure that the login name entered in reporting server is consistent with the value in RIODS.RESOURCE.REFERENCE_ID.

If there is requirement to control CLM user access in non-CLM projects, customize the SQL in step 4 according to specific requirement.