Describes the two main approaches to create reports from data in an Oracle Communications Data Model warehouse: Relational Reporting and OLAP Reporting.
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.
Note:
The use of numbers as text in Lookup code allows you to group them by using only the first character of the lookup value code. This could provide an artificial hierarchy level.
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 Extended Edition.
The rest of this chapter explains how to create Oracle Communications Data Model reports. For examples of Oracle Communications Data Model reports, see:
How to Create a New Oracle Communications Data Model Dashboard
The sample reports provided with Oracle Communications Data Model that are documented in Oracle Communications Data Model Reference
See Also:
The Oracle By Example tutorial, entitled "Using Oracle OLAP 11g With Oracle BI Enterprise Edition". To access the tutorial, open the Oracle Learning Library in your browser by following the instructions in Oracle Technology Network; and, then, search for the tutorials by name