10 Migrating Price List Data from Legacy Databases

This chapter describes the process of migrating legacy pricing and discounting data to the Oracle Communications Billing and Revenue Management (BRM) Pipeline Manager database. Before you read this chapter, you should be familiar with database administration tasks. In addition:

You must have in-depth knowledge of your legacy database.
You must have in-depth knowledge about the Pipeline Manager database.
You must know XML programming.
You must have a good understanding of XSD. See "Working with and Modifying the XSD".
You must understand the objects that are supported by the Pipeline Manager database and know the order in which the objects must be loaded into the Pipeline Manager database. For information, see "About the Types of Objects to Migrate".

Important:
Pipeline Configuration Manager is an optional component.

About Migrating Legacy Data

The Pipeline Configuration Manager is a set of scripts and utilities you can use to load data into the Pipeline Manager database.

Use the LoadIfwConfig utility to load pricing data into the Pipeline Manager database. You can migrate legacy data by adding the data to an XML file and running the LoadIfwConfig utility to load the data.

Any type of legacy database such as Oracle can be migrated to the Pipeline Manager database by using the LoadIfwConfig utility.

You use the LoadIfwConfig utility to migrate each table of your legacy database to the Pipeline Manager database. For more information, see "Loading Legacy Data into the Pipeline Manager Database with LoadIfwConfig".

During data migration, if the legacy system and Pipeline Manager are run in parallel, keep the databases synchronized by migrating data every time you make a change in the legacy database.

Overview of the Migration Process

The migration process follows these steps:

Planning the migration.

This step includes comparing the data in the legacy database and the Pipeline Manager database and figuring out how to migrate the data from one database structure to another. See "Guidelines for Mapping Legacy Data".
Extracting legacy data to an XML file by using a data transformation program. See "About Migrating Legacy Data".
Loading data into the Pipeline Manager database by using the LoadIfwConfig utility. See "Loading Legacy Data into the Pipeline Manager Database with LoadIfwConfig".

Legacy Data XML File Example

Legacy data representation in the XML file

<?xml version="1.0" encoding="UTF-8"?>
<IFW xmlns ="http://www.portal.com/tools/pricing" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.portal.com/tools/pricing IfwConfig.xsd">
<UOM UnitsOfMeasurement="hp1" Name="abc"/></IFW>

Guidelines for Mapping Legacy Data

You map the legacy pricing data to the pipeline pricing data in an XML file. This XML structure is defined in the XSD (IfwConfig.xsd). See "Working with and Modifying the XSD".

The structure of legacy pricing data is almost always different from the structure used by Pipeline Manager. For example, the legacy data might have pricing element equivalent to a pipeline rate plan, but not the equivalent of a pipeline rate plan version.

When you map legacy data, compare the structure of your legacy data against the structure of the Pipeline Manager data. Table 10-1 shows a sample comparison:

Table 10-1 Comparing Legacy and Pipeline Manager Data

Legacy Data	Pipeline Manager Data
Rate plan	Rate plan
No equivalent	Rate plan version
Rate plan configuration	Rate plan configuration
Rate plan model	No equivalent

In this example:

You must create rate plan versions. This data might be in another part of the legacy data; for example, the version information might be included in the legacy rate plan itself.
You must map the data in the legacy rate plan model to one of the pipeline rate plan elements.
The legacy data and the Pipeline Manager data have rate plans and rate plan configuration. However, you must ensure that those elements include the same data.
The XML file you create must use the structure defined in the XSD.

If you cannot directly map the data, then export the legacy data to flat files, and run a data transformation program on the flat files to produce data that can be loaded into the Pipeline Manager database. You can use any third-party tool for this purpose, depending on the type of legacy database. XML is a widely accepted programming language so many parsers and tools are available to transform data to XML.

Rate Plan XML File Example

The following examples show the rate plan structure from the XSD file, and a rate plan in the XML file:

Rate plan definition in the XSD file

