5 Report and Query Customization

Reporting Approaches in Oracle Communications Data Model

There are two main approaches to creating reports from data in an Oracle Communications Data Model warehouse:

Relational Reporting

With relational reporting, you create reports against the analytical layer entities using the fact entities as the center of the star with the reference entities (that is, DWR_ and DWL_ tables) acting as the dimensions of the star. Typically the fact entities include the derived and aggregate entities (that is, DWD_ and DWA_ tables). However in some cases, you may need to use the base entities (that is, DWB_ tables) along with the reference tables to generate more detailed reports.

The reference tables (that is, DWR_ tables) typically represent dimensions which contain a business hierarchy and are present in the form of snowflake entities containing a table for each level of the hierarchy. This allows us to attach the appropriate set of reference entities for the multiple subject area and fact entities composed of differing granularity.

For example, you can use the set of tables comprising DWR_DAY and DWR_BSNS_MO, DWR_BSNS_QTR, DWR_BSNS_YR tables to query against a DAY level CDR Wireless entity such as DWD_VOI_CALL_DAY. On the other hand, you need to use the higher level snowflakes at Month level and above such as DWR_BSNS_MO, DWR_BSNS_QTR, DWR_BSNS_YR in order to query against the MONTH level CDR Wireless entity such as DWA_VOI_CALL_MO.

The lookup tables (that is tables, with the DWL_ prefix) represent the simpler dimensions comprising a single level containing a flat list of values. Typically, most reporting tools add a superficial top level to the dimension. These could be individual tables starting with DWL_ or views (also named DWL_) on DWL_CODE_MASTER that break out different code types into separate dimensions.

OLAP Reporting

With OLAP reporting, you access Oracle OLAP cubes using SQL against the dimension and cube (fact) views. Cubes and dimensions are represented using a star schema design. Dimension views form a constellation around the cube (or fact) view. The dimension and cube views are relational views with names ending with _VIEW. Typically, the dimension view used in the reports is named dimension_hierarchy_VIEW and the cube view is named cube_VIEW.

Unlike the corresponding relational dimension entities stored in DWR_ tables, the OLAP dimension views contains information relating to the whole dimension including all the levels of the hierarchy logically partitioned on the basis of a level column (identified as level_name). On a similar note, the cube views also contain the facts pertaining to the cross-combination of the levels of individual dimensions which are part of the cube definition. Also the join from the cube view and the dimension views are based on the dimension keys along with required dimension level filters.

Although the OLAP views are also modeled as a star schema, there are certain unique features to the OLAP reporting methodology which requires special modeling techniques in Oracle Business Intelligence Suite Enterprise Edition.

The rest of this chapter explains how to create Oracle Communications Data Model reports. For examples of Oracle Communications Data Model reports, see:

• "Writing As Is and As Was Queries"

• "Tutorial: Creating a New Oracle Communications Data Model Dashboard"

• "Tutorial: Creating a New Oracle Communications Data Model Report"

• The sample reports provided with Oracle Communications Data Model that are documented in Oracle Communications Data Model Reference.

Customizing Oracle Communications Data Model Sample Reports

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 business intelligence 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 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.

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.

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;

  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

Optimizing Star Queries

A typical query in the access layer is a join between the fact table and some number of dimension tables and is often referred to as a star query. In a star query each dimension table is joined to the fact table using a primary key to foreign key join. Normally the dimension tables do not join to each other.

Typically, in this kind of query all of the WHERE clause predicates are on the dimension tables and the fact table. Optimizing this type of query is very straight forward.

To optimize this query, do the following:

• Create a bitmap index on each of the foreign key columns in the fact table or tables

• Set the initialization parameter STAR_TRANSFORMATION_ENABLED to TRUE.

This enables the optimizer feature for star queries which is off by default for backward compatibility.

If your environment meets these two criteria, your star queries should use a powerful optimization technique that rewrites or transforms your SQL called star transformation. Star transformation executes the query in two phases:

1. Retrieves the necessary rows from the fact table (row set).

2. Joins this row set to the dimension tables.

