B Oracle Communications Data Model Business Use Case

This appendix provides an overview and examples of Oracle Communications Data Model business use case scenarios.

This appendix includes the following sections:

Sample Use Case: Introduction

The sample business use case for Oracle Communications Data Model includes the following:

  • A Multi-play telecom Carrier, including:

    • SuperTelcoGroup

    • SuperTelcoCommunications

    • SuperData

The SuperTelco Communications organization comprises two business units:

  • Mobile

  • Broadband: The broadband unit, named SuperData, is an acquired company; this organization has a different hierarchy. The broadband unit includes both video and broadband data services.

Their Product Offering includes (among others):

  • Broadband Services for B2C and B2B

  • Video on Demand

  • Mobile (for example 3G) services

Their prospects and then customers will be Tom Daniels and his family (Mary Daniels and their children) and some companies.

Sample Use Case 1: Setting Up the Business Unit Organization

This business use case, describes how to build up the organization SuperTelco as a PARTY in the data model. In particular, the two main business units (Mobile with SuperTelco and Broadband with SuperData) will be modeled into Oracle Communications Data Model corresponding Subject Area, shown in Figure B-1.

Figure B-1 Organization Business Units in Sample Use Case

Description of Figure B-1 follows
Description of "Figure B-1 Organization Business Units in Sample Use Case"

Oracle Communications Data Model should capture the following administrative functions for the Mobile and Broadband business units:

  • HQ, HQ Mobile/HQ Broadband, Customer Care, Sales Marketing

  • Related geographic information (for state, county, City, Dealers/Shops and Web Service)

  • The people involved, in particular all employees (who is a manager from whom in which organization)

To work with the sample use case you build up the organization SuperTelco as a PARTY in the Oracle Communications Data Model:

  1. There are two ways to store the information for an ORGANIZATION BUSINESS UNIT:

    • Using a standard pre-defined hierarchy

    • Using a flexible hierarchy

  2. As shown in Figure B-1, the business unit follows a simple hierarchy stored in the corresponding tables:

    • ORGANIZATION BUSINESS UNIT: this is the smallest "independent" unit of an organization which can contain several sales channels and/or customer contact possibilities, such as call centers (stored in CALL CENTER), a website (stored in "Service Web Site"), and shops (in RETAIL STORE). The business unit is of a specified type, as detailed in the ORGANIZATION BUSINESS UNIT TYPE. All the information relative to this business unit, such as the business address or the company registry number is stored in the ORGANIZATION BUSINESS UNIT. It is a sub-type of PARTY.

    • This business unit is geographically (and somehow "administratively") situated in a district, region, and area. The geographic entity is stored as specified in the entities: ORGANIZATION DISTRICT, ORGANIZATION REGION, and ORGANIZATION AREA.

    • To understand the notion of "organizational chain" above the organization area, consider for example that the SuperTelco stores are located inside a given supermarket chain. SuperTelco may have a part of the organization related to this chain of supermarket, which would then be stored in the ORGANIZATION CHAIN table.

    • The ORGANIZATION CHAIN belongs to a company; in this example, the company SuperTelco is stored in the ORGANIZATION COMPANY table, itself member of a group whose information is stored in ORGANIZATION CORPORATE.

    • When you use a "banner" for a given sales channel, store the banner in the ORGANIZATION BANNER table, linking the business unit to the corporate level.

  3. As shown in Figure B-1, the business unit could also be part of a proper and changeable hierarchy (or hierarchies) that would then be stored in the corresponding tables of the so-called "flexible" Organization hierarchy:

Of these two choices: simple hierarchy and flexible hierarchy, the SuperTelco sample use case uses the flexible hierarchy. This is the preferred hierarchy for this sample because the historic growth of SuperTelco specifies that the hierarchy changes over time. To deal with the geographical organization of the SuperTelco stores dispatched in the country however, the standard hierarchy could be used. Such a hierarchy would support a detailed analysis of the local and geographical differences for the impact of a national marketing campaign.

Sample Use Case 2: Acquiring a New Customer (with Family Plan)

For the sample use case, let us assume that a father (Tom Daniels) goes to a SuperTelco dealer and asks for a Family Plan offering with the following features:

  • One main mobile phone postpaid (his)

  • One secondary mobile phone postpaid (wife)

  • Two additional mobile phones prepaid (children)

  • One Friends and Family option that allows calls between these users to be free of charge

The father is moving his service from a competitor and wants to keep his current mobile number (number portability required). This example provides details on the information stored in the various agreement, account, customer, and party entities. The actions covered in this area include the following, as shown in Figure B-2.

  • Party Interaction (Customer - Dealer)

  • Contract setup (Customer, Account, Billing, and others)

  • Subscription

  • Product Association

  • Phone number and equipment associations

Figure B-2 Customer Acquisition: Family Plan Model

Description of Figure B-2 follows
Description of "Figure B-2 Customer Acquisition: Family Plan Model"

