|Oracle® Communications Data Model Operations Guide
11g Release 1 (11.2)
Part Number E15883-01
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:
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.
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.
In the (Online) Oracle BI Administrator Tool, select Manage, then Security, then Users, and then ocdm.
Ensure that the value for Logging level is
Open the Oracle Communications Data Model Repository, select Manage, and then Cache.
In the right-hand pane of the Cache Manager window, select all of the records, then right-click and select Purge.
Run the report or query that you want to track the SQL log.
Open the query log file (
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.
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.
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.
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.
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.|
|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
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:
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 "Enabling Cube Materialized Views".
To change the measures or dimensions of a cube, you must follow the steps outlined in "Changing an Oracle Communications Data Model OLAP Cube".
There are special considerations when working with Oracle Communications Data Model cubes for forecasts as discussed in "Working with Forecast Cubes"
In the Analytic Workspace Manager, connect to the
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.
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:
Use the information in Oracle Communication 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 new forecast in Oracle Communications Data Model:
Create a new cube to contain the results of the forecast.
Write an OLAP DML forecasting context program as described in Oracle OLAP DML Reference.
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
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 );
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' );
For tables that do not have partitions (that is, lookup tables and reference tables), you can change the existing tablespace for a table as described in "Diverting Partitions into New Tablespaces".
For tables that have partitions (that is, base, derived, and aggregate tables), you can specify that new interval partitions be generated into new tablespaces as described in "Changing an Existing Tablespace".
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.
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;
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 ;
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
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".
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;
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.
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;
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:
ocdm_sys is the main schema for Oracle Communications Data Model. This schema contains all the relational and OLAP components of Oracle Communications Data Model.
The Oracle Communications Data Model data mining result tables are also in this schema. The final output from the data mining models is saved back into OCDM_SYS schema for easy integration with other components. With mining result tables in OCDM_SYS schema, customer can run report joining relational content with Mining results together much easily. For example, customer can query for revenue sum of customers belonging to a specific segment according to Customer Segmentation model.
ocdm_mining is the data mining schema of Oracle Communications Data Model. This schema contains all the mining components of Oracle Communications Data Model except the final result tables.
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:
ocdm_mining schema requires select privileges to only a small amount of tables in the
ocdm_sys schema, (that is, only those tables used to hold the results of the data models shipped with Oracle Communications Data Model). If you decide to create additional data mining models that which requires more table access from
ocdm_sys schema, grant the select privilege explicitly.
The Intra-ETL programs are executed inside the
ocdm_sys schema, therefore, they require the full access to the that schema. By default, the Oracle Warehouse Builder Intra-ETL mappings for Oracle Communications Data Model connect to the ocdm_sys schema for intra-ETL execution.
By default, the Oracle Communications Data Model sample reports connect to the ocdm_sys schema directly. For security reason, you may want to grant only select privileges to those reporting users. To do this, take the following steps:
Create a dedicated reporting user (for example, OCDM_Report).
Grant select privilege for all Oracle Communications Data Model tables required for reporting to OCDM_Report. (The easiest way to do this is to grant all Oracle Communications Data Model tables that start with a prefix of DWA, DWB, DWD, DWR, or DWL.)
Create a view (or synonym) in OCDM_Report schema that points to the OCDM_SYS tables.
In the Oracle Business Intelligence Suite Enterprise Edition repository for Oracle Communications Data Model, change the connection information to point to the new schema.