<xs:element name="RatePlan">
  <xs:annotation>
    <xs:documentation>...</xs:documentation>
  <xs:annotation>
  <xs:complexType>
    <xs:attribute name="RatePlanCode" type="xs:string" use="required"/>
    <xs:attribute name="Name" type="xs:string"/>
    <xs:attribute name="Status" default="D">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:length value="1"/>
          <xs:minLength value="1"/>
          <xs:maxLength value="1"/>
          <xs:enumeration value="S"/>
          <xs:enumeration value="T"/>
          <xs:enumeration value="A"/>
          <xs:enumeration value="D"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="SystemBrandCode" type="xs:string"/>
    <xs:attribute name="CurrencyCode" type="xs:string" use="required" fixed="USD"/>
    <xs:attribute name="IsSplitting" type="xs:boolean" default="0"/>
    <xs:attribute name="Type" default="R">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:length value="1"/>
          <xs:minLength value="1"/>
          <xs:maxLength value="1"/>
          <xs:enumeration value="W"/>
          <xs:enumeration value="R"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="TaxTreatment" type="xs:boolean" use="required"/>
    <xs:attribute name="CalendarCode" type="xs:string" use="required"/>
    <xs:attribute name="UTCTimeOffset" type="xs:string" default="+0100"/>
  </xs:complexType>
</xs:element>

Rate plan representation in the XML file

<RatePlan 
RatePlanCode="ratepc" 
Name="rateplanc" 
Status="D" 
SystemBrandCode="" 
CurrencyCode="USD" 
IsSplitting="0" 
Type="R" 
TaxTreatment="1" 
CalendarCode="cal2001" 
UTCTimeOffset="+0100"/>

Mapping Legacy Data

You extract the legacy data to a file by using a data conversion program. After the data is extracted from the legacy database, you migrate the data into an XML file. (You can also extract the data directly into an XML file.) You can insert different types of data, objects, and attributes in a single XML file.

To map legacy data, create an XML file that uses the structure defined in the XSD. Then migrate the objects in the specified order. See "About the Types of Objects to Migrate".

About the Types of Objects to Migrate

Important:

For information about the objects and fields in the Pipeline Manager database, see the documentation in Pipeline_home/database.

The objects that can be migrated to the Pipeline Manager database are defined in the XSD.

Table 10-2 shows the objects that can be migrated to the Pipeline Manager database and the prerequisite objects required for some objects. The prerequisite objects are the parent objects, which have objects dependent on them.

You must load child objects in the order shown in the table, as data in some objects depends on the existence of other objects. You load parent objects before child objects. However, sometimes a parent object can be loaded after loading a child object if the field in the child object that relates to parent object is not a mandatory field. For example, the Geomodel object is a parent of the Zonemodel object. However, the Geomodel field in the Zonemodel object is not a mandatory field, so you can load the Zonemodel object first.

Table 10-2 Order to Be Used in Loading Child Objects

