Skip Headers
Oracle® Communications Data Model Reference
Release 11.3.2

E28440-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

C Oracle Communications Data Model Assumptions

This chapter describes the basic concepts and assumptions (or business rules) in the Oracle Communications Data Model.

This chapter is organized by subject area in the typical way you might feed data into Oracle Communications Data Model.

Oracle Communications Data Model is aligned with TM Forum's Information Framework (SID) Release 12. The TM Forum provides business-critical industry standards and expertise to enable the creation, delivery, and monetization of digital services. For more information on TM Forum, see

http://www.tmforum.org/

Most entity names conform to the TeleManagement Forum Shared Information Data (SID) model Business entities, to ease the mapping when dealing with TM Forum SID certified applications. In nearly all cases, the definitions exactly fit. Hence, several notions in the following can be reviewed and deepened by looking into the TM Forum SID documentation with their examples. From a content perspective however, they usually have more attributes specific to Oracle Communications Data Model.

Understanding General Entities

General entities shown in Table C-1 should be filled upfront either manually (standard Geography hierarchy, Organization Business Units) or automatically (calendars and time of day, geography through Address Location or external data, Standard Organization Business hierarchy through Human Resource (HR) or Enterprise Resource Planing (ERP) system as input).

Table C-1 General Entities

Model Area Description

Business Unit Code and Business Unit Type Code

These attributes are leveraged for the ORGANIZATION flexible Hierarchy, related to the Organization subject area. Please do not remove and do not mix with ORGANIZATION BUSINESS UNIT CODE (or key).

CALENDAR

Calendars should be filled for 20 years starting with the "smallest" day for which Usage data (in aggregation) will be available (not customer data) and furthest day in the future.

GEOGRAPHY

The default geography of the country, for example, WORLD, REGION, COUNTRY, CITY should be defined upfront whenever possible. Create search functions to simplify the address validation (whether or not add a new CITY or COUNTY row when getting a new address). Addresses should be validated upfront in the source system or at the staging layer. Addresses are supposed to be correct and validated when in Oracle Communications Data Model.

TIME

Please fill the second, minutes, quarter of hours and hours.

TIME SLOT

By default, TIME SLOT should correspond exactly to quarter of hours. You may extend this notion to a bigger time period (for customization), leveraging a time band.


Understanding Product Offering and Specification Entities

Table C-2 Product Offering and Specifications Entities

Entity Description

PRODUCT OFFERING

Represents what is externally presented to the market for the market's use. A PRODUCT OFFERING can be assembled from a reusable PRODUCT SPECIFICATION (sometimes referred to as "product spec"). PRODUCT OFFERING is physically associated with DWR_PROD_OFR. Typical abbreviation for PRODUCT OFFERING is "Prod Offer" or "Product Offer" or "Prod Offering".

PRODUCT SPECIFICATION

Represent a tangible object or something intangible provided to customers, realized as a 'service' but not to be confused with a network service (TMF SID). Basically, a PRODUCT SPECIFICATION describes the characteristics of what the customer will be able to do once he purchases (the PRODUCT OFFERING is the market vision, while the PRODUCT SPECIFICATION is the effective Component description, from the perspective of a service provider, that are leveraged to compose the PRODUCT OFFERING). It is physically associated with DWR_PROD_SPECIt is physically associated with DWR_PROD_SPEC.

PRODUCT SPECIFICATION VERSION

A particular form or variety of a PRODUCT SPECIFICATION that is different from others or from the original. The form represents differences in properties that characterize a PRODUCT SPECIFICATION, that are not significant enough for creating a new PRODUCT SPECIFICATION.

PRODUCT

A PRODUCT SUBSCRIPTION represents an instance of a PRODUCT SPECIFICATION, typically available for sales or sold (before or after being subscribed to).

A Product is an Instance of a PRODUCT SPECIFICATION associated with a PRODUCT OFFERING. In Oracle Communications Data Model, it would typically correspond to some hardware (prepaid card, cell phone, and so on) on the shelf (somewhere) with a specific Serial Number ready to be purchased and used. A PRODUCT SUBSCRIPTION would be the realization of a PRODUCT SPECIFICATION when a Service is associated with the PRODUCT SPECIFICATION (for example a GSM service). PRODUCT corresponds to DWR_PROD).

PRODUCT SUBSCRIPTION

Represents the subscription to a PRODUCT OFFERING by a PARTY playing a PARTY ROLE, such as a Customer. A PRODUCT SUBSCRIPTION is an instance of a PRODUCT SPECIFICATION associated with an ACCOUNT, and optionally (usually) with a service and an access method (same definition as PRODUCT otherwise).

