Bookshelf Home | Contents | Index | PDF     

Administration Guide for Oracle Self-Service E-Billing > Running the ETL Jobs Using Oracle Workflow Manager >

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 Self-Service E-Billing prevents you from running an EBILL_DATALOAD job.

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 76 shows the Oracle Self-Service E-Billing database tables where data is inserted or updated whenever you move data to a new partition.

Table 76. 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_DIM

Updates the group key.

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 ETL properties file during installation. For details, see Installation Guide for Oracle Self-Service E-Billing.
    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 Self-Service E-Billing Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices.