1. Unified Inventory and Topology Deployment Guide
  2. Data Migration and Dynamic Attribute Mapping

6 Data Migration and Dynamic Attribute Mapping

This chapter describes how to perform the Data Migration and Dynamic Attribute Mapping.

Planning the Topology Migration

In preparation for implementing UTIA, you must set up the topology migration and the UIM to topology configuration. The UIM to topology migration extracts and loads necessary information from UIM into the topology graph model consisting of vertices and edges. Following the Database per Service Micro service Design Pattern, the topology graph resides in a Pluggable Database (PDB) container separated from the UIM database.

The migration consists of the following:

  • Index Rebuilding: The index rebuilding consists of re-creating indexes on tables with migrated data, dropping the temporary tables created during migration and renaming the tables with migrated data to actual topology tables.

Note:

If the UIM Entities are in ‘UNAVAILABLE’ state prior to migration, such entities will not be migrated.

Data Migration Approaches

You can follow the following approaches for data migration:

  • Data Migration through Database Link: Database Link (DBLink) is created from UTIA schema to UIM schema.
  • Data Migration through Read Access on UIM schema: UTIA schema is set up within the same PDB as that of the UIM schema. UTIA schema user is granted with SELECT (read access) along with the tables owned by UIM schema user. Data dump files are created for the migrated topology data. These dump files are then imported in the target PDB where the UTIA schema will be placed.

The prerequisites are:

  • Add DATAFILE to increase the TABLESPACE available (SYSTEM by default) for the UTIA schema user. Preferably one-fourth the size of UIM schema.

  • Data Migration to custom tablespace can be achieved by making the custom tablespace as the default tablespace for the UTIA schema user.

The Migration Steps are as follows:

  1. Build Characteristics tables for the following topology enabled entities such as Equipment, Logical Device, Network, Network Edge, Physical Device, Pipe and Place. These <ENTITY>_CHAR_MIG tables are used to store all characteristics on each entity which are used during Dynamic Attribute Migration and Customizing Topology JSON files. Build <ENTITY>_CHAR_MIG tables:
    • Open a command line window and login to SQL*Plus for the UIM database.
    • Run the following SQL scripts providing the full path of the files. For example, use the @scriptFileName command where scriptFileName is the full path and name of the file.
      • $WORKSPACEDIR/unified-topology-builder/migration_scripts/Char_Mig_tables/CREATE_CHAR_MIG_TABLE.sql
      • $WORKSPACEDIR/unified-topology-builder/migration_scripts/Char_Mig_tables/MIGRATION_CHAR1.sql
      • $WORKSPACEDIR/unified-topology-builder/migration_scripts/Char_Mig_tables/ MIGRATION_CHAR2.sql
      • $WORKSPACEDIR/unified-topology-builder/migration_scripts/Char_Mig_tables/ MIGRATION_CHAR3.sql
    • To verify if the scripts ran successfully, you can verify that the UIM schema includes the following tables:
      • EQUIPMENT_CHAR_MIG
      • LOGICALDEVICE_CHAR_MIG
      • NETWORK_CHAR_MIG
      • NETWORKEDGE_CHAR_MIG
      • PHYSICALDEVICE_CHAR_MIG
      • PIPE_CHAR_MIG
      • PLACE_CHAR_MIG
      • CHARACTERISTICS_TABLE_MAPPING_MIG

    Note:

    You can perform this step for any of the data migration approaches.
  2. The Topology schema user account must have the following privileges:
    • CREATE JOB
    • CREATE SESSION
    • ALTER SYSTEM
    • CREATE DATABASE LINK
    • CREATE PROCEDURE
    • CREATE SEQUENCE
    • CREATE TABLE
    • CREATE TYPE
    • UNLIMITED TABLESPACE
    • CREATE JOB

    These above privileges are sufficient for Approach 1, however for Approach 2:

    • Create SYNONYM.
    • Grant SELECT permission to all the tables owned by UIM schema user and UTIA schema user. 
    CREATE PROCEDURE grant_select(
    username VARCHAR2, 
    grantee VARCHAR2)
AS   
BEGIN
    FOR r IN (
        SELECT owner, table_name 
        FROM all_tables 
        WHERE owner = username
    )
    LOOP
        EXECUTE IMMEDIATE 
            'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee;
    END LOOP;
