Skip Headers
Oracle® Communications Data Model Operations Guide
11g Release 1 (11.2)
Part Number E15883-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

4 Working with an Oracle Communications Data Model Warehouse

In general, you manage an Oracle Communications Data Model data warehouse in much the same way that you manage any other data warehouse. After the initial data load, you perform incremental data loading as described in "Performing Incremental Data Loading of the Warehouse".

This chapter discusses considerations other than incremental data loading that are specific to an Oracle Communications Data Model data warehouse. This chapter includes the following topics:

Customizing the Reports Delivered with Oracle Communications Data Model

Sample reports and dashboards are delivered with Oracle Communications Data Model. These sample reports illustrate the analytic capabilities provided with Oracle Communications Data Model -- including the OLAP and data mining capabilities.

The sample reports were developed using Oracle Business Intelligence Suite Enterprise Edition which is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities. Thus, the reports also illustrate the ease with which you can use Oracle Business Intelligence Suite Enterprise Edition Answers and Dashboard presentation tools to create useful reports.

You use Oracle Business Intelligence Suite Enterprise Edition tools to customize and troubleshoot the execution of reports, as described in:

See:

Oracle Communication Data Model Reference for detailed information on the sample reports.

Note:

The reports and dashboards that are used in examples and delivered with Oracle Communications Data Model are provided only for demonstration purposes. They are not supported by Oracle.

Tools for Customizing Reports

You can use Oracle Business Intelligence Suite Enterprise Edition Answers and Dashboard presentation tools to customize the predefined sample dashboard reports:

  • Oracle BI Answers. Provides end user ad hoc capabilities in a pure Web architecture. Users interact with a logical view of the information -- completely hidden from data structure complexity while simultaneously preventing runaway queries. Users can easily create charts, pivot tables, reports, and visually appealing dashboards.

  • Oracle BI Interactive Dashboards. Provide any knowledge worker with intuitive, interactive access to information. The end user can be working with live reports, prompts, charts, tables, pivot tables, graphics, and tickers. The user has full capability for drilling, navigating, modifying, and interacting with these results.

Troubleshooting Reporting Performance

Take the following actions to identify problems generating a report created using Oracle Business Intelligence Suite Enterprise Edition:

  1. In the (Online) Oracle BI Administrator Tool, select Manage, then Security, then Users, and then ocdm.

    Ensure that the value for Logging level is 7.

  2. Open the Oracle Communications Data Model Repository, select Manage, and then Cache.

  3. In the right-hand pane of the Cache Manager window, select all of the records, then right-click and select Purge.

  4. Run the report or query that you want to track the SQL log.

  5. Open the query log file (NQQuery.log) under OracleBI\server\Log.

    The last query SQL is the log of the report you have just run.I f an error was returned in your last accessed report, there will be an error at the end of this log.