New Customer with Family Plan Data:

  1. The ORGANIZATION BUSINESS UNIT information was previously setup, as described in Sample Use Case 1: Setting Up the Business Unit Organization.

  2. The newly acquired customer information is stored in the CRM and/or the billing system. This information will feed Oracle Communications Data Model using a custom ETL. One record in CUSTOMER is inserted with a name, in this case "Tom Daniels", of type "individual". Usually customers are not required to provide additional user information when purchasing multiple numbers. If this information is provided, you can save the information with the PARTY entity (and you should use the PARTY ASSIGNMENT table to describe their relationship to "Tom Daniel" - assuming this information is available in the data source).

  3. Save the related information about a customer, such as profession, age, education, and other information in the related CUSTOMER tables, the referential lookup tables, such as SOC JOB ("Standard Occupational Classification" system for the work activity of the individual customer). Save confidential information such as the date of birth in the table called CUSTOMER RESTRICTED INFO that can be individually hidden or encrypted in the database.

  4. A contract between Tom Daniels and SuperTelco is set up in the AGREEMENT table (equivalent to a contract but generalized). The customer has a agreement with the Service Provider which defines the accounts (normally with a unique login or a unique identifier). For example this sample agreement is based on a special package "PKG_Mobile_300". The product packages available to any type of consumers (individual - B2C or businesses - B2B) are saved in the PRODUCT OFFERING entity.

  5. One customer account is inserted into the ACCOUNT entity, with the customer key pointing to the new customer instance. The account is the financial vision of the customer. There is normally only one account per customer (whatever the number of subscriptions they buy) but multiple accounts per customer is allowed (typically to either reproduce the billing vision or in some specific cases).

  6. The customer, Tom Daniels, has selected four different handsets (stored in the EQUIPMENT table; this is not visible on the diagram shown in Figure B-2).

  7. Four Mobile phone numbers are saved into the table ACCESS METHOD and the associated handset. Each phone number uses the current date for the effective date and also has the account ID pointing to the account (as the account ID was set up).

  8. A customer order, stored in the CUSTOMER ORDER entity, is generated with all the items that the customer ordered including mobile numbers, product packages, and so on (including the number portability request).

  9. A number portability request is triggered by the order and a number portability event is stored in the NP REQUEST HEADER table. Due to the number portability request, the customer order may be processed with some delay; the old network provider must respond positively to SuperTelco's number portability request. In this case, either only Tom Daniels's IMSI or all IMSIs related to Tom Daniels will be activated after the agreement date ("today"). For this case, creating an additional, custom ETL(s) to the mediation or provisioning system may be necessary.

  10. Four subscriptions are inserted into the PRODUCT SUBSCRIPTION table. A subscription is considered a "non-network event" as opposed to a call, which is a "network event". Each subscription associates one product, one customer, one account, and one ACCESS METHOD (mobile number).

  11. The customer order could be loaded into Oracle Communications Data Model through the Extract-Transform-Load scripts (ETL) at each change of status or only once it is completed and fulfilled in the BSS/OSS systems.

  12. A fulfilled (closed) customer order automatically impacts the data mining tables related to the customer segmentation, market share, and the revenue OLAP cubes: For example, due to the number portability request, the competitor looses one customer and SuperTelco wins one customer in the given segment.

  13. In the pure prepaid case, no bill is created. However, the purchase of a voucher for any type of prepaid services is taken in account in Oracle Communications Data Model: PayTV, Music downloads, Prepaid card with handset, and so on. The original prepaid allowance or the recharge will be recorded and an account is created, similarly to the postpaid case.

Sample Use Case 3: Service Implementation

After Tom purchased the family plan, made the payment, and the customer order was generated, the provisioning engine takes over.

The service implementation is stored with Oracle Communications Data Model as shown in Figure B-3.

Figure B-3 Service Implementation

Description of Figure B-3 follows
Description of "Figure B-3 Service Implementation"

For the service implementation, the provisioning engine does the following:

  1. Each "customer Order" is disassembled into multiple "Service Order", each of which is used by the provisioning engine to orchestrate the whole system. Each "Service Order" is normally corresponding to a specific "Network Element" or a group of "Network Element". For example, one customer order of Prepaid GSM phone can be fulfilled by multiple "Service Order", including account setup of in the billing and CRM systems, in Intelligent Network system, and so on.

  2. Once the "Service Order" is executed, some new services may be generated. The service may be "customer facing service", which is an internal presentation of "Subscription". The business user sees each product realization on each customer as a "subscription" to track the business usage, while the technical user (from the network) sees a customer activation (and usage) on a "network element" (including logical "network element" like phone numbers) as a "Resource Facing Service". These notions are those defined by the TeleManagement Forum.

  3. In case any "Network Element" failed, technical support can easily track which customers or accounts may be affected by following relationships from "network element" to "Service" and then to "Subscription".

  4. As subtype of the Business Interaction, the CUSTOMER FIELD SERVICE ACTIVITY table (not visible on the diagram) would store any direct interaction on customer's or network's site for this order.

Sample Use Case 4: Storing Customer Call Data

After Tom Daniels has got his phone, and after the phones for his wife and children are activated, Tom Daniels regularly calls his family and friends. Tom Daniels uses the phone primarily to make voice calls and to send SMS messages; he rarely uses the data or MMS services.

The customer call information is stored with Oracle Communications Data Model as shown in Figure B-4.

Figure B-4 Customer Call Data Model

Description of Figure B-4 follows
Description of "Figure B-4 Customer Call Data Model"

The call data can be saved in Oracle Communications Data Model:

  1. The CUSTOMER, PRODUCT SPECIFICATION, and ACCESS METHOD are set up, as described in Sample Use Case 2: Acquiring a New Customer (with Family Plan).

  2. Each time the customer makes a call, this generates a Call Detail Record (CDR) in the network, at the switch level (raw CDRs) which then will be collected by the mediation (Mediated CDRs) and forwarded to the rating and/or billing engine (Rated CDRs). This last CDR - in the wireless case - is saved into the WIRELESS CALL EVENT table in Oracle Communications Data Model (this is a sub-entity of the UDR EVENT table). A UDR EVENT is an abstract entity which defines the minimal common definition of any network events (calls and service usage of any type).

  3. The CALL CATEGORY tracks the type of a call, such as a data or a voice call.

  4. The ROAMING TYPE tracks whether the call roams from another operator or to another operator.

  5. The MEDIATED CALL EVENT table stores the CDRs from the mediation system (before entering the billing engine).

  6. The UDR EVENT table stores the call details such as the call date and time and the call duration.

Note: Depending on where the source Call Detail Record (CDR) is taken, the CDR may contain a charge for the following:

  • In case of Roaming, the (base) charge is set by the other operator (raw or mediated CDRs level), while the carrier itself usually adds a surcharge (fixed percentage or fixed price per minute - normally higher than the roaming charge).

  • In case of Value Added Service, the charge is set by the vendor (raw or mediated CDRs).

  • In case the CDR source is the billing system, after rating has taken place (rated CDRs). This is also true for CDRs from the IN Platform which is doing the rating (typically for Prepaid).