END;
	“username” – UIM Schema User
	“grantee” – UTIA Schema User within the same PDB.
  3. Static Attribute Migration:
    • Open a command line window and login to SQL*Plus for the Topology database.
    • Approach 1:
      • Migrate the static attributes data by running $WORKSPACEDIR/unified-topology-builder/migration_scripts/data_migration_script_using_dblink.sql
      • The following input arguments are expected:
        • UIM schema username
        • UIM schema password
        • Database Hostname
        • Database port number
        • Database Service name
        • Commit Size(Optional – 50000(Default))
        • Maximum number of parallel processes(Optional – 5(Default))
        • Wait Time(Optional – 2(Default in seconds))
    • Approach 2:
      • Migrate the static attributes data by running $WORKSPACEDIR/unified-topology-builder/migration_scripts/data_migration_script_using_localCopy.sql
      • The expects the following input arguments:
        • UIM schema username with in the PDB
        • Commit Size(Optional – 50000(Default))
        • Maximum number of parallel processes(Optional – 5(Default))
        • Wait Time(Optional – 2(Default in seconds))

    Note:

    Commit Size: The number of records handled by a single process, Maximum number of parallel processes – Depends on number of CPU’s available, Wait Time – Waiting interval after which the listener checks for the availability of jobs.
  4. Modify the topology JSON files in $WORKSPACEDIR/unified-topology-builder/migration_scripts/scriptGenerator/scriptGenerator_Execuable/topologyjsonfiles/ and run the following commands:

    Approach 1: java -jar scriptgenerator_dblink-1.0-jar-with-dependencies.jar

    Approach 2: java -jar scriptgenerator_localCopy-1.0-jar-with-dependencies.jar

  5. Dynamic Attribute Migration: Once the scriptgenerator_<Approach>-1.0-jar-with-dependencies.jar is run, the SQLs required for Dynamic attribute migration are generated in $WORKSPACEDIR/unified-topology-builder/migration_scripts/scriptGenerator/scriptGenerator_Executable/scriptOutFiles/dynamicAtt.sql. Run the SQL queries sequentially.
  6. Verify the migrated data by going through tables with %_FINAL or %_NEW name.
  7. Index Rebuild: The tables with names as %_FINAL and %_NEW contain the actual migrated data and indexes and constraints have to be added to these tables, these are generated in $WORKSPACEDIR/unified-topology- builder/migration_scripts / scriptGenerator/scriptGenerator_Executable/ scriptOutFiles/indexRebuild.sql. Run the SQL queries sequentially.
  8. In case of performing data migration using Approach 2, export the migrated Topology Data and import the migrated Topology Data into the target PDB where the UTIA schema is expected to be.
  9. Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. To gather new statistics, run the following command:
    EXEC DBMS_STATS.gather_schema_stats( '<TopologySchema_Name>' );

Note:

PG_PROFILE tables which store the Service Topology Data are not supported in existing migration. If you want service topology profile data in the topology schema you can create a new service configuration and approve it. In 7.5.1.0.0, Profile Data is created for every service configuration in Approved State.

Customizing Topology JSON files for Migration

The $WORKSPACEDIR/unified-topology-builder/migration_scripts/scriptGenerator/scriptGenerator_Execuable/topologyjsonfiles/ contains three topology JSON files:

  • topologyAttributeMapping.json
  • topologyRoleMapping.json
  • topologySpecificationMapping.json

Customize topologyAttributeMapping.json

[
    {
        "name": "LogicalDeviceDAO",
        "properties": [
            {
                "name": "NativeEMSName",
                "property": "NativeEMSName",
                "vertex": "",
                "columnName": ""
            }
        ]
    }
]

TopologyAttributeMapping (TAM) is an array defining how attributes of different DAO’s can map to Topology Schema. Each TAM object consists of key-value pairs of name and properties.

  • name – Maps to different entity classes and entity specification classes. For example: “LogicalDeviceDAO”, “EquipmentSpecificationDAO”, “PlaceSpecificationDAO”, “PropertyLocationDAO” and so on.
  • properties – This is an array defining how individual attributes of an entity are supposed to be stored in Topology schema. Each JSON object of the properties has:
    • name – Name of the Attribute.
    • property – Name of the key used to store the value retrieved from Attribute.
    • vertex – Build the relationship with the Vertices, from Topology Schema.
    • columnName – Column from Topology Schema used to store the Attribute values.

Note:

In “properties” array objects, “name” is a mandatory field to be provided which maps to either “property” or “vertex” or “columnName”.

An example of TAM is:

Assume, the topologyAttributeMapping.json contains the following:

[
    {
        "name": "LogicalDeviceSpecificationDAO",
        "properties": [
            {
                "name": "vendorName",
                "property": "",
                "vertex": "vendor",
                "columnName": ""
            },
            {
                "name": "modelnumber",
                "property": "Model",
                "vertex": "",
                "columnName": ""
            }
        ]
    },
    {
        "name": "EquipmentDAO",
        "properties": [
            {
                "name": " NativeEMSName",
                "property": "",
                "vertex": "",
                "columnName": "DEVICEIDENTIFIER"
            }
        ]
    }
]

In the above example:

  • LogicalDeviceSpecification table from UIM schema is expected to have “vendorName” and “modelnumber” columns which are used to do the following:
    • All LogicalDeviceSpecification’s which have a vendorName as some non-null value is moved to PG_VENDOR table and containment edges between the devices of LogicalDevice type and their respective vendors are created in PG_DEVICE_TO_VENDOR table.

      Example: Assume there are 2 Logical Devices (“LDSampleDevice1” and “LDSampleDevice2”) of specification “LDSampleSpec”, and “LDSampleVendor” is the “vendorName”. Then, vertex/record for “LDSampleVendor” is created in PG_VENDOR table and the logical devices have their respective containment edges to the “LDSampleVendor” in PG_DEVICE_TO_VENDOR table.

    • All LogicalDeviceSpecification’s which have a “modelnumber” as some non-null value is stored in “PROPERTIES” column of PG_DEVICE table. For example: “LDSampleSpec” has “APTS-123” as “modelnumber”, then it’s stored as:
      {
    "Model": "APTS-123"
}

    • Equipments which have non-null value in “NativeEMSName” are stored in “DEVICEIDENTIFIER” column of PG_DEVICE table.

Customizing “topologyRoleMapping.json”
[
    {
        "name": "ADM",
        "entityClass": [
            "LogicalDeviceDAO",
            "PhysicalDeviceDAO",
            "EquipmentDAO"
        ],
        "property": "",
        "vertex": "domain",
        "columnName": ""
    }
]

TopologyRoleMapping (TRM) is an array defining how entities which are role-enabled are stored in Topology schema. Each TRM object contains key-values pairs of “name”, “entityClass”, “property”, “vertex” and “columnName”.

  • name – Name of the Role.
  • entityClass – Entities which are enabled by the role and want data migrated for.
  • property – Name of the key used to store the Role.
  • vertex – Build the relationship with the Vertices, from Topology Schema
  • columnName – Column from Topology Schema used to store the Role.

Note:

In each TRM object “name” is a mandatory field with role information which can be mapped to either “property” or “vertex” or “columnName”. If “entityClass” is empty ([]) that is same as role information to be checked in Logical Device, Equipment, Physical Device, Place, Pipe and Network.

An example of TRM is:

Assume, the topologyRoleMapping.json contains the following:

[
    {
        "name": "ADM",
        "entityClass": [
            "LogicalDeviceDAO",
            "PhysicalDeviceDAO",
            "EquipmentDAO"
        ],
        "property": "",
        "vertex": "domain",
        "columnName": ""
    },
    {
        "name": "EIGRP",
        "entityClass": [
            "LogicalDeviceDAO"
        ],
        "property": "routingProtocol",
        "vertex": "",
        "columnName": ""
    },
    {
        "name": "Router",
        "entityClass": [
            "EquipmentDAO"
        ],
        "property": "",
        "vertex": "",
        "columnName": "nodeCategory"
    }
]

