Category Archives: CLM Reporting

Best Practices on Planning / Scoping a Cognos Reporting Environment

I thought I would share my expertise and experience on best practices in planning a new deployment for a reporting environment. I have had a ton of experience with many different organizations and industries, worldwide. I have seen alot of successful deployments and I have seen alot of…well…unsuccessful deployments.

While much of what I am about to discuss could be applicable to any software deployment, I will focus this specifically on a IBM Cognos BI, IBM Reporting for Development Intelligence and IBM Rational Insight deployment. There are many different aspects to take into consideration when you are trying to scope and plan a new reporting environment. Here is my advice on what you should do.

Start small and grow big!

You cannot address all of your needs or solve all of your problems in the first phase of your deployment. It is a common mistake to onboard too many users too quickly or over scope the initial deployment. This can lead to many issues including performance problems, server stability, user frustration, an abundance of problem tickets and even canceling the entire project effort.

You should take an incremental approach to your deployment and you will should do this in an iterative fashion. First, you need to scope a type of topology based on your current, and what you believe to be, your future needs (eg clustered , non-clustered, IHS server/Cognos Gateway or a standard distributed environment). To do this, you need to review your overall business goals, translate those to operational objectives and then break those down into individual tasks.

Here are some questions you should address when attempting to plan for a new Reporting environment:

  • What are my expected number of users in the first 3 months, 6 months, 1 year?
  • What types of reports will these users be running (live, trend, saved)?
  • Will the reports be run individually or through dashboards or schedule and emailed to stakeholders?
  • Will my users be accessing the Report server directly or through another interface (eg Rational Team Concert dashboard)?

Once you have understood these items and identified your topology type based on this, you now have the foundation for your initial deployment (note: it is possible to change your deployment topology after the fact, such as going from a vertical to horizontal cluster). Remember, start small and grow big.

The next thing you need to do is identify a small set of users (eg ~50) who will access the deployment as early adopters. You also need to identify a small set of reports (eg 5-10) to develop and deploy into production for use. Again, do not try to do too much too fast. History and experience has shown me that this approach of trying to do too much too fast has a significantly lower percentage of success.

Once you go live with your initial deployment, you need to closely monitor it and look at these key elements:


  • How are my servers performing?
  • How are my cpu and memory utilization?
  • Are my servers crashing?
  • How is the network behaving?

Usage model / User activity

  • Are my users consuming the solution as expected?
  • Are they logging into the Report server portal directly?
  • Are they accessing reports through other means such as a Rational Team Concert dashboard widgets?
  • Are they scheduling reports and having them emailed to stakeholders?
  • Are they running individual reports or viewing a dashboard of reports? How many reports (and what type) are being run in each dashboard?

User feedback

  • What are my users saying? What is their experience with the deployment?
  • Am I getting alot of problem tickets or emails about errors?
  • Are they getting any value from the deployment?

I highly recommend that you set milestones for your deployment. Here is an example of a high level roadmap. This is just an example! You can scale this however you deem appropriate so long as you start small and expand incrementally.

As you progress through milestones, you should be making adjustments based on your observations of what I mentioned that you need to monitor. You may find that you need double the amount of servers you had scoped. In this case you would either need to expand your topology quicker or onboard users at a slower rate. You may also find that you have more than enough of power within your server environment. In this case, you could either scale back a few servers, downgrade some of the machine specs or onboard more users at a faster rate.

The key to success is to start small and grow big!

Why is my Role & Membership data Missing in the Rational CLM / RRDI or Insight Data Warehouse ?

Some folks may experience no data being returned when running reports containing role or membership data from CLM (JTS) using Cognos Report Studio with RRDI or Rational Insight. Some examples would be a report design that contains data from these sections of the framework model:

Operational Data Store > Project Area > Project Role
Operational Data Store > Project Area > Project Related members
Operational Data Store > Team Area > Team Role
Operational Data Store > Team Area > Team Related members

If you are seeing no data coming from these query subjects, then it could be because one of these reasons:

1) You are not using a JTS that is version 4.0.5 or higher (the ETLs for membership and role data for projects and teams were added in version 4.0.5)
2) Your CLM user, whose credentials you use to login to RRDI with, does not have permissions to access those project/team areas
3) Your membership/role ETLs are disabled.

I am going to provide more information on #3. By default, the ETLs for membership and role data are disabled. The reason for is likely because not every user or deployment would require reporting this data. Hence, to avoid additional ETL processing overhead, it is disabled by default. If a user wants to report on that data, then they need to enable these ETLs.

Here is how to do it.

Enabling the membership/role ETLs for RRDI in the Jazz Team Server

If you are using RRDI, then you can enable the Java ETLs by logging into the the Jazz Team Server and navigating to the Administrator section. Then, go to Server > Advanced Properties. Its easier to search for “”. Here you will see the “Ignore Roles and Membership Data” property, which is set to “true” by default. Change this to “false” and save. Run a full load of the ‘Common’ job and the membership/role data will now be loaded into the data warehouse.

JTS/Admin Advanced Properties

Enabling the membership/role ETLs for Rational Insight in Cognos Data Manager

