How to avoid getting a cartesian product of two table records in a CA Service Desk Manager CA Business Intelligence report

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When a CA Business Intelligence (CABI) report is executed against the CA Service Desk Manager (CA SDM) Universe, the underlying query for such report is formed based on domsrvr/internal components of CA SDM. 

So such a query depends on:

• Universe parameters and
• Joins between the tables in the Universe and
• Joins created manually within the report (via forcing a clause within the report) and
• Joins created via Attribute Aliases section of CA SDM Administration tab and 
• Any automatic joins the domsrvr is able to create based on its internals.

Consider a couple of Universe classes - Survey Answer (svy_ans) and Ticket Details.  The intention is to see what the Survey Answers were for a survey submitted against a given CA SDM ticket.

 1) Create a new Web Intelligence (WEBI) Report by selecting a few fields (for field names look at the SQL query statement below)

Survey -> Survey Answer -> pull needed fields

Ticket -> Ticket Details -> pull some other fields

2) Run the report.

The report now shows a lot of records, like a cartesian join of two table records. It basically contains all survey answer records for every ticket record and loops through all the ticket records. This is not the original requirement.  This normally happens because the attributes in question are neither a primary key nor a foreign key although they are unique. Out of the box the above joins are not formed as Inner Joins between such tables.

 

Environment

Release: SDMU0M99000-14.1-Service Desk Manager-Full License
Component:

Resolution

Option 1: Create an explicit join between the two tables within the report. 

Edit the report and create a force join like below in the WHERE Clause section of the report:

tkt.id = svy_ans.owning_survey_object_id

Note: It can be done by following a similar approach:

1) Select tkt.id from the Result Objects pane of the Edit Query window into the Query Filters pane

     

2) Select the dropdown arrow next to "In List" and select "Equal to"

3) Select the dropdown arrow all the way towards the end and select "Object from the query"

     

4) In the resulting Objects and Variables window, select Owning Survey Object ID from the Survey Answer class and click OK

     

5) In the Edit Query window, you will see a join: id Equal To Owning Survey Object ID

     

The report now shows a survey answer record for a survey submitted to that specific ticket only, which was the original requirement.

Option 2: Create a join between the two tables within the Universe, but not in the report (Note: usage of Derived Universe is normally recommended for this. Refer to the CA SDM Implementation Guide on how to create a Derived Universe)

1) Create a join between svy_ans and tkt tables over attribute tkt.id and svy_ans.owning_survey_object_id

     

2) In the Universe Designer -> File -> Parameters -> Parameter, add a new parameter 'INNERJOIN_IN_WHERE' and set value to 'Yes'.

           

3) Save and export the Derived Universe

4) Create a new report with the fields listed in the Description section of this document.

The report now shows a survey answer record for a survey submitted to that specific ticket only, which was the original requirement.

Note: There may be other options to approach the above requirement. The above solution options are a couple of approaches that are easier to implement.