1 Introducing Oracle Communications Data Model

This chapter introduces the Oracle Communications Data Model, which is a standards-based, pre-built approach to communications data warehousing.

This chapter includes the following sections:

Overview of Oracle Communications Data Model

Oracle Communications Data Model is a standards-based, pre-built approach to communications data warehousing enabling a communications company to realize the power of insight more quickly. Oracle Communications Data Model reduces costs for both immediate and on-going operations by leveraging out-of-box Oracle based Data Warehouse and Business Intelligence solutions, making world-class database and business intelligence technology solutions available with a communications specific data model.

Oracle Communications Data Model offers a single-vendor solution package that is tightly integrated with the business intelligence platform. With pre-built data mining, Oracle Online Analytical Processing (Oracle OLAP) and dimensional models, Oracle Communications Data Model provides you with industry-specific metrics and insights that you can act on immediately to improve your bottom line. These business intelligence solution offerings take advantage of Oracle's scalability and reliability, using Oracle's familiar optimization, parallelism, and performance engineering within the database.

Oracle Communications Data Model can be used in any application environment and is easily extendable.

Oracle Communications Data Model includes an exhaustive set of embedded advanced analytics, using Oracle's OLAP and data mining technology. You can take advantage of pre-built and pre-tested solution sets designed by industry experts that deliver relevant insights, are actionable, and aimed at improving both top-line and bottom-line results. You can see summarized, aggregated information or quickly navigate to drill-down transaction details to better understand business issues.

For example, with Oracle Communications Data Model's out-of-the-box reports, you can generate reports for network analysis and churn analysis. Network analysis provides air-time, subscription, roaming, load@busy hour, under utilization and patterns reports. With churn analysis you can gain improved insight into churning that provides switching and termination trends, payment and recharging patterns, subscribers life cycle and profiling. You can add your own reports as well. Oracle Communications Data Model, combined with Oracle technology, provides all of the components required for a complete and extendable Communications Data Warehouse and Business Intelligence framework to eliminate complex and costly integration requirements, all designed to reduce your total cost of ownership.

What Are the Benefits of Using Oracle Communications Data Model?

With Oracle Communications Data Model, you can jump-start the design and implementation of a telecommunications data warehouse to quickly achieve a positive ROI for your data warehousing and business intelligence project with a predictable implementation effort.

Oracle Communications Data Model provides the following features:

  • Query and Reporting for information: provides extraction of detailed and summary data.

  • OLAP for data analysis: provides summaries, trends, and forecasts.

  • Data Mining for insight and prediction: provides knowledge discovery of hidden patterns and insights.

  • Oracle Communications Data Model is aligned with the TM Forum's Information Framework (SID) Release 12. For more information, see "About TM Forum Information Framework (SID) Alignment".

Oracle Communications Data Model provides an off-the-shelf data warehouse framework that is both adaptable and extendable. Alignment with communications industry standards ensures interoperability with other systems. The pre-built, pretuned data model with intelligent insight into detailed communications and market data, allows you to quickly gain value from your data warehousing effort, supports diverse analytical requirements, and assists in building future analytical applications. Fast, easy and predictable implementation reduces risks and enables you to achieve strategic value more rapidly by eliminating deployment delays and expenses associated with built-from-scratch or proprietary data warehouse solutions.

What Are the Components of Oracle Communications Data Model?

Oracle Communications Data Model includes the following components:

What Oracle Technologies are in Oracle Communications Data Model

Several Oracle technologies are involved in building the infrastructure for telecommunications business intelligence.

Oracle Database with OLAP, Data Mining and Partitioning Option

Oracle Communications Data Model utilizes a complete Oracle technical stack. It leverages the following data warehousing features of the Oracle database: SQL model, compression, partitioning, advanced statistical functions, materialized views, data mining, and online analytical processing (OLAP).


To achieve cost-effective scalability, availability, and reliability, you can consider using Oracle Real Application Clusters (Oracle RAC) and commodity hardware.

Oracle Development Tools

Use the Oracle tools shown in Table 1-1 to customize the predefined logical and physical models provided with Oracle Communications Data Model, or to populate the target relational tables, materialized views, or OLAP cubes.

Table 1-1 Oracle Development Tools Used with Oracle Communications Data Model

Name Use

Oracle SQL Data Modeler

To create the logical model

SQL Developer or SQL*Plus

To create or modify database objects

Analytic Workspace Manager

To populate the target OLAP cubes

Oracle Business Intelligence Suite Enterprise Edition Presentation Tools

