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.

Oracle Communications Data Model also supports On Line Analytic processing (OLAP) reporting using 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 also

"Reporting Approaches in Oracle Communications Data Model", "Oracle OLAP Cube Views", and the discussion on querying dimensional objects in Oracle OLAP User's Guide.

Example 5-1 Creating a Relational Query for Oracle Communications Data Model

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


Table 5-1 Creating a Relational Query

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 perform this 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-12','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