PRODUCT SUBSCRIPTION allows each part of a PRODUCT OFFERING (typically based on COMPOSITE PRODUCT SPECIFICATION) to be realized and instantiated separately, "atomic" product specification would be used to describe "trouble ticket" or "product subscription change", by atomic PRODUCT SPECIFICATION to allow actions such as trouble ticket or product subscription change against specific ones (and not just the composite Spec). This is also necessary if a PRODUCT OFFERING has a tariff (PRODUCT OFFERING PRICE) depending on the flexible characteristic associated to a PRODUCT SPECIFICATION.

In Oracle Communications Data Model the PRODUCT SUBSCRIPTION will not carry the AGREEMENT nor its agreement details (AGREEMENT ITEM). AGREEMENT ITEM holds the Product subscription Key. Through AGREEMENT ITEM, one can find out the Agreement Key. As a consequence, a change in PRODUCT SUBSCRIPTION may imply a change in AGREEMENT ITEM (typically for postpaid but not only) but not in AGREEMENT.

The attributes "Product Subscription Status code" and "Status code" should always be filled and equal.

PRODUCT LINE

A method used to group related PRODUCT SPECIFICATIONs marketed by the same company that differ only in size or style. This strict definition (TMF SID) is in fact open for any definition that fits the Communications Service Provider (CSP).

PRODUCT SPECIFICATION CATEGORY

A method used to group a set of PRODUCT SPECIFICATIONs that are classified together because of common characteristics. This strict definition (TMF SID) is in fact open for any definition that fits the CSP.

Plan Type

The attribute Plan Type in PRODUCT OFFERING corresponds to either "Prepaid" or "Postpaid". It is used in the PRODUCT OFFERING Hierarchy. Possible extensions are "Hybrid" but should be carefully looked at, especially with respect to the implications on some critical intra-ETLs such as COUNT DAY DRVD, REVENUE DAY DRVD and others.

SKU ITEM

SKU ITEM tracks physical inventory. Hence, there must be one row (that is one key) per concrete object. SKU ITEM can roll up to two hierarchies: ITEM SPECIFICATION and PRODUCT SPECIFICATION. Those two hierarchy attributes are present in SKU ITEM.

ACCESSORIES, HANDSET MODEL and EQUIPMENT

These are a subset of SKU ITEM with fewer attributes and some unique attributes. Just like SKU ITEM, they can roll up to ITEM SPECIFICATION or PRODUCT SPECIFICATION.

ACCESSORIES are always add-ons to a main item.

For each instances of ACCESSORIES, EQUIPMENT and HANDSET MODEL (in their respective DWR_XXX_INSTNC tables), do the following:

  • Add automatically 1 row to DWR_SKU_ITEM for each row of those instances with all the columns you can feed.

  • SKU_TYP_CD values should have additional lookup values such as: 'ACCESSORIES', 'EQUIPMENT', 'HANDSETMODEL';

SUPPLEMENTARY SERVICE

In Oracle Communications Data Model, only Postpaid-related supplementary Services have been picked, leveraging any PRODUCT SPECIFICATIONs defined in DWR_SPLMNTR_SRVC. Note however that the supplementary service derived table only runs over PRODUCT SPECIFICATION, looking for specific Product Specification codes, instead of linking to DWR_SPLMNTR_SRVC, because the target attributes are fixed.

VAS Service

Value-Added Service VAS applies only to WIRELESS CALL EVENT: the following tables should be taken. The current Intra-ETL version for VAS Usage derived, only uses DWB_ISP_USG_EVT, DWB_MMS_EVT, DWB_UDR_EVT, and DWB_SMS_EVT.DWB_UMS_EVT,

DWB_WRLS_CALL_EVT

DWB_WRLS_CALL_EVT

DWB_SMS_EVT

DWB_MMS_EVT

DWB_WRLS_CNTNT_DNLDG_EVT

DWB_CNTNT_DLVRY_EVT

DWB_GPRS_USG_EVT

DWB_ISP_USG_EVT

DWB_UMS_EVT

DWB_WRLS_RMNG_EVT


Understanding Party, Party Subtypes, and Party Role

Table C-3 Party and Party Subtype Entities

Entity Description

CUSTOMER

Customer has PROFILES and they have sub types of Individual and Business (termed as Organization). In addition Customer Contains PROSPECT also plus other related reference entities such as Customer Financial and Non Financial relationship (Accounts, Affiliation, Group, Occasion, Preference, Demography). Some of the "Named" Demography attributes are kept in CUSTOMER entity.

PARTY and PARTY ROLE

A party is an umbrella relationship across customer, vendor, employee and similar entities. For a given natural key, a Party ID ties up all these disparate entities and allows to leverage each other while maintaining their individual signatures. Remember PARTY as an after thought which enables you to tie together the disparate entities with a common ID based on same Natural key. By definition all these disparate entities are created at a different time frame with diverse OLTP systems but all of them would carry same natural key if the same party is defined in the system as a customer, an employee and a vendor as well.

By default, only two types of PARTY are available in Oracle Communications Data Model: Individual and ORGANIZATION. This can be extended but consider extending and also updating the various tables associated with a PARTY ROLE such as CUSTOMER if they are impacted.

The same party can play several party roles with respect to the CSP: A person could be customer, employee and dealer of the CSP at the same time. It would have the same party key. But for a given interaction or event, only one party role is typically used (unless he sells to himself a product offering, for example). Standard Party Roles like CUSTOMER, VENDOR, DEALER, EMPLOYEE, SALES CHANNEL REPRESENTATIVE are pre-defined in Oracle Communications Data Model with their sub-types.


Understanding Events and Usage Entities

There are several types of events to distinguish in Oracle Communications Data Model:

  • About Business Interactions describes business interactions in general (as super type) and Party Interaction Events.

  • About Agreements describes agreements (as a subtype of Business Interaction but differing from Event Party Interaction).

  • About BSS/OSS Interaction Events describes events related to BSS or OSS systems that are neither Business Interactions nor Agreements, that is, not defined either of these.

  • About Usage Events (UDR Events) describes usage event, also called UDR Events, and their numerous subtypes.

  • Network related events like ResourceAlarm, Service Problems and Performance Measurement events. There is no specific section in this document as their definition (as in the TMF SID) immediately provides the scope of data to be used. Note that TROUBLE TICKET is a sub-type of BUSINESS INTERACTION that is (usually) related with one or more SERVICE PROBLEM(s) and one or more RESOURCE ALARM(s).

  • About Process Events describes process events (from an application perspective) and describes any process in the telecommunication world that needs to be monitored: Billing, Service Fulfillment, and so on. Such events are usually directly related with KPIs.

About Business Interactions

Business Interactions are related to the transactions stored into back office applications. Table C-4 describes business interactions.

Table C-4 Business Interactions

Entity Description

BUSINESS INTERACTION

and

BUSINESS INTERACTION HISTORY

Maintain these entities as two identical entities with identical structure. BUSINESS INTERACTION is created once for a given interaction and should be subsequently updated with the latest status, whereas BUSINESS INTERACTION HISTORY always has a new record created for the same interaction with different status code (STAT_CD) and transaction date (TRX_DT).

EVENT PARTY INTERACTION

Use by default for all business interactions involving a PARTY, which are not AGREEMENT.

PURCHASE ORDER

This is when the CSP sends a purchase order to a VENDOR. It has nothing to do with Customers (stored in CUSTOMER ORDER).


Table C-5 lists the tables that are sub-types of BUSINESS INTERACTION or related to business interactions.

Table C-5 Business Interaction Tables

Business Interaction Tables (Back office Applications Transactions)

DWB_AGRMNT_APRVL

DWB_AGRMNT_TERM

DWB_BSNS_INTRACN

DWB_BSNS_INTRACN_HIST

DWB_BSNS_INTRACN_ITEM

DWB_BSNS_INTRACN_ITEM_PRICE

DWB_BSNS_INTRACN_PYMT_ASGN

DWB_BSNS_INTRACN_RL

DWB_BSNS_INTRACN_VRSN

DWB_CNSEQ_PRFMNC_NTFCTN

DWB_CUST_ORDR

DWB_CUST_ORDR_LN_ITEM

DWB_INVC_ADJ

DWB_NP_RQST_HDR

DWB_NP_RQST_LN_ITEM

DWB_PCHSE_ORDR

DWB_PCHSE_ORDR_LN_ITEM

DWB_PCHSE_ORDR_LN_ITEM_STATE

DWB_PCHSE_ORDR_STATE

DWB_PRFMNC_NTFCTN

DWB_RSCE_BSNS_INTRACN_RL

DWB_RSCE_ORDR

DWB_RSCE_ORDR_ITEM

DWB_SRVC_ORDR

DWB_SRVC_ORDR_LN_ITEM

DWR_ACCT_BSNS_INTRACN_RL

DWR_AGRMNT

DWR_AGRMNT_ITEM

DWR_BSNS_INTRACN_CHAR_VAL

DWR_BSNS_INTRACN_LOC_ASGN

DWR_EQPMNT_RNTNG_AGRMNT

DWR_INSTLMNT_AGRMNT

DWR_PRTY_BSNS_INTRACN_RL

DWR_SRVC_LVL_AGRMNT

DWR_SRVC_LVL_AGRMNT_ITEM

DWR_VNDR_AGRMNT


Use the table DWB_BSNS_INTRACN for all business interaction cases not covered by EVENT PARTY INTERACTION or AGREEMENT.

About Business Interaction Item Tables