Objects	Parent Object Prerequisites
Edrcdesc	None
Edrcfield	Edrcdesc
Aliasmap	Edrcfield Edrcdesc
Calendar	None
Holiday	Calendar
Pipeline	Edrcdesc
Uom	None
Currency	None
Resource	Currency
Taxcode	None
Taxgroup	None
Tax	Taxgroup Taxcode
GLaccount	Taxcode
Revenuegroup	None
Rum	None
Rumgroup	None
Rumgrouplnk	Rumgroup Rum Uom
Service	GLaccount Revenuegroup Rumgroup
Serviceclass	Service
Refmap	None
Mapgroup	None
Servicemap	Mapgroup Service
Usageclass	None
Usageclassmap	Usageclass Mapgroup
Uscgroup	None
Rscgroup	None
Apngroup	None
Zonemodel	Geomodel Apngroup
Impactcat	None
Geomodel	Ruleset
Geoarealnk	Geomodel
Geozone	Zonemodel Service Impactcat
Standardzone	Service Zonemodel Impactcat
Usagetype	None
Uscmap	Usagetype Zonemodel Uscgroup Impactcat
Apnmap	Apngroup Impactcat
Discarding	Pipeline
Daycode	None
Timeinterval	None
Timezone	None
Timemodel	None
Timemodellnk	Timezone Daycode Timeinterval Timemodel
Pricemodel	None
Pricemdlstep	Pricemodel Resource Rum GLaccount Revenuegroup
Discountmodel	None
Dscmdlver	Discountmodel
Discountmaster	None
Discountdetail	Discountmaster
Dsctrigger	None
Dsccondition	Dsctrigger
Discountrule	Discountmaster
Discountstep	Discountrule
Dscmdlcnf	Discountrule Dsctrigger Dscmdlver
Rateplan	Calendar Systembrand Currency
Rateplanver	Zonemodel Rateplan
Rateplancnf	Service Rateplanver Pricemodel Timezone Timemodel Impactcat
Rateadjust	Rateplanver
Specialdayrate	None
Specialdaylnk	Rateplanver Specialdayrate
Scenario	Edrcdesc
Uommap	Uom Rum
Systembrand	None
Exchangerate	Currency
Rscmap	Serviceclass Rscgroup
Rule	None
Ruleitem	None
Ruleset	None
Rulesetlist	Ruleset
Sla	Uscgroup Rscgroup Ruleset
Splittingtype	Pipeline Systembrand
IcDaily	None
IcDailyalternate	None
Condition	Scenario Edrcfield
Icproduct	None
Networkoper	Taxgroup Currency
Networkmodel	Rateplan Systembrand Networkoper Currency
Icproductgrp	Networkmodel
Icproductcnf	Icproduct Icproductgrp
Icproductrate	Icproduct Networkoper Rateplan Networkmodel
Noproduct	Networkoper Currency
Noproductcnf	ICproduct Timezone Noproduct Impactcat
Nosp	Mapgroup
Poi	None
Segment	None
Segratelnk	Rateplan Segment
Segzonelnk	Segment Zonemodel
Ciberocc	Networkoper
Socialnumber	None
Seqlogout	None
Dbversion	None
Iscript	None
Tam	None
Seqcheck	None
SeqlogIn	None
Duplicatecheck	None
Csstate	None
Destindesc	None
Classitem	None
Class	None
Queue	None
Changeset	None
Switch	Networkoper
Trunk	Switch Networkoper
Grouping	Scenario Edrcfield
Groupingcnf	Grouping Class
Classcon	GroupingCnf
Poiarealnk	Networkmodel Poi
Nobillrun	Networkoper Networkmodel
Aggregation	Scenario Edrcfield
Classlnk	Class Classitem
Classconlnk	Classitem Classcon
Dictionary	Queue
Trunkcnf	Trunk IcProductgrp Networkmodel Poi Switch Networkoper
Lergdata	Geoarealnk
Dscbalimpact	Discountstep
Cslock	Changeset
Csaudit	Changeset
Csreference	Changeset

Working with and Modifying the XSD

The XSD describes the structure of the XML document. The XML file you create must comply with the structure defined in the XSD.

The XSD defines the following items for an XML file:

The elements and attributes, their data types, and the default and fixed values for the elements and attributes.
Elements that are child elements, and the number and order of the child elements.
If an element can be empty or can include text.

You can also modify the XSD to provide default values for certain attributes such as status, and provide domain constraints to the attributes. For example, the status can be A=active, D=de-active, T=test, or S=simulation. If the status S is not valid, you can restrict the XSD to allow only the status A, D, or T.

Note:

The structure of the default XSD cannot be changed.

Before Loading Legacy Data

Before you begin to load the legacy data, you must do the following:

Install and configure the Pipeline Manager database.
Prepare the legacy data for mapping. See "Guidelines for Mapping Legacy Data".
Map the legacy data to Pipeline Manager database format. See "Mapping Legacy Data".

Configuring the Registry File for LoadIfwConfig Utility

The LoadIfwConfig.reg file provides database connection information to the LoadIfwConfig utility. You can edit this file manually, but it is also updated when you run the pin_setup utility.

The LoadIfwConfig.reg file is located in Pipeline_home/tools/XMLloader.

Most of the entries are standard connection entries, with these exceptions:

