Implementation and Operations Guide
Release 11.3.1
E28442-04
September 2012
Oracle Communications Data Model Implementation and Operations Guide, Release 11.3.1
E28442-04
Copyright © 2011, 2012, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
This appendix provides information about sizing and configuring an Oracle Communications Data Model warehouse. It contains the following topics:
Businesses now demand more information sooner and are delivering analytics from their Enterprise Data Warehouse (EDW) to an ever-widening set of users and applications. In order to keep up with this increase in demand the EDW must now be near real-time and be highly available. Regardless of the design or implementation of a data warehouse the initial key to good performance lies in the hardware configuration used. This has never been more evident than with the recent increase in the number of data warehouse appliances in the market.
But how do you go about sizing such a system? You must first understand how much throughput capacity is required for your system and how much throughput each individual CPU or core in your configuration can drive, thus the number one task is to calculate the database space requirement in your data warehouse.
There are two data volume estimate resources in a data warehouse environment:
The estimated raw data extract from source systems. This estimate affects the ETL system configuration and the stage layer database space in data warehouse system. Because this value is determined by your unique OLTP system, you must calculate this information yourself.
The space needed for data stored to support the objects defined in the default Oracle Communications Data Model schema. This appendix provides information you can use to make this calculation.
Calculation Factors When Making a Data Volume Calculation for an Oracle Communications Data Model Warehouse
Consider the following calculation factors when making a data volume calculation:
Calculates data unit volume within different type:
Reference and lookup tables data. Assume this data is permanently stored.
Base tables data (transaction data). Assume that this data is stored within its life cycle.
Star schema (derived and summary). Assume that this data is stored within its life cycle.
Calculate each type of data retention.
Define how many months or years of each type of tables to retain.
Calculate data growth.
Assume that annual growth rate: applies to both transaction and reference data and data in the star schema.
Assume that annual change rate applies only to reference data.
Calculate Staging Area data requirements, if proposed.
|
Tip: Multiply ETL volume by day by number of days held for problem resolution and re-run of transform with new extract from source systems. |
Calculate data volume for indexes, temporary tables, and transaction logs.
Calculate the space requirement for business intelligence tools, such as cubes, and data mining.
Consider the redo log and Oracle ASM space requirement.
Consider the RAID architecture [RAID 1, 0+1, 5]
Consider the backup strategy.
Consider the compress factor if applied.
Consider the OS and file system disk space requirements.
Formula to Determine Minimum Disk Space Requirements for an Oracle Communications Data Model Warehouse
Use the following formula, based on the factors outlined in "Calculation Factors When Making a Data Volume Calculation for an Oracle Communications Data Model Warehouse", to determine the minimum disk space requirements for an Oracle Communications Data Model warehouse.
Disk Space Minimum Requirements = Raw data size * Database space factor * (1+GrthperY)nY*OS and File system factor * Compress Factor * Storage Redundant factor
where:
Raw data size = (reference and lookup data per year + base/transaction data per year + derived and summary data per year +staging data +other data(OLAP/Data Mining))
Database space factor = Indexes + Temporary Tables + Logs]
GrthperY = growth rate per year
OS and File system factor is the install and configuration and maintain space for OS and DB
Redundant factor= ASM disk space and RAID factor. [ RAID 1=2, RAID 5=1.25 or 1.33 ]
Compress factor depends how you apply the compress function. If you are executing on an Exadata Database machine, it has a huge savings in disk space by using compression.
Many data warehouse operations are based upon large table scans and other I/O-intensive operations, which perform vast quantities of random I/Os. In order to achieve optimal performance the hardware configuration must be sized end to end to sustain this level of throughput. This type of hardware configuration is called a balanced system. In a balanced system all components - from the CPU to the disks - are orchestrated to work together to guarantee the maximum possible I/O throughput. I/O performance is always a key consideration for data warehouse designers and administrators. The typical workload in a data warehouse is especially I/O intensive, with operations such as large data loads and index builds, creation of materialized views, and queries over large volumes of data. Design the underlying I/O system for a data warehouse to meet these heavy requirements.
To create a balanced system, answer the following questions:
How many CPUs are required? What speed is required?
What amount of memory is required? Data warehouse do not have the same memory requirements as mission-critical OLTP applications?
How many I/O bandwidth components are required? What is the desired I/O speed?
Each component must be able to provide sufficient I/O bandwidth to ensure a well-balanced I/O system.
The following topics provide more information about configuring a balanced system for Oracle Communications Data Model:
Maintaining High Throughput in an Oracle Communications Data Model Warehouse
Configuring I/O in an Oracle Communications Data Model for Bandwidth not Capacity
Planning for Growth of Your Oracle Communications Data Model
Balanced Hardware Configuration Guidelines for Oracle Communications Data Model
The hardware configuration and data throughput requirements for a data warehouse are unique mainly because of the sheer size and volume of data. Before you begin sizing the hardware configuration for your data warehouse, estimate the highest throughput requirement to determine whether current or proposed hardware configuration can deliver the necessary performance. When estimating throughput, use the following criteria:
The amount of data accessed by queries during peak time, and the acceptable response time
The amount of data that is loaded within a window of time
Based on the data volume calculated and the highest throughput requirement, you can estimate the I/O throughput along with back-end ETL process and front end business intelligence applications by time unit. Typically, a value of approximately 200MB per second I/O throughput per core is a good planning number for designing a balanced system. All subsequent critical components on the I/O path - the Host Bus Adapters, fiber channel connections, the switch, the controller, and the disks - have to be sized appropriately.
When running a data warehouse on an Oracle Real Application Cluster (Oracle RAC) it is just as important to size the cluster interconnect with the same care and caution you would use for the I/O subsystem throughput.
When configuring the storage subsystem for a data warehouse, it should be simple, efficient, highly available and very scalable. An easy way to achieve this is to apply the S.A.M.E. methodology (Stripe and Mirror Everything). S.A.M.E. can be implemented at the hardware level or by using Oracle ASM (Automatic Storage Management) or by using a combination of both. There are many variables in sizing the I/O systems, but one basic rule of thumb is that the data warehouse system has multiple disks for each CPU (at least two disks for each CPU at a bare minimum) to achieve optimal performance.
A data warehouse designer plans for future growth of a data warehouse. There are several approaches to handling the growth in a system, and the key consideration is to be able to grow the I/O system without compromising on the I/O bandwidth. You cannot, for example, add four disks to an existing system of 20 disks, and grow the database by adding a new tablespace striped across only the four new disks. A better solution would be to add new tablespaces striped across all 24 disks, and over time also convert the existing tablespaces striped across 20 disks to be striped across all 24 disks.
When creating a data warehouse on a new system, test the I/O bandwidth before creating all of the database data files to validate that the expected I/O levels are being achieved. On most operating systems, you can perform the test using simple scripts to measure the performance of reading and writing large test files.
You can reference the follow tips for a balanced hardware configuration:
Total throughput = #cores X 100-200MB (depends on the chip set)
Total host bus adapter (HBA) throughput = Total core throughput
|
Note: If total core throughput is 1.6 GB, you need four 4 Gbit HBAs. |
Use one disk controller per HBA port (throughput capacity must be equal).
Switches must have the capacity as HBAs and disk controllers.
Use a maximum of ten physical disk per controller (that is, use smaller drives: 146 or 300 GB).
Use a minimum of 4 GB of memory per core (8 GB if using compress).
Interconnect bandwidth equals I/O bandwidth (InfiniBand).
Oracle now provides the Oracle Database Machine, Exadata which combines industry-standard hardware from Oracle, Oracle Database 11g Release 2, and Oracle Exadata Storage Server Software to create a faster, more versatile database machine. It's a completely scalable and fault tolerant package for all data management, especially for data warehousing.
Oracle also has a series of Optimized Warehouse Reference configurations that help customers take the risk out of designing and deploying Oracle data warehouses. Using extensive field experience and technical knowledge, Oracle and its hardware partners have developed a choice of data warehouse reference configurations that can support various sizes, user populations and workloads. These configurations are fast, reliable and can easily scale from 500 GB to over 100 TB on single and clustered servers to support tens to thousands of users.
This appendix provides information about populating an Oracle Communications Data Model warehouse using the Oracle Communications Billing and Revenue Management Adapter for Oracle Communications Data Model (BRM Adapter).
This appendix contains the following topics:
What is the BRM Adapter for Oracle Communications Data Model?
Components of BRM Adapter for Oracle Communications Data Model
BRM Adapter for Oracle Communications Data Model Execution Flows
Populating a Warehouse Using the BRM Adapter for Oracle Communications Data Model
For information on installing and configuring the BRM Adapter for Oracle Communications Data Model, see Oracle Communications Data Model Installation Guide.
For detailed information about the objects and ETL associated with the BRM Adapter for Oracle Communications Data Model, see Oracle Communications Data Model Reference.
The Oracle Communications Billing and Revenue Management Adapter for Oracle Communications Data Model (BRM Adapter) is an option to the Oracle Communications Data Model.
The BRM Adapter loads data from an Oracle Communications Billing and Revenue Management source system into Oracle Communications Data Model. You can load data in both an initial and an incremental manner. The data from Oracle Communications Billing and Revenue Management populates the Oracle Communications Data Model derived and aggregate tables, reports, and mining models.
The BRM Adapter for Oracle Communications Data Model uses the following components:
BRM Adapter for Oracle Communications Data Model Database Schemas
Schema Definitions Added for the BRM Adapter for Oracle Communications Data Model
Oracle GoldenGate Replication: used for real time data feed to the staging database
Oracle Communications Data Model
The BRM Adapter for Oracle Communications Data Model loads data from tables listed in Table D-1into Oracle Communications Data Model.
Table D-1 BRM Adapter Source Tables
| BRM Adapter Source Tables |
|---|
|
ACCOUNT_NAMEINFO_T |
|
ACCOUNT_T |
|
BAL_GRP_T |
|
BILL_T |
|
BILLINFO_T |
|
CONFIG_BEID_BALANCES_T |
|
CONFIG_BUSINESS_TYPE_T |
|
CONFIG_CUR_CONV_RATES_T |
|
CONFIG_T |
|
DD_OBJECTS_T |
|
DEAL_PRODUCTS_T |
|
DEAL_T |
|
DISCOUNT_T |
|
EVENT_ACTIVITY_TLCS_T |
|
EVENT_ACTV_TLCS_SVC_CODES_T |
|
EVENT_BAL_IMPACTS_T |
|
EVENT_BILLING_PAYMENT_CASH_T |
|
EVENT_BILLING_PAYMENT_CC_T |
|
EVENT_BILLING_PAYMENT_CHECK_T |
|
EVENT_BILLING_PAYMENT_DD_T |
|
EVENT_BILLING_PAYMENT_FAILED_T |
|
EVENT_BILLING_PAYMENT_PAYORD_T |
|
EVENT_BILLING_PAYMENT_POST_T |
|
EVENT_BILLING_PAYMENT_T |
|
EVENT_BILLING_PAYMENT_WTRAN_T |
|
EVENT_BROADBAND_USAGE_T |
|
EVENT_DLAY_ACTV_TLCS_SVC_CDS_T |
|
EVENT_DLAY_ACTV_TLCS_T |
|
EVENT_DLAY_SESS_TLCS_SVC_CDS_T |
|
EVENT_DLAY_SESS_TLCS_T |
|
EVENT_DLYD_SESSION_TLCO_GPRS_T |
|
EVENT_DLYD_SESSION_TLCO_GSM_T |
|
EVENT_RUM_MAP_T |
|
EVENT_SESS_TLCS_SVC_CODES_T |
|
EVENT_SESSION_DIALUP_T |
|
EVENT_SESSION_TELCO_GPRS_T |
|
EVENT_SESSION_TLCO_GSM_T |
|
EVENT_SESSION_TLCS_T |
|
EVENT_T |
|
EVENT_TAX_JURISDICTIONS_T |
|
IFW_CURRENCY |
|
IFW_TIMEZONE |
|
IFW_USAGETYPE |
|
INVOICE_T |
|
ITEM_T |
|
NOTE_T |
|
PAYINFO_CC_T |
|
PAYINFO_DD_T |
|
PAYINFO_INV_T |
|
PAYINFO_T |
|
PLAN_SERVICES_T |
|
PLAN_T |
|
PRODUCT_T |
|
PURCHASED_DISCOUNT_T |
|
PURCHASED_PRODUCT_T |
|
RATE_PLAN_T |
|
SERVICE_EMAIL_T |
|
SERVICE_T |
|
SERVICE_TELCO_FEATURES_T |
|
SERVICE_TELCO_GPRS_T |
|
SERVICE_TELCO_GSM_T |
The BRM Adapter for Oracle Communications Data Model uses the following Database schemas:
Source schemas the source system is Oracle Communications Billing and Revenue Management and database schemas is BRM_SRC (PIN).
The specific source objects are described in "BRM Source Schema Tables".
Staging layer schema. The BRM Adapter for Oracle Communications Data Model uses a staging area when loading data from the BRM system to foundation layer objects of an Oracle Communications Data Model warehouse. The staging layer is used for changed data and historical data storage and as a source schema to load Oracle Communications Data Model tables.
The BRM Adapter for Oracle Communications Data Model defines the staging as a database schema named brm_stg .
The brm_stg schema is described in "BRM Adapter for Oracle Communications Data Model Staging Schema Objects".
Target schema. The target of the BRM Adapter for Oracle Communications Data Model is an Oracle Communications Data Model warehouse. The specific target objects are the foundation layer objects of the Oracle Communications Data Model. These objects are defined in the ocdm_sys schema which is delivered with Oracle Communications Data Model and documented in Oracle Communications Data Model Reference.
The installation of the BRM Adapter for Oracle Communications Data Model defines additional procedures in the ocdm_sys schema. These procedures are outlined in "BRM Adapter for Oracle Communications Data Model OCDM_SYS Objects".
Installing and configuring the BRM Adapter for Oracle Communications Data Model as described in Oracle Communications Data Model Installation Guide adds the following definitions:
A database schema named brm_stg that defines a staging area for use by the adapter. For information on the definitions in this database, see "BRM Adapter for Oracle Communications Data Model Staging Schema Objects".
Procedure definitions that are used to populate the foundation layer of an Oracle Communications Data Model warehouse. These definitions are added to the ocdm_sys schema delivered with Oracle Communications Data Model. For more information on these procedures, see "BRM Adapter for Oracle Communications Data Model OCDM_SYS Objects".
After the installation of BRM Adapter for Oracle Communications Data Model, a staging schema is created, (brm_stg). The staging schema contains the following tables, views, functions, and procedures.
Verify that these objects are in the respective BRM staging schema (brm_stg).
Staging Schema Tables
Corresponding to each Oracle Communications Billing and Revenue Management source table to be loaded, each of the following tables are created or updated in the staging schema:
Normal Staging Tables (XXX) (the table name is same as source table name)
Previous Day Tables (XXX_LD)
Delta Tables (XXX_D)
Delta History Tables (XXX_D_H)
Golden Gate Option Input Parameter Table (BRM_MAPPING_TAB)
Loading Parameter Table BRM_ETL_PARAMETER (having mainly From Date, To Date and Loading date)
Staging Schema Views
For each Oracle Communications Billing and Revenue Management source system view used in the BRM Adapter for Oracle Communications Data Model, two views are created in staging schema (brm_stg), (XXX_ vw), with the names:
EVENT_BILLING_PAYMENT_T_MAP_VW
SERVICE_T_MAP_VW
Staging Schema Functions
For each Oracle Communications Billing and Revenue Management source system function used in the BRM Adapter for Oracle Communications Data Model (having name UTC_TO_ORACLE) is created in staging schema (brm_stg).
Staging Schema Procedures
To maintain the everyday source data changes and history of the data changes three procedures are created in staging schema (brm_stg) for both Golden Gate and Non-Golden Gate options:
PRE_STAGING_LOAD (For Oracle Golden Gate Option)
PRE_OCDM_LOAD (For Non - Oracle Golden Gate Option)
POST_STAGING_LOAD (For Oracle Golden Gate Option)
Installing and configuring the BRM Adapter for Oracle Communications Data Model adds the following procedure definitions to the ocdm_sys schema delivered with Oracle Communications Data Model creates procedures in ocdm_sys schema to populate the integrated BRM data.
Verify that these objects are in the Oracle Communications Data Model schema (ocdm_sys).
Procedures
DISB_CONS
ENAB_CONS
INSERT_REF
SCD2_UPDATE
The BRM Adapter for Oracle Communications Data Model uses ETL created by Oracle Data Integrator (ODI) as the ETL for performing an initial load of the foundation layer objects of the Oracle Communications Data Model (that is, the base, reference, and lookup tables). You can also use ODI ETL to perform a scheduled refresh of the staging database.
For more information on the use of ODI with the BRM Adapter for Oracle Communications Data Model, see the following topics:
After you have performed an initial load of the foundation layer objects of the Oracle Communications Data Model using ODI, you can use Oracle GoldenGate to perform real-time feed of BRM source data to the staging database.
For more information on the use of Oracle GoldenGate with the BRM Adapter for Oracle Communications Data Model, see the following topics:
The BRM Adapter for Oracle Communications Data Model uses a different execution flow depending on whether you are using the adapter with Oracle GoldenGate:
Execution Flow Using BRM Adapter: ODI Pull Version (without Oracle GoldenGate)
Execution Flow Using BRM Adapter: with Oracle GoldenGate and ODI
You can use the BRM Adapter for Oracle Communications Data Model with Oracle Data Integrator (ODI) alone. This method of using the BRM Adapter for Oracle Communications Data Model is the ODI-pull version.
Figure D-1 illustrates the BRM Adapter for Oracle Communications Data Model execution flow when using the adapter only with ODI (ODI-pull version).
Figure D-1 Execution Flow When Using the Adapter without Oracle GoldenGate
The ODI-pull version gives you the option to load data at regular periods. When using the ODI-pull approach, the execution flow is as follows (as shown in Figure D-1):
(Steps 1 and 3) The BRM Adapter, using ODI, loads the data from the source database into stage database.
(Steps 2 and 5) Using pre and post procedures, the BRM Adapter loads data into several stage database tables; for example, lastday (xxx_ld), delta (_delta), and history tables.
(Step 4 in the diagram) The BRM Adapter loads the delta tables in the staging area using normal table 'MINUS' last day table.
(Step 6 in diagram) The BRM Adapter loads the data from the delta tables into foundation layer of the Oracle Communications Data Model warehouse.
When you use the BRM Adapter for Oracle Communications Data Model with Oracle GoldenGate to refresh the foundation layer of the Oracle Communications Data Model warehouse, this method allows the foundation layer data to be synchronized with the BRM source data.
Figure D-2 illustrates the BRM Adapter for Oracle Communications Data Model execution flow when using the BRM Adapter with Oracle GoldenGate.
Figure D-2 Execution Flow When Using the BRM Adapter with Oracle GoldenGate
When using Oracle GoldenGate with the BRM Adapter for Oracle Communications Data Model, the execution flow is as follows (as shown in Figure D-2):
Assuming that all Oracle GoldenGate processes for the BRM Adapter for Oracle Communications Data Model are running, whenever new data is placed in the source database, Oracle GoldenGate extracts the new incoming data and uses it to populate the staging tables.
Steps 2 and 3 in Figure D-2 (Staging area): in this part of the execution process you can hold all the changed data including the current and the historical data. The procedure STG_OCDM_OGG_CDC_LOAD_SCENERIO_PKG handles this process; this runs after the normal tables are updated by the Oracle GoldenGate extract process.
|
Note: Only the data in the delta history tables remains forever and is always in sync with source data. Once the data in the delta staging tables is loaded into the foundation layer of the Oracle Communications Data Model warehouse, the delta tables are truncated. |
The final step Step 4 in Figure D-2 is to load data from the Delta tables to the foundation layer of Oracle Communications Data Model; to do this use the ODI package STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG which is dependent on STG_OCDM_OGG_CDC_LOAD_PKG.
After the initial installation, if you are planning to use Oracle GoldenGate for incremental loads, you should first use the ODI-pull procedures described in "Execution Flow Using BRM Adapter: ODI Pull Version (without Oracle GoldenGate)" to load data from the source system to the staging system and from the staging system to Oracle Communications Data Model. After the initial load, you can then setup the incremental load to keep the Oracle Communications Data Model data synchronized with the Oracle Communications Billing and Revenue Management source system, as described in "Refreshing the Foundation Layer with Real-Time Data".
You use the BRM Adapter for Oracle Communications Data Model to populate or refresh the foundation layer of the Oracle Communications Data Model warehouse (that is, the base, reference, and lookup tables defined in the ocdm_sys schema). For more information, see:
After you use the BRM Adapter for Oracle Communications Data Model to populate the foundation layer, you populate or refresh the access layer of the Oracle Communications Data Model (that is, the derived tables, aggregate tables, OLAP cubes, and data mining models defined in the ocdm_sys schema) in the same manner as you would if you were not using an application adapter for Oracle Communications Data Model to populate the foundation layer. For more information, see "Performing an Initial Load of the Access Layer" and "Refreshing the Access Layer of an Oracle Communications Data Model Warehouse".
To perform an initial load of the foundation layer of an Oracle Communications Data Model warehouse using the BRM Adapter for Oracle Communications Data Model with Oracle Data Integrator (ODI), take the following steps:
Verify that the installation and configuration created the schema objects described in "Schema Definitions Added for the BRM Adapter for Oracle Communications Data Model".
|
See: Oracle Communications Data Model Installation Guide for instructions on installing and configuring the BRM Adapter for Oracle Communications Data Model for use by ODI. |
Setting the BRM_ETL_PARAMETER:
Connect to the staging schema (for example: brm_stg).
Delete data from the table BRM_ETL_PARAMETER, then set the values:
FROM_DATE_ETL=01/01/1900; TO_DATE_ETL=current time of BRM system; LOAD_DT=sysdate
For example:
delete from brm_etl_parameter;
commit;
Insert into BRM_ETL_PARAMETER
(PROCESS_NAME, FROM_DATE_ETL, TO_DATE_ETL, LOAD_DT)
Values ('BRM-ADAPTER', TO_DATE('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/22/2012 10:28:55', 'MM/DD/YYYY HH24:MI:SS'), sysdate);
commit;
Note: you can obtain the current Oracle Communications Billing and Revenue Management time by pvt (pin_virtual_time).
From ODI Studio Designer, go to the BRM_OCDM project, then take the following steps:
Select SRC_STG_NONOGG, then Packages, and then, select and execute the following package:
SRC_STG_LOAD_NON_OGG_PKG
Select STG_OCDM, then Packages, and then, select and execute the following package:
STG_OCDM_INITIAL_LOAD_PKG
|
See: "Execution Flow Using BRM Adapter: ODI Pull Version (without Oracle GoldenGate)" for a description of the execution flow for the initial load. |
Once you have performed an initial data load of an Oracle Communications Data Model warehouse, when you need to refresh the data, you use ODI to refresh all of the data in the staging area and in the foundation layer of the Oracle Communications Data Model warehouse.
If you are not using the BRM Adapter for Oracle Communications Data Model with Oracle GoldenGate to populate the foundation layer of an Oracle Communications Data Model warehouse, then you refresh the foundation layer on a scheduled basis using Oracle Data Integrator (ODI).
To refresh the data in the foundation layer using only ODI, take the following steps:
Verify that the installation and configuration created the schema objects described in "Schema Definitions Added for the BRM Adapter for Oracle Communications Data Model".
|
See: Oracle Communications Data Model Installation Guide for instructions on installing and configuring the BRM Adapter for Oracle Communications Data Model for use by ODI. |
Reset the BRM_ETL_PARAMETER table parameter values:
Connect to the staging schema (for example: brm_stg).
Delete data from the table BRM_ETL_PARAMETER, then set the values:
FROM_DATE_ETL=TO_DATE_ETL(last time loading)+ 1 second; TO_DATE_ETL=current time of BRM system; LOAD_DT =time when loading
For example:
delete from brm_etl_parameter;
commit;
Insert into BRM_ETL_PARAMETER
(PROCESS_NAME, FROM_DATE_ETL, TO_DATE_ETL, LOAD_DT)
Values ('BRM-ADAPTER', TO_DATE('05/22/2012 10:28:56', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/24/2012 10:28:55', 'MM/DD/YYYY HH24:MI:SS'), sysdate);
commit;
Note: you can obtain the current Oracle Communications Billing and Revenue Management time by pvt (pin_virtual_time).
From ODI Studio Designer, go to the BRM_OCDM project, then take the following steps:
Select SRC_STG_NONOGG, then Packages, and then, select and execute the following package:
SRC_STG_LOAD_NON_OGG_PKG
Select STG_OCDM, then Packages, and then, select and execute the following package:
STG_OCDM_INITIAL_LOAD_PKG
After performing an initial load of the Oracle Communications Data Model warehouse as described in "Initial Loading Using the BRM Adapter for Oracle Communications Data Model", you can refresh the data in the foundation layer of an Oracle Communications Data Model warehouse on a real-time basis.
Take the followings steps to ensure that to the data in the foundation layer of an Oracle Communications Data Model warehouse in this manner:
Install the BRM Adapter for Oracle Communications Data Model and configure it for use by both Oracle GoldenGate and Oracle Data Integrator (ODI) and as described in Oracle Communications Data Model Installation Guide.
Verify that the installation and configuration created the schema objects described in "Schema Definitions Added for the BRM Adapter for Oracle Communications Data Model".
Oracle GoldenGate handles step 1, as shown in Figure D-2. All you need to do is check whether the Oracle GoldenGate processes in the source and staging systems are in RUNNING mode (all GoldenGate processes keep checkpoint information and restart from the point of crash without any loss of data, provided nobody alters the process to restart from a different position).
From the GGSCI prompt, issue info all commands to verify that the Oracle GoldenGate processes needed by the BRM Adapter for Oracle Communications Data Model are running.
| Source System Processes | Staging System Processes |
|---|---|
| Manager process
Extract process ( Extract Pump process ( | Manager process
Replicate process ( |
The following code shows the issuing of these commands with successful results.
GGSCI> (mypc1) 5> info all
Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTBRM 47:29:00 00:00:20 EXTRACT RUNNING EXTPBRM 00:00:00 47:29:06
GGSCI> (ocdm01) 2> info all
Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPBRM 00:00:00 00:03:09
|
Tips: If you have two source systems, check process status on both source systems.For commands to manage Oracle GoldenGate processes, see Oracle Communications Data Model Installation Guide. |
To perform Step 2, 3, 4 as shown in Figure D-2, using ODI run the following package: STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG.
From ODI Studio Designer, go to the BRM_OCDM project, then take the following steps:
Select STG_OCDM, then select Packages, and then, select and execute the STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG.
|
See: See "Execution Flow Using BRM Adapter: with Oracle GoldenGate and ODI" for an explanation of the execution flow. |
|
Note: The STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG is a scenario based package has a package and a SCD2_UPDATE procedure; this package is based on another packaged named STG_OCDM_OGG_CDC_LOAD_PKG which consists of interfaces and a procedure PRE_OCDM_LOAD. The STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG waits for data to perform data load from the staging system to the target OCDM_SYS |
This section describes the internal operations performed by each executable object mentioned in "Initial Loading Using the BRM Adapter for Oracle Communications Data Model" and "Refreshing the Data Using the BRM Adapter for Oracle Communications Data Model (Change Data Capture: CDC)".
SRC_STG_LOAD_NON_OGG_PKG Package
The SRC_STG_LOAD_NON_OGG_PKG package in SRC_STG_NONOGG folder of BRM_OCDM project is using to load data into BRM staging database from BRM source database. Following steps were included in this process of loading staging database.
Execute PRE_STAGING_LOAD procedure to load previous day tables(XXX_LD). This procedure performs following operations:
Drop Previous Day Tables (XXX_LD)
Rename normal staging tables (XXX) to previous day tables (XXX_LD)
Create normal staging tables (XXX) structure from previous day tables (XXX_LD)
(Drop XXX_LD -> rename XXX to XXX_LD ->Create XXX from XXX_LD).
Execute Non Golden Gate Interfaces (XXX_NONGG) one by one to load normal staging tables (XXX).
There are 101 Non Golden Gate Interfaces out of which 51 have the naming convention (XXX_NONGG) and the other 51 use the naming convention (XXX_NONGG_IU); these are executed in sequence to load Staging tables form Source tables (src.XXX to stg.XXX).
SRC_STG_NONGG Interfaces:
1. ACCOUNT_NAMEINFO_T_NONGG_IU 2. ACCOUNT_NAMEINFO_T _NONGG 3. ACCOUNT_T_NONGG_IU 4. ACCOUNT_T_NONGG 5. BAL_GRP_T_NONGG_IU 6. BAL_GRP_T_NONGG 7. BILLINFO_T_NONGG_IU 8. BILLINFO_T_NONGG 9. BILL_T_NONGG_IU 10. BILL_T_NONGG 11. CONFIG_T_NONGG_IU 12. CONFIG_T_NONGG 13. CONFIG_BEID_BALANCES_T_NONGG_IU 14. CONFIG_BEID_BALANCES_T_NONGG 15. CONFIG_BUSINESS_TYPE_T_NONGG_IU 16. CONFIG_BUSINESS_TYPE_T_NONGG 17. CONFIG_CUR_CONV_RATES_T_NONGG_IU 18. CONFIG_CUR_CONV_RATES_T_NONGG 19. DD_OBJECTS_T_NONGG_IU 20. DD_OBJECTS_T_NONGG 21. DEAL_PRODUCTS_T_NONGG_IU 22. DEAL_PRODUCTS_T_NONGG 23. DEAL_T_NONGG_IU 24. DEAL_T_NONGG 25. DISCOUNT_T_NONGG_IU 26. DISCOUNT_T_NONGG 27. EVENT_BAL_IMPACTS_T_NONGG_IU 28. EVENT_BAL_IMPACTS_T_NONGG 29. EVENT_BILLING_PAYMENT_CASH_T_NONGG_IU 30. EVENT_BILLING_PAYMENT_CASH_T_NONGG 31. EVENT_BILLING_PAYMENT_CC_T_NONGG_IU 32. EVENT_BILLING_PAYMENT_CC_T_NONGG 33. EVENT_BILLING_PAYMENT_CHECK_T_NONGG_IU 34. EVENT_BILLING_PAYMENT_CHECK_T_NONGG 35. EVENT_BILLING_PAYMENT_DD_T_NONGG_IU 36. EVENT_BILLING_PAYMENT_DD_T_NONGG 37. EVENT_BILLING_PAYMENT_FAILED_T_NONGG_IU 38. EVENT_BILLING_PAYMENT_FAILED_T_NONGG 39. EVENT_BILLING_PAYMENT_PAYORD_T_NONGG_IU 40. EVENT_BILLING_PAYMENT_PAYORD_T_NONGG 41. EVENT_BILLING_PAYMENT_POST_T_NONGG_IU 42. EVENT_BILLING_PAYMENT_POST_T_NONGG 43. EVENT_BILLING_PAYMENT_T_NONGG_IU 44. EVENT_BILLING_PAYMENT_T_NONGG 45. EVENT_BILLING_PAYMENT_WTRAN_T_NONGG_IU 46. EVENT_BILLING_PAYMENT_WTRAN_T_NONGG 47. EVENT_BROADBAND_USAGE_T_NONGG_IU 48. EVENT_BROADBAND_USAGE_T_NONGG 49. EVENT_SESSION_DIALUP_T_NONGG_IU 50. EVENT_SESSION_DIALUP_T_NONGG 51. EVENT_SESSION_TELCO_GPRS_T_NONGG_IU 52. EVENT_SESSION_TELCO_GPRS_T_NONGG 53. EVENT_SESSION_TLCO_GSM_T_NONGG_IU 54. EVENT_SESSION_TLCO_GSM_T_NONGG 55. EVENT_SESSION_TLCS_T_NONGG_IU 56. EVENT_SESSION_TLCS_T_NONGG 57. EVENT_SESS_TLCS_SVC_CODES_T_NONGG_IU 58. EVENT_SESS_TLCS_SVC_CODES_T_NONGG 59. EVENT_T_NONGG_IU 60. EVENT_T_NONGG 61. IFW_CURRENCY_NONGG_IU 62. IFW_CURRENCY_NONGG 63. IFW_TIMEZONE_NONGG_IU 64. IFW_TIMEZONE_NONGG 65. IFW_USAGETYPE_NONGG_IU 66. IFW_USAGETYPE_NONGG 67. INVOICE_T_NONGG_IU 68. INVOICE_T_NONGG 69. ITEM_T_NONGG_IU 70. ITEM_T_NONGG 71. NOTE_T_NONGG_IU 72. NOTE_T_NONGG 73. PLAN_SERVICES_T_NONGG_IU 74. PLAN_SERVICES_T_NONGG 75. PLAN_T_NONGG_IU 76. PLAN_T_NONGG 77. PRODUCT_T_NONGG_IU 78. PRODUCT_T_NONGG 79. PURCHASED_DISCOUNT_T_NONGG_IU 80. PURCHASED_DISCOUNT_T_NONGG 81. PURCHASED_PRODUCT_T_NONGG_IU 82. PURCHASED_PRODUCT_T_NONGG 83. RATE_PLAN_T_NONGG_IU 84. RATE_PLAN_T_NONGG 85. SERVICE_EMAIL_T_NONGG_IU 86. SERVICE_EMAIL_T_NONGG 87. SERVICE_TELCO_FEATURES_T_NONGG_IU 88. SERVICE_TELCO_FEATURES_T_NONGG 89. SERVICE_TELCO_GPRS_T_NONGG_IU 90. SERVICE_TELCO_GPRS_T_NONGG 91. SERVICE_TELCO_GSM_T_NONGG_IU 92. SERVICE_TELCO_GSM_T_NONGG 93. SERVICE_T_NONGG_IU 94. SERVICE_T_NONGG 95. PAYINFO_T_NONGG_IU 96. PAYINFO_T_NONGG 97. PAYINFO_CC_T_NONGG_IU 98. PAYINFO_CC_T_NONGG 99. PAYINFO_DD_T_NONGG_IU 100. PAYINFO_DD_T_NONGG 101. PAYINFO_INV_T_NONGG_IU 102. PAYINFO_INV_T_NONGG
Execute POST_STAGING_LOAD procedure to load the data into Delta Tables (XXX _DELTA) and Delta History Tables (XXX_DELTA_H).
Truncate delta tables (XXX _DELTA)
Insert data into delta tables (XXX _DELTA) from normal staging tables (XXX) and previous day tables (XXX_LD).
Update delta tables (XXX _DELTA) columns as per the source changes (OPTYPE, LAST_UPDATE_TIME & CHANGE_DATE)
Insert data into delta history tables(XXX_DELTA_H) from delta tables (XXX _DELTA)
(Truncate XXX_DELTA -> Insert into XXX_DELTA from (XXX minus XXX_LD) -> Insert into XXX_DELTA_H from XXX_DELTA)
The SRC_STG_LOAD_NON_OGG_PKG package in SRC_STG_NONOGG folder of BRM_OCDM project is using to load the staging delta and delta history tables. This package will call the PRE_OCDM_LOAD procedure and execute in an infinite loop. In each loop of iteration, it performs the following steps and loads the data into Delta Tables (XXX _DELTA) and Delta History Tables (XXX_DELTA_H).
Insert data into delta history tables(XXX_DELTA_H) from delta tables (XXX _DELTA)
Truncate delta tables (XXX _DELTA)
Insert data into delta tables (XXX _DELTA) from normal staging tables (XXX)
Wait for new data in any XXX _DELTA tables, ODI uses an tool called ODI WAIT FOR DATA which will be in the waiting state; the job of this Tool (ODI WAIT FOR DATA) will be waiting for any data changes in staging layer delta tables (XXX _DELTA).Once it finds any changes it will execute the concerned interface where the data will be populated into related OCDM foundation layer tables
(Insert into XXX_DELTA_H from XXX_DELTA and Truncate XXX_DELTA and Insert into XXX_DELTA from XXX)
STG_OCDM_INITIAL_LOAD_PKG Package
The STG_OCDM_INITIAL_LOAD_PKG package in STG_OCDM folder of BRM_OCDM project loads the OCDM foundation layer tables from BRM Staging Database delta tables (XXX _DELTA). The package STG_OCDM_INITIAL_LOAD_PKG executes 46 STG-OCDM interfaces (XXX_MAP) which are executed in sequence, where the data is populated into related OCDM foundation layer tables form staging delta tables (XXX _DELTA).
STG-OCDM Interfaces:
1. ACCOUNT_ACCT_MAP 2. ACCOUNT_ADDR_LOC_MAP 3. ACCOUNT_CUST_MAP 4. ACCOUNT_GEO_CITY_MAP 5. ACCOUNT_GEO_CNTRY_MAP 6. ACCOUNT_GEO_STATE_MAP 7. ACCOUNT_INVC_DLVRY_MAP 8. ACCOUNT_POSTCD_MAP 9. ACCOUNT_PRTY_CNCT_INFO_MAP 10. ACCOUNT_PRTY_MAP 11. BAL_GRP_GRP_MAP 12. BILLINFO_ACCT_MAP 13. BILLINFO_PYMT_MTHD_TYP_MAP 14. BILL_INVC_MAP 15. CONFIG_BEID_BAL_TYP_MAP 16. CONFIG_BUSINESS_TYPE_T_MAP 17. CONFIG_CUR_CRNCY_EXCHNG_RATE_MAP 18. DD_OBJECTS_SRVC_SPEC_MAP 19. DEAL_PKG_ASGN_MAP 20. DEAL_PROD_MAP 21. EVENT_BAL_IMPACTS_NTWK_EVT_ACCT_BAL_IMPC_MAP 22. EVENT_BILLING_ACCT_RCHRG_MAP 23. EVENT_BROADBAND_USAGE_BRDBND_USG_EVT_MAP 24. EVENT_CALL_EVT_MAP 25. EVENT_DATA_SRVC_EVT_MAP 26. EVENT_SESSION_GPRS_USG_EVT_MAP 27. IFW_CRNCY_MAP 28. IFW_TIMWZONE_TIME_MAP 29. IFW_USAGETYPE_SRVC_TYP_MAP 30. INVOICE_INVC_DLVRY_MAP 31. ITEM_INVC_ITEM_MAP 32. NOTE_EVT_PRTY_INTRACN_MAP 33. PAYINFO_ACCT_PREF_PYMT_MTHD_MAP 34. PAYINFO_CC_PRTY_MAP 35. PAYINFO_DD_PRTY_MAP 36. PAYINFO_INV_T_PRTY_MAP 37. PLAN_MKT_PLN_MAP 38. PLAN_SERVICES_PROD_MKT_PLN_ASGN_MAP 39. PRODUCT_DISCOUNT_PROD_MAP 40. PRODUCT_DISCOUNT_PROD_RLTN_MAP 41. PURCHASED_PRODUCT_PRICE_CHRG_MAP 42. PURCHASED_PRODUCT_SBRP_MAP 43. RATE_PLAN_PROD_RTNG_PLN_MAP 44. SERVICE_CUST_FCNG_SRVC_MAP 45. SERVICE_SRVC_STAT_HIST_MAP 46. SERVICE_TELCO_FEATURES_SRVC_CHTRSTC_MAP
The following mentioned STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG package in STG_OCDM folder of BRM_OCDM project executes the OCDM foundation layer tables population interfaces and these packages needs to be executed and should be in running mode.
Note: STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG is a package which is having a package STG_OCDM_OGG_CDC_LOAD_PKG and a SCD2_UPDATE procedure, the scenario package is based on STG_OCDM_OGG_CDC_LOAD_PKG which consist of 46 interfaces and a procedure PRE_OCDM_LOAD. The STG_OCDM_OGG_CDC_LOAD_SCENARIO_PKG will be in an Infinite loop process which will wait for data to perform data load from stage(BRM_STG) to target (OCDM_SYS)
If you are using Oracle GoldenGate, verify that Oracle GoldenGate is working so that the Adapter can load data into the staging system. The processes listed should be running on both the source systems and the staging system as shown:
Source Processes
Manager Process
Extract Process (EXTBRM)
Extract Pump Process (EXTPBRM)
For example, on the BRM_SRC Source System, the source processes should look as follows:
GGSCI> (mypc1) 5> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTBRM 47:29:00 00:00:20 EXTRACT RUNNING EXTPBRM 00:00:00 47:29:06
Staging Processes
GGSCI> (ocdm01) 2> info all
Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPBRM 00:00:00 00:03:09
|
Tips: If you have two source systems, check process status on both source systems. For commands to manage Oracle GoldenGate processes, see Oracle Communications Data Model Installation Guide. |
This chapter provides information about customizing the access layer of Oracle Communications Data Model. It includes the following topics:
Introduction to Customizing the Access Layer of Oracle Communications Data Model
Dimensional Components in the Oracle Communications Data Model
The access layer of Oracle Communications Data Model provides the calculated and summarized ("flattened") perspectives of the data needed by business intelligence tools. Access layer objects are populated using the data from the foundation layer 3NF objects.
The access layer objects in the ocdm_sys schema include: derived and aggregate tables, OLAP cubes, and materialized views. This layer also contains data mining models. The results of the these models are stored in derived tables. The models themselves are defined in the ocdm_mining schema.
When designing and customizing access layer objects:
Follow the general guidelines for customizing physical objects given in "General Recommendations When Designing Physical Structures".
Design the access layer objects to support the business intelligence reports and queries that your site makes. See Chapter 5, "Report and Query Customization."
The following topics provide specialized information about designing and customizing access layer objects:
Derived tables are tables that have as values the result of a non-aggregate calculation against the data in the foundation layer tables. Derived tables have a DWD_ prefix.
There are two main types of derived tables in the default Oracle Communications Data Model and the way you customize these tables varies by type:
Tables that hold the results of a calculation such as the DWD_BER_FER_ERR_RATIO_DAY table that contains values that are the daily BER (Bit Error Rate) and FER (Frame Error Rate) statistics about the network elements. For information on customizing these tables, see "Creating New Derived Tables for Calculated Data".
Result tables for the data mining models (for example, DWD_CUST_MNNG). For information on customizing data mining models, see "Customizing Oracle Communications Data Model Data Mining Models".
|
See: The Derived Tables topic in Oracle Communications Data Model Reference for a list of all of the derived tables in the default Oracle Communications Data Model. For a list of only those derived tables that are results tables for the data mining models, see the chapter on Data Mining Models in Oracle Communications Data Model Reference. |
If, during fit-gap analysis, you identified a need for calculated data that is not provided by the default derived tables, you can meet this need by defining new tables. When designing these tables, name the tables following the convention of using the DWD_ prefix for derived tables.
Some derived (DWD_) tables in the default ocdm_sys schema are the results of data mining models defined in the default Oracle Communications Data Model. Those models are defined in the default ocdm_mining schema that also comes with Oracle Communications Data Model.
All Oracle Communications Data Model mining models use materialized views defined in the ocdm_mining schema as source input. Those materialized views are defined in ocdm_mining_etl.sql file in $ORACLE_HOME/ocdm/pdm/mining/src. Different mining models use different source materialized views.
When creating a customized Oracle Communications Data Model warehouse, you can customize the data mining models in the following ways:
Create a new model as discussed in "Creating a New Data Mining Model for Oracle Communications Data Model".
Modify an existing model as discussed in "Modifying Oracle Communications Data Model Data Mining Models".
To write a new data mining model:
Ensure that the ocdm_mining schema includes a definition for a materialized view that you can use as input to the model. Define a new materialized view, if necessary.
Create the model as you would any data mining model. Follow the instructions given in Oracle Data Mining Concepts. Add the model to the ocdm_mining schema.
Add any physical tables needed by the model into the ocdm_sys schema. Follow the naming conventions outlined in "Conventions When Customizing the Physical Model" and use a DWD_ prefix for results tables.
In the ocdm_mining schema, grant SELECT privileges to the results tables created in Step 3.
Modify the intra-ETL to support the use of the data mining model.
To customize Oracle Communications Data Model mining models, take the following steps:
Change the definition for source materialized views used as input to the mining model.
Train the model again by calling Oracle Communications Data Model mining package.
Ensure that the model reflects the new definition (for example, that a new column has been added).
Example 3-1 Adding a New Column to a Mining Model in Oracle Communications Data Model
To add a new column to create_churn_svm_model, take the following steps:
Add the new column the following materialized views that are used as input to create_churn_svm_model.
DMV_CUST_CHRN_SRC_SRC DMV_CUST_CHRN_SRC_PRD DMV_CUST_CHRN_SRC_TST DMV_CUST_CHRN_APPLY_ALL
Train the model by issuing the following statement.
ocdm_mining.create_churn_svm_model( MONTH_CODE );
Execute the following statement to query the result table and ensure the new column name is included in the query result:
SELECT attribute_name FROM TABLE(Oracle Communications Data Model
SELECT ATTRIBUTE_SET ROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('OCDM_CHURN_SVM'))
WHERE CLASS='1' );
After you have populated Oracle Communications Data Model foundation layer and the derived tables, you can leverage the prebuilt Oracle Communications Data Model mining model for some more advanced analysis and predictions.
This tutorial shows you how to predict the customers who will terminate the service in next 3 months (churners) based on the populated Oracle Communications Data Model warehouse. Using prebuilt Oracle Communications Data Model Mining models, you can easily and very quickly see the prediction result of your customers, without having to go through all of the data preparation, training, testing and applying process that you must perform in a traditional from-scratch mining project.
|
See: Oracle Data Mining Concepts for more information about the Oracle Database mining training and scoring (applying) process. |
After the initially generating a mining model, as time goes by, the customer information and their behavior change. Consequently, you must refresh the previous trained mining models based on the latest customer and usage data. You can follow the process in this tutorial to refresh the mining models to acquire predictions on latest customer information.
This tutorial shows you how to generate the Churn Prediction model through Oracle Communications Data Model Mining APIs. To use different parameters in the training process, or customize the model in more advanced fashion, you can also use Oracle Data Miner to do the same work.
This tutorial consists of the following:
Before starting this tutorial:
Review the Oracle by Example (OBE) tutorial "Using Oracle Data Miner 11g Release 2." To access this tutorial, open the Oracle Learning Library in your browser by following the instructions in "Oracle Technology Network"; and, then, search for the tutorial by name.
Install Oracle Communications Data Model.
Populate the base, reference and lookup tables.
Execute the intra-ETL.
Ensure that at least the following tables contain valid data:
DWD_ACCT_STTSTCDWD_VAS_SBRP_QCK_SUMMDWR_BSNS_MODWR_CUSTDWR_HHDWR_JB|
Note: If you have not populated the real customer data, and only want to learn the Oracle Communications Data Model mining model, you can use the sample data by taking the following steps:
|
This tutorial requires a valid, populated Oracle Communications Data Model warehouse.
|
Oracle by Example: For more information about using SQL Developer, refer to tutorial "Getting Started with Oracle SQL Developer 3.0". To access this tutorial, open the Oracle Learning Library in your browser by following the instructions in "Oracle Technology Network"; and, then, search for the tutorial by name. |
To prepare the environment, take the following steps:
In SQL Developer, connect to the ocdm_sys and the ocdm_mining schemas.
|
Tip: SQL Developer can be found on any Oracle Database Installation under$ORACLE_HOME/sqldeveloper. |
After you connect to the ocdm_sys schema, you can see all the tables in that schema. You can narrow down the list by right clicking the "table" and then applying filters:
(Optional) As mentioned in the "Preparing Your Environment", if you have not populated those tables with your own data, you can try with some sample data. After you download the sample data, follow those steps to import the data:
Grant dba to ocdm_sys by issuing the following statement:
grant dba to ocdm_sys.
Disable all foreign keys on those tables used by the tutorial:
ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_CS5_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_POT_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLCS_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_SJ_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_SEG_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLCT_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_LAN_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_AL1_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_PT_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_BLC_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLMS_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLJ_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLG_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_HOU1_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLE_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_DLN_FK". ALTER TABLE "DWR_CUST" DISABLE CONSTRAINT "CI_OI_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_CI_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_BM_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_RSCR_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_AL1_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DAB_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DLCRB_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DLCC_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DAS_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_PM_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DOBU_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_CR1_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_LAB_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_ACCT_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_CHR_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DLAON_FK". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_DLCT_FK1". ALTER TABLE "DWD_ACCT_STTSTC" DISABLE CONSTRAINT "ACS_GEOC_FK1". ALTER TABLE "DWD_VAS_SBRP_QCK_SUMM" DISABLE CONSTRAINT "VS_AM_FK". ALTER TABLE "DWD_VAS_SBRP_QCK_SUMM" DISABLE CONSTRAINT "VS_CI_FK". ALTER TABLE "DWD_VAS_SBRP_QCK_SUMM" DISABLE CONSTRAINT "VS_BM_FK". ALTER TABLE "DWD_VAS_SBRP_QCK_SUMM" DISABLE CONSTRAINT "VS_DLCT_FK1".
Import the sample dump into ocdm_sys schema by issuing the following statement.
imp ocdm_sys/ocdm_sys@tnsname file=ocdm.4.mining.dmp full=y ignore=y
Review tables to ensure they contain valid data, either from your own customer data, or from the sample data.
Review the DWR_CUST table.
Click each of the following tables to ensure that the table is properly populated:
DWD_ACCT_STTSTCDWD_VAS_SBRP_QCK_SUMMDWR_BSNS_MODWR_CUSTDWR_HHDWR_JBCheck that the DWD_CUST_MNNG and DWD_CUST_PROD_AFFLTN results tables are empty before running the model generation function as described in "Generating the Churn Prediction Model".
This tutorial uses two procedures from Oracle Communications Data Model Mining APIs:
pkg_ocdm_mining.refresh_mining_source that populates some mining source tables from the ocdm_sys schema tables.
pkg_ocdm_mining.create_churn_svm_model that trains the model, and generates the result of prediction.
Take the following steps to use the procedures:
Refresh the Oracle Communications Data Model mining source tables by executing the following SQL statements.
select count (*) from dmv_cust_chrn_src_all; exec pkg_ocdm_mining.refresh_mining_source; select count (*) from dmv_cust_chrn_src_all;
These statements display the number of tables before refreshing.
Refresh the tables and rexecute the statements to display the number of tables after refreshing.
Generate the Churn Prediction model by executing the following SQL statements.
select count(*) from dwd_cust_mnng; select count(*) from dwd_chrn_svm_factor; exec pkg_ocdm_mining.create_churn_svm_model; select count(*) from dwd_cust_mnng;
These statements:
Show the record counts before generation.
Generate the mode.
Show the count after generation.
|
Note: This tutorial does not refresh all models. It only refreshes one churn prediction model. To refresh all of the default mining models based on latest customer data, follow the instructions in "Refreshing the Data in an Oracle Communications Data Model Warehouse". |
After you have refreshed, trained, and generated the model, check the DWD_CUST_MNNG results table in ocdm_sys schema by taking the following steps:
Issue the following query.
Select cust_key, prdct_churn_svm_ind, prdct_churn_svm_prob From dwd_cust_mnng where rownum < 10;
For each customer identified by CUST_KEY, the PRDCT_CHRN_SVM_IND column gives a Boolean prediction of whether a customer will churn in next 3 months. Zero (0) stands for non-churner, while one (1) stands for churner. The PRDCT_CHURN_SVM_PROB column provides a more detailed probability (0~1) of how likely a customer is going to churn.
(Optional) If you have also installed the Oracle Communications Data Model sample reports in Oracle Business Intelligence Suite Enterprise Edition 11g, you can also view the results as an Oracle Business Intelligence Suite Enterprise Edition report.
|
See: Oracle Communications Data Model Installation Guide for more information on installing the sample reports and deploying the Oracle Communications Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance. |
In the Oracle Business Intelligence Suite Enterprise Edition report, the customer revenue information such as the contract ARPU, debt value is shown combined with the prediction results.
Aggregate tables are tables that aggregate or "roll up" the data to one level higher than a base or derived table. The aggregate tables in the default Oracle Communications Data Model are actually materialized views and have a DWA_ prefix. These aggregate tables provide a view of the data similar to the view provided by a fact table in a snowflake schema.
The default Oracle Communications Data Model defines several aggregate tables. For example, the DWA_BER_FER_ERR_RATIO_MONTH table aggregates the values of the DWD_BER_FER_ERR_RATIO_DAY table to the month level.
|
See: The "Aggregate Tables" topic in Oracle Communications Data Model Reference for a list of the aggregate tables in the default Oracle Communications Data Model. |
If, during fit-gap analysis, you identified a need for simple aggregated data that is not provided by the default aggregate tables, you can define new materialized views. When designing these tables, keep the following points in mind:
Create a query for the materialized view that aggregates up only a single level. For example, if aggregating over time, then aggregate only from day to month.
|
Note: When you must aggregate up many levels (for example in time, month, quarter, and year) or different hierarchies (for example, the fiscal and calendar hierarchies for a time dimension), do not define aDWA_ table; instead, define the aggregations by creating OLAP cubes. |
Name the tables following the conventions outlined in "General Naming Conventions for Physical Objects" and use a DWA_ prefix.
There is often much discussion regarding the 'best' modeling approach to take for any given data warehouse with each style, classic 3NF and dimensional having their own strengths and weaknesses. It is likely that data warehouses must do more to embrace the benefits of each model type rather than rely on just one - this is the approach that was adopted in designing the Oracle Communications Data Model. The foundation layer of the Oracle Communications Data Model is a 3NF model. The default Oracle Communications Data Model also provides a dimensional model of the data. This dimensional model of the data is a perspective that summarizes and aggregates data, rather than preserving detailed transaction information.
Familiarize yourself with dimensional modeling by reading the following topics before you begin to customize the dimensional model of the default Oracle Communications Data Model:
Characteristics of the OLAP Cubes in Oracle Communications Data Model
Defining New Oracle OLAP Cubes for Oracle Communications Data Model
Changing an Oracle OLAP Cube in Oracle Communications Data Model
Creating a Forecast Cube for Oracle Communications Data Model
Choosing a Cube Partitioning Strategy for Oracle Communications Data Model
Choosing a Cube Data Maintenance Method for Oracle Communications Data Model
The simplicity of a dimensional model is inherent because it defines objects that represent real-world business entities. Analysts know which business measures they are interested in examining, which dimensions and attributes make the data meaningful, and how the dimensions of their business are organized into levels and hierarchies.
In the simplest terms, a dimensional model identifies the following objects:
Measures. Measures store quantifiable business data (such as sale s, expenses, and inventory). Measures are sometimes called "facts". Measures are organized by one or more dimensions and may be stored or calculated at query time:
Stored Measures. Stored measures are loaded and stored at the leaf level. Commonly, there is also a percentage of summary data that is stored. Summary data that is not stored is dynamically aggregated when queried.
Calculated Measures. Calculated measures are measures whose values are calculated dynamically at query time. Only the calculation rules are stored in the database. Common calculations include measures such as ratios, differences, moving totals, and averages. Calculations do not require disk storage space, and they do not extend the processing time required for data maintenance.
Dimensions. A dimension is a structure that categorizes data to enable users to answer business questions. Commonly used dimensions are Customers, Products, and Time. A dimension's structure is organized hierarchically based on parent-child relationships. These relationships enable:
Navigation between levels.
Hierarchies on dimensions enable drilling down to lower levels or navigation (rolling up) to higher levels. Drilling down on the Time dimension member 2005 typically navigates you to the quarters Q1 2005 through Q4 2005. In a calendar year hierarchy, drilling down on Q1 2005 would navigate you to the months, January 05 through March 05. These kinds of relationships make it easy for users to navigate large volumes of multidimensional data.
Aggregation from child values to parent values.
The parent represents the aggregation of its children. Data values at lower levels aggregate into data values at higher levels. Dimensions are structured hierarchically so that data at different levels of aggregation are manipulated efficiently for analysis and display.
Allocation from parent values to child values.
The reverse of aggregation is allocation and is heavily used by planning budgeting, and similar applications. Here, the role of the hierarchy is to identify the children and descendants of particular dimension members of "top-down" allocation of budgets (among other uses).
Grouping of members for calculations.
Share and index calculations take advantage of hierarchical relationships (for example, the percentage of total profit contributed by each product, or the percentage share of product revenue for a certain category, or costs as a percentage of the geographical region for a retail location).
A dimension object helps to organize and group dimensional information into hierarchies. This represents natural 1:n relationships between columns or column groups (the levels of a hierarchy) that cannot be represented with constraint conditions. Going up a level in the hierarchy is called rolling up the data and going down a level in the hierarchy is called drilling down the data.
There are two ways that you can implement a dimensional model:
Relational tables in a star schema configuration. This traditional method of implementing a dimensional model is discussed in "Characteristics of Relational Star and Snowflake Tables".
Oracle OLAP Cubes. The physical model provided with Oracle Communications Data Model provides a dimensional perspective of the data using Oracle OLAP cubes. This dimensional model is discussed in "Characteristics of the OLAP Dimensional Model".
In the case of relational tables, the dimensional model has historically been implemented as a star or snowflake schema. Dimension tables (which contain information about hierarchies, levels, and attributes) join to one or more fact tables. Fact tables are the large tables that store quantifiable business measurements (such as sales, expenses, and inventory) and typically have foreign keys to the dimension tables. Dimension tables, also known as lookup or reference tables. contain the relatively static or descriptive data in the data warehouse.
A star schema borders on a physical model, as drill paths, hierarchy and query profile are embedded in the data model itself rather than the data. This in part at least, is what makes navigation of the model so straightforward for end users. Star schemas usually have a large fact table surrounded by smaller dimension tables. Dimension tables do not change very much. Most of the information that the users need are in the fact tables. Therefore, star schemas have fewer table joins than do 3NF models.
A star schema is so called because the diagram resembles a star, with points radiating from a center. The center of the star consists of one or more fact tables and the points of the star are the dimension tables.
Snowflake schemas are slight variants of a simple star schema where the dimension tables are further normalized and broken down into multiple tables. The snowflake aspect only affects the dimensions and not the fact table and is therefore considered conceptually equivalent to star schemas. Snowflake dimensions are useful and indeed necessary when there are fact tables of differing granularity. A month-level derived or aggregate table (or materialized view) must be associated with a month level snowflake dimension table rather than the default (lower) Day level star dimension table.
When a relational table acts as a dimension to a fact table, it is recommended that you declare that table as a dimension (even though it is not necessary). Defined dimensions can yield significant performance benefits, and support the use of more complex types of rewrite.
To define and declare the structure of the dimension use the CREATE DIMENSION command. Use the LEVEL clause to identify the names of the dimension levels.
To improve the data quality of the dimension data in the data warehouse, it is recommended that you validate the declarative information about the relationships between the dimension members after any modification to the dimension data.
To perform this validation, use the VALIDATE_DIMENSION procedure of the DBMS_DIMENSION package. When the VALIDATE_DIMENSION procedure encounters any errors, the procedure places the errors into the DIMENSION_EXCEPTIONS table. To find the exceptions identified by the VALIDATE_DIMENSION procedure, query the DIMENSION_EXCEPTIONS table.
You can schedule a call to the VALIDATE_DIMENSION procedure as a post-process step to the regular Incremental Dimension load script. This can be done before the call to refresh the derived or aggregate tables of the data model through materialized view refresh, intra-ETL package calls.
Oracle OLAP Cubes logically represent data similar to relational star tables, although the data is actually stored in multidimensional arrays. Like dimension tables, cube dimensions organize members into hierarchies, levels, and attributes. The cube stores the measure (fact) data. The dimensions form the edges of the cube.
Oracle OLAP is an OLAP server embedded in the Oracle Database. Oracle OLAP provides native multidimensional storage and speed-of-thought response times when analyzing data across multiple dimensions. The database provides rich support for analytics such as time series calculations, forecasting, advanced aggregation with additive and nonadditive operators, and allocation operations.
By integrating multidimensional objects and analytics into the database, Oracle provides the best of both worlds: the power of multidimensional analysis along with the reliability, availability, security, and scalability of the Oracle database.
Oracle OLAP is fully integrated into Oracle Database. At a technical level, this means:
The OLAP engine runs within the kernel of Oracle Database.
Dimensional objects are stored in Oracle Database in their native multidimensional format.
Cubes and other dimensional objects are first class data objects represented in the Oracle data dictionary.
Data security is administered in the standard way, by granting and revoking privileges to Oracle Database users and roles.
OLAP cubes, dimensions, and hierarchies are exposed to applications as relational views. Consequently, applications can query OLAP objects using SQL as described in "Oracle OLAP Cube Views" and Chapter 5, "Report and Query Customization."
Oracle OLAP cubes can be enhanced so that they are materialized views as described in "Cube Materialized Views".
Benefits of Using Oracle OLAP
The benefits of using Oracle OLAP are significant; Oracle OLAP offers the power of simplicity and provides: One database, standard administration and security, standard interfaces and development tools.
The Oracle OLAP dimensional data model is highly structured. Structure implies rules that govern the relationships among the data and control how the data can be queried. Cubes are the physical implementation of the dimensional model, and thus are highly optimized for dimensional queries. The OLAP engine leverages this innate dimensionality in performing highly efficient cross-cube joins for inter-row calculations, outer joins for time series analysis, and indexing. Dimensions are pre-joined to the measures. The technology that underlies cubes is based on an indexed multidimensional array model, which provides direct cell access.
The OLAP engine manipulates dimensional objects in the same way that the SQL engine manipulates relational objects. However, because the OLAP engine is optimized to calculate analytic functions, and dimensional objects are optimized for analysis, analytic and row functions can be calculated much faster in OLAP than in SQL.
The dimensional model enables Oracle OLAP to support high-end business intelligence tools and applications such as OracleBI Discoverer Plus OLAP, OracleBI Spreadsheet Add-In, Oracle Business Intelligence Suite Enterprise Edition, BusinessObjects Enterprise, and Cognos ReportNet.
Oracle OLAP Dimensional Objects
Oracle OLAP dimensional objects include cubes, measures, dimensions, hierarchies, levels and attributes. The OLAP dimensional objects are described in detail in Oracle OLAP User's Guide. Figure 3-2 shows the general relationships among the objects.
Figure 3-2 Diagram of the OLAP Dimensional Model
When you define an OLAP cube, Oracle OLAP automatically generates a set of relational views on the cube and its dimensions and hierarchies
Cube view. Each cube has a cube view that presents the data for all the measures and calculated measures in the cube. You can use a cube view like a fact table in a star or snowflake schema. However, the cube view contains all the summary data in addition to the detail level data. The default name of a cube view is cube_VIEW.
Dimension and hierarchy views. Each dimension has one dimension view plus a hierarchy view for each hierarchy associated with the dimension. The default name for a dimension view is dimension_VIEW. For a hierarchy view, the default name is dimension_hierarchy_VIEW.
These views are related in the same way as fact and dimension tables in a star schema. Cube views serve the same function as fact tables, and hierarchy views and dimension views serve the same function as dimension tables. Typical queries join a cube view with either a hierarchy view or a dimension view.
SQL applications query these views to display the information-rich contents of these objects to analysts and decision makers. You can also create custom views that follow the structure expected by your applications, using the system-generated views like base tables.
|
See also: The discussion on querying dimensional objects in Oracle OLAP User's Guide and Chapter 5, "Report and Query Customization." |
Oracle OLAP cubes can be enhanced so that they are materialized views. A cube that has been enhanced in this way is called a cube materialized view and has a CB$ prefix. Cube materialized views can be incrementally refreshed through the Oracle Database materialized view subsystem, and they can serve as targets for transparent rewrite of queries against the source tables.
The OLAP dimensions associated with a cube materialized view are also defined with materialized view capabilities.
Necessary Cube Characteristics for Cube Materialized Views
A cube must conform to the following requirements, before it can be designated as a cube materialized view:
All dimensions of the cube have at least one level and one level-based hierarchy. Ragged and skip-level hierarchies are not supported. The dimensions must be mapped.
All dimensions of the cube use the same aggregation operator, which is either SUM, MIN, or MAX.
The cube has one or more dimensions and one or more measures.
The cube is fully defined and mapped. For example, if the cube has five measures, then all five are mapped to the source tables.
The data type of the cube is NUMBER, VARCHAR2, NVARCHAR2, or DATE.
The source detail tables support dimension and rely constraints. If they have not been defined, then use the Relational Schema Advisor to generate a script that defines them on the detail tables.
The cube is compressed.
The cube can be enriched with calculated measures, but it cannot support more advanced analytics in a cube script.
Adding Materialized View Capabilities
To add materialized view capabilities to an OLAP cube, take the following steps:
In the Analytic Workspace Manager, connect to the ocdm_sys schema.
From the cube list, select the cube which you want to enable.
In the right pane, select the Materialized Views tab.
Select Enable Materialized View Refresh of the Cube. then click Apply.
|
Note: You cannot enable the cube materialized view for a forecast cube. |
|
Oracle by Example: For more information on working with OLAP cubes, see the following OBE tutorials:
To access the tutorials, open the Oracle Learning Library in your browser by following the instructions in "Oracle Technology Network"; and, then, search for the tutorials by name. |
The default access layer of Oracle Communications Data Model provides a dimensional perspective of the data using Oracle OLAP cubes.
There are OLAP cubes defined in the default ocdm_sys schema. These cubes have the general characteristics described in "Characteristics of the OLAP Dimensional Model". Specifically, OLAP cubes in the Oracle Communications Data Model have the following characteristics:
All of the default OLAP cubes are loaded with data from DWA_ tables that are materialized views.
The cubes were defined and built using the Analytical Workspace Manager (AWM) client tool.
A relational view (with a _VIEW suffix) is defined over each of the OLAP cubes.
All of the OLAP cubes in the Oracle Communications Data Model are cube materialized views (that is, CB$ objects).
|
Note: immediately after installation, all materialized views underlying the OLAP cubes are disabled by default. To enable the cube materialized views, you must follow the steps outlined in "Adding Materialized View Capabilities". |
For information on the using OLAP cubes in your customized version of Oracle Communications Data Model, see Oracle OLAP User's Guide and the following topics:
Defining New Oracle OLAP Cubes for Oracle Communications Data Model
Changing an Oracle OLAP Cube in Oracle Communications Data Model
Creating a Forecast Cube for Oracle Communications Data Model
Choosing a Cube Partitioning Strategy for Oracle Communications Data Model
Choosing a Cube Data Maintenance Method for Oracle Communications Data Model
You can add new OLAP cubes to the ocdm_sys schema. For consistency's sake, design and define these new cubes as described in "Characteristics of the OLAP Cubes in Oracle Communications Data Model".
Take the following steps to define new cubes:
Ensure that there is an aggregate table (DWA_) to use as the "lowest leaf" data for the cube. See "Aggregate Tables in the Oracle Communications Data Model" for information on creating new tables.
Use the AWM to define new Cubes for a customized version of Oracle Communications Data Model. Follow the instructions given for creating cubes and dimensions in Oracle OLAP User's Guide.
Use the information provided in "Characteristics of the OLAP Dimensional Model". and the Oracle OLAP User's Guide to guide you when you design and define new OLAP cubes. Also, if you are familiar with a relational star schema design as outlined in "Characteristics of Relational Star and Snowflake Tables", then you can use this understanding to help you design an OLAP Cube:
Fact tables correspond to cubes.
Data columns in the fact tables correspond to measures.
Foreign key constraints in the fact tables identify the dimension tables.
Dimension tables identify the dimensions.
Primary keys in the dimension tables identify the base-level dimension members.
Parent columns in the dimension tables identify the higher level dimension members.
Columns in the dimension tables containing descriptions and characteristics of the dimension members identify the attributes.
You can also get insights into the dimensional model by looking at the sample reports included with Oracle Communications Data Model.
|
See: Oracle Communications Data Model Installation Guide for more information on installing the sample reports and deploying the Oracle Communications Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance. |
|
Tip: While investigating your source data, you may decide to create relational views that more closely match the dimensional model that you plan to create. |
Add materialized view capabilities to the OLAP cubes as described in "Adding Materialized View Capabilities".
|
See also: Oracle OLAP User's Guide, "Defining New Oracle OLAP Cubes for Oracle Communications Data Model", and the sample reports in Oracle Communications Data Model Reference. |
|
Oracle by Example: For more information on creating OLAP cubes, see the "Building OLAP 11g Cubes" OBE tutorial.To access the tutorial, open the Oracle Learning Library in your browser by following the instructions in "Oracle Technology Network"; and, then, search for the tutorial by name. |
Common customizations to Oracle Communications Data Model cubes are changing the dimensions or the measures of the cube.
Since all Oracle Communications Data Model cubes load data from tables with the DWA_ prefix, to change the measures or dimensions of one cube, you must take the following steps:
Use the information in Oracle Communications Data Model Reference, to identify the DWA_ table from which the OLAP cube is populated.
Change the structure of the DWA_ table identified in Step 1.
Change the OLAP cube and cube materialized views to reflect the new structure.
To create a forecast cube for Oracle Communications Data Model:
Create a cube to contain the results of the forecast as described in "Defining New Oracle OLAP Cubes for Oracle Communications Data Model".
|
Note: You cannot enable materialized views for an Oracle Communications Data Model forecast cube. |
Write an OLAP DML forecasting context program as described in Oracle OLAP DML Reference.
Partitioning is a method of physically storing the contents of a cube. It improves the performance of large cubes in the following ways:
Improves scalability by keeping data structures small. Each partition functions like a smaller measure.
Keeps the working set of data smaller both for queries and maintenance, since the relevant data is stored together.
Enables parallel aggregation during data maintenance. Each partition can be aggregated by a separate process.
Simplifies removal of old data from storage. Old partitions can be dropped, and new partitions can be added.
The number of partitions affects the database resources that can be allocated to loading and aggregating the data in a cube. Partitions can be aggregated simultaneously when sufficient resources have been allocated.
The Cube Partitioning Advisor analyzes the source tables and develops a partitioning strategy. You can accept the recommendations of the Cube Partitioning Advisor, or you can make your own decisions about partitioning.
If your partitioning strategy is driven primarily by life-cycle management considerations, then you should partition the cube on the Time dimension. Old time periods can then be dropped as a unit, and new time periods added as a new partition. The Cube Partitioning Advisor has a Time option, which recommends a hierarchy and a level in the Time dimension for partitioning.
The level on which to partition a cube is determined based on a trade off between load performance and query performance.
Typically, you do not want to partition on too low a level (for example, on the DAY level of a TIME dimension) because if you do then too many partitions must be defined at load time which slows down an initial or historical load. Also, a large number of partitions can result in unusually long Analytic Workspace attach times and slows down the Time Series-based calculations. Also, a Quarterly Cumulative measure (Quarter to Date Measure) needs to access 90 or 91 partitions to calculate a specific value for one Customer and Organization. All dimension members above the partition level of partition dimension (including those belonging to nondefault hierarchies) would be present in a single default template. Day level partitioning makes this very heavy since all higher level members are stored in default template. However, the advantage of partitioning DAY if the OLAP Cube load frequency is daily then there you must only load from a new partition in fact table into a single partition in the OLAP cube every day. This greatly improves the load performance since percentage-based refresh can be enabled if the $cۜcube is materialized-view enabled and has materialized-view logs.
Recommendations: Cube Partitioning Strategy
Usually a good compromise between the differing load and query performance requirements is to use an intermediate level like MONTH as the partition level. Time series calculations within a month (week to date, month to date, and so on) are fast and higher level calculation like year to date needs to refer to 12 partitions at most. Also this way the monthly partition is defined and created only one time (that is during the initial load on first of each month) and is then reused for each subsequent load that month. The aggregation process may be triggered off at the month level (instead of specific day level) and some redundant aggregations (of previously loaded dates of current month) may occur each time but it should result in satisfactory load and query performance.
|
See also: "The discussion on choosing a partition strategy in Oracle OLAP User's Guide, "Indexes and Partitioned Indexes in the Oracle Communications Data Model", and "Partitioning and Materialized Views". |
While developing a dimensional model of your data, it is a good idea to map and load each object immediately after you create it so that you can immediately detect and correct any errors that you made to the object definition or the mapping.
However, in a production environment, you want to perform routine maintenance as quickly and easily as possible. For this stage, you can choose among data maintenance methods. You can refresh all cubes using the Maintenance Wizard. This wizard enables you to refresh a cube immediately, or submit the refresh as a job to the Oracle job queue, or generate a PL/SQL script. You can run the script manually or using a scheduling utility, such as Oracle Enterprise Manager Scheduler or the DBMS_SCHEDULER PL/SQL package. The generated script calls the BUILD procedure of the DBMS_CUBE PL/SQL package. You can modify this script or develop one from the start using this package.
The data for a partitioned cube is loaded and aggregated in parallel when multiple processes have been allocated to the build. You are able to see this in the build log.
In addition, each cube can support these data maintenance methods:
Custom cube scripts
Cube materialized views
If you are defining cubes to replace existing materialized views, then you use the materialized views as an integral part of data maintenance. Note, however, that materialized view capabilities restrict the types of analytics that can be performed by a custom cube script.
|
Oracle by Example: See the following OBE tutorial for an example of how Oracle uses cube materialized views for transparent access to a relational star schema.:
To access the tutorial, open the Oracle Learning Library in your browser by following the instructions in "Oracle Technology Network"; and, then, search for the tutorial by name. |
Materialized views are query results that have been stored or "materialized" in advance as schema objects. From a physical design point of view, materialized views resemble tables or partitioned tables and behave like indexes in that they are used transparently and improve performance.
In the past, organizations using summaries spent a significant amount of time and effort creating summaries manually, identifying which summaries to create, indexing the summaries, updating them, and advising their users on which ones to use. With the advent of materialized views, a database administrator creates one or more materialized views, which are the equivalent of a summary. Thus, the workload of the database administrator is eased and the user no longer needed to be aware of the summaries that had been defined. Instead, the end user queries the tables and views at the detail data level. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables and reduces response time for returning results from the query.
Materialized views improve query performance by precalculating expensive join and aggregation operations on the database before executing and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request.
The default Oracle Communications Data Model defines many materialized views. In the default ocdm_sys schema, you can identify these materialized views by looking at objects with the prefixes listed in the following table.
| Prefix | Description |
|---|---|
DWA_ | Aggregate tables which are materialized views.
See: Aggregate tables in Oracle Communications Data Model Reference for a list of these objects in the default data model. "Aggregate Tables in the Oracle Communications Data Model" for more information on customizing these objects,. |
CB$ | An OLAP cube enhanced with materialized view capabilities.
See: OLAP cube materialized views in Oracle Communications Data Model Reference for a list of these objects in the default data model. "Characteristics of the OLAP Cubes in Oracle Communications Data Model" for information on OLAP cubes. Note: Do |
DMV_ | Materialized views created for performance reasons (that is, not an aggregate table or a cube materialized view).
See: Oracle Communications Data Model Reference to identify these objects in the default data model. |
The following topics provide more information on using and creating materialized views in your customized Oracle Communications Data Model:
Refresh option vary by the type of materialized view:
In data warehouses, materialized views normally contain aggregates. The _DWA tables in the default Oracle Communications Data Model are this type of materialized view.
For a materialized view with aggregates, for fast refresh to be possible:
The SELECT list must contain all of the GR OUP BY columns (if present)
There must be a COUNT(*) and a COUNT(column) on any aggregated columns.
Materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression.
Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE).
You can define that the materialized view be refreshed ON COMMIT or ON DEMAND. A REFRESH ON COMMIT materialized view is automatically refreshed when a transaction that does DML to a materialized view's detail tables commits.
When you specify REFRESH ON COMMIT, the table commit can take more time than if you have not. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.
Some materialized views contain only joins and no aggregates (for example, when a materialized view is created that joins the sales table to the times and customers tables). The advantage of creating this type of materialized view is that expensive joins are precalculated.
Fast refresh for a materialized view containing only joins is possible after any type of DML to the base tables (direct-path or conventional INSERT, UPDATE, or DELETE).
A materialized view containing only joins can be defined to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.
If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:
A materialized view log must be present for each detail table unless the table supports partition change tracking. Also, when a materialized view log is required, the ROWID column must be present in each materialized view log.
The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
If some of these restrictions are not met, you can create the materialized view as REFRESH FORCE to take advantage of fast refresh when it is possible. If one table does not meet all of the criteria, but the other tables do the materialized view is still fast refreshable with respect to the other tables for which all the criteria are met.
To achieve an optimally efficient refresh:
Ensure that the defining query does not use an outer join that behaves like an inner join. If the defining query contains such a join, consider rewriting the defining query to contain an inner join.
If the materialized view contains only joins, the ROWID columns for each table (and each instance of a table that occurs multiple times in the FROM list) must be present in the SELECT list of the materialized view.
If the materialized view has remote tables in the FROM clause, all tables in the FROM clause must be located on that same site. Further, ON COMMIT refresh is not supported for materialized view with remote tables. Except for SCN-based materialized view logs, materialized view logs must be present on the remote site for each detail table of the materialized view and ROWID columns must be present in the SELECT list of the materialized view.
A nested materialized view is a materialized view whose definition is based on another materialized view. A nested materialized view can reference other relations in the database in addition to referencing materialized views.
In a data warehouse, you typically create many aggregate views on a single join (for example, rollups along different dimensions). Incrementally maintaining these distinct materialized aggregate views can take a long time, because the underlying join has to be performed many times.
Using nested materialized views, you can create multiple single-table materialized views based on a joins-only materialized view and the join is performed just one time. In addition, optimizations can be performed for this class of single-table aggregate materialized view and thus refresh is very efficient.
Some types of nested materialized views cannot be fast refreshed. Use EXPLAIN_MVIEW to identify those types of materialized views.
You can refresh a tree of nested materialized views in the appropriate dependency order by specifying the nested =TRUE parameter with the DBMS_MVIEW.REFRESH parameter.
Example 3-2 Refreshing Oracle Communications Data Model Nested Materialized Views
For example, if you call DBMS_MVIEW.REFRESH ('SUM_SALES_CUST_TIME', nested => TRUE), the REFRESH procedure first refreshes the join_sales_cust_time materialized view, and then refreshes the sum_sales_cust_time materialized view.
The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements:
Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, for best performance, create a single-column bitmap index on each materialized view key column.
In the case of materialized views containing only joins using fast refresh, create indexes on the columns that contain the rowids to improve the performance of the refresh operation.
If a materialized view using aggregates is fast refreshable, then an index appropriate for the fast refresh procedure is created unless USING NO INDEX is specified in the CREATE MATERIALIZED VIEW statement.
Because of the large volume of data held in a data warehouse, partitioning is an extremely useful option when designing a database. Partitioning the fact tables improves scalability, simplifies system administration, and makes it possible to define local indexes that can be efficiently rebuilt. Partitioning the fact tables also improves the opportunity of fast refreshing the materialized view because this may enable partition change tracking refresh on the materialized view.
Partitioning a materialized view has the same benefits as partitioning fact tables. When a materialized view is partitioned a refresh procedure can use parallel DML in more scenarios and partition change tracking-based refresh can use truncate partition to efficiently maintain the materialized view.
Using Partition Change Tracking
It is possible and advantageous to track freshness to a finer grain than the entire materialized view. The ability to identify which rows in a materialized view are affected by a certain detail table partition, is known as partition change tracking. When one or more of the detail tables are partitioned, it may be possible to identify the specific rows in the materialized view that correspond to a modified detail partition(s). those rows become stale when a partition is modified while all other rows remain fresh.
You can use partition change tracking to identify which materialized view rows correspond to a particular partition. Partition change tracking is also used to support fast refresh after partition maintenance operations on detail tables. For instance, if a detail table partition is truncated or dropped, the affected rows in the materialized view are identified and deleted. Identifying which materialized view rows are fresh or stale, rather than considering the entire materialized view as stale, allows query rewrite to use those rows that refresh while in QUERY_REWRITE_INTEGRITY = ENFORCED or TRUSTED modes.
Several views, such as DBA_MVIEW_DETAIL_PARTITION, detail which partitions are stale or fresh. Oracle does not rewrite against partial stale materialized views if partition change tracking on the changed table is enabled by the presence of join dependent expression in the materialized view.
To support partition change tracking, a materialized view must satisfy the following requirements:
At least one detail table referenced by the materialized view must be partitioned.
Partitioned tables must use either range, list or composite partitioning.
The top level partition key must consist of only a single column.
The materialized view must contain either the partition key column or a partition marker or ROWID or join dependent expression of the detail table.
If you use a GROUP BY clause, the partition key column or the partition marker or ROWID or join dependent expression must be present in the GROUP BY clause.
If you use an analytic window function or the MODEL clause, the partition key column or the partition marker or ROWID or join dependent expression must be present in their respective PARTITION BY subclauses.
Data modifications can only occur on the partitioned table. If partition change tracking refresh is being done for a table which has join dependent expression in the materialized view, then data modifications should not have occurred in any of the join dependent tables.
The COMPATIBILITY initialization parameter must be a minimum of 9.0.0.0.0.
Partition change tracking is not supported for a materialized view that refers to views, remote tables, or outer joins.
Using data compression for a materialized view brings you a additional dramatic performance improvement.
Consider data compression when using highly redundant data, such as tables with many foreign keys. In particular, likely candidates are materialized views created with the ROLLUP clause.
The Oracle Communications Data Model Implementation and Operations Guide describes best practices for implementing a data warehouse based on the Oracle Communications Data Model.
This preface contains the following topics:
This document is intended for business analysts, data modelers, data warehouse administrators, IT staff, and ETL developers who implement an Oracle Communications Data Model warehouse.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
Oracle provides many resources for you when implementing the Oracle Communications Data Model.
Oracle Communications Data Model Documentation Set
For more information on Oracle Communications Data Model, see the following documents in the Oracle Communications Data Model Release 11g documentation set:
Oracle Technology Network
Visit the Oracle Technology Network (OTN to access to demos, whitepapers, Oracle By Example (OBE) tutorials, updated Oracle documentation, and other collateral.
Registering on OTN
You must register online before using OTN, Registration is free and can be done at
www.oracle.com/technetwork/index.html
Oracle Documentation on OTN
The Oracle Documentation site on OTN provides access to Oracle documentation. After you have a user name and password for OTN, you can go directly to the documentation section of the OTN Web site at
www.oracle.com/technetwork/indexes/documentation/index.html
Oracle Learning Library on OTN
The Oracle Learning Library provides free online training content (OBEs, Demos and Tutorials). After you have a user name and password for OTN, you can go directly to the Oracle Learning Library Web site at
www.oracle.com/technetwork/tutorials/index.html
Then you can search for the tutorial or demo (within "All") by name.
For example, search within "All" for "OCDMTutorial" to go to the tutorial for Oracle Communications Data Model.
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
d. $SJ$Vs vP/b0? £a"~!aP&Ƌ$6tK+Iu b,&CW
FG/wvP ? 6,!7Rm`big8:/ X_a<1ϋ llUm
FNRotߢKhdgegsmM
zudsQ6[&{ZvxwXU3 1R K fb48A*P ,
1oM3@R " -$B[-RYB'VAq\Y" U
giPD- DjxF*!&>#G2kJhN%߃tXh)5̩)#a((/rr(C!*2)++r,2,ײ,2-߲.2/2..s/r000'1+32/3734423O/?5s4S36s6_7ks6 8W7s73:39;;s<3<׳<3=߳>3?3>>t?&Թ@-
+1{h0: @8jr 2*O/#'/r0/!0֤ *P*As fK'qBKB*rr Go"(BQA"IKKpȲ>
L B?
U~l"22hiĂ&S#u f% К"E2rU5a`?`vaaKYb3b?d#6e;vee76f[6gogk6hGvfvgb6dd6ivkvjjjvmmӶlnvolvp6qppwqqvr3r?t#7u;wuu77v[7wowKf.
@ 1:r G4::
l*6:bvMN/-&y!<5#LނKoCB"px"BYcoFc6;
6P
"? @(mvƠ d,x\qV2#x T&71?(_?=,$O2V)H:.y99Ϲ9y9 a&-K ׂy
+C/ 'A|7 >>n.Aұ8SWK+R8W!(oA*A @6QxR@ -^o3 Nq@a7aJ=Ҩ̢kXOyH" /o[u#pP`)nC?0
lr(j
=C; ?oĩi_O vI$G /!J*|3 x'^FUG>ygyo^⧧z3aз{?|]?'|G?}g}߇?~秿~?=g` 8@ЀD`@{g_%8A
ro`5AvЃ aE8BVx@
UBЅ/a e8CІ7auCYN8D
BRF4
ĈЉOb8E*VъWQC.vы_cE "b(6эot8G:юwc G>яd 9HBҐD$8FF6ґSAHђd&5INvғe(E9JRҔDe*UJVҕe,e9KZҖe.uK^rf09Fp."e49MjVӚf6Mnvӛg8Ź_ӜDg:YhLUB 6 X8O~ӟh@:PԠ,:PJRB'mT#/`3σvԣ iHE:RԤ'eBRTd֔oCuH
2D!D2R|$ IFZ2%'LrR'5ܯLAqR\| .Z+^-
xz+`0b"_`ÖfZlc&6jN<6SM٬f7pChKa:NGԚzN1m̧Ϸn g@HPud!92TtBGnv^F/юrhH=:R$EISҕ0UiL[:ӗ4ũMsӝ@iP{:
ԣ*SKTR" B :XjV
*X"VV, _-"XmM\TԊwiz+E~յ_;06X;G8^bcbG=]dc&,fŬ uvt,g?eKSyXժb3j#Q;[ *nU Sh \wonp\]ntk=ѱU m]vwH^a>Qнt{SFW] a~Mߢ u" :!x, aX%,V$VG\/a(v8fE,/s'U|1/N#e c7ָq0 +
ѣ 萋, ,B2hLbVy*gѤk:P!md27yri,5Gͧ͘|7C (-pr="C2} 2m/2I>iy
T0QY5j7yeUa>V:aZ[&zV^r+Q<ծmquƾ6La, 1z4Hcٹߞl)+7rgVg[\7nuxw=oyכwo}wEӖo7p9
pGw,[6e yȱ=@'d]^ /o#AKw *jaS
w| x#>[V
*
qT )]n[D۰9յ7|տ>'^݊x!XJYvjm 0[$!
;Ko¨>D%M}ijA
} 4 ֯͟R<Կ&_ӿO
@ ܿ?<@4@l\@@@ $@|@
A ,A\Al@|
AA,@슼8!Hj01 @#>& e@#4C4p?j9KC(
*g鞢GI)K *0zxBRʪF*Loƀ4*r ٧F⛆!jG6`y4 Ha 6`.2)̔6*k(Hꚧ+ ݪQ]j)qݾ֤kR#RkV}j)GL ` JǮ+Ȇ +RW+:fJFcNr*Na+,rꙦ!",kl>ɶJbw܂,*>lzl*ԭL݂RȎ-ٮzj:6 ۺ&kkr&vj欢6-6J}P(++^ h)V.dǢkنI+>lpj
ݽ:`c*jݩ.k
.kjk0aSbfʗҎ" j6ҪLf&ɲ'ŵ-:m1ߖ&_jlZ&MkĦ&(Tm*o4z"oo?hSoN@NlѶzF`Fh,>nj3d2-K^EUL?B 0pHQPpMЛH[ł[F np؊0
W7
/_qdq m`L11q1 1avSX1 1sT 1 r|e!+=pAV #?orj앁*22B2'wfRlG<'%sbrp#{+`H(=(FP**hrN*0311 32'2/3373?34G4O35W5_36g6o37w73883993::3;33(? S.(1bπ.`0/W,3@$T'3 %_ĕ-2+4DGt`H![<+Aí\/I5t&?DJ (W `KA'ɴA Д> %˔t!&Q 5NKW4Mc( _,u1RwWoHTK45tY#@MБTWՒhW]I5S7EZA3O͑\3Wߵb/&oL5*u/iPa3P'iQ36iuT^W.Vam?liwm7*jDk*ho Ŀ3qq 7r'r/7s7sk%sp?7uWu_7vg3IPwwwwjGi7*G @0וz7{{7|Ƿ|7}}7~~778 8'w0zGO8W_8xl(o=8y5]I*8888Ǹ88縎8xw9 9'OÐxsmW_9go9w99999ǹ999繞997 G /:7?:GO:W:go:˹w锟P'
{93
p9B::纮QSʭ;;w'{wOԛ: a|7` Poyu#;;cHU4Azǻ
`Ȝw{{
;;;n'6H5{h h뉻SV:T BOIkE@5$
Ւ<ʧʯ<˷˿<̳xWô{h5A
4|x_|3ϛ|7?=#