The rows from the fact table are retrieved by using bitmap joins between the bitmap indexes on all of the foreign key columns. The end user never needs to know any of the details of STAR_TRANSFORMATION, as the optimizer automatically chooses STAR_TRANSFORMATION when it is appropriate.

Example 5-2, "Star Transformation" gives the step by step process to use STAR_TRANSFORMATION to optimize a star query.

The following figure shows the typical execution plan for a star query when STAR_TRANSFORMATION has kicked in. The execution plan may not look exactly as you expected. There is no join back to the customer table after the rows have been successfully retrieved from the Sales table. If you look closely at the select list, you can see that there is not anything actually selected from the Customers table so the optimizer knows not to bother joining back to that dimension table. You may also notice that for some queries even if STAR_TRANSFORMATION does kick in it may not use all of the bitmap indexes on the fact table. The optimizer decides how many of the bitmap indexes are required to retrieve the necessary rows from the fact table. If an additional bitmap index would not improve the selectivity, the optimizer does not use it. The only time you see the dimension table that corresponds to the excluded bitmap in the execution plan is during the second phase or the join back phase.

Troubleshooting Oracle Communications Data Model Report 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 using 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. If an error was returned in your last accessed report, there is an error at the end of this log.

The following examples illustrate how to use these error messages:

• Example 5-3, "Troubleshooting an Oracle Communications Data Model Report"

• Example 5-4, "Troubleshooting a Report: A Table Does Not Exist"

• Example 5-5, "Troubleshooting a Report: When the Database is Not Connected"

Writing As Is and As Was Queries

Two common query techniques are "as is" and "as was" queries:

• Characteristics of an As Is Query

• Characteristics of an As Was Query

• Examples: As Is and As Was Queries Against Oracle Communications Data Model

SELECT cust.cust_nm, cust.m_status, SUM(fct.tx) 
FROM taxpaid fct, customer cust, county cnty 
WHERE fct.cust_id = cust.cust_id 
AND cust.cnty_id = cnt.cnt_id
GROUP BY cust.cust_nm, cust.m_status
ORDER BY 1,2,3;

select
   cust.cust_nm, cust.m_status, sum(fct.tax)
from
   TaxPaid fct, 
   (
      select 
         cust_act.cust_id, cust_pit.cust_cd, cust_pit.cust_nm,
         cust_pit.m_status, cust_pit.gender,
         cust_pit.cnty_id, cust_pit.cnty_cd, cust_pit.cnty_nm
      from Customer cust_act
      inner join (
         select
            cust_id, cust_cd, cust_nm,
            m_status, gender,
            cnty_id, cnty_cd, cnty_nm
         from Customer cust_all
         where to_date('15-JAN-2012', 'DD-MON-YYYY') between eff_from and eff_to
      ) cust_pit   
      on (cust_act.cust_cd = cust_pit.cust_cd)
   ) cust,
   (
      select 
         cnty_act.cnty_id, cnty_pit.cnty_cd, cnty_pit.cnty_nm
      from County cnty_act
      inner join (
         select
            cnty_id, cnty_cd, cnty_nm
         from County cnty_all
         where to_date('15-JAN-2012', 'DD-MON-YYYY') between eff_from and eff_to
      ) cnty_pit
      on (cnty_act.cnty_cd = cnty_pit.cnty_cd)
   ) cnty  
where fct.cust_id = cust.cust_id
and cust.cnty_id = cnty.cnty_id
GROUP BY cust.cust_nm, cust.m_status
order by 1,2,3;

SELECT cust.cust_nm, cnty.cnty_nm, SUM(fct.tax)
FROM TaxPaid fct, customer cust, county cnty
WHERE fct.cust_id = cust.cust_id
AND cust.cnty_id = cnty.cnty_ID
GROUP BY cut.cust_nm, cnty.cnty_nm
ORDER BY 1,2,3;

select
   cust.cust_nm, cnty.cnty_nm, sum(fct.tax)