The ITEM CODE in this entity is only a sequential number for business interaction detail. It has nothing to do with ITEM SPECIFICATION (DWR_ITEM_SPEC) or SKU ITEM. This also applied to AGREEMENT ITEM.

SEQ in Table C-6 means a number representing the rank in a series of objects is expected.

Table C-6 Business Interaction Items Tables

Table Sequence Number

DWB_AGRMNT_TERM

SEQ

DWB_BSNS_INTRACN_ITEM

SEQ

DWB_BSNS_INTRACN_ITEM_PRICE

SEQ

DWB_CUST_FLD_SRVC_DTL

SEQ

DWB_CUST_ORDR_LN_ITEM

SEQ

DWB_EVT_PRTY_INTRACN_ITEM

SEQ

DWB_NP_RQST_LN_ITEM

SEQ

DWB_PCHSE_ORDR_LN_ITEM

SEQ

DWB_PCHSE_ORDR_LN_ITEM_STATE

SEQ

DWB_RSCE_ORDR_ITEM

SEQ

DWB_SRVC_ORDR_LN_ITEM

SEQ

DWB_TRBLE_TCKT_ITEM

SEQ

DWR_AGRMNT_ITEM

SEQ

DWR_SRVC_LVL_AGRMNT_ITEM

SEQ

DWR_BSNS_INTRACN_LOC_ASGN

SEQ

DWR_BSNS_INTRACN_CHAR_VAL

SEQ


About Event Party Interactions

The event party interactions stores all interactions with a party, whether by phone, letter or fax, on-site visit (whether a party enters a shop or gets the visit of a sales representative), chat on the web or simple web navigation.

The EVENT PARTY INTERACTION should be used by default for all business interactions involving a PARTY, which are not AGREEMENT.

The related EVENT PARTY INTERACTION ID should point to the first EVENT PARTY INTERACTION related to the same subject ("Thread"). Hence, to have the list of interaction related to each other, they will ALL have the same RLTD_INTRACN_ID except the 1st one (empty).

The assumed logical hierarchy is: EVENT => BUSINESS INTERACTION => EVENT PARTY INTERACTION.

When no specific table for a given EVENT PARTY INTERACTION is available in Oracle Communications Data Model, as shown in Table C-7, use the generic EVENT PARTY INTERACTION.

When no specific tables for a given BUSINESS INTERACTION which is neither EVENT PARTY INTERACTION, AGREEMENT, or CUSTOMER ORDER, use generic BUSINESS INTERACTION.

When no specific tables for a given EVENT (see list in EVENT_BSNS_INTRACN_INFORMATION worksheet), use generic EVENT table (DWB_EVT).

Table C-7 shows the list of entities related to EVENT PARTY INTERACTION.

Table C-7 Event Party Interaction Tables

Event Party Interaction (Contact Center)

DWB_APNMNT

DWB_CUST_FLD_SRVC_ACTVTY

DWB_CUST_FLD_SRVC_DTL

DWB_EVT_PRTY_INTRACN

DWB_EVT_PRTY_INTRACN_CHAT_DTL

DWB_EVT_PRTY_INTRACN_ITEM

DWB_EVT_PRTY_INTRACN_PRTCPTN

DWB_INTRACN_NAVGTN_HIST

DWB_INTRACN_QUES_RESPN

DWB_INTRACN_TRNSFR_HIST

DWB_IVR_INTRACN_NAVGTN_HIST

DWB_PRTY_PRMTN_RESPN

DWB_SRVC_RQST

DWB_TRBLE_TCKT

DWB_TRBLE_TCKT_FLD_SPPRT_ASGN

DWB_TRBLE_TCKT_ITEM

DWB_WEB_INTRACN_NAVGTN_HIST

DWR_EVT_PRTY_INTRACN_CHAR_VAL


The trouble ticket is more the result of an interaction but, since it could be directly created by a third party or the customer itself (typical by support request systems).

EVENT PARTY INTERACTION ITEM is a subtype of BUSINESS INTERACTION ITEM (as EVENT PARTY INTERACTION is a subtype of BUSINESS INTERACTION).

If, for any reason, some entities related to the super-type (BUSINESS INTERACTION) are required to add information to EVENT PARTY INTERACTION that are not available otherwise, the creation of a row in BUSINESS INTERACTION is then required with EVENT CODE (EVT_CD) and INTERACTION ID (INTRACN_ID) in BUSINESS INTERACTION being copied from EVENT PARTY INTERACTION. Only the required keys shall be copied (and the Business Interaction type = "EVENT PARTY INTERACTION"). The hierarchy is EVENT => BUSINESS INTERACTION => EVENT PARTY INTERACTION. This duplication of information is only required to avoid customization and benefit, only where needed, of the super-type/subtype relationships.

