23 Mapping Legacy Data to the BRM Database

This chapter explains, in general terms, what you need to understand about your legacy data to successfully migrate it to an Oracle Communications Billing and Revenue Management (BRM) database.

Note:

Describing your legacy data in detail is beyond the scope of this chapter because each system is unique.

About Creating XML Files

To migrate data, you create XML files that contain the data from your legacy database. You then migrate the data to the BRM database by using the pin_cmt utility.

Sample XML files for each type of data are available in BRM_home/apps/cmt/sample_data. BRM_home is the directory in which BRM is installed.

About the XSD Files

Conversion Manager includes two sets of XSD files:

A set of conceptual files that you can use for mapping legacy data to BRM objects. These files are easier to read than the set of files used for converting data.
A set of physical files that are used by Conversion Manager when converting data. These files are not as easy to read, but they are written in a way that optimizes performance.

The difference between the types of files is how the XML tags are named. In the conceptual files, the XML tags use the same field names as the BRM object fields.

For example:

In the conceptual files:

PIN_FLD_ACCOUNT_NO = <FldAccountNo>
In the physical files:

PIN_FLD_ACCOUNT_NO = <ActNo>

To set up mapping, you can use the easily-readable conceptual files to determine how to map data. Then you can edit the physical files, after you know how the data is mapped.

The conceptual files are in BRM_home/apps/cmt/schema_files/conceptual.

The physical files are in BRM_home/apps/cmt/schema_files/physical.

Use the following physical XSD files to create your XML files:

Use the CMT_Subscriber.xsd file for subscriber data.
Use the CMT_Balances.xsd file for balance data.
Tips:
- Before you migrate data, validate the XML files with the physical XSD files.
- Do not use spaces while generating the XML files. Use tabs (\t) and new lines (\n).

About the Types of Data to Convert

Table 23-1 shows the types of legacy data that can be loaded into the BRM database:

Table 23-1 Legacy Data Storable in BRM Database

Type of Data	Description
Account data	Subscriber data such as name, address, profile, current account balances, group charge sharing, and account hierarchy.
Product, discounts, and deals	Deals purchased by the customer and associated with the account.
Bill	Bill information such as billing cycle and balances from the legacy billing system.
Services	Data pertaining to services owned by the account, such as a wireless data service. For example, when converting data for a wireless data service, the following data is also converted: Device SIM Device number

Tables Affected by the Conversion Process

Table 23-2 shows the BRM tables that are affected by the conversion process. For more information on the BRM tables and the fields in each table, see the following:

"Storable Class-to-SQL Mapping" in BRM Developer's Reference
"Storable Class Definitions" in BRM Developer's Reference

Note:
The database tables are created during BRM installation.

Table 23-2 Tables Affected by the Conversion Process