In the above example,

  • A record for ADM is created in PG_DOMAIN table and all logical devices, equipments, and physical devices that are enabled by the ADM role, have the corresponding records in the PG_DEVICE_TO_DOMAIN table.
  • All logical devices enabled by the EIGRP role have the PROPERTIES column populated with
    {
    "routingProtocol": "EIGRP"
}
  • All equipments enabled by the Router role have Router stored in the NODECATEGORY column of PG_DEVICE table.

Customizing “topologySpecificationMapping.json”

[
    {
        "name": "EthernetDevice",
        "entityType": "LogicalDeviceSpecificationDAO",
        "relatedVertices": [
            {
                "vertex": "domain",
                "value": "Ethernet"
            }
        ],
        "characteristics": [
            {
                "name": "zoneID",
                "property": "",
                "vertex": "",
                "columnName": "ZONEID"
            }
        ]
    }
]

TopologySpecificationMapping (TSM) is an array defining how characteristics of a specification are mapped Topology schema and how all entities of a specification can have containment edge to other entities. Each TSM object contains key-values pairs of “name”, “entityType”, “relatedVertices” and “characteristics”.

  • name – Name of the Specification.
  • entityType – The type of entity does the specification represent.
  • relatedVertices – Create containment edges for all entities of the given specification with the vertex and value. This contains an array of objects which have:
    • vertex – To which vertex the containment edges must be created to.
    • Value – The value of the vertex.
  • characteristics – Array of characteristics provided by the specification and how they are stored in Topology schema.
    • name – Name of the characteristic(case-sensitive)
    • property- Name of the key used to store the characteristic.
    • vertex – Build the relationship with vertices in Topology schema.
    • columnName – Column from Topology schema in which the characteristic is stored.

Note:

In each TSM object “name” and “entityType” are mandatory fields with specification and type of specification information. “relatedVertices” is used to create direct containment edges for all entities of the specification in question. “characteristics” is an array of objects where “name” is mandatory and talks about the characteristics provided by specification and can be mapped to either “property” or “vertex” or “columnName”.

An example of TSM is:

Assume, the topologySpecificationMapping.json contains the following:

[
    {
        "name": "cableModem",
        "entityType": "PhysicalDeviceSpecificationDAO",
        "characteristics": [
            {
                "name": "deviceType",
                "property": "deviceType",
                "vertex": "",
                "columnName": ""
            }
        ]
    },
    {
        "name": "EthernetDevice",
        "entityType": "LogicalDeviceSpecificationDAO",
        "relatedVertices": [
            {
                "vertex": "domain",
                "value": "Ethernet"
            }
        ],
        "characteristics": [
            {
                "name": "Tech",
                "property": "",
                "vertex": "Technology",
                "columnName": ""
            }
        ]
    },
    {
        "name": "Generic_Address",
        "entityType": "PlaceSpecificationDAO",
        "characteristics": [
            {
                "name": "CityName",
                "property": "",
                "vertex": "",
                "columnName": "city"
            },
            {
                "name": "StateName",
                "property": "",
                "vertex": "",
                "columnName": "state"
            },
            {
                "name": "PostalCode",
                "property": "",
                "vertex": "",
                "columnName": "postalCode"
            }
        ]
    }
]

In the above example,

  • “cableModem” is a PhysicalDeviceSpecification which has a characteristic “deviceType”. This characteristic is stored in “PROPERTIES” column of PG_DEVICE table.
    {
    "DeviceType": "deviceType"
}
  • A record for “Ethernet” is added to PG_DOMAIN table. All devices of “EthernetDevice” specification have containment edges to “Ethernet” in PG_Device_To_Domain table.
  • “EthernetDevice” has a characteristic called “Tech”, so all unique values of “Tech” characteristic are added to PG_Technology. And for each “EthernetDevice” depending on its “Tech” characteristic respective containment edges are built.
  • “Generic_Address” is a Place which has “CityName”, “StateName” and “PostalCode” characteristics which are mapped to “CITY”,”STATE” and “POSTALCODE” columns of PG_LOCATION table.

Customizing Topology JSON Files

