General Requirements

Data migration from other billing systems is carried out via making tables for an intermediate dataset in the CSV format.

• Encoding is UTF-8 without BOM
• Field separator is a semicolon
• Field values are enclosed in double quotation marks (including empty values as empty quotes (;"";)
• Field names for the table are entered in the first row
• Primary keys (ID fields) and external keys (*_ID fields) should be numeric
• Every table should be exported as a separate CSV file. The filename is equal to the table name and filename extension is csv.
• All of the exported files have to be packed into a ZIP archive. The archive may contain only the exported files in CSV format.

Example — file ACCOUNTS.csv:

"ID";"USER_ID";"ACC_NUM";"ACC_TYPE_ID";"CUR_ID";"BANK_ID";"BALANCE";"CREDIT";"CREDIT_END_DATE";"BALANCE_DATE"
"1001";"101";"123456";"1";"1";"";"926,07";"500";"";"23.09.2017 23:59:59"

Next, using a special tool, the CSV files are uploaded to the Hydra Billing for further reference data matching and the actual database migration.

Simplifications and Assumptions

1. Organizations and individuals are stored in the same table (USERS) and can be differentiated by a special feature (USERS.JUR field).
2. Only the name of an organization is shown from its address and bank details, the rest is to be input manually (if necessary, add custom fields with the details to the table).
3. The current amount of consumed services (traffic, minutes) does not migrate, but the end date of the current billing period does.
4. Service providing settings (recurrence, quotas, units, costs, etc.) do not migrate, they should be input manually and then matched.

Exporting From the Old Billing System

Matching Tables Obligatory for Exporting 

Listed in this section are the tables with the data which is not migrated, but is used for matching the values in the Migration application of the system being migrated against customer statuses in Hydra. 

The Customer Statuses Registry

The STATUS table 

In the simplest case, there are only two statuses in the registry (activated, deactivated). 

The Network Services Registry 

The NS_LIST table 

Note: this registry is created manually and contains a list of network services: RADIUS, Customer Self-Care Portal, telephony, email, etc. To access them, a customer requires a password and a login.

The Units Registry 

The UNITS_LIST table 

Note: the values are matched against the units registry in Hydra.

The Services and Price Plans Registry 

The SERVICES table 

Note: the values stored in this registry are not migrated, but they are matched against the Product catalog entries (TYPE = "Y" entries are to be matched with Everything → Price plans group items, and TYPE = "N" entries — with Everything → Services ones).

The Currency Registry 

The CURRENCY table

Note: the values are matched against the units registry in Hydra. 

The Types of Equipment Registry 

The EQUIP_TYPE table

Note: the values are matched against the product catalog entries, typically against those in the Active equipment section.

The Provider Equipment Registry

The OP_EQUIP table

Note: The table is used for correct bindings to provider equipment already exported into the system. When starting the migration section of the provider equipment the matching is carried out based on the exported data.

The Customer Groups Registry 

The GROUPS table

Note: the values are matched against the groups registry. If there are no groups in your system, it still makes sense to add such values in the registry, and bind all customers to groups depending on their categories, for example, Individuals, Organizations, Employees, etc. 

The Bank Registry 

The BANKS table 

Note: the values are matched against the bank registry. Consequently, the banks that come up while exporting should be added into Hydra manually.

The Account Types Registry 

The ACC_TYPES table 

Note: the values are matched against the account types registry. 

The Identification Document Types Registry 

The AUTH_DOC_TYPES table

Note: the values are matched against the identification document types registry

Optional Matching Tables

When setting up correct default control values, it is not obligatory to export tables from this section.

The Phone Number Types Registry 

The PHONE_TYPES table 

Note: the values are matched against the address types registry.

The Street Address Binding Types Registry 

The FACT_ADDR_BIND_TYPES table

Note: the values are matched against the address types registry. 

The Payment Types 

The PAYMENT_TYPE table

Note: the registry is used for differentiating payments of different types (adjustment payments, cash, bank transfer, JSIP, Visa, Webmoney, etc.) which is useful for creating reports.

The Multi-Subsidiary Structure

In case of a multi-subsidiary structure, you should use the subsidiaries registry to which the FIRM_ID fields in the USERS and OP_EQUIP tables are linked.

The FIRMS table

It is matched against the company structure.

Tables Containing Data for Migration

Use the data in the following tables when migrating to create entities of the necessary type. 

The Customers and Basic Subjects Registry 

The USERS table 

Note: the list of customer personal data attributes is extendable if necessary.

The Accounts Registry

The ACCOUNTS table

Grouping Customers

The GROUPLINK table

Note: if there are no groups in the system used for exporting, then at least you should divide all exported customers into three groups: organizations, individuals, and employees. I.e. you need to add these three groups to the GROUPS table and specify links to them (specify who to add to a group according to certain criteria when exporting)

The Customer Premises Equipment

The EQUIP table

Note: OP_EQUIP_ID, OP_EQUIP_PORT, OP_EQUIP_PORT_TYPE fields are used for specifying the CPE binding to provider’s equipment and are optional.

The Customer Subscriptions to Network Services and Applications Access Credentials

The NETSERV table

Note: the login and passwords for VPN, Customer Self-Care Portal, SIP account and other resources controlled by the Billing are exported into this table. For security reasons, during test exporting, passwords can be substituted with asterisks or random rows. Authentic passwords are to be exported only once, during the final migration.

The Contracts

The CONTRACTS table

Subscribing Customers to Services

The SUBSCR table

Note: services and the price plan are exported for the next period. If you have a fixed charging day bound to the 1st day of a month, then export as PAY_DAY = 1 (typically used by organizations).

The Payments

The PAYMENTS table

The Chargings

The WRITEOFFS table

The amounts in the WRITEOFFS table should not be negative - only positive or 0. This table is used for exporting chargings over previous charging periods (executed charge logs) and details on current billing period of customers (charge logs in force).

Additional Tables

The Multiple Comments

Loading multiple comments to customers and CPE is supported.

The COMMENT_TYPES table

It is matched against the Comment Types Registry

The USER_COMMENTS table

The EQUIP_COMMENTS table

Addresses

During the migration process some of the fields are decomposed, and the data from them is recorded into additional tables. Particularly, an address in USERS.ADDR undergoes parsing and is then recorded in the PARSED_USER_ADDRESSES table. Thus, if the billing that undergoes data exporting stores decomposed information on the address it is not necessary to write it in one row as you can fill in the table instead.

Parsed addresses for basic subjects

The PARSED_USER_ADDRESSES table

Parsed CPE addresses

The PARSED_EQUIP_ADDRESSES table 

Parsed phone numbers of basic subjects

The USER_PHONES table

Matching exported customers against those in Hydra

During the latter migration it may be useful to load the customer contents from the exported data into the contents of the already existing customer in Hydra. To do so you should fill in the USER_MAPPINGS table.

The USER_MAPPINGS table

Division employees

Oftentimes, the number of employees is quite big to be entered manually. You need to do it by adding employees as customers into the USERS table, then granting the permission to access applications with the help of NETSERV (similarly to granting them access to the Customer Self-Care Portal) and filling in the following tables.

The DEPARTMENTS table

The EMPLOYEE_TYPES table

The EMPLOYEES table