Depending on the type of analysis, it is usually recommended for revenue assurance to check at least both mediated (before the billing system) and rated or billed CDRs (from the billing). The raw CDRs, direct input from the network, are usually more complex to deal with (binary type of data, a potential factor 100 in number of CDRs and additional signaling information) but are very interesting from a network operation and revenue assurance point of view.

Sample Use Case 5: Customer Billing

At the end of each bill cycle period (usually a specific day of the month for a given bill cycle), SuperTelco runs the billing process over the calling records for the customer and generates an invoice. In our example, Tom Daniels receives an invoice of $100 for all the phone numbers (Postpaid only normally, but one could think that he could also have agreed to pay by default every month some Recharges for his children "Prepaid" phones). Tom Daniels has to pay SuperTelco within a month or the service could be suspended.

Oracle Communications Data Model stores the customer billing, invoice, and payment information as shown in Figure B-5.

Figure B-5 Billing and Payment Data Model (simplified and missing some entities)

Description of Figure B-5 follows
Description of "Figure B-5 Billing and Payment Data Model (simplified and missing some entities)"

Billing Data in Oracle Communications Data Model:

  1. The section, Sample Use Case 4: Storing Customer Call Data, describes the collection of call data records data.

  2. To store the details of the product charging information, Oracle Communications Data Model uses the PRODUCT SPECIFICATION sub-entities such as: PRODUCT OFFERING RATING PLAN, PRODUCT OFFERING RATING PLAN DETAIL, and PRODUCT OFFERING PRICE and PRICE TYPE.

  3. Each ACCOUNT is billed independently. If a customer owns multiple agreements, multiple INVOICEs are generated in the same month.

  4. An agreement may have a different billing period than other agreements associated to the same account. A billing period may be specified monthly, bi-weekly, and so on.

  5. After the billing and invoicing have occurred in the billing system, the INVOICE, INVOICE ITEM and INVOICE ITEM DETAIL tables store all the information of the invoice for the given billing period (usually a month). The term at which the customer has to pay the invoice is saved in the INVOICE PAYMENT TERM TYPE associated with each invoice (for example, one month or 90 days). The term is fixed when the agreement is signed.

  6. In case a discount or adjustment is applied to the invoice, this information is stored in the corresponding table (INVOICE DISCOUNT or INVOICE ADJUSTMENT). An EMPLOYEE can make invoice adjustments to the amount limited by INVOICE ADJUSTMENT QUOTA.

  7. The invoice delivery to Tom Daniels is a small part in the complete billing and invoice issuing and dispatching processes. The specific information related to Tom's invoice is stored in the INVOICE PROCESS ASSIGNMENT table (not visible in the diagram) and eventually in the INVOICE itself (see the date at which it is created, issued and dispatched).

  8. The ACCOUNT PAYMENT METHOD stores the payment method chosen when the agreement was signed and this method is the default for the payment transaction.

  9. When Tom Daniels pays the invoice, for example using a bank transfer, the payment is stored in ACCOUNT PAYMENT and assigned to the corresponding open invoice. The ACCOUNT PAYMENT is stored into the INVOICE PAYMENT ASSIGNMENT. The ACCOUNT PAYMENT table stores any type of payment (normal payment, recharge, transfer, refund, and so on). You may define a view for each subtype whenever required.

  10. The difference between the INVOICE amount and the payment adds to the debt (the debt is not shown in Figure B-5).

Note: for the revenue assurance sub-area and its corresponding reports, it is important to store the itemized bill in Oracle Communications Data Model. The usage items (detailed call list) can then be compared, one by one, with the rated CDRs and using this method you can find the difference between rated and billed CDRs.

The section, Sample Use Case 7: Targeted Promotion for Video-on-Demand Services" shows a campaign set-up with the prospect choice. For this campaign, a measure of the campaign success could be obtained by analyzing the number of subscribers who contacted the call center and requested a product change based on the promotion, as a factor of time, in hours or days, between sending the promotion and customer call-back.

Sample Use Case 6: Changing Plan and Billing Address

SuperTelco launches a campaign to promote a package with converged broadband and mobile services. Tom Daniels sees the promotion message, delivered through an SMS campaign, and decides to take advantage of the promotion. He calls the call center and asks to change his product package to obtain the new converged family plan that includes broadband services. Later, using the SuperTelco Web Self-Service Interface he changes his billing address.

The section, Sample Use Case 7: Targeted Promotion for Video-on-Demand Services shows a campaign set-up with the prospect choice. For this campaign, a measure of the campaign success could be obtained by analyzing the number of subscribers who contacted the call center and requested a product change based on the promotion, as a factor of time, in hours or days, between sending the promotion and customer call-back.

SuperTelco uses Oracle Communications Data Model to store this customer interaction as shown in Figure B-6 and as outlined in the corresponding steps.

Figure B-6 Changing Plan and Billing Address

