This chapter describes how to load data from legacy billing systems into Oracle Communications Billing and Revenue Management (BRM) table partitions.
Before you read this chapter, you should be familiar with the following:
Partitioning. See "Partitioning Tables" in BRM System Administrator's Guide.
Conversion Manager. See "Understanding Conversion Manager".
To load legacy data into BRM table partitions, do the following:
Create back-dated partitions for storing legacy data. See "About Your Partitioning Scheme".
Configure Conversion Manager to encode a creation timestamp into specified object POIDs. See "About Making Conversion Manager Aware of Partitions".
Some BRM tables, such as the BILL_T table, store large amounts of data, which can slow BRM search times. Dividing large tables into smaller, more manageable chunks, called partitions, makes it easier for BRM to find data because it needs to search only a single partition for data rather than an entire table.
In BRM, you create table partitions based on dates and divided by a specified interval: month, week, or day. For example, you could set one partition for May 1 to May 31, the subsequent one for June 1 to June 30, and so on. Objects are stored in partitions according to their creation date and time. For example, /event objects created in May 2009 could be stored in the May 2009 partition of the EVENT_T table.
Before you begin loading legacy data into BRM, you must determine your partitioning scheme, including which tables to partition and how many months of legacy data to load into BRM. For example, you might decide to load only the last five months of your billing data into the BRM system. In this case, you would create five back-dated partitions with a monthly interval for the BILL_T table.
After determining your partitioning scheme, you must create the following:
Back-dated table partitions for your legacy data. For example, if you are loading legacy billing and item data into BRM, create back-dated table partitions for the /bill storable class (BILL_T table) and the /item storable class (ITEM_T table). Your back-dated tables must also comply with the following restrictions:
The oldest back-dated partition that you create must have a start date prior to the creation date of the oldest legacy object you are loading into BRM. For example, if the oldest object that you are loading has a creation date of March 8, 2005, you must create a back-dated table partition that starts on or before March 7, 2005.
The most recent back-dated partition that you create must have an end date that is at least one day prior to the BRM installation date. For example, if you install BRM on August 15, 2009, the most recent back-dated partition must end on or before August 14, 2009.
Future-dated partitions for data created on your new BRM system. Create future-dated partitions for any storable classes you would like partitioned. The first future-dated partition must start two days after the BRM installation date. For example, if you install BRM on August 15, 2009, the first partition's start date must be August 17, 2009, or later.
For example, assume you install BRM on August 15, 2009, and the oldest billing object in your legacy system has a creation date of January 20, 2009. In this example, you would create seven back-dated partitions and one or more future-dated partitions for your /bill storable class (BILL_T table) as shown in Table 26-1:
Table 26-1 Partitioning Scheme Example
| Partition | Date Range for Each BILL_T Partition |
|---|---|
|
1 |
January 15, 2009, through February 14, 2009 |
|
2 |
February 15, 2009, through March 14, 2009 |
|
3 |
March 15, 2009, through April 14, 2009 |
|
4 |
April 15, 2009, through May 14, 2009 |
|
5 |
May 15, 2009, through June 14, 2009 |
|
6 |
June 15, 2009, through July 14, 2009 |
|
7 |
July 15, 2009, through August 14, 2009 |
|
8 |
August 17, 2009, through September 16, 2009 |
|
9 |
September 17, 2009, through October 16, 2009 |
|
10 |
partition_last |
In this example, objects are loaded into partitions as shown below:
Objects with creation timestamps prior to February 15, 2009, are automatically loaded into Partition 1.
Objects with creation timestamps on or after February 15, 2009, and prior to March 15, 2009, are loaded into Partition 2.
Objects with creation timestamps after August 14, 2009, and prior to August 17, 2009, are loaded into Partition 8.
Note:
If an object's creation timestamp is not covered by one of the partition ranges, the object is automatically loaded into the next higher-range partition.Objects with creation timestamps after October 16, 2009, are loaded into partition 10 (partition_last).
Note:
If an object's creation timestamp is not covered by one of the partition ranges and a higher-range partition does not exist, the object is automatically loaded into partition_last.Conversion Manager can now encode timestamps into the object POIDs of partitioned and purgeable storable classes, allowing the object to be loaded into the correct table partition. For example, if a legacy bill object was created in May, Conversion Manager encodes the creation timestamp into the /bill object's POID. The /bill object can then be loaded into the BILL_T table's May 2009 partition.
The timestamp encoded in object POIDs can be either:
The creation timestamp passed in by the legacy system. In this case, you must pass the object creation timestamp in the CrtT element of the input XML file.
The system time when you run Conversion Manager. It does not use the timestamp from pin_virtual_time.
You must make sure that the creation time is applied consistently to all related objects. For example:
When you migrate unpaid bills and open items from your legacy billing system to BRM, the bill objects and their associated item objects must all use the legacy object's creation time. Otherwise, the billing and invoicing utilities will not find all items associated with a bill.
When you migrate balances from your legacy system to BRM, the account objects and their associated balances must all use the legacy object's creation time.
When Conversion Manager is configured to encode timestamps into object POIDs, it performs these additional tasks:
Determines which storable classes are partitioned and purgeable by reading the BRM data dictionary. In the data dictionary, partitioned and purgeable storable classes have their IS_PARTITIONED field set to 1 and their PARTITION_MODE field set to Finite.
Reserves a set of POIDs for each partitioned, purgeable storable class and a single set of POIDS for all nonpartitioned storable classes. For example, if the /bill and /item storable classes are partitioned and purgeable, Conversion Manager reserves three sets of POIDs: one for /bill objects, one for /item objects, and one for objects of all nonpartitioned storable classes.
Assigns POIDs to objects based on the storable class type:
If the storable class is partitioned and purgeable, Conversion Manager assigns a POID from the storable class's reserve of partitioned POIDs and encodes the timestamp. It uses either the timestamp passed in from the legacy system or the system time when you ran Conversion Manager.
If the storable class is not partitioned, Conversion Manager assigns a POID from the reserve of nonpartitioned POIDs.
Note:
If the set of reserved POIDs is already depleted, Conversion Manager reserves a new set of POIDs before assigning one to an object.The object is then loaded into a partition according to the storable class type and the timestamp encoded in the object POID.
During the loading phase, if no corresponding partition is available for the timestamp encoded in the object POID, the object is loaded into the next higher-range partition. If no higher-range partition is available, the object is loaded into partition_last.
If a storable class is not partitioned, the object is loaded directly into the target table.
To configure Conversion Manager to encode timestamps in object POIDs, you specify the following in the Conversion Manager Infranet.properties file:
The number of POIDs to reserve at a time.
Which timestamp to encode into POIDs.
For more information, see "Configuring Conversion Manager for Partitioning".
To set up your system to load data from legacy billing systems into BRM table partitions:
Make sure partitioning is enabled in your BRM system by doing either or both of the following:
When installing BRM, choose to partition your tables.
After installing BRM, convert nonpartitioned storable classes to partitioned storable classes.
For more information, see "Partitioning Tables" in BRM System Administrator's Guide.
Determine the oldest object for each storable class that will be partitioned in the BRM database. The oldest partition's start date must be prior to the oldest object's creation date for that storable class.
Create partitions with the partition_utils utility. See "Creating Partitions for Your Legacy Data".
Configure Conversion Manager for partitioning. See "Configuring Conversion Manager for Partitioning".
Include the legacy object's creation timestamp in the Conversion Manager input XML file. See "Passing Object Creation Timestamps in the Input XML File".
To create back-dated partitions, enter the following command:
partition_utils -o add -t realtime -s start_date -u month|week|day -q quantity [-c storable_class] [-w width] -b
where:
start_date specifies the starting date for the first partition. The format is MMDDYYYY. The start date must be prior to the BRM installation date. The oldest partition's start date must occur prior to the oldest object's creation date.
quantity specifies the number of partitions to add.
storable_class specifies the storable class to store in the partition. The default is /event.
width specifies the number of units in a partition (for example, 3).
You must also create future-dated partitions for objects generated by your new BRM system. For more information on how to create future-dated partitions, see "Partitioning Tables" in BRM System Administrator's Guide.
By default, Conversion Manager does not encode timestamps in object POIDs. You configure Conversion Manager to do so through its Infranet.properties file.
To configure Conversion Manager for partitioning:
Open the BRM_Home/apps/CMT/Infranet.properties file in a text editor. BRM_Home is the directory in which you installed the BRM software.
Set the entries for creating partitioning-aware object POIDs:
Use infranet.cmt.timestampvalidation to specify which timestamp to encode in generated POIDs. See "Configuring which Timestamp to Encode".
Use infranet.cmt.noofrecords to specify the number of POIDs to reserve for partitioned POIDs. See "Configuring the Number of POIDs to Reserve".
Save and close the file.
The timestamp encoded in object POIDs can be either the object creation time passed in from the legacy system or the system time when you run pin_cmt.
Important:
If you specify to use the system time, Conversion Manager loads all of the legacy data into one partition. You must configure and size the appropriate partition to store the volume of data that will be loaded into it.You specify which timestamp to encode by using the following Infranet.properties file entry:
infranet.cmt.timestampvalidation = Value
where Value is one of the following:
0: The timestamp is set to the creation time passed in the input XML file. If a creation time is not passed in, Conversion Manager assigns the system time when you run pin_cmt.
1: The timestamp is set to the creation time passed in the XML file only. If a creation time is not passed in, Conversion Manager generates an error. This setting ensures that all partitioned classes include the original creation timestamp. This is the default.
2: The timestamp is set to the system time when you run pin_cmt. If a creation timestamp is passed in, Conversion Manager generates an error. This ensures that all partitioned classes are loaded with the system time.
Caution:
This parameter cannot enforce validations across multiple runs of Conversion Manager. Loading some objects with the system time and other objects with the object creation time could cause system inconsistencies and business operations to fail.Conversion Manager reserves a set of POIDs for each storable class type that is partitioned and reserves another set of POIDs for all other nonpartitioned storable classes.
To configure the number of POIDs to reserve:
For partitioned storable classes, use the following entry:
infranet.cmt.noofrecords = Number
where Number specifies the number of POIDs to reserve. The default is 1.
Conversion Manager reserves the specified number of POIDs for each storable class that is partitioned.
For nonpartitioned storable classes, use the following entries:
infranet.cmt.noofrecords = Number infranet.cmt.avgnoofservices = Services infranet.cmt.avgnoofdevices = Devices
where:
Number specifies the number of POIDs to reserve. The default is 1.
Services specifies the average number of services in your accounts. The default is 5.
Devices specifies the average number of devices in your accounts. The default is 2.
Conversion Manager multiplies these three values (Number * Services * Devices) to compute the total number of POIDs to reserve for nonpartitioned storable classes.
For example, assume you partition the /bill and /item storable classes and the Infranet.properties file includes the following entries:
infranet.cmt.noofrecords = 1000 infranet.cmt.avgnoofservices = 5 infranet.cmt.avgnoofdevices = 3
In this example, Conversion Manager reserves the following POIDs:
1,000 POIDs for /bill objects.
1,000 POIDs for /item objects.
15,000 POIDs for all other objects.
The Conversion Manager input XML file includes a CrtT element for all storable classes. If you want to encode the legacy object's creation time in your POIDs, you must pass the object's creation time in the CrtT element of the input XML file.
When passing the legacy object's creation time, make sure:
The object creation timestamp is correct. Conversion Manager does not perform any validations on values passed in the CrtT element.
Objects using the legacy creation time are not combined in the same partitioned, purgeable table as objects using the system time.