To customize the topology JSON files:

  1. When migrating Attribute or Role or Characteristic data to “PROPERTIES” column of respective entity, make sure the key used doesn’t include any empty space or special characters:

    {
    "name": "Vendor Name",
    "property": "",
    "vertex": "vendor",
    "columnName": ""
}

    The above example “Vendor Name” contains empty space. Instead use “VendorName” or “Vendor_Name”.

  2. In topologySpecificationMapping.json if the characteristic being migrated has length greater than 30 characters or contains special characters, the <ENTITY>_CHAR_MIG, do not have the characteristic as is. Instead, it has been casted to coded value, which can be derived from “CHARACTERISTICS_TABLE_MAPPING_MIG” in UIM schema.

    For example: “Inter-rack_Power_Distribution” (CHAR_NAME) is the name of the characteristic which has been casted to “C46575002” (COLUMN_NAME).

    {
    "name": "Inter-rack_Power_Distribution",
    "property": "",
    "vertex": "",
    "columnName": "nodeCategory"
}
The above example would result in a column not found error, instead characteristic must be migrated as follows:
{
    "name": "C46575002",
    "property": "",
    "vertex": "",
    "columnName": "nodeCategory"
}

Dynamic Data Mapping from UIM

The dynamic data mapping takes advantage of UIM characteristics and provides maximum flexibility for mapping fields from UIM to the topology model.

The dynamic data mapping:

  • Does not require any additions, updates, migrations, or deployments of your existing specifications.
  • Guarantees the value is set correctly and does not require a user to select the correct value.
  • Allows UTIA to support data extensions to the topology model without an upgrade.
  • Vertex and Edge Labels or Properties in UTIA may require different names than Characteristics, or Attributes or Roles in the implemented UIM model.
  • These items are supported through dynamic data mapping.

The examples are:

  • UIM has a 'Vendor' attribute on the Logical Device and Equipment Specifications but some users have added 'manufacturer' to their Physical Device Specifications.
  • Some vertices are not identified specifically in UIM such as Domain and Service Type. These values are implied based on the '5G' cartridge or the 'FTTx' cartridge but are not specifically identified on the entity.

Prerequisites for Dynamic Data Mapping from UIM

The prerequisites are as follows:

  • The following configuration files are required:
    • topologyAttributeMapping.json
    • topologyRoleMapping.json
    • topologySpecificationMapping.json.
  • These files must exist in the <domain>/UIM/config/topologyMappings directory.
  • Files with these names plus the extension .sample are provided.
  • Prior to migration, the correct configurations must be provided. Else, the data will not be mapped correctly to UTIA.
  • If the file does not exist an error occurs during UIM entity creation.
  • If you want to skip this process, you can remove the .sample extension and proceed with the default settings.

Mapping the Dynamic Data from UIM

To map the dynamic data from UIM, the following definitions are required:

  • vertex: A node in the Topology Model, examples are Vendor, Domain, Technology, Network Type, Device, Location
  • property: A column on every vertex and edge in the Topology model.
    • It supports JSON allowing for unlimited additional attributes.
    • Property is the name of the key used to store the value retrieved from the UIM attribute.
  • properties: is an array defining how individual attributes of an entity are to be stored in Topology schema.
  • columnName: An existing column on a physical table in the Topology Model used to store the attribute.
  • name: Maps to different entity classes and entity specification classes. For example: “LogicalDeviceDAO”, “EquipmentSpecificationDAO”, “PlaceSpecificationDAO”, “PropertyLocationDAO” and so on.

The following POST operation creates a logical device, you can see the relationships and properties with which the dynamic properties are supported.

POST: http://localhost:8080/vertex

Body: 

{"entityId":<entityID>,"entityVersion":<entityVersion>,"businessObjectClass":"LogicalDeviceDAO","id":"<ID>","name":"<name>","specName":"<specificationName>","latitude":0.0,"longitude":0.0,"inventoryStatus":"INSTALLED","referenceId":<referenceID>,"relationships":{"vendor":"<vendor>"},"properties":{"deviceIdentifier":"<deviceIdentifier>"}}

Note:

  • In this example, the TopologyAttributesMapping.json file provides the instructions to UTIA and the file is available in the UIM/config/topologyMappings directory.
  • The topologyAttributesMapping file is used to address hard coded attributes from UIM tables.
  • See topologyAttributesMapping.json for more information.