Description of Figure B-6 follows
Description of "Figure B-6 Changing Plan and Billing Address"

  1. The section, "Sample Use Case 1: Setting Up the Business Unit Organization" covers information about the call center.

    The call center agent, stored in CALL CENTER AGENT, as well as the team and department, in CALL CENTER table, are uniquely identified in Oracle Communications Data Model. The call center agent may be an employee (stored then in EMPLOYEE) of SuperTelco or an employee of a partner company that runs the call center for SuperTelco. For this example the CALL CENTER AGENTis a subtype of EMPLOYEE. All INTERACTION CHANNELs need to be configured, such as the CALL CENTER and any Web or Online business system, or a counter (in a shop), to make sure that one can trace the interaction with the customer at any time.

  2. The details for interaction information for the call center are stored as a "non network event". Depending on the method Tom Daniels uses to contact the call center, the corresponding code from INTERACTION TYPE is stored with the event:

    • Using EVENT PARTY INTERACTION (use interaction type as "Call" in this case). This information is aggregated in the CALL CENTER CALL MONTH AGGR for further analysis.

    • A thread will be defined by the first interaction of the chain in EVENT PARTY INTERACTION to store the reason for the customer call. A thread groups all interactions having to do with the same list of requests, inquiries and issues the customer deals with. This information is aggregated in the CALL CENTER CASE MONTH AGGR for further analysis.

  3. When the customer confirms the agreement change, the product change process occurs in the CRM and billing system. This process triggers two PRODUCT SUBSCRIPTION events for the ACCOUNT (when the converged product is a complete package which cannot be split). Oracle Communications Data Model stores the following events:

    If as part of the commercial process for this offering defined by the Service Provider the AGREEMENT requires changes, then do the following:

    • Close the old agreement with a "cancellation reason" specified (find the cancellation reason in the lookup table AGREEMENT STATUS REASON).

    • Create a new agreement with the corresponding AGREEMENT TERM supplied.

    • If the AGREEMENT does not need to be replaced and the new product uses the same agreement, then one case either:

      a) Close the Agreement Item with the old PRODUCT SUBSCRIPTION.

      b) Create a new Agreement Item with the new PRODUCT SUBSCRIPTION.

      c) You may also change the product assignment for the existing agreement in the table AGREEMENT PRODUCT SPEC ASSIGNMENT with a specific assignment code.

    Important Notes:

    • If by changing the PRODUCT SUBSCRIPTION, the main PRODUCT OFFERING changes, a new line in AGREEMENT (new agreement key) is required, even if the agreement code (Agreement identifier) does not change.

    • A product change impacts several other tables on the next automatic data movement (and their corresponding reports).

      • The CANNIBALIZATION DETAIL DAY DRVD table which captures the individual record related to the tariff and package change. This table fills the Cross and Up-sell mining model.

      • The customer Lifetime Value associated table is also updated. The agreement or product has changed and this change impacts the likelihood to churn.

      • The Revenue Forecast OLAP cube also changes for this customer.

The details for the product charge information are stored in the various PRODUCT SPECIFICATION sub-entities, including: PRODUCT OFFERING RATING PLAN and PRODUCT OFFERING RATING PLAN DETAIL.

Note: the Oracle Communications Data Model does not rate, from the monetary perspective, any kind of event (no "shadow billing" as such), although one could customize Oracle Communications Data Model for this purpose.

The customer table, using the entity CUSTOMER and the attribute Billing Address Location Code, stores the customer's billing address. This attribute links to the actual address entity ADDRESS LOCATION. The billing address is one type with a value from the ADDRESS TYPE for the new address. For example, when Tom Daniels changes the billing address, using the SuperTelco Web Self-Service Interface, the change is captured by the ETLs (from the CRM or from the web interface) and is stored in Oracle Communications Data Model as a the non-network event (from the source Web Interface, the Web based customer self-care system, typically where you login to obtain your offer).

When Tom Daniels has given the new address, the two addresses are linked with the ADDRESS RELATED entity. With more than one address, changes are required in the ADDRESS RELATED and CUSTOMER entities:

  • The current billing address in ADDRESS RELATED has the value "Old Billing Address" as reason.

  • The new billing address reason is assigned: if this is a new home address the new address exists in Oracle Communications Data Model and becomes the new billing address.

  • The ADDRESS STATUS of new address is set to "Active" while the ADDRESS STATUS for the old address becomes "Inactive".

  • In the CUSTOMER table, the new billing address location is overwritten and the billing address effective date is updated to the correct date.

  • The change of address may impact the customer profiling mining model.

Additionally, the PARTY STATUS HISTORY could be updated (depending on what information the Service Provider requires).

Sample Use Case 7: Targeted Promotion for Video-on-Demand Services

SuperTelco analyzes the current customer base to identify the customers who are most likely to purchase the Video-On-Demand service. The Marketing department would also like to increase the number of customers in the loyalty program (this can help limit churn). Using the Data Mining tool for target promotion, the business analyst in the SuperTelco Marketing generates a list of customers that are likely to be interested in this service and that are not currently members of a loyalty program ("supervised" mining).

A sample of the target list of customers is selected to test the promotion. Customer Tom Daniels is among the target list of customers. SuperTelco sends the target customers an email. In order to collect customer feedback, SuperTelco decides that the test promotion customers must contact the call center to get the Video-On-Demand service and one free DVD.

SuperTelco uses Oracle Communications Data Model to store this customer interaction as shown in Figure B-6 and as outlined in the corresponding steps.

Figure B-7 Targeted Promotion Data Model

Description of Figure B-7 follows
Description of "Figure B-7 Targeted Promotion Data Model"

Tom Daniels decides to buy the service and calls the CALL CENTER to get the new promotion, including:

  • A month of Video-On-Demand service for ten dollars.

  • Five films per month free and one free DVD.

  • During the call he is offered the option to be added to the loyalty program with 500 Loyalty bonus points.

The section, "Sample Use Case 6: Changing Plan and Billing Address" covers the impact of a product change.

The business analyst prepares the campaign, selects the prospects, and measures the campaign success as follow:

  1. The marketing manager determines the number of customers that are members of the loyalty program. Membership in the loyalty program seems to be a factor in reducing churn and increasing SuperTelco's knowledge of a customer's preferences. To increase the number of customers in the loyalty program the marketing manager decides to contact existing customers to proposing a new offering, the Video-On-Demand product, and bind the offering to the loyalty program membership. The loyalty program membership is proposed whether the customer takes advantages of the Video-On-Demand promotion or not. Thus, the promotion includes two promotions:

    1. Service Offering: Video-On-Demand

    2. Loyalty Program Membership

  2. The product setting for Video-On-Demand is specified in the PRODUCT SPECIFICATION and PRODUCT OFFERING tables. The purpose and summary information for each promotion is specified in the PROMOTION table. Some PROMOTIONs may serve a single strategic purpose (the CAMPAIGN tracks the promotion purpose).

  3. The business analyst for this campaign has the following requirements:

    1. Prospects for Video-On-Demand should have an active broadband service.

    2. Prospects for the loyalty program should not yet be a member of the loyalty program.

    3. Prospects should only be individuals.

    4. Prospects should not be in a campaign or have recently, within the last three months, been contacted for a promotional offering.

    5. Prospect revenue should be at least in the middle range.

    6. Prospect payment should be on-time, debt aging at zero or near zero, and the prospect should have had no service suspension for bad payments.

    7. Before proposing the promotion on a large scale the business analyst should select a list of two hundred sample customers to test the campaign.

  4. Because of the information received the business analyst uses the "supervised" method for targeted promotion data mining, using the specified criteria to find the prospect list.

  5. The business analyst determines that there are two possibilities to generate the prospect list contacts:

For the sample use case the customer Tom Daniels is part of the two hundred customer test sample. He is tagged as a prospect for this campaign and will appear in the table PROSPECT. Tom Daniels can be a prospect of only one campaign at a time. This is strictly necessary to correctly measure the campaign response. Because Tom Daniels is an individual, the table PROSPECT INDIVIDUAL is filled; in addition, some data may be collected during the promotion customer interaction.

Following Tom Daniels's interaction with the CALL CENTER, as specified in the PARTY INTERACTION THREAD, the tables INITIATIVE RESULT TYPE, PARTY PROMOTION RESPONSE, and PROSPECT, field Prospect Result Code, are updated:

  1. Tom Daniels bought the service as specified in the promotion and the video chosen by Tom Daniels is recorded for further analysis (for billing and because the interest is saved information on "Tom Daniels's interest" and on most successful "Videos" type and name).

  2. Tom Daniels accepts membership in the loyalty program, stored in the LOYALTY PROGRAM entity, thus increasing the number of loyalty program members and the knowledge of Tom Daniels's interests.

Each response from a targeted customer is recorded in PARTY PROMOTION RESPONSE. A positive response is stored as part of the mining result to the campaign, thus providing a better score to individual customers in a similar segment as Tom Daniels. The scoring table is reused to calculate the likelihood of a positive answer to the campaign when the campaign is broadened beyond the test to other customers.

Note: A customer email triggered this initiative and the initiative was completed by the call center. Thus, Tom Daniels's CALL CENTER call was triggered by the email so the medium of this targeted promotion is email while the sales channel is the CALL CENTER.

As a consequence of the new loyalty program membership and the associated 500 bonus points, a "CRM" event of type Loyalty is created and stored in the LOYALTY MEMBERSHIP ENROLL table. A new MEMBERSHIP ACCOUNT is created. A membership account is an account of type Loyalty. It is specifically tracked separately and should never be part of the standard ACCOUNT BALANCE. The 500 Bonus point shall also appear in the EVENT LOYALTY PROGRAM table and also in ACCRUAL EVENT, in both cases associated with Tom's membership account. Tom Daniels also appears in the MEMBERSHIP ACCOUNT BALANCE HISTORY table (LOYALTY MEMBER POINT DAY DRVD and LOYALTY PROGRAM MO AGGR) coming from the previously defined CALL CENTER entity. The PARTY STATUS HISTORY is changed and some fields of CUSTOMER are updated (for example, Initiative Number and Customer Balance).

Note: Earning Loyalty point events (“accrual” events) are expected to come from a Billing System (or equivalent, as long as the loyalty balance is tracked), whatever their origin (purchase, retail transaction, payment, usage and so on).

Sample Use Case 8: Retention of Terminating Agreement

After a period as a customer, Tom Daniels's agreement and plan ends. Before the agreement ends SuperTelco notices that he is likely to churn, according to the socio-demographic data, the subscriptions he has, the usage and revenue pattern (based on comparisons with the customer segment).

The call center proposes that this customer continue with a new offering:

  • Family Broadband

  • Video-On-Demand and Phone

  • The new generation phone as equipment

  • A special 12% Discount for 12 months (12 month sign up)

SuperTelco uses Oracle Communications Data Model to store this customer interaction as shown in Figure B-8 and as outlined in the corresponding steps.

Figure B-8 Retention of Terminating Contract Model

Description of Figure B-8 follows
Description of "Figure B-8 Retention of Terminating Contract Model"