Oracle Business Intelligence Suite Enterprise Edition is a comprehensive suite of enterprise Business Intelligence products that delivers a full range of analysis and reporting capabilities. You can use Oracle Business Intelligence Suite Enterprise Edition Answers and Dashboard presentation tools to customize the predefined dashboard reports that are provided with Oracle Communications Data Model.

What is Oracle Communications Data Model

Oracle Communications Data Model leverages several Oracle Database data warehouse and Business Intelligence concepts that need to be clarified to understand the structure and use of Oracle Communications Data Model.

Oracle Communications Data Model provides "One Single True Vision of the Business". This unique architecture provides the Communications Service Provider (CSP) Flexibility, Agility, Scalability and Accuracy to obtain a real competitive advantage.

A typical enterprise data warehouse architecture, as shown in Figure 1-1, is composed of several layers ordered by the growing actionable value of the information in the warehouse:

  • The Data Source layer (operational systems, Commercial-Off-The_shelf solution, unstructured and syndicated data, with possibly a Master Data Management system).

  • The Staging layer: Typically used for transformation and data cleansing. It is also sometimes used as Operational Data Store, in particular for real-time operational reporting.

  • The Foundation layer: It is typically used to store all transactions and reference data at the most atomic level. Best practices require that this level is 3rd normal form, to avoid data redundancy.

  • The Access and Performance or Analytical layer: this is the layer optimized for the business end-users. It usually contains the star schema to answer business questions, and OLAP tools, and mining models.

  • The Information (or Information Access) layer: This is the metadata layer and above, accessed by end-users through their Business Intelligence or reporting tools, or even external analytical tools (other OLAP or Mining tools). This layer is usually changeable by normal end-users (within their roles and responsibility). This is where the performance management applications provide their reports, where user roles, alerts, guided analytics, dashboards and reports are defined (usually by a specific BI administrator).

  • The data movement from one layer to the other runs through ETL / ELT tools. One distinguishes the standard ETL/ELT (from data sources to foundation layer) from the intra-ETLs (from foundation layer up to the reporting).

Figure 1-1 Data Warehouse Reference Architecture with Oracle Communications Data Model (Green)

Description of Figure 1-1 follows
Description of "Figure 1-1 Data Warehouse Reference Architecture with Oracle Communications Data Model (Green)"

Within a standard enterprise data warehouse architecture, as shown in Figure 1-1, if an adapter is used, for example the NCC Adapter or the BRM Adapter, the Staging area is also provided. Oracle Communications Data Model covers Foundation Layer, plus the intra-ETL part, and includes parts of the reporting area if OBIEE is used (Oracle Communications Data Model also includes the pre-built OBIEE repository).

To summarize Oracle Communications Data Model includes the parts shown in Figure 1-2.

Figure 1-2 Oracle Communications Data Model Inner Structure

Description of Figure 1-2 follows
Description of "Figure 1-2 Oracle Communications Data Model Inner Structure"

The Oracle Communications Data Model Foundation Layer (FDL) is composed of the components shown in Table 1-2.

Table 1-2 Oracle Communications Data Model Foundation Layer Components

Component Usage

Reference entities and tables

  • Used to store master reference entities required by a service provider's operation

  • Non-changing infrequently-changing data

  • These entities translate into dimensions and hierarchies

  • Physically, table names start with "DWR_".

Base entities and tables

  • They store transactions from systems of record (CRM, Billing, OSS, and so on).

  • It contains data at atomic level with the lowest level of granularity possible

  • Required to perform detailed analysis, and uncovering causal effects and associations

  • Physically, table names start with "DWB_"

Lookup entities and tables

  • Hold descriptions for common code lookups (for example, plan type, reason code, and so on).

  • Their goal is to save space since one does not have to store long descriptions in each transaction record.

  • Physically, table names start with "DWL_"

Control tables

  • These are only used and filled by the intra-ETLs.

  • Physically, tables names start with "DWC_"

The Analytic Layer serves as an abstraction layer to simplify analytical access; this layer is a subject oriented representation of data ("shellfish" model). The analytic layer is easily understood by end-users and is simpler to navigate. This layer consists of aggregates, summaries, hierarchical relationships, and so on. The analytic layer is composed of star schemas, materialized views, OLAP cubes, and so on and is populated using intra-ETL processes from data in the Foundation Data Layer (FDL). The Oracle Communications Data Model Analytic layer is composed of the components shown in Table 1-3.

Table 1-3 Oracle Communications Data Model Analytic Layer Components

Component Usage

DERIVED entities and tables