If you are using Rational Insight, then you can enable the Data Manager ETLs by logging into your Data Manager client. Then, navigate to Builds and JobStreams > Jobs > JFSJobs > JFS4.0.5Jobs > JFS_Common4.0.5. You need to enable the following fact builds:


Data Manager - JFS_Common4.0.5

To enable each fact build, double-click on each build shown in the image above and ensure the “Exclude this node from processing” property is unchecked. Then click “Ok” and save the catalog. Run a full load of the ‘JFS_Common4.0.5’ job. The role/membership data will now be loaded into the data warehouse.

Configure fact build properties

IBM Rational Insight Enablement Links

Alot of folks ask me for information on how to do things or if certain things can be done. Many ask for some presentation collateral. Here is a list of some links to collateral that contain alot of great information about IBM Rational Insight that I have created over time that will address alot of your questions:

IBM Rational Insight Overview 2014

The IBM Rational Insight Reporting Solution

Deploying IBM Rational Insight in a Heterogeneous Environment

Improve Predictability and Efficiency with Kanban Metrics using IBM Rational Insight (presentation)

Improve Predictability and Efficiency with Kanban Metrics using Rational Insight (article version)

Integrating Rational Insight with HP Quality Center and other Third Party tools

Configure Rational Insight with an additional Jazz Team Server

Configure LDAP for Rational Insight when Integrated with Multiple Jazz Team Servers

How to implement delta loads using Rational Insight

Integrating a Microsoft Excel spreadsheet with Rational Insight (live data model)

Define Aggregate Rules for Semi-Additive Measures in Framework Manager

IBM Rational Insight – Setting up your environment for remote execution using Data Manager Network Services

Configuring Rational Insight for Rational Team Concert custom attributes (for RTC version 3.0 and earlier)

Here are some helpful IBM links:

IBM Rational Insight 1.1.1.x Infocenter

IBM Rational Insight Data Models (ETLs, Data Warehouse, Reports)

IBM Rational Insight & CLM Data Dictionaries

Insight / CLM Integration

IBM Rational Insight & IBM Rational Reporting for Development Intelligence (RRDI) – how they compare

This is another one of those situations where there can be alot of confusion between these two IBM report offerings. I did a similar comparison between IBM Rational Insight and IBM Cognos BI in a previous post IBM Rational Insight & IBM Cognos BI – How they compare. I suggest reading that post which has descriptions for several of the components that will be mentioned here. Please note that everything I mention here is accurate as of the date of this post and things are always subject to change in the future.

First, I am going to discuss what these two solutions have in common, and then I will talk about some of the key differences. Finally, I will provide some common use cases I have come across and which solution would be the most appropriate.

RRDI is an optional component when someone owns the Rational Collaborative Lifecycle Management solution or any piece of the solution. RRDI is a subset of Insight, or rather, Insight is an extension that is built on top of RRDI. The core binaries are the same. When you install the Insight report server, you will have two sets of bits to lay down, the RRDI components and the Insight extension. If you read my other post referenced above, you would already know that Insight is built using the IBM Cognos BI technology.

Both solutions contain a Report Server, which are deployed using the same bits, hence identical. The web UI navigation and experience is identical. The report authoring tools (Report Studio, Query Studio) are identical. Insight comes with some additional tools that appear in the report server which I will mention below.

Both solutions report off of Data Warehouse. The Data warehouses are almost identical for both solutions as they contain all the same core schemas. There is only one difference. The data warehouse that RRDI uses comes from the Rational CLM solution which contains an RICALM schema for certain specific CLM data points (Team Concert, Quality Manager, Requirements Composer). Insight contains an RIBA schema for some scorecard metrics. RRDI does not contain this RIBA schema. If you integrate Insight with one of the Rational CLM tools, then you will add the RICALM schema to the existing Insight data warehouse.

Now I’d like to discuss differences between the two solutions. I will only elaborate on the major differences, however I’ve provided a list here to display all of the key differences:

Differences between RRDI & Insight
*Both solutions can achieve cross-project reporting.

  1. RRDI can only report off of data that resides in the CLM data warehouse. This is typically limited to Rational Team Concert, Rational Quality Manager and Rational Requirements Composer. Please note that there are ways to load other tools’ data into the CLM data warehouse but that will not be discussed here. Insight is not limited to just CLM product data and can integrate with almost any data source in several ways (eg ODBC, REST APIs, XML).
  2. RRDI is limited to reporting on application data coming from one Jazz Team Server. Insight can report on application data from multiple Jazz Team Servers.
  3. RRDI’s framework data model and the CLM Java ETLs are non-customizable and no developer tools are provided. Insight’s framework data model and Data Manager ETLs are fully customizable and a set of developer tools are provided.