About Agreements

Agreement (DWR_AGRMNT) shall be used by default for all agreements, prepaid (tacit or explicit), postpaid, interconnection and roaming, reseller, and so on.

Agreement KEY and INTERACTION ID should have identical value. The interaction ID field is just to ensure that it is a child of BUSINESS INTERACTION (similarly for AGREEMENT ITEM).

An invoice requires an agreement but an agreement may have 0 to n invoices related.

About BSS/OSS Interaction Events

Any events which are not a Business interaction nor an AGREEMENT as such, shall be a subtype of EVENT. It could typically be a purely technical event (Activation, suspension, deactivation and so on).

For example, a EVENT PRODUCT SUBSCRIPTION WIRELESS (DWB_EVT_PROD_SBRP_WRLS) could be used to store information of the EVENT of activation, suspension and deactivation. It shall not prevent updating the status of the corresponding PRODUCT SUBSCRIPTION (DWR_PROD_SBRP).

Table C-8 lists the tables that would correspond to such an event with their sources (assumed). Please note that BLLG stands for Billing Application, TXN for Transaction, CRM for Customer Relationship Management Application, FIN for Finance Application (such as JD Edwards, ERP or other), and HR for Human Resource applications (like PeopleSoft or others). NTWK stands for Network Applications (typically the network monitoring or Server control applications).

Table C-8 BSS/OSS Interaction Event Tables

EVENT TABLES (HR/Billing/Financial/CRM) Transactions not result Source TYPE

DWB_ACCT_BAL_IMPC

BLLG TXN

DWB_ACCT_PYMT

BLLG TXN

DWB_BLK_LST_HIST

CRM TXN

DWB_EMP_EXP_RPT

HR/FIN TXN

DWB_EMP_EXP_RPT_ITEM

HR/FIN TXN

DWB_EMP_EXP_RPT_STATE

HR/FIN TXN

DWB_EVT

CRM TXN a priori

DWB_EVT_ACCS_MTHD_ACTVTY

CRM TXN

DWB_EVT_ACCT

CRM TXN

DWB_EVT_AGRMNT

CRM TXN

DWB_EVT_CMPST_PROD_SPEC

CRM TXN

DWB_EVT_COST

FIN TXN

DWB_EVT_CRCUT_RNTL

CRM TXN

DWB_EVT_EMP_ACTVTY

HR TXN

DWB_EVT_EMP_PYRL

HR TXN

DWB_EVT_EQPMNT_INSTNC

CRM TXN

DWB_EVT_FINCL

FIN TXN

DWB_EVT_GEO

CRM TXN

DWB_EVT_LYLTY_PROG

CRM TXN

DWB_EVT_PROD_SBRP_WRLS

CRM TXN

DWB_EVT_PRPD_MBL

CRM TXN

DWB_EVT_PRTY_ASGN

CRM TXN

DWB_EVT_PRTY_PRFL

CRM TXN

DWB_EVT_SBRP_CHNG

CRM TXN

DWB_EVT_SIM_CARD

CRM TXN

DWB_EVT_STAT

CRM TXN

DWB_EXP_RPT_PRTY_ASGN

HR/FIN TXN

DWB_PRICE_EVT

BLLG TXN

DWB_PRPD_RCHRG

BLLG TXN

DWB_PTV_FULL_CHNL_ACTVTN

CRM TXN

DWB_SL_CMISN_DTL

HR/FIN TXN

DWB_SL_CMISN_PYRL

HR/FIN TXN

DWR_EVT_LOC

CRM Reference

DWR_ISP_USER

NTWK Reference

DWR_PRCE_EVT_PRODOFR_PRCE_ASGN

BLLG Reference


About Usage Events (UDR Events)

There are many pre-defined types of usage event data records. Call Data Records or any usage data records should be loaded into the corresponding UDR EVENT table (or any of its sub-entities that are more or less directly corresponding to the considered data record).

There are important assumptions for UDR Events:

  • A UDR shall be stored in one table only. It is not required to repeat it in the super entity.

  • It is recommended to create a sub-entity of UDR EVENT for each group of usage with their specific characteristics if they are not already pre-defined in Oracle Communications Data Model. Otherwise, use the corresponding tables. The Intra-ETL should be correspondingly expanded to take those new tables into account (where relevant).

  • A UDR EVENT shall be stored in daily partitions (or lower) leveraging the starting date of the usage event (EVT_STRT_DT). In particular, if late CDRs (like Roaming) are loaded, they have to be loaded in the older partitions!

    The BILLING DATE field shall be used to flag WHEN the UDR EVENT was loaded to the Billing System (if any). It means "BILLING SYSTEM DATE". It is not the necessarily date at which the event has been billed (put to an invoice). Note that for Prepaid, BILLING SYSTEM DATE and EVT_STRT_DT should be very similar if not equal (or maybe equal to EVT_END_DT!).

  • For the Intra-ETL treatment in Oracle Communications Data Model, the current time windows considered applies on the EVENT START DATE. An alternative (customization) would be to use the UDR EVENT BILLING_DATE to determine which CDRs are to be considered.