Provide a transition level to STARs. This layer is denormalized and is typically used for operational reporting and data mining, to uncover new insights and predict the future and:

  • Provides information that can only be derived from base data, usually at day level.

  • Includes information such as churn factors, profiling and prediction, congestion or under utilization, and so on.

  • Leverages data mining, advanced statistics, and complex queries.

  • Physically, tables start with "DWD_".

Examples of derived tables include: Account Debt per Day, Account Payments per Day, Call Center Calls per Day, Commissions per Day, Connections/Disconnections Day, Costs - Customer & Organizational, Customer Mining, Market Share per Month, Network Availability per Day, Sales Campaign Summary, Sales Representative Statistics, and so on.

There are also six mining models at this level: Customer profiling/segmentation, Lifetime value prediction, Customer sentiment, Churn prediction, Important churn factors, Cross-sell opportunity.

AGGREGATE entities and tables

Provide information to analyze and summarize, usually at the monthly level and:

  • Leverages base and derived data models to provide aggregated data such as summaries, averages, and so on.

  • Enables dimensional analysis on wide variety of subject areas.

  • Leverages Oracle OLAP cubes (pre-built OLAP cubes are available. For more information, see Chapter 9, "Oracle Communications Data Model OLAP Model Cubes".

  • Contains tables starting with "DWA_"; usually materialized views.

  • Represents the information access layer: It covers all the metadata.

About Business Areas and Subject Areas in Oracle Communications Data Model

A Business Area is a broad slice through Oracle Communications Data Model grouping where all tables that cover the associated business processes (reports, metadata, Mining, OLAP, 3NF) are all accessible through the same GUI (if OBIEE is used). A business area is a conceptual grouping, used at the default report level. The reports are organized by related subject areas gathered in "business areas". Table 1-4 lists the Oracle Communications Data Model business areas.

Figure 1-3 represents a Business Area: a broad slice through Oracle Communications Data Model covering all the entities and mapped business processes associated with the business area.

A Subject Area is a thin slice through Oracle Communications Data Model grouping all tables, mainly at the foundation layer, that cover a specific (logical) concept, business process or question. For example, the subject area PARTY defines the notion of a "PARTY". The "Individual" and "Organization" are both a subset of PARTY. The CUSTOMER, OPERATOR, and VENDOR are example of Party types. At the opposite of this abstract subject area, the subject area CALL CENTER, with pre-built aggregates provided in Oracle Communications Data Model on top of the foundation layer covers all the customer interactions that are done through a call center, whether inquiry, complaints, or change requests.

From an implementation perspective, Oracle Communications Data Model can be filled by subject or business area, without taking care of having to feed all tables in order to have tangible and usable results.

After filling all reports of a given business area with data, this does not mean that the whole business area is covered. Feeding all the tables needed to have all reports of a given business area probably also feeds some reports of other business areas. For example, some PRODUCT, COST and COLLECTION AGENCY entities are required in the Business Area Revenue (for the Revenue OLAP cube). This also partly covers the Product Management, Cost and Contribution, as well as the Partner Management business areas.

Figure 1-3 Business Area: A broad Slice through Oracle Communications Data Model

Description of Figure 1-3 follows
Description of "Figure 1-3 Business Area: A broad Slice through Oracle Communications Data Model"

About the Logical Data Model and Physical Data Model

A logical data model describes how to store information that defines business processes. The logical data model is an interface between business and technical staff, and allows these groups to provide a common understanding of business data elements and requirements.

The logical data model also provides the foundation for designing an Enterprise Data Warehouse. In Oracle Communications Data Model, the logical data model is designed to avoid data redundancy, as much as possible, without impacting performance, and thus prevent data and business transaction inconsistency. The idea is to facilitate data re-use and sharing, hence reducing development and maintenance cycle and cost.

The logical data model is a single source for the model definition, with its own naming conventions that are valid for both business and IT.

In describing the business processes independently of the data sources and the technology, the logical data model clarifies the functional specifications, while avoiding (unnecessary) assumptions.

This implies that, in principle, the logical data model of Oracle Communications Data Model could work on any platform. However, on top of the fact that it would not be supported by Oracle, such an implementation would not benefit from all the pre-built pre-integrated technologies leveraged with Oracle Communications Data Model, in particular in the analytical layer, such as Partitioning, OLAP, Mining models, and so on.

The Oracle Communications Data Model physical data model is the concrete implementation of the logical data model. It is fully technology dependent. The physical data model transforms business relationships into keys or indexes. It takes into account the infrastructure and technology to optimize the performance for end-users. The physical data model has its own naming convention in parallel to the one of the logical data model. Looking at the physical data model, one should be able to "build-back" the logical data model from the entity relationship, even if one could not have all the key understanding of a business process behind, unless one knows the business.

About Entity Relationships in Oracle Communications Data Model

A relationship between two entities should exist in the model only if there is a direct (business) relationship between those entities. You can categorize the relationships as:

  • Description or added information: (typically for Lookup tables) An entity contains codes that describes or validates the various values that an attribute of the original entity possesses. Physically, the two entities are related through a Foreign Key.

  • Direct relationship: Typically, when two entities are related from a business point of view, the model needs to make sure this relationship is explicitly present and described. A direct relationship contains a direct business link between the entities (typically serves, uses, owns, and so on). There must be a distinction between clear 1 to many (1:n) or 0 to many (0:n), and many to many (n:m) relationship.

    • 0:n or 1:n relationships: typically business types like "owns", "has got", "serves", "uses"… It is usually directly linked to an attribute (like description), and may be a foreign key link.

    • m:n relationships: If the relationship can be "many to many", use an "Assignment" Entity between the entities to transform this m:n relationship in m:1 (or 0) and (0 or) 1:n.


Very often, two rows of a given entity (say "ENTITY") have to be related with one another. Most of the time, Oracle Communications Data Model uses a table named "ENTITY ASSIGNMENT".

For example: PARTY and PARTY ASSIGNMENT, as shown in Figure 1-4.

Figure 1-4 PARTY and PARTY ASSIGNMENT Entities

Description of Figure 1-4 follows
Description of "Figure 1-4 PARTY and PARTY ASSIGNMENT Entities"

PARTY ASSIGNMENT represents the relationship between two parties uniquely identified in Oracle Communications Data Model, whatever the role they play within the model: As Customer, Employee, dealer or even all three for the same individual!

In Party, the PARTY TYPE is a "person", an "organization", or an "organization business unit". It is not "Customer". This is a Party role. A given Party can have several roles which are "chosen" depending on the type of business interaction that takes place. However, the type never changes.

The only exception to this rule is with ADDRESS LOCATION. One uses the "ADDRESS RELATED" table, for example to feed the fact that an alternative billing address has been given by customer when the first one fails or because he is in holiday.

Understanding Named and Flexible Hierarchies

A named hierarchy is a pre-built hierarchy of general interest, usually used/seen in the market, with fixed levels (with a specific name for each).

A flexible hierarchy is a hierarchical structure that is freely definable: for levels, attributes per level, relationships and numbers of hierarchies (for the same base entity) with various possible versions.

These hierarchies do the following:

  • Follow Slowly Changing dimension Type II rules.

  • Have the same leaf level (Organization Business Unit for "Organization" and Address Location for "Geography")

  • Have pre-built tools to feed/change them easily (for implementation team).

  • Can be associated in parallel (for example, In an Organization, several hierarchies can be defined: Administrative Hierarchy and Sales Hierarchy)

ORGANIZATION BUSINESS UNIT refers to lowest-level internal business unit of the organization that delivers a limited range of specific communications services or merchandise through any sales channel (website, store, and so on), as shown in Figure 1-5.

Figure 1-5 Organization Business Unit Entity

Description of Figure 1-5 follows
Description of "Figure 1-5 Organization Business Unit Entity"

ORGANIZATION BUSINESS ENTITY refers to any internal logical entity that is recognized as a part of the enterprise for business analysis and transactions. Classification for a business entity can include company, operation unit, store, or warehouse. This is part of "Flexible Hierarchy" of organization.

Address Related is the only exception in Oracle Communications Data Model to relationship between the same entity.

Calendar and Time in Oracle Communications Data Model

Oracle Communications Data Model includes five pre-defined calendars:

  • Business: allows to define operator's own Calendar according to its business operation.)

  • Gregorian: Standard 365-days calendar

  • Fiscal: Follows Accounting or Legal Requirements

  • Ad: Follows Ad Cycle

  • Planning: Planning cycle calendar

In Oracle Communications Data Model, the business calendar is by default the same as the natural calendar (=Gregorian), since most of operators run billing process monthly according to natural calendar. Business calendar can be modified according to different business operations.

A flexible calendar script can populate the calendar based on input parameters.

Time Transformation

A Time Transformation does the following:

  • Relates the elements of time-based attributes to other elements of the same attribute and specify the relationship between elements for some time-based frame of reference.

  • Supports both "one-to-one" and "many to many" Transformation …

    • For every element in the table, there is one corresponding element for the time frame in question (for example, current week to the same week last year).

    • Supports "many-to-many" transformations for calculating year-to-date, season-to-date and similar totals. These tables specify all of the elements that are to be included in calculating a total from a given reference point.

  • Time of Day allows granularity to the Quarter-hour level.

  • Year-to-date transformation specifies all of the days or weeks that are included in the transformation from a given day or week since the beginning of the year.

Product and Product Instance: In Product Management and Provisioning Business Area

PRODUCT: is what customer can get. It composes the offering:


  • PRODUCT may have valid equipment functionality and versions.

  • PRODUCT may be particularly offered only locally or in a limited region.

PRODUCT INSTANCE: represents the real instance of a given PRODUCT that a customer can purchase or rent. For example:

  • Specified Song Corresponding to Product MUSIC DOWNLOAD

  • TV channel Corresponding to Product PAY TV

  • Product Instance could also be a physical instance of Equipment which customer can leverage to access the service from operator. It could be used for inventory management. For example:

    • Handset (with IMEI)

    • Land line phone (with serial number)

    • Set-top box

    • Cable modem

Concept: Business Interaction / Events in All business areas

Business Interaction: "an arrangement, contract, or communication between an enterprise and one or more other entities such as individuals and organizations (or parts of organizations). Interactions take on the form of requests, responses, and notifications". (TMF-SID definition)

Event: an interaction of any kind between at least two parties. There are two types of events:

  • "Network" Event: A Call Data Record or a Traffic event on the network made by a customer, a partner, or someone else calling the customer (but not originated from the CSP itself)

  • "Non-Network" Event: all other (business) interactions:

    • Customer interaction with the call center, the web interface…,

    • SLA with partners

    • Interaction between Mediation and Order Management System

Scenario 2: Product and Subscription

For a Standard Offer with a two hundred dollar monthly fee that includes three products for customer, as in Figure 1-6.

Figure 1-6 Product and Subscription Offer for Customer A

Description of Figure 1-6 follows
Description of "Figure 1-6 Product and Subscription Offer for Customer A"

Figure 1-7 shows this relationship in Oracle Communications Data Model.

Figure 1-7 Product and Subscription Scenario

Description of Figure 1-7 follows
Description of "Figure 1-7 Product and Subscription Scenario"

About TM Forum Information Framework (SID) Alignment

Oracle Communications Data Model is aligned with TM Forum's Information Framework (SID) Release 12. The TM Forum is the world's leading industry association focused on enabling best-in-class IT for service providers in the communications, media, and cloud service markets. The TM Forum provides business-critical industry standards and expertise to enable the creation, delivery, and monetization of digital services. For more information on TM Forum, see


The TM Forum's Information Framework (SID) provides a common reference model for enterprise information in the communications industry. The SID model does the following:

  • Attempts to cover all information required in a Service Provider's operations.

  • Provides an information reference model and a common vocabulary.

  • Consists of business entities and their associated attribute definitions. Business entities describe items of interest to the business. For example, customer order, product offering, service specification, and so on. The attributes are facts that describe a business entity.

Business Areas in Oracle Communications Data Model

A Business Area is a broad slice through Oracle Communications Data Model grouping where all tables that cover the associated business processes (reports, metadata, Mining, OLAP, 3NF) are all accessible through the same GUI (if OBIEE is used). A business area is a conceptual grouping used at the default report level. The reports are organized by related subject areas gathered in "business areas".

Table 1-4 lists the Oracle Communications Data Model business areas.

Table 1-4 Business Areas

Business Area Description

Customer Management

Covers the complete customer lifecycle, from acquisition to churn, including the customer interaction. This area also contains the notion of account (shared with revenue), contract, subscription, and so on.


Covers all revenue sources (pre-paid/postpaid, equipment rental, or sales), the sales process, the debt, payment and refund/adjustment processes. This area is the accounting view of a customer or the sales process.

Product Management

Covers the complete product lifecycle, from creation to drop-off

Provisioning and Activation

Covers the complete order management and installation process. This area is also the place for a number portability, trouble ticketing (shared with Customer Management) and SLA management item


Covers all the network related subject areas that are not provisioning. In this area, the complete network of the Service Provider can be described and analyzed. This covers network usage, and network health


Covers all the loyalty, campaign, and promotion processes with the notion of prospect and contact list, as well as market share (common with Customer management)

Cost and Contributions

Is the financial perspective of the business, with all the costs associated with running a communications service provider business, whether as operator, MVNO, or simple content provider. This area intersects with all other business areas, but limits itself to cost and profitability measurements

Partner Management

Covers all types of partners, whether interconnection or roaming operators, content providers, dealers (sales), suppliers, external debt collection agencies, and so on