|Oracle® Communications Data Model Reference
11g Release 2 (11.2)
Part Number E15886-02
This appendix provides an overview and examples of Oracle Communications Data Model business use case scenarios.
This appendix includes the following sections:
The sample business use case for Oracle Communications Data Model includes the following:
Carrier Company, including:
Product Offering, including:
Video and Mobile services
Customers, including, for example: Tom Daniels, Mary Daniels, and others
In this business use case you build up the organization SuperTelco as a PARTY in the data model. The SuperTelco Communications organization comprises two business units:
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.
Oracle Communications Data Model captures the following administrative functions for the Mobile and Broadband business units:
HQ, HQ Mobile/HQ Broadband, Customer Care, Sales Marketing
Geographic (for state, county, City, Dealers/Shops and Web Service)
The people (who is a manager from whom in which organization)
To work with the sample use case you need to fill in table details and organize tables. In this sample business use case you build up the organization SuperTelco as a PARTY in the Oracle Communications Data Model. SuperTelco Communications comprises two business units, as shown in Figure B-1.
Figure B-1 Organization Business Units in Sample Use Case
To work with the sample use case you build up the organization SuperTelco as a PARTY in the Oracle Communications Data Model:
There are two ways to store the information for an ORGANIZATION BUSINESS UNIT:
Using a standard pre-defined hierarchy
Using a flexible hierarchy
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 or customer contact possibilities, such as call centers, in CALL CENTER, a web site, 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.
This business unit is geographically situated in a district, region, and area. The geographic entity is stored as specified in the entities: ORGANIZATION DISTRICT, ORGANIZATION REGION, and ORGANIZATION AREA.
Above the organization area, for the notion of organizational chain, consider for example, the SuperTelco stores located inside a given supermarket chain, and stored in the ORGANIZATION CHAIN table.
The ORGANIZATION CHAIN belongs to a company; for 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.
As shown in Figure B-1, the business unit follows a flexible hierarchy stored in the corresponding tables:
A Business Unit is an ORGANIZATION BUSINESS ENTITY: This entity is a reference that allows a flexible definition for the hierarchy level and the attributes you choose per level (the entities ORGANIZATION LEVEL ATTRIBUTE VALUE and ORGANIZATION LEVEL ATTRIBUTES store attributes).
The ORGANIZATION LEVEL defines the levels of the flexible hierarchy (with level attributes and possible values as stored in the entities ORGANIZATION LEVEL ATTRIBUTE VALUE and ORGANIZATION LEVEL ATTRIBUTES).
The hierarchy between levels is defined in ORGANIZATION HIERARCHY LEVEL, which belongs to a given ORGANIZATION HIERARCHY. Thus, for a given organization several hierarchies can be defined (administrative, geographic, and so on).
A hierarchy has a version, defined in ORGANIZATION HIERARCHY VERSION. This allows you to change the hierarchy, depending on the historical development of the organization.
A Business entity is assigned to a given level through the ORGANIZATION HIERARCHY LEVEL ASSIGNMENT table.
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 for this sample, 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.
To provide a new customer for the sample use case, the father 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 contract, 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)
Phone number and equipment associations
Figure B-2 Customer Acquisition: Family Plan Model
New Family Plan Data:
The ORGANIZATION BUSINESS UNIT information was previously setup, as described in "Sample Business Use Case: Setting Up the Business Unit Organization".
To acquire a customer, using a custom ETL to the CRM or to the billing system to feed Oracle Communications Data Model, one record in CUSTOMER is inserted with a name, for example "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.
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). Note that you save confidential information such as the date of birth in a special table called CUSTOMER RESTRICTED INFO.
A contract between Tom Daniels and SuperTelco is set up in the CONTRACT table. The customer has a contract with the Service Provider which defines the accounts (normally with a unique login or a unique identifier). For example this sample contract is based on a special package "PKG_Mobile_300". The product packages available to individual consumers are saved in the PRODUCT MARKET PLAN entity.
One customer account is inserted into the ACCOUNT entity, with the customer key pointing to the new customer instance.
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).
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.
A number portability request is triggered by the order and 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 contract date ("today"). In this case, an additional, custom ETL to the mediation or provisioning system may be necessary.
Four subscriptions are inserted into the 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).
Once the customer order is fulfilled in the BSS/OSS systems, the customer order is loaded into Oracle Communications Data Model through the Extract-Transform-Load scripts (ETL).
A customer order fulfillment automatically impacts the data mining tables related to the customer segmentation, market share, and the revenue OLAP cubes (due to the number portability the competitor looses one customer and SuperTelco wins one customer in the given segment).
In the pure prepaid case, no bill is created. However, the purchase of a voucher for any type of prepaid services: PayTV, Music downloads, Prepaid card with handset, and so on, or the recharge will be recorded and an account is created, similarly to the postpaid case.
After Tom Daniels has his phone, and after the phones for his wife and children are activated, Tom Daniels regularly calls his friends and family. 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-3.
Figure B-3 Customer Calls Model
To save call data in Oracle Communications Data Model:
The CUSTOMER, PRODUCT, and ACCESS METHOD are set up, as described in "Sample Business Use Case: Acquiring a New Customer (with Family Plan)".
Each time the customer makes a call, this generates a Call Detail Record (CDR) in the network, at the switch level and forwards the CDR to the rating system. This CDR is saved into the WIRELESS CALL EVENT table in Oracle Communications Data Model (this is a sub-entity of the NETWORK EVENT table). A NETWORK EVENT is an abstract entity which defines the minimal common definition of any network events (calls and service usage of any type).
The CALL CATEGORY tracks the type of a call, such as a data or a voice call.
The ROAMING TYPE tracks whether the call roams from another operator or to another operator.
The MEDIATED CALL EVENTs represent calls that mobile users make or receive while on other operator networks.
The NETWORK 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 charge is set by the other operator (raw CDRs)
In case of Value Added Service, the charge is set by the vendor (raw CDRs)
In case the CDR source is the billing system, after rating has taken place (rated CDRs)
Depending on the type of analysis, it is usually recommended for revenue assurance to check both the raw CDRs, input from the network or before the billing system, and the billing output (rated CDRs).
At the end of each month SuperTelco runs the billing process over the calling records for the customer and generates an invoice. For example, the customer then receives an invoice of $100 for all the phone numbers. Then the customer, Tom Daniels, pays SuperTelco within one month or the service could be suspended.
Oracle Communications Data Model stores the customer billing, invoice, and payment information as shown in Figure B-4.
Figure B-4 Billing and Payment Data Model
Billing Data in Oracle Communications Data Model:
The section, "Sample Business Use Case: Storing Customer Call Data", describes the collection of call data records data.
To store the details of the product charging information, Oracle Communications Data Model uses the PRODUCT sub-entities such as: PRODUCT RATING PLAN, PRODUCT RATING PLAN DETAIL and PRODUCT CHARGE TYPE.
An account may have a different billing period than other associated accounts. A billing period may be specified monthly, bi-weekly, and so on.
After the billing and invoicing has occurred in the billing system, for each postpaid customer, the INVOICE, INVOICE ITEM and INVOICE ITEM DETAIL store all the information for the invoice for the given billing period (usually this is 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). The term is fixed when the contract is signed.
In case a discount or adjustment is applied on 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.
Once the invoice is delivered to Tom Daniels, this "non-network event" is stored in the EVENT INVOICE DELIVERY.
The ACCOUNT PREFERRED PAYMENT METHOD stores the payment method chosen when the contract was signed and this method is the default for the payment transaction.
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.
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 can then be compared, one by one, with the rated CDRs and using this method you can find the difference between rated and billed revenue.
After acquiring the broadband business, 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. Also, using the SuperTelco Web Self-Service Interface he changes his billing address.
The section, "Sample Business Use Case: 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 contact the call center and request 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-5 and as outlined in the corresponding steps.
Figure B-5 Changing Plan and Billing Address Data Model
The section, "Sample Business Use Case: SuperTelco Business Unit Organization" covers information about the call center.
The call center agent, stored in CALL CENTER AGENT and possibly EMPLOYEE, and the team and department, in CALL CENTER table, are uniquely identified in Oracle Communications Data Model. The call center agent may be an employee of SuperTelco or an employee of a partner company that runs the call center for SuperTelco. For this example the CALL CENTER AGENT is 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.
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:
When the customer confirms the contract change the product change process occurs in the CRM and billing system. This process triggers two 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:
The first event is a cancellation for the existing SUBSCRIPTION ("PKG_Mobile_300"). The
effective_to_date attribute changes to the current date.
The second event for the SUBSCRIPTION is a new product subscription for the converged package (as described in "Sample Business Use Case: Setting Up the Business Unit Organization").
The third event involves creating the link between the two subscriptions and uses the table SUBSCRIPTION ASSIGNMENT which stores the relationship between two events.
If, as part of the commercial process for this offering defined by the Service Provider the CONTRACT requires changes, then do the following:
Close the old contract with a "cancellation reason" specified (find the cancellation reason in the lookup table CONTRACT STATUS REASON). Create a new contract with the corresponding CONTRACT TERM VALUE supplied.
If the CONTRACT does not need to be replaced and the new product uses the same contract, then change the product assignment for the existing contract in the table CONTRACT PRODUCT ASSIGNMENT with a specific assignment code.
Note that a product change impacts several other tables on the next automatic data movement (and their corresponding reports):
The CANNIBALIZATION DETAIL MONTH AGGR table which captures the individual record related to the tariff and package change. This table fills the Cross and Up-sell mining model.
The CHURN PREDICT SOURCE DERIVED "Churn Predict Source". The contract or product has changed and this change impacts the likelihood to churn.
Customer Lifetime Value associated table is also updated. The contract or product has changed and this change impacts the likelihood to churn.
The Revenue Forecast OLAP cube also changes for this customer.
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 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).
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.
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).
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.
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 Business Use Case: 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 follows:
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:
Service Offering: Video-On-Demand
Loyalty Program Membership
The product setting for Video-On-Demand is specified in the PRODUCT and PRODUCT MARKET PLAN 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).
The business analyst for this campaign has the following requirements:
Prospects for Video-On-Demand should have an active broadband service.
Prospects for the loyalty program should not yet be a member of the loyalty program.
Prospects should only be individuals.
Prospects should not be in a campaign or have recently, within the last three months, been contacted for a promotional offering.
Prospect revenue should be at least in the middle range.
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.
Before proposing the promotion on a large scale the business analyst should select a list of two hundred sample customers to test the campaign.
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.
The business analyst determines that there are two possibilities to generate the prospect list contacts:
The operator can buy a CONTACT LIST from an external marketing data provider. The SOURCE SYSTEM contains possible sources for this type of data. The marketing department can also design criteria based on which customers to select from a CONTACT LIST. The customer information may not be in the operator's customer database yet. In this case the customer information is recorded in PARTY and PARTY CONTACT LIST PARTICIPATION that associate the PARTY and a CONTACT LIST. The PROMOTION CONTACT LIST UTILIZATION records which promotion utilizes which CONTACT LIST.
The operator can run data mining, provided with Oracle Communications Data Model including the "Targeted Product Promotion", or "Customer Segmentation". This corresponds to a Mining result table whose name is "DWD_CUST_PROD_AFFLTN". The output from the mining model CUSTOMER SEGMENTATION MODEL is specified in the entity CUSTOMER SEGMENT.
For more information, see Chapter 10, "Oracle Communications Data Model Data Mining Models" and "Model 4: Cross-Sell Opportunity".
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 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. Note that 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:
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).
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 "non-network" event of type Loyalty is created and stored in the EVENT LOYALTY PROGRAM table. Tom Daniels also appears in the LOYALTY PROGRAM table (LOYALTY PROGRAM MO AGGR) coming from the previously defined CALL CENTER in the LOYALTY PROGRAM CHANNEL entity. The PARTY STATUS HISTORY is changed and some fields of CUSTOMER are updated (for example,
Initiative Number and
After a period as a customer, Tom Daniels's contract and plan ends. Before the contract ends SuperTelco notices that hi is likely to churn, according to the usage and revenue pattern (based on comparisons with the customer segment).
The call center proposes that this customer continue with a new offering:
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-6 and as outlined in the corresponding steps.
Figure B-6 Retention of Terminating Contract Model
The terminating contract and call center retention involves the following steps:
Tom Daniels's churn likelihood increases as the end of the contract 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 PARTY LOYALTY PROGRAM PARTICIPATION).
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 table. For more information, see "Model 1: Churn Prediction", and "Model 3: Customer Churn Factor".
There are usually two possible actions when a contract is due to terminate:
Do nothing: In this case the contract 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 contract 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 contract where the customer may get an offer from a competitor. If the contract ended automatically an action of the Service Provider is required for a renewal.
Assuming that Tom Daniels is contacted then SuperTelco needs to know what to propose. Choices for this contact include the following:
Renew the contract 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 contract with new hardware and a discount if the customer engages for more than twelve months.
For the sample use case with Tom Daniels, contract 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 contract 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.
Note that 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.
From the process perspective this use case is similar to the targeted promotion as described in "Sample Business Use Case: Targeted Promotion for Video-on-Demand Services" with similar entities and similar changes. After the customer accepts the new offer, a new CONTRACT is setup. In addition to the new CONTRACT Tom Daniels may be granted a gift option. For example, the new contract offer might include a new handset or a one month data service free pass. How the customer decides to pick up the gift is tracked in EVENT GIFT REDEMPTION.
Additionally to the party interaction, a non network event is stored in the table EVENT LOYALTY PROGRAM REDEMPTION to contain the free handset information. The free handset comes out of the association with the GIVE AWAY TYPE table assigned from the corresponding market plan (in PRODUCT MARKET PLAN ASSIGNMENT table). The handset itself is in the ITEM table.
In addition, to provide information on the kind of handsets Tom Daniels could afford, use the table REDEMPTION MO AGGR.
Note that if Tom Daniels was not a member of a loyalty program a similar offer could be available; the interaction for this handset offering would be stored into the EVENT GIFT REDEMPTION table.
This use case expands the details for customer information, as described in the section, "Sample Business Use Case: Acquiring a New Customer (with Family Plan)". This use case provides details for how sales information from a dealer is stored. Recall that in the use case in "Sample Business Use Case: Acquiring a New Customer (with Family Plan)", 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, then, consider the commissions and costs spending for dealers and for a 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-7 and as outlined in the corresponding steps.
Figure B-7 Dealer and Employee Sales Commission Model
The information for the customer and account setup is described in "Sample Business Use Case: Acquiring a New Customer (with Family Plan)".
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".
An organization structure or a relationship to individuals.
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.
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.
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, a contract is settled with four activations, four handsets (ITEMs) and probably five products (one per mobile and the shared Friends and Family offering (even if there is only one contract). This has the following consequences in Oracle Communications Data Model:
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.
John Dealer "costs" increase correspondingly, as he wins a percentage of the generated revenue.
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.
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 DAY DRVD and SALES MONTH AGGR).
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. Note that often at this stage a fraud detection mechanism is applied to limit dealer or customer fraud.
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. Whereas the customer cost may be increased by the cost of the call center agent interaction only (assuming the SMS sent to Tom Daniels is not considered). Note that the fact that Tom Daniels changes his package will probably impact the Band ARPU that would also change the bonus for John Dealer.
As Tom Daniels's contract 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 contract renewal, is increasing even if the relative margin will probably decrease over the month.
At each end of month when the sales agent commissions are paid by payroll, the information in SALES COMMISSION PAYROLL is populated.
Sometimes certain dealers may commit fraud when bringing in new customers. For example, a dealer may have friends sign contracts to win a gift but then terminate the contract. The new customers brought in by the fraudulent dealer may be identified by SUBSCRIBER CHURN STATISTIC MONTH DRVD. In this derived table some statistical functions are applied to find a high churn rate by a possibly cheating DEALER, compared to all other dealers.
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 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.
SuperTelco uses Oracle Communications Data Model to handle a network fault, as shown in Figure B-8 and as outlined in the corresponding steps.
Figure B-8 Handling a Network Fault Model
Consider the case and steps required to handle a network fault:
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 the repair team four hours to repair and restart the network cell.
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.
Up to this stage, Oracle Communications Data Model does not play a role. Assume 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.
The SuperTelco manager gets the network fault information in real-time from the network applications team.
The first questions for the manager after identifying the issue are:
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?
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 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 NETWORK ELEMENTs, all the faulted network elements are tracked in NETWORK ELEMENT FAULT ASSIGNMENT.
Each occurrence of a network failure is recorded in NETWORK FAULT. When a network fault happens at customer site, technical support activities to solve the problem are saved in the CUSTOMER FIELD SUPPORT when loaded into Oracle Communications Data Model. The CUSTOMER FIELD SUPPORT entity is a subtype of CUSTOMER FIELD SERVICE ACTIVITY.
Once the network fault is resolved the resolution type of the network fault is loaded according to FAULT RESOLUTION TYPE.
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 of NETWORK ELEMENT. A simple report showing the affected areas and services also lists the services associated with the cell.
You can obtain a list of impacted customers through the NETWORK FAULT SUBSCRIPTION ASSIGNMENT, which links the network fault to the 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.
Similarly, the exact list of products impacted, per customer, can easily be provided (related to the service that is down and the subscriptions concerned).
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.
When a customer calls the call center, an interaction event is created in EVENT PARTY INTERACTION, with an interaction type of
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.
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.
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).
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).
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:
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:
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.
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.
Design and Development:
ETL (Billing to Oracle Communications Data Model and other DWH to Oracle Communications Data Model).
Logical Data Model and Reports Design Enhancement
Training and Testing:
Scenarii creation and run
Acceptance Testing with some (trained) power-users
Initial / history data load
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.
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).
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:
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:
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.
She uses the ETLs that were feeding the Product and Customer DWHs and adapt them to feed Oracle Communications Data Model directly.
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.
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.
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.
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.