Whenever a new type of usage data record is available, you have several choices:

  • Map it to an existing table that match the definition, adding as many missing fields as required (but knowing that those fields will not be considered by default by the intra ETLs).

  • Map what is possible to the UDR EVENT table (or any sub-table) to only keep track of its presence in Oracle Communications Data Model as well as the fields used by the Intra-ETL but do not extend the other fields (quickest way for out-of-the-box implementation of Oracle Communications Data Model, knowing the data will not allow detailed and complete analysis on fields that are not stored).

  • Create a subtype of the UDR Event table adding all the fields required and modifying the Intra ETLs to take it into account where required

The best alternative depends on the project condition. But keeping as much atomic information as possible is usually preferred unless the data amount or effort to load it is not worth the potential business value. In such case, a pre-processing in the staging area or through big data might be worth considering (for example, for network signaling data).

Table C-9 lists the pre-defined tables for UDR Events.

Table C-9 Predefined UDR Event Tables

UDR EVENT TABLES (Network Transactions) Source TYPE

DWB_BRDBND_USG_EVT

UDR EVT

DWB_CNTNT_DLVRY_EVT

UDR EVT

DWB_DATA_SRVC_EVT

UDR EVT

DWB_ERRD_MDTD_CALL_EVT

UDR EVT

DWB_ERRD_RAW_WRLS_CALL_EVT

UDR EVT

DWB_ERRD_RTD_WRLS_CALL_EVT

UDR EVT

DWB_FIXED_LN_CALL_EVT

UDR EVT

DWB_GPRS_USG_EVT

UDR EVT

DWB_IDD_CALL_EVT

UDR EVT

DWB_INTRNT_ACCS_EVT

UDR EVT

DWB_ISP_USG_EVT

UDR EVT

DWB_MDTD_CALL_EVT

UDR EVT

DWB_MMS_EVT

UDR EVT

DWB_PTV_QPI_SRVC_EVT

UDR EVT

DWB_PTV_USG_EVT

UDR EVT

DWB_RAW_MMS_EVT

UDR EVT

DWB_RAW_WRLS_CALL_EVT

UDR EVT

DWB_RTD_UDR_EVT

UDR EVT

DWB_SMS_EVT

UDR EVT

DWB_TAP_IN_WRLS_RMNG_EVT

UDR EVT

DWB_TAP_OUT_WRLS_RMNG_EVT

UDR EVT

DWB_UDR_EVT

UDR EVT

DWB_UMS_EVT

UDR EVT

DWB_VOIP_CALL_EVT

UDR EVT

DWB_WRLS_CALL_EVT

UDR EVT

DWB_WRLS_CNTNT_DNLDG_EVT

UDR EVT

DWB_WRLS_RMNG_EVT

UDR EVT


About Process Events

Process events are meant to store and manage the various eTOM ("enhanced Telecommunications Operations Mapping") processes run in the Telecommunications industry, from a process perspective. Process events are mainly aimed to measure Operational Excellence.

A complete generalized model of processes has been created, with default parameters (such as Manual Indicator, Customer Type, Billing Cycle, and so on). You can use the default parameters and an open parameter list with "operators" (GREATER THAN, BETWEEN, LIKE...) and values to cover many changeable processes.

Similar to other areas, Oracle Communications Data Model uses a PROCESS (definition) and PROCESS SPECIFICATION (for the grouping of similar processes with similar characteristics), with their relationships. And an instance or run of a given process occurs through a PROCESS EVENT, referring to a PROCESS. PROCESS EVENT that are sequentially related should be stored in PROCESS EVENT ASSIGNMENT, normally illustrating the PROCESS RELATIONSHIP previously defined. However, since nothing runs necessarily fine forever, the PROCESS EVENT ASSIGNMENT allows relationships between PROCESS EVENTs that are normally not related. For example, after several tentative of automatic invoice recycling, a manual invoice review could take place. All those processes are expected to be stored and related through this entity.

Oracle Communications Data Model is not designed to act as a Process Management Tool. Oracle Communications Data Model shall only be the information collector to enable end-users to improve their daily tasks by providing them the relevant or right information at the right time in the breadth they need, and allowing them to deepen their analysis down to atomic level of any area they are entitled to search.

Oracle Communications Data Model provides an example and illustration for the following processes: the billing cycle from invoice generation and printing (issuing) to invoice dispatching.