Table Name	Description
ACCOUNT_T	Master account table that represents billable accounts in BRM. Only one row is added to this table for each account in BRM.
ACCOUNT_EXEMPTIONS_T	Contains an entry for each tax exemption that applies to an account.
ACCOUNT_NAMEINFO_T	Contains name and address information for accounts. The table contains one row for each name and address type (home address, work address, mailing address, and so forth). The table must contain at least one row for each row in ACCOUNT_T. For example, you can have billing name and address information, technical contact name and address information, and sales name and address information.
ACCOUNT_PHONES_T	Contains a phone number and a phone type for each account. The table contains one row for each phone type (up to seven types are defined, including home, work, fax, and pager). If an account does not contain a phone number, the table does not contain a row for the account.
BALANCE_GROUP_T	Stores the balance information such as dollars, free minutes, bytes, and frequent flyer miles for various resources in an account. A balance group includes one or more sub-balances for each resource. The sub-balance contains the current amount, resource type, validity dates for the resource, rollover data, and sub-balance contributors.
BAL_GRP_BALS_T	Stores balance group data.
BAL_GRP_SUB_BALS_T	Stores sub-balance data.
BILL_T	Includes billing information, such as the amount due, amount adjusted, currency, and bill number. A /bill object is created at the end of a billing cycle. A /bill object is created for every account. The account receivable for a bill is stored in the /item objects that point to the balance groups associated with the bill. The /bill object points to the /account object, the account's /billinfo object, and the /invoice object.
BILLINFO_T	Stores all billing, payment method, accounting cycle, and hierarchy information necessary to bill an account. A /billinfo object is created for every account. If the bill unit is subordinate, the /billinfo object points to the parent account and the parent account's /billinfo object.
DEVICE_T	Stores information about devices. The table contains a separate /device object for every device managed by BRM. Generic data applicable to all devices is stored in the parent /device object. Subclasses such as /device/num store information specific to a particular device type.
DEVICE_NUM_T	Stores device information specific to phone numbers managed by Number Manager.
DEVICE_SERVICES_T	Stores device/service mapping data.
DEVICE_SIM_T	Stores device information specific to SIM cards managed by SIM Manager.
EVENT_T	Contains a row for each account that has a nonzero balance forward amount. This table stores data for every event that occurs for an account or service. In the case of conversion of an account that has a balance, you should add a row to reflect the posting of that balance. You also need to write an entry to this table for each entry you write to the EVENT_BILLING_DEAL_INFO_T and EVENT_BILLING_PRODUCT_ACTION_T tables. In addition, you can optionally add rows to this table to store past activity for an account. For example, use this table to add rows for importing past billing and payment history into BRM. These rows can be brought over as memo type events, which can be viewed in Customer Center, but they do not affect the current account balance (which should be calculated and posted separately).
EVENT_BAL_IMPACTS_T	Stores event data.
EVENT_ESSENTIALS_T	Stores event data.
GROUP_T	Represents collections of other storable objects that have an assigned set of shared attributes. This table is optional. If you use it at conversion time, one row must be added to this table if the account is a parent or sponsoring account.
GROUP_BILLING_MEMBERS_T	Rows for this table are optional and are included only for child accounts (that have a parent account) or for accounts that are members of a brand. During conversion, one row needs to be added to this table for each child account (regardless of the child account's payment method).
GROUP_PERMITTEDS_T	Indicates which accounts are group accounts. Rows for this table are optional and are included only for parent accounts that have at least one child account.
GROUP_SHARING_MEMBERS_T	Stores members of a resource sharing group.
GROUP_SHARING_CHARGES_T	Stores charges of a resource sharing group.
GROUP_SHARING_DISCOUNTS_T	Stores discounts of a charge sharing group.
GROUP_SHARING_PROFILES_T	Stores the profile data that is shared in a profile sharing group.
ITEM_T	Created to bundle events, this table summarizes billable item activity by type. Rows in this table are added for each row in the BILL_T table. In a conversion scenario, only one type of ITEM_T entry is important, the /usage item. Other items like the cycle forward item, payment item, dispute item, and so on are normally not pertinent for conversion. For conversion, a default /item/misc is created to bundle events. Also, a service-level item is created during conversion only if the recreate ='/item/<item-type>' is specified in the input XML data.
JOURNAL_T	Stores general ledger (G/L) journal data.
ORDERED_GROUPS_T	Stores ordered balance group data.
ORDERED_BALGROUP_T	Stores ordered balance group data.
PAYINFO_T	Stores generic payment method information for an account. Only one row is added in this table for each row in ACCOUNT_T. For each row in PAYINFO_T, you must add a row to the applicable payment method table. For example, add a row to PAYINFO_INV_T if the payment method for an account is Invoice. Additionally, if you have created a custom payment method and a PAYINFO_paymentMethod_T table, where paymentMethod is the custom payment method, you must add a row to that table for each account with that payment method.
PAYINFO_CC_T	Contains a row for each account that has a payment method of Credit Card. This row is in addition to a row in PAYINFO_T. Note: This payment method assumes that you are using the BRM-provided FirstUSAPaymentech or FDC modules to process your credit cards or that the clearing house or bank you use can process the same information. If your credit card processing is significantly different, you might want to set it up as another payment method and store information on that payment method separately.
PAYINFO_DD_T	Contains a row for each account that has a payment method of Direct Debit. This row is in addition to a row in PAYINFO_T.
PAYINFO_INV_T	Contains a row for each account that has a payment method of Invoice. Optionally, the table can also contain purchase order (PO) information.
PROFILE_T	Contains profile information, if any. This table can be left empty if you decide not to convert profile information. You can populate only one row in this table for each row in ACCOUNT_T.
PROFILE_customTable_T	Rows are added to this table only if rows are added to PROFILE_T, where customTable is the unique identifier you choose; for example, company name. For each row added to PROFILE_T, a corresponding row is added to PROFILE_customTable_T.
PROFILE_ACCT_EXTRATING_DATA_T	Contains profile information.
PROFILE_SERV_EXTRATING_T	Contains profile information.
PROFILE_SERV_EXTRATING_DATA_T	Contains profile information.
PURCHASED_DISCOUNT_T	Contains an entry for each discount owned by an account at the time of conversion.
PURCHASED_PRODUCT_T	Contains an entry for each product owned by an account at the time of conversion.
SERVICE_T	Stores generic service type information for accounts. The table contains one row for each applicable service for each entry in ACCOUNT_T. In addition to a row in this table, a row must be created in the service type table, such as IP service or email.
SERVICE_ALIAS_LIST_T	Stores service aliases, used to identify customers by phone number.
SERVICE_TELCO_T	Stores telco service data.
SERVICE_TELCO_FEATURES_T	Stores telco service data.
SERVICE_TELCO_GSM_T	Stores telco service data.
UNIQUENESS_T	Stores data that enforces uniqueness of logins across different database schemas in a multischema environment.

Audit Tables Affected by the Conversion Process

The following audit tables are affected by the conversion process:

AU_ACCOUNT_T
AU_BAL_GRP_T
AU_GROUP_SHARING_CHARGES_T
AU_GROUP_SHARING_DISCOUNTS_T
AU_GROUP_SHARING_PROFILES_T
AU_GROUP_T
AU_ORDERED_BALGROUP_T
AU_ORDERED_GROUPS_T
AU_PROFILE_ACCT_EXTRATING_DATA_T
AU_PROFILE_SERV_EXTRATING_DATA_T
AU_PROFILE_SERV_EXTRATING_T
AU_PROFILE_T
AU_PURCHASED_DISCOUNT_T
AU_PURCHASED_PRODUCT_T
AU_SERVICE_ALIAS_T
AU_SERVICE_T
AU_UNIQUENESS_T

To configure Conversion Manager to load legacy data into other audit tables:

Go to the BRM_home/apps/cmt/ctl_files directory.
Copy the control file for the source table (TableName) to the BRM_home/apps/cmt/ctl_files/audit directory:
```
cp TableName.ctl BRM_home/apps/cmt/ctl_files/audit/TableName.ctl
```
where TableName is the name of the source table associated with the audit table. For example, to create a control file for the AU_ACCOUNT_NAMEINFO_T audit table, copy the control file for the ACCOUNT_NAMEINFO_T table to the BRM_home/apps/cmt/ctl_files/audit directory.
Open the BRM_home/apps/cmt/ctl_files/audit/TableName.ctl file in a text editor.

Change all instances of TableName to AU_TableName.

For example, change these lines:

LOAD DATA
APPEND
INTO TABLE ACCOUNT_NAMEINFO_T

to the following:

LOAD DATA 
APPEND 
INTO TABLE AU_ACCOUNT_NAMEINFO_T

Save and close the file.
Open the BRM_home/apps/cmt/ctl_files/Infranet.properties file in a text editor.

Add TableName to the infranet.cmt.auditloaders line:

infranet.cmt.auditloaders = account_t; bal_grp_t; group_sharing_charges_t;...; TableName

For example, to add the control file for the AU_ACCOUNT_NAMEINFO_T table:

infranet.cmt.auditloaders = account_t; bal_grp_t; group_sharing_charges_t;...; account_nameinfo_t;

Save and close the file.