For example:

  • Error: Query Status: Query Failed: [nQSError: 15018] Incorrectly defined logical table source (for fact table Customer Mining) does not contain mapping for [Customer.Cell Phone Number, Customer.Customer Segment Name, Customer.Party Name].

    Meaning: This error occurs when there is a problem in the Business layer in your Oracle Business Intelligence Suite Enterprise Edition repository. Check the mappings.

    Action: Check the mapping for Customer.Cell Phone Number, Customer.Customer Segment Name, and Customer.Party Name.

  • Error: Query Status: Query Failed: [encloser: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist.

    Meaning: This error occurs when the Physical layer in your Oracle Business Intelligence Suite Enterprise Edition repository has the table which actually does not exist in database.

    Action: To find out which table has problem: Copy the sql query to database environment and execute the query. The table which does not exist will be marked out by the database client.

  • Error: Query Status: Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: connect failed because target host or object does not exist.

    Meaning: This error occurs when because the Database is not connected.

    Action: Check connecting information in physical layer and ODBC connection to ensure that the repository is connecting to the correct database.

Writing Your Own Queries and Reports

The ocdm_sys schema defines the relational tables and views in Oracle Communications Data Model. You can use any SQL reporting tool to query and report on these tables and views.

See:

Oracle Communication Data Model Reference for more information on Oracle Communications Data Model relational tables and views.

Oracle Communications Data Model also supports On Line Analytic Processing (OLAP) reporting through the use of OLAP cubes defined in the ocdm_sys schema. You can query and write reports on OLAP cubes by using SQL tools to query the views that are defined for the cubes or by using OLAP tools to directly query the OLAP components.

See:

Oracle Communication Data Model Reference for more information on Oracle Communications Data Model OLAP cubes and the relational views by which you can access them, and Oracle OLAP Application Developer's Guide for information on how to create queries and reports on OLAP cube data.

Example 4-1 Creating a Relational Query

For example, assume that you want to know the total call minutes for the top ten customers in the San Francisco area for Mar 2009. To answer this question, you might have to query the tables described in the following table.

Entity Name Table Name Description
WIRELESS CALL EVENT DWB_WRLS_CALL_EVT Occurrences of the wireless call.
CUSTOMER DWR_CUST Individual customers
ADDRESS LOCATION DWR_ADDR_LOC All addresses. The table has levels as country, state, city, address, and so on.
GEOGRAPHY CITY DWR_GEO_CITY The CITY level of the GEOGRAPHY hierarchy.

To make the query, you execute the following SQL statement.

SELECT cust_key, tot_call_min FROM 
(select         round(sum(call.call_drtn)/60,2) tot_call_min , call.cust_key
from    DWB_WRLS_CALL_EVT call,
DWR_CUST     cust,
DWR_ADDR_LOC addr,
DWR_GEO_CITY city
         Where to_date(to_char(call.evt_begin_dt,'MON-YY'),'MON-YY') like        to_date('MAR-07','MON-YY')
and cust.cust_key = call.cust_key
and cust.addr_loc_key = addr.addr_loc_key
and addr.geo_city_key = city.geo_city_key
and initcap(city.geo_city_name)='San Francisco'
group by call.cust_key
order by 1 desc) WHERE ROWNUM < 10;

The result of this query is shown below.

CUST_KEY TOT_CALL_MIN 
---------- ------------ 
      3390        101.6 
      4304       100.25 
      4269        97.37 
      4152        93.02 
      4230        92.97 
      4157        92.95 
      3345        91.62 
      4115        48.43 
      4111        44.48

Modifying and Creating New OLAP Cubes

You create new OLAP cubes in Oracle Communications Data Model in much the same way as you would in any other data warehouse. See Oracle OLAP Application Developer's Guide for more information.

Additionally, since all OLAP cubes are loaded with data from the materialized views that have a DWA_ prefix, when working with OLAP cubes in Oracle Communications Data Model, keep the following points in mind:

Enabling Cube Materialized Views

To enable the cube materialized views, take the following steps:

  1. In the Analytic Workspace Manager, connect to the ocdm_sys schema.

  2. From the cube list, select the cube which you want to enable.

  3. In the right pane, select the Materialized Views tab.

  4. Select Enable Materialized View Refresh of the Cube. then click Apply.

Note:

You cannot enable the cube materialized view for a forecast cube.

Changing an Oracle Communications Data Model OLAP Cube

Since all Oracle Communications Data Model cubes load data from tables with the DWA_ prefix, if you want to change the measures or dimensions of one cube, you must take the following steps:

  1. Use the information in Oracle Communication Data Model Reference, to identify the DWA_ table from which the OLAP cube is populated.

  2. Change the structure of the DWA_ table identified in Step 1.

  3. Change the OLAP cube and cube materialized views to reflect the new structure.

Working with Forecast Cubes

You cannot enable materialized views for an Oracle Communications Data Model forecast cube.

To create a new forecast in Oracle Communications Data Model:

  1. Create a new cube to contain the results of the forecast.

  2. Write an OLAP DML forecasting context program as described in Oracle OLAP DML Reference.

Modifying Data Mining Models

To customize Oracle Communications Data Model mining models, take the following steps:

  1. Change the definition for source materialized views used as input to the mining model.

    All Oracle Communications Data Model mining models use materialized views 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.

    For example, the activity create_churn_svm_model uses the DMV_CUST_CHRN_SRC_PRD source materialized view. To add a new column into this model, modify the following materialized views to add a new column:

    DMV_CUST_CHRN_SRC_SRC
DMV_CUST_CHRN_SRC_PRD
DMV_CUST_CHRN_SRC_TST
DMV_CUST_CHRN_APPLY_ALL

  2. Train the model again by calling Oracle Communications Data Model mining package.

    For example, to train the churn svm model, execute the following statement.

    ocdm_mining.create_churn_svm_model( MONTH_CODE );

  3. Ensure that the new column has been added into the model.

    For example, use 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(
    SELECT ATTRIBUTE_SET ROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('OCDM_CHURN_SVM'))
    WHERE CLASS='1' );

See also:

"Refreshing Data Mining Models", "Troubleshooting Data Mining Model Creation", and "Working with User Privileges"

Changing the Tablespaces and Partitions Used by Tables

You can change the tablespace and partitions used by tables. What you do depends on whether or not the Oracle Communications Data Model table has partitions:

Diverting Partitions into New Tablespaces

By default, Oracle Communications Data Model defines the partitioned tables as interval partitioning, which means the partitions are created only when new data arrives.

Consequently, for Oracle Communications Data Model tables that have partitions (that is, Base, Derived, and Aggregate tables), if you want the new interval partitions to be generated in new tablespaces rather than current ones, please issue the following statements.