PROCESSes also measure some of the TMF KPIs with respect to Billing Operational efficiency. For those specific measures in particular, one has to look into the generation process for all invoice-related processes that occurred in a given month.

One can create a specific subtype of process event table to make sure all the statistics related to the process is stored - if the default statistics are not sufficient. One must be aware however that the multiplication of sub-tables for processes may tend to confuse the reader or make the model hard to follow and keen to error (by forgetting to link or check one or the other sub-table). As always, it is a matter of balance.

With respect to the derived layer, do not mix INVOICE DRVD (DWD_INVC_DAY) and PROCESS INVOICE DAY DRVD (DWD_INVC_PRCS_DAY). The first summarize the invoice information available at a given day (equal to BILLING DATE) while the other collects information about the end-to-end process, observed from the end-date of the Dispatching process. Recall that not only the invoice generation process but any type of process could be monitored if the information is fed to the process tables.

Understanding Account and Agreement Entities

Oracle Communications Data Model defines a hierarchy between the ACCOUNT, AGREEMENT, and PRODUCT SUBSCRIPTION:

  • Account: The financial vision of a customer or the payer (in TMF SID, it corresponds to CustomerAccount). An account can have sub-accounts (through ACCOUNT RELATIONSHIP) if required.

  • AGREEMENT: an agreement must be associated with ACCOUNT. They must be defined for Postpaid (typically equivalent to contract) and can be defined for Prepaid (possibly as instance of a tacit pre-defined agreement). It is the implementor's choice (and a business decision). It is however expected in most Intra-ETLs that a dummy agreement is created with the main PRODUCT OFFERING associated for each Prepaid account.

Data Model and Entity Notes

Notes on data model entities.

Table C-10 Notes Table

Entity Description

Invoice

If Invoice Creation Date is not defined, BILLING DATE shall be used. Note that BILLING DATE must be present as it is used for several Intra-ETLs and several links. Outstanding Invoices are Invoices to be paid, not necessarily overdue, but at least dispatched. Overdue invoices are invoices not being paid by (end of) DUE DATE.

Age On Net

AGE_ON_NET can be defined for whatever "entity" (Agreement, customer, product subscription) one considers. By default, the creation date (CRT_DT) or start date (STRT_DT) shall be used and not worry too much about "holes" (approximation).

The following lists the various "ages" Oracle Communications Data Model could consider (only the first choice is used by default):

  • CONTRACT AGE: Age of the current (or considered) contract. it starts on contract start date and stops increasing when contract is churned.

  • CUSTOMER AGE ON NET: to calculate it correctly, one would require that the following should be stored at CUSTOMER level (customization):

  • AGE_ON_NET_START_DATE: earliest date of any(!) agreement (postpaid or prepaid) at which the customer started being active.

  • AGE_ON_NET_TOT_MISSINGMONTHS: Total number of months (decimal) where the customer was absent (not active, without any agreements) to cover any holes, and so on.

The formula should then be: AGE_ON_NET (default in months) =

CEIL(MONTHS_BETWEEN(SYSDATE,AGE_ON_NET_START_DATE) MINUS AGE_ON_NET_TOT_MISSINGMONTHS).

To simplify this in Oracle Communications Data Model: AGE_ON_NET (default in months) =

CEIL(MONTHS_BETWEEN(MIN(SYSDATE, nvl(CHRN_DT, to_date('20990101','yyyymmdd'))),CRT_DT) )

Please note the approximation that CUSTOMER effective start date is considered to be its CREATION date. Similarly, CONTRACT AGE (default in months) =

MONTHS_BETWEEN(MIN(SYSDATE, nvl(END_DT, to_date('20990101','yyyymmdd'))), STRT_DT).

Loyalty

Loyalty is a specific subject area as such. In Oracle Communications Data Model the Loyalty program and balances are associated with a specific MEMBERSHIP ACCOUNT, which is only associated to customer (and not to ACCOUNT). Any event that could impact Loyalty points should be stored in EVENT LOYALTY PROGRAM (DWB_EVT_LYLTY_PROG) and from there, the ETL (not the intra-ETL!) should also feed DWB_ACCRUAL_EVT (points winning) and DWB_REDEM_EVT (redemption, expiration)

ACCOUNT BALANCE IMPACT records with ACCT_BAL_TYP Like '%LYLTY%' should have a corresponding event created into DWB_LYLTY_PROG_EVT records.

MEMBERSHIP ACCOUNT BALANCE HISTORY is a snapshot (similar to ACCOUNT BALANCE)

The events that could feed EVENT LOYALTY PROGRAM as earning points should all come from Billing System, whatever their origin (usage, customer order, payment...).

It is however possible to also link a pure retail transaction but this has to be done explicitly in Oracle Communications Data Model (Customization).

