CA Business Intelligence (CABI) report using all CA Service Desk Manager (CA SDM) KPI objects returns no data.

Document ID : KB000048164
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

CABI report using all of the following CA SDM KPI objects returns no data:

  1. Collected SQL and Stored Query KPI Values

  2. Collected System KPI counts

  3. Collected System KPI Durations

Solution:

The three KPI Objects (Collected SQL And Stored Query KPI Values, Collected System KPI Counts and Collected System KPI Durations) serve the same purpose(fetch KPI name), but with a different where clause.

Below are the SELECT and WHERE clauses of each object.

Collected SQL And Stored Query KPI Values


   SELECT
   @Select(Kpi Catalog Attributes\Kpi Name)
   WHERE
   @Select(Kpi Catalog Attributes\Kpi Type) > 0

Collected System KPI Counts


   SELECT
   @Select(Kpi Catalog Attributes\Kpi Name)
   WHERE
   @Select(Kpi Data\Metric Type)=0 AND @Select(Kpi Catalog Attributes\Kpi Type) = 0

Collected System KPI Durations


   SELECT       
   @Select(Kpi Catalog Attributes\Kpi Name) 
   WHERE
   @Select(Kpi Data\Metric Type)=3 AND @Select(Kpi Catalog Attributes\Kpi Type) = 0

When including all the three KPI objects into a CABI report, all the three where clauses of the three KPI objects are appearing in SQL query that is generated.


   ((kcd.metric_type)= 0 AND (kcd.kpi_type) = 0) AND ((kcd.metric_type)= 3 AND (kcd.kpi_type) = 0) AND ((kcd.kpi_type)> 0) 

By analyzing the above SQL query, KCD.METRIC_TYPE cannot equal 0 and equal to 3 at the same time.

Therefore, you cannot use the three KPI objects at the same time in the same CABI report.

This is by design and is working as expected.