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.
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 |
|
Occurrences of the wireless call. |
|
CUSTOMER |
|
Individual customers |
|
ADDRESS LOCATION |
|
All addresses. The table has levels as country, state, city, address, and so on. |
|
GEOGRAPHY CITY |
|
The |
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