The events that could feed LOYALTY PROGRAM EVENT as redemption are of 3 types:

  • Retail transaction

  • Customer Order

  • Account Payment (Payment with points)

Please not that the Derived Loyalty table is only at LOYALTY PROGRAM level.

Table C-11 provides information on a Loyalty events

Count Day

The COUNT DAY DRVD stores all counts with respect to customer, account, agreement, product subscriptions, to be able to get at any day in time the effective count of the entity status at the end of a given day.

Oracle Communications Data Model assumes that if either Customer Surrogate Key, Account Surrogate Key or Agreement Surrogate key changes, the associated product subscriptions (Product subscription key) shall also change.

If for Customer and ACCOUNT, it is clear due to the (forced) links between account/customer and product subscription, this link is not compulsory between agreement and product subscription. (It could easily be made as customization). The link between agreement and Product subscription is only through AGREEMENT ITEM, and agreement can change in principle without impacting product subscription.


Table C-11 Loyalty Event Additional Information

Physical Column NameIn DWB_EVT_LYLTY_PROG Logical Column Name (and comments)In DWB_EVT_LYLTY_PROG Redemption Earning

ACCT_BAL_TYP _CD

Account Balance Type Code

RTL_TNDR_LI(retail receipt is not part of Invoice) => NULL/

CUST_ORDR => no INVC_CD => NULL

Acct_Pymt. ACCT_BAL_TYP_CD

ACCT_BAL_TYP_CD of ACCT_BAL_IMPC_BAL

ACCT_KEY

Account Key

ACCT_KEY from all 3 transactions

ACCT_KEY of ACCT_BAL_IMPC_BAL

CRNCY_CD

Currency Code

Retail: ISO_CRNCY_CD

CUST_ORDR: ISO_CRNCY_CD

ACCT_PYMT: CRNCY_CD

Not applicable.

EVT_CD

Event Code

Code of the event that caused the impact to the loyalty points

CUST_ORDR_NBR / RTL_TNDR_LI_TRX_NBR/ Acct_Pymt_CD

whatever is in EVT_CD (+ acct_key+ acct_bal_typ_key) of ACCT_BAL_IMPC_BAL

INVC_CD

Invoice Code

RTL_TNDR_LI( if retail receipt is not part of Invoice) => NULL

CUST_ORDR => no INVC_CD => NULL

Acct_Pymt.INVC_CD otherwise

Only if relevant and available.

LYLTY_PROG_EVT _TYP_CD

Type of Event Number stored as text to give some flexibility in event type. You may add at least 999 different codes if required.

'3000' or '3xxx'

'2000' or '2XXX'

PRMTN_KEY

Promotion

What is bought through redemption is always a Promotion:

RTL_TNDR_LI: CPN_TNDR_PRMTN_KEY

CUST_ORD : PRMTN_KEY

ACCT_PYMT: PRMTN_KEY

 

PRTY_LYLTY_PROG _PRTCPTN_KEY

PARTY Participation to the Loyalty Program

One can choose which party one wants to store here: The partner or the customer.

Best way: access DWR_LYLTY_ACCT_IDNTFR table (through MEMBERSHIP ACCOUNT ACCOUNT IDENTIFIER = FREQ SHOPPR NBR for RETAIL and CUST ORDR and MEMBERSHIP ACCOUNT ACCOUNT IDENTIFIER for PAYMNT) + get LYLTY_PROG from this table

Alternative: Use through CUST_KEY (PRTY_KEY) and find LYLTY_PROG in DWR_PRTY_LYLTY_PROG_PRTCPTN

Similar as earning is not immediately available in the event.

RDMPTN_TYP_CD

Redemption Type Code

RTL_tndr_LI & Cust ORDR => "Amount Redeemed"

ACCT_PYMT=> ACCT_PYMT_RSN_TYP_CD

null

REDMD_AMT

Redeemed Amount

RTL_tndr_LI: TNDR_AMT

Cust_ord: EXTND_AMT

ACCT_PYMT:PYMT_AMT

Not applicable.

SL_CHNL_KEY

Sales Channel Key

RTL_tndr_LI: Use null or ORG_BSNS_UNIT_TYP_CD

Cust_ord: SL_CHNL_KEY

ACCT_PYMT:PYMT_CHNL_KEY

Not applicable.

SL_CHNL_RPRSTV _KEY

Sales Channel Representative

Retail: OPRTR_EMP_KEY

CUST_ORDR: SL_CHNL_RPRSTV_KEY

ACCT_PYMT: EMP_KEY

Not applicable.

TRX_DT

Transaction Date

CUSt_ordr. INTRACN_DT

rtl_tndr_li: END_DT_TIME

acctpymt: pymt_DT

Event start date that caused the increase in loyalty points.