Here I have listed some common use cases that I have come across in working with clients and which reporting solution best meets those use cases.

  1. An organization has certain teams that use the IBM Rational CLM suite of tools and they also have other teams using some tools from HP and Microsoft to capture similar data. They are looking to create enterprise-level reports that aggregate data across all teams to provide a high level view to their executives.

    Appropriate Solution: Rational Insight
  2. An organization is using IBM Rational Team Concert for their change and configuration management (several CCMs with only one Jazz Team Server). They have a business need to create metrics based on their CCM data only.

    Appropriate Solution: RRDI
  3. An organization has several teams using Rational Quality Manager, Rational Team Concert and Rational Requirements Composer. Each team has their own Jazz Team Server (multiple Jazz Team Servers) and multiple CLM applications.

    Appropriate solution: Rational Insight

Workaround for RTC 4.0.5 Data Manager ETLs when run against a 4.0.3 CCM App server

I found this bug today and wanted to share my solution to the problem. My scenario is I have Insight v1.1.1.3 deployed and integrated with 2 CLM deployments, one of which is version 4.0.5 and the other is version 4.0.3. The RTC Data Manager ETLs run fine against the 4.0.5 CCM server however, when running the 4.0.3 DM ETLs against the 4.0.3 CCM server, errors are thrown.

Viewing the ri_jdbc.log file, I was able to detect the first error in the “RTC_WorkItemApprovalState4.0.3” fact build (see below for full error). It is complaining about a column it is trying to query that does not exist. The column is ‘projectAreaArchived’.

When I checked the XML data configuration file in the XDC tool, the respective resource (WorkItemApprovalState) was indeed missing this data element.


The solution to this is to expose the required data element which indeed does exist in the RTC reportable REST API.

  1. Launch the XML Data Configuration tool
  2. Load the workitem403.xdc file
  3. Navigate to the data mapping table: “Resources” > “RTCWorkItem” > “WORKITEM_CCM” > “WorkItemApprovalState” > “WorkItemApprovalState
  4. Double-click on the data mapping table
  5. Select the “Columns” tab and then select “Modify”
  6. Navigate through the RTC reportable REST API to find the ‘/archived’ data element. Highlight it and select “Create”. This will add the new element to the data mapping template.
  7. Rename it to ‘projectAreaArchived’ by clicking in the “Table Column Name” cell and modifying the text
  8. Save the configuration
  9. Close the “WorkItemApprovaState” data mapping template tab
  10. You should receive a dialog that tells you the template has been changed and asks you to update it. Select “Ok”
  11. Save your configuration once again
  12. Your new column will appear faded. You have added the column but now need to load it. Highlight your new column and click on “Load”.
  13. Save your configuration again and this new column is now available made to Insight, hence the ETL will no longer complain about it.

There are 4 different places where this error will be thrown. You will only see one error at a time as the ETL halts upon the first error in this case. Here are the 4 fact builds this affects:


Follow the steps I provided above for each of these 4 fact builds and you can proceed with your deployment without error.

For reference, here is the full error found in the ri_jdbc.log:

query => SELECT “id”,
FROM “WORKITEM_CCM”.”WorkItemApprovalState”
WHERE modifiedsince=’1899-12-31 00:00:00′ AND projectAreaArchived=false
03/06/2014 13:27:27,444 ERROR Thread-5 : CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
03/06/2014 13:27:27,444 ERROR Thread-5 : CRRRE1203E: The column ‘projectAreaArchived’ does not exist. CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
Caused by: CRRRE1203E: The column ‘projectAreaArchived’ does not exist.
… 1 more
03/06/2014 13:27:27,459 INFO main : CRRRE1228I: Close this statement

Timesheet entry data availability in RTC & Insight Data Warehouse

CLM included new ETLs for Timesheet Entry data in RTC version 4.0.4.

First, with respect to RTC itself, I found out that the RTC 4.0.4 Timesheet ETL requires a patch to get the Java ETLs to work correctly.

With respect to using Rational Insight, a user would only see the Timesheet ETLs in the Data Manager ETL catalog if they downloaded the version 4.0.4 and higher Data Manager PKG ETLs files from RTC. What I found to be a bit confusing, was that they are labeled with the version 4.0.3:



Development confirmed that the reason that they are labeled with 4.0.3 and not 4.0.4 was because these ETL jobs were originally scheduled for RTC version 4.0.3 but were actually delivered in 4.0.4. So, if you have integrated Insight with RTC 4.0.4 or  higher, expect to see these Timesheet ETLs under the RTC 4.0.3 jobs folder.

This Timesheet ETL job will run against RTC version 4.0.3. I have implemented this successfully. If you have integrated Insight (v1.1.1.2) with RTC v4.0.3 and want the Timesheet entry data job, you will need to do the following:

  1. Obtain the etl PKG file for RTC 4.0.4
  2. Create a new “test” Insight ETL catalog in Data Manager
  3. Import the Insight OOTB ETL catalog, then import the JTS/RTC PKG files from version 4.0.4.
  4. Once imported, create a new package file and select only the “RTC_Timesheet403” & “RTC_Timesheet403_FullLoad” jobs (located in RTCJobs > RTC4.0.3Jobs).
  5. Now, launch your ETL package where you require the Timesheet jobs and import the new PKG file you just created in the previous step.

Please note that IBM considers this a customization and that you will own this solution as it is not officially supported by IBM. My legal disclaimer applies here.