Customizing the Oracle Communications Data Model Physical Model

The starting point for the Oracle Communications Data Model physical data model is the 3NF logical data model.

The physical data model mirrors the logical model as much as possible, although some changes in the structure of the tables or columns may be necessary, and defines database objects (such as tables, cubes, and views).

To customize the default physical model of the Oracle Communications Data Model perform the following steps:

Use the information presented in General Recommendations When Designing Physical Structures to guide you when designing the physical objects.

  1. Answer the questions outlined in "Questions to Answer Before You Customize the Physical Model".
  2. Familiarize yourself with the characteristics of the logical and physical model of Oracle Communications Data Model.
  3. Modify the foundation level of your physical model of Oracle Communications Data Model, as needed.

    When defining physical structures:

    • Keep the foundation layer in 3NF form.

    • Follow the conventions used when creating the default physical model of Oracle Communications Data Model .

    Tip:

    Package the changes you make to the physical data model as a patch to the ocdm_sys schema.

  4. Modify the access layer of your physical model of Oracle Communications Data Model.
  5. Modify existing or create a new Intra-ETL packages to feed the changes you make in the access layer.
  • Questions to Answer Before You Customize the Physical Model
    When designing the physical model, remember that the logical data model is not one-to-one with the physical data model. Consider the load, query, and maintenance requirements when you convert the logical data model into the physical layer.
  • Conventions When Customizing the Physical Model
    When developing the physical model for Oracle Communications Data Model, the conventions outlined below were followed. Continue to follow these conventions as you customize the physical model.

Related Topics

Questions to Answer Before You Customize the Physical Model

When designing the physical model, remember that the logical data model is not one-to-one with the physical data model. Consider the load, query, and maintenance requirements when you convert the logical data model into the physical layer.

For example, answer the following questions before you design the physical data model:

  • Do you need the physical data model to cover the full scope of the logical data model, or only part of the scope?

    Common Change Scenarios provides an overview discussion of making physical data model changes when your business needs do not result in a logical model that is the same as the Oracle Communications Data Model logical model.

  • What is the result of the source data profile?

  • What is the data load frequency for each table?

  • How many large tables are there and which tables are these?

  • How will the tables and columns be accessed? What are the common joins?

  • What is your data backup strategy?

Conventions When Customizing the Physical Model

When developing the physical model for Oracle Communications Data Model, the conventions outlined below were followed. Continue to follow these conventions as you customize the physical model.

General Naming Conventions for Physical Objects

Follow these guidelines for naming physical objects that you define:

  • When naming the physical objects follow the naming guidelines for naming objects within an Oracle Database schema. For example:

    • Table and column names must start with a letter, can use only 30 alphanumeric characters or less, cannot contain spaces or some special characters such as "!" and cannot use reserved words.

    • Table names must be unique within a schema that is shared with views and synonyms.

    • Column names must be unique within a table.

  • Although it is common to use abbreviations in the physical modeling stage, as much as possible, use names for the physical objects that correspond to the names of the entities in the logical model. Use consistent abbreviations to avoid programmer and user confusion.

  • When naming columns, use short names if possible. Short column names reduce the time required for SQL command parsing.

  • The ocdm_sys schema uses the prefixes and suffixes listed in the following table to identify object types:

    Table 2-1 Default Physical Object Prefixes and Suffixes in Oracle Communications Data Model

    Prefix or Suffix Used for Name of These Objects

    _VIEW

    A relational view of an OLAP cube, dimension, or hierarchy.

    CCB_

    Customized OLAP cubes.

    CUBE

    Created when OLAP cubes are built. Used to store logs and results.

    DM$

    Created when the mining models are trained. Used to store trained model and logs.

    DMV_

    Materialized view created for performance reasons (that is, not an aggregate table or an OLAP cube).

    DR$

    Created when the mining models are trained. Used to store trained model and logs.

    DWA_

    Aggregate tables which are materialized views.

    DWB_

    Base transaction data (3NF) tables.

    DWC_

    Control tables.

    DWD_

    Derived tables -- including data mining result tables.

    DWL_

    Lookup tables.

    DWR_

    Reference data tables.

    DWV_

    Relational view of time dimension

Domain Definition Standards

A domain is a set of values allowed for a column. The domain can be enforced by a foreign key, check constraints, or the application on top of the database. Define the standards for each domain across the model such as:

  • Date and time type, such as 'YYYY-MM-DD'. For example, be aware that most date columns (abbreviation DT) in Oracle Communications Data Model may contain the time, such as EVT_STRT_DT. There is no separate TIME column.

  • Numeric value in different situations. For example, all columns of type COUNT are NUMBER(16,0) while all monetary-like columns (AMOUNT) are NUMBER(22,7).

  • Character string length in different situations. For example, all Code columns are VARCHAR2(120), Name (NAME) and Description columns (DSCR) are respectively 500 and 1000 characters long (with some exceptions). Indicator columns (IND) are CHAR(1).

  • Coded value definition such as key or description. For example, all "Key" columns are NUMBER(30).

Related Topics