The terminating agreement and call center retention involves the following steps:

  1. Tom Daniels's churn likelihood increases as the end of the agreement approaches. Because he is an important customer, belonging to the loyalty program, the churn likelihood should be lower than in other segments (according to AWARD LEVEL when Tom Daniels participates in the loyalty program by LOYALTY MEMBERSHIP ENROLL).

  2. The operator may run Oracle Communications Data Model mining model to identify the highest probability churners. The result from mining model is saved in CUSTOMER MINING_TBS table. For more information, see "Model 1: Prepaid Churn Prediction", and "Model 3: Customer Profiling".

  3. There are usually two possible actions when a agreement is due to terminate:

    • Do nothing: In this case the agreement renews itself automatically when it is not actively canceled (assume that the customer will not churn). This is typically the case for a "sleeping" customer" that does not take the latest cheaper offering.

    • Actively contact the customer: In this case, contact the customer before the customer is sent an end agreement term letter (do this if it appears that the probability of customer churn is high and this customer is worth the investment). This action is particularly true for short-term churn-conditions. For example, when a communication is indicated up to one month before the end of the agreement where the customer may get an offer from a competitor. If the agreement ended automatically an action of the Service Provider is required for a renewal.

  4. Assuming that Tom Daniels is contacted, SuperTelco needs to know what to propose. Choices for this contact include the following:

    • Renew the agreement with no changes: this is possible but usually after several years this option in not attractive, due to competition.

    • Proposing a new offering.

    • Renewing the agreement with new hardware and a discount if the customer engages for more than twelve months.

      For the sample use case with Tom Daniels, agreement renewal with new hardware might be a good offering when the handsets for all the family members are old, over two years old, as specified in the information from HANDSET INSTANCE (subtype of EQUIPMENT INSTANCE). By offering a agreement renewal with new hardware, you could allow the customer to use-up some loyalty points he has earned (by selecting different equipment). Additionally, binding the customer to twelve more months according to his ARPU Band could be worth a 12% discount.

      When you offer a new handset, this could provide new capabilities. For example, applications to download that could generate additional revenue for SuperTelco. This expectation can be reinforced due to the age of the children.

  5. From the process perspective this use case is similar to the targeted promotion as described in "Sample Use Case 7: Targeted Promotion for Video-on-Demand Services" with similar entities and similar changes. After the customer accepts the new offer, a new AGREEMENT is setup. In addition to the new AGREEMENT, Tom Daniels is granted a gift. In this example, the new agreement offer includes a new handset or a one month data service free pass. How the customer decides to pick up the gift is tracked in REDEMPTION EVENT.

  6. Additionally to the party interaction, a non network event is stored in the table REDEMPTION EVENT to contain the free handset information. The free handset comes out of the association with the RETAIL TENDER LINE ITEM (as GIVE AWAY TYPE table assigned from the corresponding product offering (in PRODUCT OFFERING table). The handset itself is in the HANDSET INSTANCE table.

  7. If Tom Daniels was not a member of a loyalty program a similar offer could be available; the purchase for this handset offering would be stored into the CUSTOMER ORDER LINE ITEM or PURCHASE ORDER LINE ITEM table.

Sample Use Case 9: Dealer and Employee Sales Commission

This use case expands the details for customer information, as described in the section, "Sample Use Case 2: Acquiring a New Customer (with Family Plan)". This use case provides details for how sales information from a dealer is stored. Recall that in Use Case 2, the customer Tom Daniels asked for a family plan offering with the following features:

  • Four numbers: two Postpaid mobile and two PrePaid

  • One Friends and Family option

During the interaction the customer calls the call center to get the phone and broadband offering and the Video On Demand Service. Assuming that SuperTelco rewards dealers depending on customer revenue, the number of services and the customer loyalty, one shall consider the commissions and costs spending for dealers and for a given campaign:

Actions for the dealer and employee sales commission use case include the following:

  • Party interaction, customer, and dealer

  • Impact on commission and cost

  • Loyalty campaign cost

SuperTelco uses Oracle Communications Data Model to store this dealer and customer interaction as shown in Figure B-9 and as outlined in the corresponding steps.

Figure B-9 Dealer and Employee Sales Commission Data Model

Description of Figure B-9 follows
Description of "Figure B-9 Dealer and Employee Sales Commission Data Model"

  1. The information for the customer and account setup is described in "Sample Use Case 2: Acquiring a New Customer (with Family Plan)".

  2. At implementation time or when the dealer first appeared, the dealer is entered as a DEALER, for example John Dealer, a sub-type of the PARTY table. A DEALER includes the associated entities:

    1. An address (stored in ADDRESS LOCATION and related to DEALER).

    2. A SALES CHANNEL and a channel to identify the dealer. The SALES CHANNEL is an abstracted umbrella that unifies both an external DEALER and the internal sales agents as an EMPLOYEE. The JOB ROLE for each employee is in EMPLOYEE JOB ROLE ASSIGNMENT. For example, the job role for a Sales Employee should be "Sales Agent".

    3. An organization structure or a relationship to individuals (ORGANIZATION BUSINESS UNIT).

    4. A discount group in the DISCOUNT GROUP entity within the DEALER DISCOUNT GROUP ASSIGNMENT table. All the discounts the provider allows for a dealer are defined in DEALER DISCOUNT GROUP ASSIGNMENT (as a group). This entity feeds the dealer cost and customer cost table.

  3. As an employee in sales, John Dealer is associated with a sales commission plan code from the SALES COMMISSION PLAN table (using JOB ROLE). The details of the plan SALES COMMISSION PLAN DETAIL or the type of commission COMMISSION TYPE are stored in associated entities so that the full commissions and rewards for the item, equipment, services, and product market plan sold are set-up. The EMPLOYEE JOB ROLE ASSIGNMENT.

  4. The Party interaction between John Dealer and Tom Daniels generates a new CUSTOMER ORDER. The customer order is generated in the BOSS/OSS system and loaded into Oracle Communications Data Model. For each customer order the SALES COMMISSION DETAIL is loaded to track how much commission should be granted to the DEALER in this sales transaction. Once the CUSTOMER ORDER is fulfilled in the provisioning system, an agreement is settled with four activations, four handsets (ITEM SPECIFICATIONs) and probably five products (one per mobile and the shared Friends and Family offering (even if there is only one agreement). This has the following consequences in Oracle Communications Data Model:

    1. John Dealer generated revenue increases and the number of customer and subscriptions: the revenue is compared to the quota the dealer had at the beginning of the month on each of these items, revenue, number of customers, and subscriptions, for the calculation of the dealer's commission and potential bonus and for the final dealer report.

    2. John Dealer "costs" increase correspondingly, as he wins a percentage of the generated revenue.

    3. The number of handsets available at John's shop is reduced by four (two Postpaid and two Prepaid). The out-of-stock forecast mining model is automatically fed and correspondingly updated.

    4. The commission associated with the handsets through the commission indicator attribute ("Commission Ind") will trigger the calculation of an extra commission for the items sold, aggregated on the monthly basis (using COMMISSION DRVD and SALES CAMPAIGN SUMMARY MONTH AGGR).

  5. Assuming SuperTelco rewards on the effective revenue generated by the customer, depending on the ARPU band of the account associated with the customer, the special bonus for John Dealer is updated with Tom Daniel's profile and added as a supplementary cost for the dealer and for the customer. Often at this stage a fraud detection mechanism is applied to limit dealer or customer fraud.

  6. As Tom Daniels changes the package to the convergent offering, due to a campaign, SuperTelco does not reward John Dealer. The campaign cost may be increased by the cost of creating and sending the SMS, in general, and by the cost of the call center agent interaction. The customer cost could also only be increased by the cost of the call center agent interaction (assuming the SMS sent to Tom Daniels is not considered). The fact that Tom Daniels changes his package will probably impact the Band ARPU that could also change the bonus for John Dealer.

  7. As Tom Daniels's agreement comes to an end SuperTelco may decide to reward only the call center as a successful clawback action rather than granting further John Dealer with a bonus for the loyalty of the customer, as the later was not involved at all in the action. The customer cost for Tom Daniels would still increase. The employee and call center cost would also correspondingly increase (here, probably only the employee cost, as the call center cost must be considered to be the sum of the labor, employee, costs and other costs). For example the rent for the building or of the call center service is typically associated with the location of the call center only. Note that its total margin, due to the revenue generation through the agreement renewal, is increasing even if the relative margin will probably decrease over the month.

  8. At each end of month when the sales agent commissions are paid by payroll, the information in SALES COMMISSION PAYROLL is populated.

  9. Sometimes certain dealers may commit fraud when bringing in new customers. For example, a dealer may have friends sign agreements to win a gift but then terminate the agreement. The new customers brought in by the fraudulent dealer may be identified by SUBSCRIPTION STATISTIC MONTH AGGR. In this table some statistical functions are applied to find a high churn rate by a possibly cheating DEALER, compared to all other dealers.

Sample Use Case 10: Handling a Service Problem

The Network Monitoring System detects a failure at a switch. SuperTelco wants to understand how many customers are affected by the incident. The Network Monitoring System queries the Network Inventory to get the resource ID of the faulty element. The Network Monitoring System then generates a "network failure" event and Oracle Communications Data Model captures this event.

SuperTelco uses Oracle Communications Data Model to handle a network fault, as shown in Figure B-10 and as outlined in the corresponding steps.

SuperTelco includes the full network structure as specified in Oracle Communications Data Model and both the network operating and the network inventory applications provide information to Oracle Communications Data Model once a day.

This model has been strongly extended to better fit the TeleManagement Forum Shared Information Model (TMF SID) ResourceAlarm and ServiceProblem Aggregate Business Entities.

Figure B-10 Handling a Network Fault Data Model

Description of Figure B-10 follows
Description of "Figure B-10 Handling a Network Fault Data Model"

Consider the case and steps required to handle a network fault:

The first questions for the manager after identifying the issue are:

  1. One evening at 8pm a network cell suffers a power outage after being hit by lightning, and the network cell goes out and does not restart. The real-time network monitoring system alerts SuperTelco maintenance central. This allows SuperTelco to quickly identify where the failure is (site, location, default configuration before the break-down) and SuperTelco sends a team to look at the issue. Assume that this outage is a cell which is difficult to reach, despite being in a high density population area; thus it takes four hours to the maintenance team to repair and restart the network cell.

  2. While the network is down, SuperTelco customers call the call center from fixed lines to complain. Some customers threaten to quit the service if the problem persists.

  3. Up to this stage, Oracle Communications Data Model does not play a role. One could assume that Oracle Communications Data Model gets this summary information events, status, and so on, daily (at 2am the following morning). Note if the ETLs for the network controlling applications are configured such that Oracle Communications Data Model is updated in near real-time, for example hourly, then Oracle Communications Data Model may know about the event sooner.

  4. The SuperTelco manager gets the network fault information in real-time from the network applications team.

    • Where is the cell located?

    • Whose qualified team is in charge now?

    • Which services are impacted?

    • Which customers may be impacted?

    • What is the average revenue impact if nothing is done?

  5. Network applications should be able to answer directly where the cell is located and the team in charge. Note that Oracle Communications Data Model could also identify this information if the ID of the cell that broke down is supplied (even if Oracle Communications Data Model does not yet know that it broke down). A simple adhoc query on the RESOURCE (previously called NETWORK ELEMENT) table, and its sub-tables, could answer the question:

    "Where is my network element ID xxx?"

    To answer the question:

    "Who is in charge according to the maintenance plan?"

    Oracle Communications Data Model can supply this information with a customization of the model (this information is not available out-of-the-box). If the network fault happens to multiple RESOURCEs, all the faulted network elements are tracked in RESOURCE FAULT ASSIGNMENT. We assume that corresponding RESOURCE ALARMs are triggered.

  6. Each occurrence of a network failure is recorded in SERVICE PROBLEM. When a network fault happens at customer site, technical support activities to solve the problem are saved in the CUSTOMER FIELD SERVICE ACTIVITY when loaded into Oracle Communications Data Model.

  7. Once the network fault is resolved the resolution type of the network fault is loaded according to FAULT RESOLUTION TYPE.

  8. The list of services impacted is related to the list of elements which were out. In the sample use case, with a lightning strike, consider the full wireless traffic is down in the area near the antenna. Because this area is a high density area, one could expect that other antennae may partly cover the geographic coverage. In a GSM network, geographic areas are divided into different CELLs which are served by the corresponding BASE STATION CONTROLLER. The BASE STATION CONTROLLER is a subtype COMPOUND RESOURCE (itself a sybtype of RESOURCE). A simple report showing the affected areas and services also lists the services associated with the cell.

  9. You can obtain a list of impacted customers through the SERVICE PROBLEM SUBSCRIPTION ASSIGNMENT, which links the network fault to the PRODUCT SUBSCRIPTION table. The later contains the Circuit Component Code attribute that allows you to use the table CIRCUIT COMPONENT to get the NETWORK TOUCHPOINT concerned, the CELL SITE being a sub-table of NETWORK TOUCHPOINT. Consequently, a simple query on all subscriptions whose circuit component is tied to the cell ID that failed provides a list all the customer information associated with the given cell.

  10. Similarly, the exact list of products impacted, per customer, can easily be provided (related to the service that is down and the subscriptions concerned).

  11. With a list of products impacted, the manager can check how many calls normally run Friday evening between 8pm and 12pm, and get the average revenue generated at that time for those customers. This provides the average revenue loss within the four hours of time-off. The manager may send an email to the call center with the list of potential customers, to warn the call center that within the next three to four hours, those customers may be complaining about a loss of coverage.

  12. When a customer calls the call center, an interaction event is created in EVENT PARTY INTERACTION, with an interaction type of Complain.

  13. With the email and the customer list, a call center manager can warn the call center employees, and possibly ask for a additional personnel to manage the potential increase of complaint calls. Not that it is important to identify all the customer calls to the call center, associated with the failed cell that may be related to the network issue. This identification can be done either upfront in real-time by the call center agent or on a later with analysis from Oracle Communications Data Model. Note: the call center manager may have then an explanation ready for the next monthly meeting when he shows the customer satisfaction report.

  14. For the most valuable customers that complain and threaten to churn, the customer care manager may decide to run a compensation program. For example, by providing ten free SMS or ten minutes for free next month for private customers and provide a 10% discount for business customers at risk of churning.

  15. Later, if these procedures were not carried out, an increase in churn for the following month may be quickly related to the network issue: the default reports might show an alert due to an unusual increase in churn in a specific area (using the outlier function of the database associated to the alert functionality of Oracle Business Intelligence Suite Enterprise Edition).

  16. With Oracle Communications Data Model, there are therefore several ways to come to the same conclusion, in our case:

    • The network cell ID (near real-time).

    • The abnormally limited geographic distribution of origin of some complaints (most probably the next two days).

    • The abnormally increase of churn in a limited region (a month later).

Sample Use Case 11: Implementing a Business Area

The CFO requests that the SuperTelco IT manager (Susan) has to implement all the billing related reports of Oracle Communications Data Model

For simplification, assume that:

  • The CFO wants to get the value as quickly as possible, so that Susan is not supposed to customize anything unless strictly necessary.

  • SuperTelco uses Oracle Business Intelligence Suite Enterprise Edition as the reporting tool.

  • Oracle Communications Data Model is installed but all tables are completely empty.

Despite the fact that some DWHs exist, on customers and products, Susan goes forward as for a "greenfield" implementation. But she will reuse part of the work that was done before, either directly from the DWH tables, used as a source to Oracle Communications Data Model or using the ETLs to directly feed Oracle Communications Data Model tables.

In a second phase, the CFO requests a special report to take the customers that are diplomats and hence do not pay any VAT. A special customer code must be created and the CFO wants a report only for these specially coded customers. Thus, Susan decides she needs to enhance the customer table with a column Tax Rate Amount and introduce a new Customer Type: Diplomat. These changes should be done in parallel in the CRM, in the Customer DWH, and in the billing system.

To implement these steps, the IT manager, Susan, does the following:

  1. The project follows a typical DWH project plan with one important exception: because Oracle Communications Data Model is a "DWH-out-of-the-box", with an optimized design and an automatic data movement, intra-ETL provided, the main challenges for Susan are:

    1. Limiting the Scope of the project to quickly deliver value to the CFO:

      Identifying the reports associated with the chosen business area.

      Identifying the OLAP cubes and Mining needed or wanted by the business.

      Identifying the input tables required to fulfill the expectations.

      Identifying from the source systems the data needed to fill the tables.

    2. Analysis:

      Identifying the gaps between the organization needs and Oracle Communications Data Model out-of-the-box delivery. In Susan's case, one could assume these are reduced to a minimum. If it has not been a "greenfield" implementation, the gap analysis between the existing reports and underlying DWH structure with Oracle Communications Data Model should also be run.

      Identifying and writing down the difference in semantics between the various terms (normally, this should be quickly done after training with Oracle Communications Data Model). Mapping the source systems (in this case, only the billing and maybe the Product and Customer DWH) to Target Data Element.

    3. Design and Development:

      ETL (Billing to Oracle Communications Data Model and other DWH toOracle Communications Data Model).

      Logical Data Model and Reports Design Enhancement

    4. Training and Testing:

      Scenarii creation and run

      Acceptance Testing with some (trained) power-users

    5. Deployment:

      Initial / history data load

      Incremental load

    6. Maintenance:

  2. Within a given business area, Susan will find the reports available out-of-the-box (directly looking at the reports themselves or in the associated documentation) and discuss those the CFO wants to see absolutely.

  3. Once with the list of reports to feed, Susan checks the documentation to find out the entities from which these reports are filled and the programs used. She first turns to the Oracle Metadata dashboard (visible in Oracle Business Intelligence Suite Enterprise Edition): for each report, she finds all the tables that need to be filled (Dashboard Report-Entities) and gets also access to the Intra-ETLs that access these tables (Dashboard Entities-Programs).

  4. Going down to the entity description, she can decide which attributes (columns) per table she needs to fill and compare those with the data she can get out of its different sources. Note that Susan will be able to find which KPIs is associated to which column in the Excel file OCDM_KPI_Aggr_spec.xls:

    1. UDR EVENT as rated event from the billing system.

    2. INVOICE details from the billing system.

    3. Customer data either from the billing or the CRM system, or from its own Customer DWH.

    4. Product and product rating data from either the billing system or its Product DWH.

  5. Finally, it is Susan's decision to determine the source and then create the ETLs that load the corresponding information. In this case, she has two possibilities, the choice between the two being rather an architecture/process decision:

    1. She uses the Product and Customer DWHs as the base for true and up-to-date customer and product information (product and customer "hubs" principle). If she used the standard DWH principles, those are probably in 3NF format, thus easing the mapping process to Oracle Communications Data Model base tables for customers, products and services.

    2. She uses the ETLs that were feeding the Product and Customer DWHs and adapt them to feed Oracle Communications Data Model directly.

  6. Important for Susan is that, as soon as some data are available in Oracle Communications Data Model, it will be automatically pushed to reporting level, in the OLAP cubes and to the various mining models (following the plan agreed at implementation time). She can therefore cross-check the data at each Oracle Communications Data Model level (reference, base, derived, aggregation,…) and compare them with previous reports she has. The difference in definitions (what is a subscriber, a customer, an offering, a service,…?) must have been run upfront to be able to compare the data and clarify any differences appearing.

  7. On the second phase, adding a new type costs nothing but adding one line in the corresponding lookup table (CUSTOMER TYPE). The ETLs should be able to reference correctly the new customer type.

  8. For the tax customization, Susan will check in the Oracle Metadata dashboard the list of all intra-ETLs and programs hit by a customization of the customer table: in principle, there are a lot impacted. However, with a new attribute, most of them won't need any changes; only those that need to aggregate the result of any facts according to this new column must be extended.

  9. With this information, Susan will access and adapt the code of each intra-ETL she needs to. She will then adapt Oracle Business Intelligence Suite Enterprise Edition repository and the sample reports to present the new dimension.