Use the LogFileName entry to specify the file where debug messages are written.

Important:
To record debug messages, you must use the verbose on command when you run the LoadIfwConfig utility.
Use the LoadDataFromDB entry to increase performance. Enabling this entry loads all the price list data from the database into memory, where the utility can access it faster.

Sample LoadIfwConfig.reg file:

LOADIFWCONFIG
{
DataPool
   {
      Database
      {
         ModuleName = DbInterface
         Module
        {
            DatabaseName = dduttadb 
            UserName     = PIN 
            PassWord     = 5A46BAEBC6C2C1C3A796C20A000E1E091066017D 
            AccessLib    = oci61
         }
      }
   }
XMLSchemaFile = Metadata.xml
LogFileName = LoadIfwConfig.log
LoadDataFromDB = False
}

Loading Legacy Data into the Pipeline Manager Database with LoadIfwConfig

To load legacy data into the Pipeline Manager database:

Go to Pipeline_home/bin.
Run LoadIfwConfig using one of the following commands:
- Non-interactive mode
```
LoadIfwConfig   [-i]  input_file   [-I] [-c]
```
- Interactive mode
```
LoadIfwConfig  [read input_file] [Insert] [commit]
```
  Important:
  The utility must be run from the directory where the XML file is located.

The LoadIfwConfig utility uploads the data from the XML file into the Pipeline Manager database and commits the data. If there is failure, the LoadIfwConfig utility rolls back the data and notifies you on your computer screen.

Deleting Data from the Pipeline Manager Database with LoadIfwConfig

To delete an object in a table, you must specify the primary field of the object in the input XML file. The object can be deleted only if the primary field has no dependent child objects.

To delete data from the Pipeline Manager database:

Go to Pipeline_home/bin.
Run LoadIfwConfig using one of the following commands:
- Non-interactive mode
```
LoadIfwConfig   [-i]  input_file   [-p] [-c] output_file
```
- Interactive mode
```
LoadIfwConfig  [read input_file] [delete] [commit] output_file
```
Only the dependent objects are deleted, and the deleted objects are backed up in the output file.

Deleting Data Sample XML File

Deleting data representation in the XML file

<?xml version="1.0" encoding="UTF-8"?>
<IFW xmlns="http://www.portal.com/tools/pricing" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.portal.com/tools/pricing IfwConfig.xsd">
<UOM UnitsOfMeasurement="hp1" Name="abc"/></IFW>

Updating Data with the LoadIfwConfig Utility

To update data from the Pipeline Manager database:

Go to Pipeline_home/bin.

Run LoadIfwConfig using one of the following commands:

Non-interactive mode

LoadIfwConfig   [-i]  input_file   [-p] output_file

Interactive mode

LoadIfwConfig  [read input_file] [update] [commit] output_file

Exporting Data from the Database with the LoadIfwConfig Utility

To export data in a table, you must specify the primary field of the object in the input XML file. The utility exports all dependent data. For example, if you specify to export rate plans, the utility exports rate plan versions, rate plan configurations, price models, impact categories, and time zones.

This sample shows an the contents of an XML file for exporting data:

<?xml version="1.0" encoding="UTF-8"?>
<IFW xmlns="http://www.portal.com/tools/pricing" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.portal.com/tools/pricing IfwConfig.xsd">
<UOM UnitsOfMeasurement="hp1" Name="abc"/></IFW>

To export data from the Pipeline Manager database:

Go to Pipeline_home/bin.

Run LoadIfwConfig using one of the following commands:

Non-interactive mode

LoadIfwConfig [-i]  input_file   [-f]  [-r]  [-o]  output_file

Interactive mode

LoadIfwConfig [read input_file] [fetch] [write] [commit] output_file

The exported objects are written in XML format.

Troubleshooting

Errors during data loading, deleting, and exporting are usually caused by the following:

The XML data does not comply with the XSD structure.
You are trying to load data into a table that does not exist.
You are trying to load data but the prerequisite data does not exist. See "About the Types of Objects to Migrate".
You are trying to delete data that has other data depending on it.