from
  TaxPaid fct,
   (
      select 
         cust_act.cust_id, cust_pit.cust_cd, cust_pit.cust_nm,
         cust_pit.m_status, cust_pit.gender,
         cust_pit.cnty_id, cust_pit.cnty_cd, cust_pit.cnty_nm
      from Customer cust_act
      inner join (
         select
            cust_id, cust_cd, cust_nm,
            m_status, gender,
            cnty_id, cnty_cd, cnty_nm
         from Customer cust_all
         where to_date('15-JAN-2012', 'DD-MON-YYYY') between eff_from and eff_to
      ) cust_pit
      on (cust_act.cust_cd = cust_pit.cust_cd
   ) cust,
   (
      select 
         cnty_act.cnty_id, cnty_pit.cnty_cd, cnty_pit.cnty_nm
      from County cnty_act
      inner join (
         select
            cnty_id, cnty_cd, cnty_nm
         from County cnty_all
         where to_date('15-JAN-2012', 'DD-MON-YYYY') between eff_from and eff_to
      ) cnty_pit
      on (cnty_act.cnty_cd = cnty_pit.cnty_cd)
   ) cnty
where fct.cust_id = cust.cust_id
and cust.cnty_id = cnty.cnty_id
GROUP BY cust.cust_nm, cnty.cnty_nm
order by 1,2,3;

Tutorial: Creating a New Oracle Communications Data Model Dashboard

This tutorial explains how to create a dashboard based on the Oracle Communications Data Model webcat included with the sample Oracle Business Intelligence Suite Enterprise Edition reports delivered with Oracle Communications Data Model.

In this example assume that you want to create a dashboard named "Dropped call and Failed Call", and put both "Dropped Call Rate Report" and "Failed Call Rate Report" into this new dashboard.

To create a dashboard, take the following steps:

1. In the browser, open the login page at http://servername:9704/analytics where servername is the server on which the webcat is installed.

2. Login with username of ocdm, and provide the password.

   Then, click newDashboard to create an Oracle Business Intelligence Suite Enterprise Edition dashboard.

   
   Description of the illustration dbd1.gif
   
   

3. Input name and description, save it to the Network Health folder. Click OK.

   
   Description of the illustration dbd2.gif
   
   

4. In the Catalog view, expand the Network Health Analysis folder. You can see the Failed Call Rate Report and Dropped Call Rate Report.

   
   Description of the illustration dbd3.gif
   
   

5. Drag the Failed Call Rate Report and Dropped Call Rate Report from the Catalog view into the right panel.

   
   Description of the illustration dbd4.gif
   
   

6. You can change the layout of this section to organize the two reports by horizontal or vertical.

   
   Description of the illustration dbd5.gif
   
   

   Note that the page name is still "Page1" so you must change it.

7. To change the page name:

   1. Select the Dashboard.

      
      Description of the illustration dbd6.gif
      
      

   2. In Dashboard Properties window, click Change Name.

      
      Description of the illustration dbd7.gif
      
      

   3. Change the name to "Dropped Call Rate and Failed Call Rate", then click OK.

      
      Description of the illustration dbd8.gif
      
      

8. Click Save on the top of the dashboard. Now you have a new dashboard.

   
   Description of the illustration dbd9.gif
   
   

Tutorial: Creating a New Oracle Communications Data Model Report

This tutorial explains how to create a report based on the Oracle Communications Data Model webcat included with the sample Oracle Business Intelligence Suite Enterprise Edition reports delivered with Oracle Communications Data Model.

In this example, assume that you want to create a report named "Dropped call vs. Failed Call" to put both dropped call rate and failed call rate in one report.

To create a this new report, take the following steps:

1. In the browser, open the login page at http://servername:9704/analytics where servername is the server on which the webcat is installed.

2. Login with username of ocdm, and provide the password.

   Then, click newAnalysis to create an Oracle Business Intelligence Suite Enterprise Edition report.

   
   Description of the illustration rpt1.gif
   
   

3. Select Subject Area, then select ODWT to create a relational report.
   Description of the illustration rpt2.gif
   
   

4. Drag and put the dimension and fact columns into the Select Columns panel.
   Description of the illustration rpt3.gif
   
   

5. Select the Results tab to view the report
   Description of the illustration rpt4.gif
   
   

6. Click New View to add a chart into report.
   Description of the illustration rpt5.gif
   
   

7. Click Save to save this report to the Network Health Analysis folder

   
   Description of the illustration rpt6.gif