Bookshelf Home | Contents | Index | PDF     

Administration Guide for Oracle Billing Insight > Database Partitioning >

Moving Data Between Partitions

You can redistribute data between existing partitions, or groups, such as when a particular partition becomes over-crowded or to provide certain companies with their own dedicated partition.

It is recommended to move data between partitions during off-hours or times of low application usage. While a move function is in process, Oracle Billing Insight prevents you from running an billing data load process.

Do not mix line-of-business and billing systems during a move.

Once you complete a successful move, you cannot move a company or accounts back to the original group.

Table 77 shows the Oracle Billing Insight database tables where data is inserted or updated whenever you move data to a new partition.

Table 77. Database Tables Modified by Moving Data Between Partitions
Schema
Table
Description

OLAP

EDX_RPT_TOP_100_LONG_CALL

Inserts to the work-in-process (WIP) table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_TOP_100_EXPENSIVE_
CALL

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_STATEMENT_PAYMENT_
FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_STATEMENT_FACT

Updates the group key and ETL key with new values.

OLAP

EDX_RPT_STATEMENT_ADJUST_
FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_SERVICE_USAGE_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_SERVICE_TARIFF_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_SERVICE_PRODUCT_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_SERVICE_MISC_FACT

This table is used in the Utilities application.

OLAP

EDX_RPT_SERVICE_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_SERVICE_DETAIL_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_SERVICE_CHARGE_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_HIERARCHY_XREF_DIM

Updates the group key.

OLAP

EDX_RPT_HIERARCHY_TREE_DIM

Updates the group key.

OLAP

EDX_RPT_HIERARCHY_NODE_
PERIOD

Inserts to the WIP table and then inserts to the production table.

OLAP

EDX_RPT_ETL_LOAD_FACT

Inserts a new record to log the move by period and new group key.

OLAP

EDX_RPT_CONSUM_FACT

Inserts to the WIP table and then inserts to the production table. This table is used in the Utilities application only.

OLAP

EDX_RPT_AVG_BILLED_SUMM

Inserts to the WIP table and then inserts to the production table. This table is used in the Utilities application only.

OLAP

EDX_RPT_ACCOUNT_WSPACE

Inserts to the WIP table and then inserts to the production table.

OLAP

EDX_RPT_ACCOUNT_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLAP

EDX_RPT_ACCOUNT_CHARGE_FACT

Inserts to the WIP table and then performs Partition Exchange Loading to the production table.

OLTP

ETL_GROUP_SOURCE

Inserts a new record to log the move by the new ETL key, load datetime.

OLTP

EDX_OMF_SERVICEAGREEMENT

Updates the group key.

OLTP

EDX_HIER_NODE_PERIOD

Insert the group and ETL keys with new values.

OLTP

EDX_HIER_HNODE

Updates the group key.

OLTP

EDX_HIER_HIERARCHY

Updates the group key.

To move data for one company at a time to a new partition group (Business Edition)

  1. Verify the following information:
    • The B2B data exists and is in a published state.
    • The new group exists and there is an entry in the EDX_RPT_PARTN_MGMT table for the partition group.
    • The current partition group the company is in has an entry in the mapping table EDX_RPT_PARTN_COMP_MAP.
  2. Using a SQL tool such as Toad, Oracle SQL Developer, or SQL*Plus, execute the function fn_GroupMove_Company, located in the pkg_olap_group_mgmt package. Specify the following input parameters with the function:
    • p_new_group_key. The key of the group you are moving the data to.
    • p_old_group_key. The key of the group you are moving the data from.
    • p_company_cd. The code of the company whose data you want to move.

To move data for one or more accounts to a new partition group (Consumer Edition)

  1. Verify the following information:
    • The B2C data exists and is in a published state.
    • The new group exists and there is an entry in the EDX_RPT_PARTN_MGMT table for the group.
    • The current groups the accounts are in each have an entry in the mapping table EDX_RPT_PARTN_ACCT_MAP.
  2. Create an input file listing the account numbers you want to move. Use the following format for the data file, using pipe delimiters (|). Place the input file in the same folder you configured for the GRP_MOVE_INDIR function in the BillingInsight_etl.properties file during installation. For details, see Installation Guide for Oracle Billing Insight.
    Field Name
    Position
    Type

    Biller ID

    COL1

    VARCHAR2(20)

    Account Number

    COL2

    VARCHAR2(255)

    From Group Key

    COL3

    NUMBER(7)

    To Group Key

    COL4

    NUMBER(7)
  3. Using a SQL tool such as Toad, Oracle SQL Developer, or SQL*Plus, execute the function fn_GroupMove_Account located in the pkg_olap_group_mgmt package. Specify the following input parameters with the function:
    • p_file_name. The name of the input file with the account numbers to move.
    • p_in_location. The directory path where the file to be moved is currently located.
    • p_out_location. The directory path where the file will be moved to for processing.
    • p_new_group_key. The new group to move the data to.
    • p_old_group_key. The group the data is to move from.
    
Administration Guide for Oracle Billing Insight Copyright © 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices.