Updating the DENORMALIZE_BSM_RELATIONSHIPS table

The DENORMALIZE_BSM_RELATIONSHIPS table is created and populated during the installation of BMC Analytics for Business Service Management (BMC Analytics for BSM), but you must update the data in the table after relationships in the business service model are updated. You must schedule the procedure to run weekly or daily.

Before you begin

Before you can run the procedure, you must have all the rights to compile and run the database package, and the business service must be at either the root or leaf/ground level. Consider the following points:

• The DENORMALIZE_BSM_RELS_PARENT and DENORMALIZE_BSM_RELS_CHILD procedures take the dataset ID as input and consider the business services and relationships under that dataset ID. If you do not specify a dataset ID, the procedure uses the default golden dataset BMC.ASSET.
  The DENORMALIZE_BSM_RELS_PARENT procedure works only for CIs that belong to the BMC_BUSINESSSERVICE class as the root of the acyclic graph, whereas the DENORMALIZE_BSM_RELS_CHILD procedure works only for CIs that belong to the BMC_BUSINESSSERVICE class as the leaf of the acyclic graph.
• The procedure does not insert business services that have cyclic graphs.
• The procedure does not consider the CIs that are marked as deleted in the relationships table.
• The procedure considers business services that are at the root or leaf/ground level. If a business service is a child of any CI, it is not considered.
  • If the business service (for example, IT Payroll Service) is a part of the tree and the ultimate parent for the tree is Business Service (for example, Payroll Service), the business service is added to the DENORMALIZE_BSM_RELATIONSHIPS table and displayed on the report as a child service of the ultimate parent service (for example, Payroll Service).
  • If the business service (for example, Email Service) is a part of the tree and the ultimate child (ground level child) for the tree is Business Service (for example, Message Service), the business service is added in to the DENORMALIZE_BSM_RELATIONSHIPS table and displayed on the report as a parent service of the ultimate child service (for example, Message Service)
• The procedure deletes all the records from the database and reloads them again for the specified dataset. There is no incremental loading of data.

To run the procedures

Run the DENORMALIZE_BSM_RELS_PARENT procedure to update the data in the DENORMALIZE_BSM_RELATIONSHIPS table.

Execute the following command at the SQL prompt. If you do not specify a dataset ID, the product defaults to BMC.ASSET.

SSQL>EXECUTE ARADMIN. PKG_BSM_RELS_PARENT.DENORMALIZE_BSM_RELS_PARENT ();

The following example uses the DISCOVERY dataset:

SQL> EXECUTE ARADMIN. PKG_BSM_RELS_PARENT.DENORMALIZE_BSM_RELS_PARENT ('DISCOVERY');

The DENORMALIZE_BSM_RELS_PARENT procedure calls the PC_BSM_RELS_TRAVERSAL_PARENT procedure and traverses one root level business service at a time in a recursive mode. The DENORMALIZE_BSM_RELS_CHILD procedure calls the PC_BSM_RELS_TRAVERSAL_CHILD procedure and traverses one leaf level business service at a time in a recursive mode. At the same time, this procedure adds the node order and node level used in the universe and for reporting purposes. If the procedure identifies a cyclic graph (loop) at any level for this business service, it does not consider any CIs and relationships under that business service.

Following is the pseudo code for the DENORMALIZE_BSM_RELS_PARENT procedure:

PROCEDURE DENORMALIZE_BSM_RELS_PARENT (DATASETID default 'BMC.ASSET')

BEGIN Procedure

Delete rows in the table DENORMALIZE_BSM_RELATIONSHIPS where the datasetid=DATASETID and markasdeleted is null and AND
RELATION_CODE=1

Select all the CI's which are Business Services and which are the root level for Each Business Service

Loop

Execute the procedure PC_BSM_RELS_TRAVERSAL_PARENT

End Loop

END Procedure

Following is the pseudo code for the PC_BSM_RELS_TRAVERSAL_PARENT procedure:

Procedure PC_BSM_RELS_TRAVERSAL_PARENT (nodeid, DATASETID default 'BMC.ASSET')

BEGIN Procedure

Select all the children for the SourcenodeCIID, datasetid parameters;

If Cyclic loop Exit;

Set nodeorder = nodeorder + 1;

For each child for the sourcenodeid

Loop

Set the values for the columns in the table;

Insert values into the DENORMALIZE_BSM_RELATIONSHIPS table;

Increment the Node Level;

-- Next statement is the recursive call to the same procedure

Execute procedure PC_BSM_RELS_TRAVERSAL_PARENT;

End Loop

END Procedure

Following is the pseudo code for the DENORMALIZE_BSM_RELS_CHILD procedure:

PROCEDURE DENORMALIZE_BSM_RELS_CHILD (DATASETID default 'BMC.ASSET')

BEGIN Procedure

Delete rows in the table DENORMALIZE_BSM_RELATIONSHIPS where the datasetid=DATASETID and markasdeleted is null and AND
RELATION_CODE=0

Select all the CI's which are Business Services and which are the leaf level

For Each Business Service

Loop

Execute the procedure PC_BSM_RELS_TRAVERSAL_CHILD

End Loop

END Procedure

Following is the pseudo code for the PC_BSM_RELS_TRAVERSAL_CHILD procedure:

Procedure PC_BSM_RELS_TRAVERSAL_CHILD (nodeid, DATASETID default
'BMC.ASSET')

BEGIN Procedure

Select all the children for the DestinationnodeCIID, datasetid parameters;

If Cyclic loop Exit;

Set nodeorder = nodeorder + 1;

For each child for the destinationnodeid

Loop

Set the values for the columns in the table;

Insert values into the DENORMALIZE_BSM_RELATIONSHIPS table;

Increment the Node Level;

-- Next statement is the recursive call to the same procedure

Execute procedure PC_BSM_RELS_TRAVERSAL_CHILD;

End Loop

END Procedure

Related topic

DENORMALIZE_BSM_RELATIONSHIPS table