ALTER TABLE table_name MODIFY DEFAULT ATTRIBUTES 
TABLESPACE new_tablespace_name;

When new data is inserted in the table specified by table_name, a new partition is automatically created in the tablespace specified by tablespace new_tablespace_name.

Changing an Existing Tablespace

For Oracle Communications Data Model tables that do not have partitions (that is, lookup tables and reference tables), if you want to change the existing tablespace for a table then issue the following statement.

ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;

Working with Compression

By using a compression algorithm specifically designed for relational data, Oracle is able to compress data much more effectively than standard compression techniques. More significantly, unlike other compression techniques, Oracle incurs virtually no performance penalty for SQL queries accessing compressed tables.

Oracle Communications Data Model leverages the compress feature for all base, derived, and aggregate tables which reduces the amount of data being stored, reduces memory usage (more data per memory block), and increases query performance.

You can specify table compression by using the COMPRESS clause of the CREATE TABLE statement or you can enable compression for an existing table by using ALTER TABLE statement as shown below.

alter table <tablename> move compress;

For example, you could issue the following statements to create a compressed table named DWB_ACCS_MTHD_PORT_HIST.

COMPRESS
Create table DWB_ACCS_MTHD_PORT_HIST 
(NP_RQST_HDR_CD                 VARCHAR2(30) 
,ACCS_MTHD_KEY                   NUMBER(30)  NOT NULL ENABLE  
,EXTRNL_OPRTR_KEY                NUMBER(30)  
…..
)  
tablespace TBS_BASE 
COMPRESS ;

Working with Parallelization

Oracle Communications Data Model leverages the parallel query and parallel DML feature of Oracle Database. Parallel operations speed up DML statement execution by dividing the work among multiple child processes. Each child process executes its portion of the work under its own parallel process transaction.

As shown in the following example, all Oracle Communications Data Model base, derived, and aggregate tables are, by default, created with initial parallel degree of 4.

PARALLEL (DEGREE 4) 
Create table DWB_ACCS_MTHD_PORT_HIST 
(NP_RQST_HDR_CD                 VARCHAR2(30) 
,ACCS_MTHD_KEY                   NUMBER(30)  NOT NULL ENABLE  
,EXTRNL_OPRTR_KEY              NUMBER(30)  
…..
)  
tablespace TBS_BASE 
PARALLEL (DEGREE 4) ;

If you have a bigger machine, (for example 16 CPUs), then you can alter the degree of parallelization for the entire session as descried in "Enabling Parallel Execution for a Session" or for certain DML operations as described in "Enabling Parallel Execution of DML Operations". Regardless at which level you enable prallelism, the setting of parallelism for a table influences the optimizer. Consequently, when using parallel query, also enable parallelism at the table level as described in "Enabling Parallel Execution at the Table Level".

Enabling Parallel Execution for a Session

Parallel query is the most commonly used of Oracle's parallel execution features. Parallel execution can significantly reduce the elapsed time for large queries. To enable parallelization for an entire session, execute the following statement.

alter session enable parallel query;

Enabling Parallel Execution of DML Operations

Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments. To enable parallelization of DML statements, execute the following statement.

alter session enable parallel dml;

When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that statement can be parallelized. The rules vary depending on whether or not the statement is a DML INSERT statement, or a DML UPDATE or DELETE statement.

Rules for Parallelizing DML UPDATE and DELETE statements

The following rules apply when determining how to parallelize DML UPDATE and DELETE statements:

  • Oracle can parallelize UPDATE and DELETE statements on partitioned tables, but only when multiple partitions are involved.

  • You cannot parallelize UPDATE or DELETE operations on a nonpartitioned table or when such operations affect only a single partition.

Rules for Paralellizing DML INSERT statements

The following rules apply when determining how to parallelize DML INSERT statements:

  • Standard INSERT statements using a VALUES clause cannot be parallelized.

  • Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.

Enabling Parallel Execution at the Table Level

The setting of parallelism for a table influences the optimizer. Consequently, when using parallel query, also enable parallelism at the table level by issuing the following statement.

alter table <table_name> parallel 32;

Working with User Privileges

Installing the Oracle Communications Data Model component creates two accounts: the OCDM_SYS and OCDM_MINING accounts. Installing the Oracle Communications Data Model sample reports create the OCDM_SAMPLE_SYS account. Please make sure you unlock those two accounts with new password following "Post-installation" section.

See:

Oracle Communication Data Model Installation Guide for information on installing Oracle Communications Data Model and for unlocking the OCDM_SYS and OCDM_MINING accounts

The OCDM_SYS and OCDM_MINING accounts serve different purposes:

The installation process grants the necessary privileges required for users of both accounts. Once you have installed the product, you only need to consider user privileges in the following situations:

Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us