The POST operation tells the topology:

  • Map LogicalDevice.deviceIdentifier to the property deviceIdentifer.
  • Map LogicalDeviceSpecification.vendorName to the vertex = vendor
  • This is based on the UIM ClassName, it works with any Class or specification that is topology-enabled.

You can add a role to the Logical Device from the list of roles that are configured in the TopologyRoleMapping.json file.

You can see that GET that the Logical Device tracks the deviceIdentifier in the properties column using:

GET: http://localhost:8080/vertex/typeid/1/referenceid/<refID>

{"businessObjectClass":"LogicalDeviceDAO","entityId":<entityID>,"entityVersion":<entityVersion>,"id":"<versionID>","inventoryStatus":"INSTALLED","latitude":0.0,"longitude":0.0,"name":"<name>","properties":{"deviceIdentifier":"<deviceID>"},"referenceId":<referenceID>,"specName":"<specificationName>"}

PUT: http://localhost:8080/vertex

{"businessObjectClass":"LogicalDeviceDAO","entityId":<entityID>,"entityVersion":3,"id":"<ID>","inventoryStatus":"INSTALLED","latitude":0.0,"longitude":0.0,"name":"<name>","properties":{"deviceIdentifier":"<ID>","transmission":"Optical_Transmission"},"referenceId":<refernceID>,"specName":"<specificationName>"}

In the body:

  • The role “Optical_Transmission” is mapped to the property field with name = “transmission”.
  • The role was given a name = “transmission” which was provided by the UIM admin.
  • Add, update and delete are supported. This works for Equipment and Physical Device (any topology-enabled entity that supports roles).
  • Roles can be mapped to properties, vertices or columns.

The rules to perform this are:

  • The Vertex must exist. The mapping can be performed to multiple vertices and can have multiple values.
  • Property: There can be multiple properties. The UIM integrator is responsible for not having similar or misspelled values.
  • ColumnName: A column can only have 1 value. The user is currently responsible for assuring this value is unique. It can be overlaid. This should be used for a queried attributes where an index is needed.
  • The possible values of "columnName" are the following:
    • PG_DEVICE - [ NODECATEGORY, MACADDRESS, IPV4, IPV4SUBNET, IPV6, IPV6SUBNET, ZONEID, DEVICEIDENTIFIER, NETWORKSTATUS, NODETYPE]
    • PG_LOCATION - [DISTRICT, PROVINCE, OPERATOR, CITY, STATE, POSTALCODE, COUNTRY, AREA, CIRCLE]
    • PG_COMMICATION - [FROMNODEDATA, TONODEDATA, RATECODE, TECHNOLOGY]
    • PG_NETWORK - [CATEGORY, SUBCATEGORY, TOPOLOGYTYPE, SUBTYPE]

Note:

UIM currently supports city, state, country and postalcode attributes from the PropertyLocationDAO and PropertyAddressDAO. The street address or subunit (apt#, room #) are not supported.

The supported UIM classes are:

LogicalDeviceDAO, GeographicPlaceDAO, PhysicalDeviceDAO, NetworkDAO, NetworkEdgeDAO, EquipmentDAO, GeographicSiteDAO, PropertyLocationDAO

Note:

This includes the corresponding supported specification classes.

The last configuration is TopologySpecificationMapping.json.

  • The related vertices field automatically adds a relationship edge between any instance of the specification to the vertex with the provided name and value.
  • A characteristic does not need to be added and set on the specification to be tracked in topology.
  • This allows our current RI cartridges to be used without any modifications.
  • The characteristics column works the same as roles.
  • It automatically adds a relationship to a vertex, sets properties or sets a column value.
  • Any current characteristics can be used. No changes are needed.

PUT: http://localhost:8080/vertex

{"entityId":<entityID>,"entityVersion":<entityVersion>,"businessObjectTypeId":1,"businessObjectClass":"LogicalDeviceDAO","id":"<ID>","name":"<name>","specName":"router","latitude":<latitude>,"longitude":<longitude>,"inventoryStatus":"INSTALLED","isTopLevelNode":true,"nodeAvailable":true,"placeNode":false,"referenceId":<referenceID>,"createdUser":"test","lastModifiedUser":"test","relationships":{"vendor":"<vendor>","domain":"Ethernet"},"properties":{"deviceIdentifier":"<deviceID>"}}