TrueSight Capacity Optimization (TSCO) - Unable to populate domain tree from Database Extractor Object Relationships ETL

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    TrueSight Capacity Optimization


    APPLIES TO:

    All Versions



    PROBLEM:

    Unable to populate domain tree from Database Extractor Object Relationships ETL.  The ETL runs with OK status, but entities are not being populated into the workspace.

    Current SQL queries from the ETL look like this:

    Entities query:

       
       DMObjrelSqlE: custom sql code: select ts,entcatnm, entcatnmparent, entnm, ds_entnm, stronglookupfields, weaklookupfields, ds_entnmparent  from "scmRelat" 
      
      
    Relationship query:  
       
          
      
      
       
       DMObjrelSqlE: sql CI relationships code: select ts,entcatnm, entcatnmparent, entnm, ds_entnm, stronglookupfields, weaklookupfields, ds_entnmparent  from "scmRelat" 
      

     


    CAUSE:

    There need to be 2 properly formed SQL queries for the ETL to function, an Entities query, and a Relationship query. (The example shows nearly identical queries from same source). These should differ to match the needed.


    SOLUTION:

    Confirm that the queries for this ETL are written to include the following information:
     

    Configuring the SQL query

      

    The Entities query must list all entities involved in the CMDB transaction:

                                                                                                                                                                                     

    Field name

    Note

    Mandatory

    ENTNM

    Resource name

    Yes

    DS_ENTNM

    Resource name in the data source

    Yes

    ENTCATNM

    Entity type (APP, WKLD, SYS)

    Yes

    ENTTYPENM

    Type definition

    No

    DESCRIPTION

    Description

    No

    LOCATIONNM

    Location Name

    No

      

    The Relationships query must provide all parent/child relationships among entities:

                                                                                                                                                              

    Field name

    Note

    Mandatory

    ENTNM

    Child resource name

    Yes

    DS_ENTNM

    Child resource name in the data source

    Yes

    ENTCATNM

    Child entity type (APP, WKLD, SYS)

    Yes

    ENTCATNMPARENT

    Father entity type (APP, WKLD, SYS)

    Yes

    DS_ENTNMPARENT

    Father resource name in the data source

    Yes

       These queries should be different.  

    Failing to include SQL to include the information listed will result in the entities and their relationships not being properly created or asserted. For more details, please refer to the product documentation for this ETL, found here: https://docs.bmc.com/docs/display/btco107/Generic+-+Object+relationship+SQL+extractor

      

    Additional Information

      

    In relation to the Generic Object Relationship SQL Extractor the most common issues are:
      (1) There are entities in the RELATIONS query that have not been defined in the ENTITIES query.  These may be at the child element level or at the parent element level.

    Assuming your ENTITIES and RELATIONS query are in a database table called ENTITIES and RELATIONS the following SQL can detect that for the parent entities:
      SELECT DS_ENTNMPARENT FROM RELATIONS R WHERE R.DS_ENTNMPARENT NOT IN (SELECT E.DS_ENTNM FROM ENTITIES E) AND DS_ENTNMPARENT != '_ROOTAPP_';

    The following SQL can detect that for the child entities:
      SELECT DS_ENTNM FROM RELATIONS R WHERE R.DS_ENTNM NOT IN (SELECT E.DS_ENTNM FROM ENTITIES E);

      (2) In the ENTITIES query when using multiple lookup the STRONGLOOKUPFIELDS and WEAKLOOKUPFIELDS columns will be used when attempting to map that entity to an existing entity with a matching lookup in the TSCO database.  In the RELATIONS SQL statement when using multiple lookup the STRONGLOOKUPFIELDS and WEAKLOOKUPFIELDS columns will be used to map the DS_ENTNMPARENT to the correct entity in the ENTITIES query output.  So, when using multiple lookup it is necessary to define the appropriate STRONGLOOKUPFIELDS and WEAKLOOKUPFIELDS in both queries.

      (3) One cannot mix single and multiple lookup entities in a single Object Relationship SQL Extractor.

    So, a common initial error  one will see are the APP (folder) entities using single lookup but system entities using multiple lookup like this (sample ENTITIES SQL query output in CSV format):
    ENTNM,DS_ENTNM,ENTCATNM,STRONGLOOKUPFIELDS,WEAKLOOKUPFIELDS
    folder1,folder1,APP,,,
    folder2,folder2,APP,,,
    system1,NAME#system1,SYS,NAME,NAME
    system2,NAME#system2,SYS,NAME,NAME

    That isn't a valid configuration because the APP entities aren't defining their STRONGLOOKUPFIELDS and WEAKLOOKUPFIELDS but the SYS entities are.  Once any entity in the ETL is using multiple lookups all entities need to use multiple lookup.

    So, that could be changed to something like this:

    ENTNM,DS_ENTNM,ENTCATNM,STRONGLOOKUPFIELDS,WEAKLOOKUPFIELDS
    folder1,APPNM#folder1,APP,APPNM,APPNM
    folder2,APPNM#folder2,APP,APPNM,APPNM
    system1,NAME#system1,SYS,NAME,NAME
    system2,NAME#system2,SYS,NAME,NAME

    Now both the APP entities and the SYS entities are both using multiple lookup.

      (4) The Object Relationship SQL Extractor must define relationships that start with a root domain of _ROOTAPP_.  The Object Relationship ETL can't build a partial tree which is an add on to a tree built by another ETL.

    For example,Object Relationship ETL 1 can't build:
      _ROOTAPP_
        -> folder 1
        -> folder 2

    And then have Object Relationship ETL 2 just define relations for the bottom folders to the entities that live in those folders:
        -> folder 1
          -> system A
        -> folder 2
          -> system B

    Sometimes people try that because they want ETL 1 to be configured to create new entities (create new folders) but then configure ETL 2 to not create new entities (because they only want to map to existing system entities).  The hierarchy will not be properly constructed if it isn't built all the way back to the _ROOTAPP_ folder level.

    (5) If the ETL is configured with "Skip Entity Creation" set to 'true' or the ETL is expected to associate its data with existing TSCO system entities then the ETL must be associated with the Entity Catalog in which the existing entities reside.  Entities will be looked up in the shared catalog associated with the relationships ETL using the Lookup values defined in the ETL.  Workspace domain folders aren't entities -- they are "Application" (APP) objects reported in the APPLICATION table.  APP objects have visibility to all ETLs since they don't have any association to a particular entity catalog.

    So, as long as the ETL is in the correct catalog to be able to find the system entities then it will be able to find the APP entities and make an association with them.

     


    Article Number:

    000351622


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles