Skip Headers
Oracle® Retail Data Model Implementation and Operations Guide
Release 11.3.2

Part Number E20363-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

5 Report and Query Customization

This chapter provides information about creating reports, queries, and dashboards against the data in Oracle Retail Data Model warehouse. It contains the following topics:

Reporting Approaches in Oracle Retail Data Model

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

Relational Reporting

With relational reporting, reports are created 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_ tables and DWA_ objects). When you need more detailed reports, generate the reports with base tables (DWB_), Reference tables (DWR_) and lookup tables (DWL_).

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_SKU_ITEM and DWR_ITEM, DWR_ITEM_SBC, DWR_ITEM_CLASS, and DWR_ITEM_DEPT tables to query against the SKU ITEM level Space Utilization entity such as DWD_SPACE_UTLZTN_ITEM_DAY. On the other hand, you need to use the higher level snowflakes at ITEM level and above such as DWR_ITEM, DWR_ITEM_SBC, DWR_ITEM_CLASS, and DWR_ITEM_DEPT to query against the DEPARTMENT level Space Utilization entity such as DWA_SPACE_UTLZTN_DEPT_DAY.

The lookup tables (that is tables, with the DWL_ prefix) represent the simpler dimensions comprising a single level containing a flat list of values.

OLAP Reporting

With OLAP reporting, Oracle OLAP cubes are accessed 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 objects stored in DWR_ tables, the OLAP dimension views contains information relating to the entire dimension including all the levels of the hierarchy logically partitioned by 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.

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.

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

Customizing Oracle Retail Data Model Reports

Sample reports and dashboards are delivered with Oracle Retail Data Model. These reports illustrate the analytic capabilities provided with Oracle Retail Data Model -- including the OLAP and data mining capabilities.

See:

Oracle Retail Data Model Installation Guide for more information on installing the reports and deploying the Oracle Retail Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance.

The Oracle Retail Data Model 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.

Oracle Business Intelligence Suite Enterprise Edition products are used to display and customize the predefined sample reports, or new reports can be created:

See:

Oracle Retail Data Model Reference for detailed information on the sample reports.

Writing Your Own Queries and Reports

The ordm_sys schema defines the relational tables and views in Oracle Retail Data Model. You can use any SQL reporting tool to query and report on these tables and views.

Oracle Retail Data Model also supports On Line Analytic Processing (OLAP) reporting using OLAP cubes defined in the ordm_sys schema. You can query and write reports on OLAP cubes using SQL tools to query the views that are defined for the cubes or using OLAP tools to directly query the OLAP components.

See also:

"Reporting Approaches in Oracle Retail 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 Retail Data Model

For example, assume that you want to know the total number of hours worked by each employee for September 2011. To answer this question, you might have to query the tables described in the following table.

Entity Name Table Name Description
Employee Labor Derived DWD_EMP_LBR Summary of employee labor in a day.
Employee DWR_EMP An individual who works for the retail organization, accepts direction from the retail store management and satisfies the statutory criteria requiring that payroll taxes and benefit contributions be paid by the retailer.
Business Month DWR_BSNS_MO Months as defined in the Business calendar.

To perform this query, execute the following SQL statement:

SELECT DE.emp_key,DBM.mo_cd,SUM(DEL.hrs_wrkd) AS tot_hrs_wrkd
FROM dwd_emp_lbr DEL, dwr_emp DE, dwr_bsns_mo DBM
WHERE DEL.emp_key = DE.emp_key
AND TO_DATE(DEL.day_key,'YYYYMMDD') BETWEEN DBM.mo_strt_dt AND DBM.mo_end_dt
AND DBM.mo_desc = 'BY 2011 M9'
GROUP BY DE.emp_key, DBM.mo_cd;

The result of this query is:

EMP_KEY    MO_CD       TOT_HRS_WRKD
1005875    20110905    82
1005479    20110905    46
1005552    20110905    32
1005539    20110905    46
1005813    20110905    44
1005460    20110905    50
1006208    20110905    54
1005829    20110905    56

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 type of query, do the following:

Using this option 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.

Example 5-2 Star Transformation

A business question that could be asked against the star schema in Figure 5-1, "Star Schema Diagram" would be "What was the total number of Widgets sold in Nashua during the month of May 2008?"

  1. The original query.

    SELECT SUM(DRSRL.qty) tot_Widgets_in_Nashua 
    FROM dwb_rtl_sl_rtrn_li DRSRL, dwr_cust DC, dwr_sku_item DSI, dwr_day DD
    WHERE DRSRL.cust_key = DC.cust_key
    AND   DRSRL.sku_item_key = DSI.sku_item_key
    AND   DRSRL.day_key = DD.bsns_day_key
    AND   DC.city = 'Nashua'
    AND   DSI.sku_item_name = 'Widgets'
    AND   DD.bsns_mo_desc = 'BY 2008 M5'
    ;
    

    As you can see all of the where clause predicates are on the dimension tables and the fact table (dwb_rtl_sl_rtrn_li) is joined to each of the dimensions using their foreign key, primary key relationship.

  2. Take the following actions:

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

    2. Set the initialization parameter STAR_TRANSFORMATION_ENABLED to TRUE.

  3. The rewritten query. Oracle rewrites and transfers the query to retrieve only the necessary rows from the fact table using bitmap indexes on the foreign key columns:

    SELECT SUM(DRSRL.qty) tot_Widgets_sld_in_Nashua
    FROM dwb_rtl_sl_rtrn_li DRSRL
    WHERE DRSRL.cust_key IN (SELECT DC.cust_key FROM dwr_cust DC WHERE DC.city = 'Nashua')
    AND   DRSRL.sku_item_key IN (SELECT DSI.sku_item_key FROM dwr_sku_item DSI WHERE DSI.sku_item_name = 'Widgets')
    AND   DRSRL.day_key IN (SELECT DD.bsns_day_key FROM dwr_day DD WHERE DD.bsns_mo_desc = 'BY 2008 M5')
    ;
    

    By rewriting the query in this fashion you can now leverage the strengths of bitmap indexes. Bitmap indexes provide set based processing within the database, allowing you to use various fact methods for set operations such as AND, OR, MINUS, and COUNT. So, you use the bitmap index on day_key to identify the set of rows in the fact table corresponding to sales in May 2008. In the bitmap the set of rows are actually represented as a string of 1's and 0's. A similar bitmap is retrieved for the fact table rows corresponding to the sale of the Widgets and another is accessed for sales made in Nashua. At this point there are three bitmaps, each representing a set of rows in the fact table that satisfy an individual dimension constraint. The three bitmaps are then combined using a bitmap AND operation and this newly created final bitmap is used to extract the rows from the fact table needed to evaluate the query.

  4. Using the rewritten query, Oracle joins the rows from fact tables to the dimension tables.

    The join back to the dimension tables is normally done using a hash join, but the Oracle Optimizer selects the most efficient join method depending on the size of the dimension tables.

The typical execution plan for a star query when STAR_TRANSFORMATION has kicked in. The execution plan may not look exactly as you expect. There is no join back to the dwr_cust table after the rows have been successfully retrieved from the dwb_rtl_sl_rtrn_li table. In the select list, there is not anything actually selected from the dwr_cust 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 Retail 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 ordm.

    Ensure that the value for Logging level is 7.

  2. Open the Oracle Retail 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 a Report: A Table Does Not Exist

Assume the log file shows the following error:

Query Status: Query Failed: [encloser: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist.

This error occurs when the physical layer in your Oracle Business Intelligence Suite Enterprise Edition repository uses a table which actually does not exist in the Database.

To find out which table has problem:

  1. Copy the SQL query to the database environment.

  2. Execute the query.

The table which does not exist is marked out by the database client.

Example 5-4 Troubleshooting a Report: When the Database is Not Connected

Assume the log file contains the following error.

Error: 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 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.

Writing As Is and As Was Queries

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

Characteristics of an As Is Query

An As Is query has the following characteristics:

  • The resulting report shows the data as it happened.

  • The snowflake dimension tables are also joined using the surrogate key columns (that is the primary key and foreign key columns).

  • The fact table is joined with the dimension tables (at leaf level) using the surrogate key column.

  • Slowly-changing data in the dimensions are joined with their corresponding fact records and are presented individually.

  • It is possible to add up the components if the different versions share similar characteristics.

Characteristics of an As Was Query

An As Was query (also known as point-in-time analysis) has the following characteristics:

  • The resulting report shows the data that would result from freezing the dimensions and dimension hierarchy at a specific point in time.

  • Each snowflake table is initially filtered by applying a point-in-time date filter which selects the records or versions which are valid as of the analysis date. This structure is called the point-in-time version of the snowflake.

  • The filtered snowflake is joined with an unfiltered version of itself by using the natural key. All of the snowflake attributes are taken from the point-in-time version alias. The resulting structure is called the composite snowflake.

  • A composite dimension is formed by joining the individual snowflakes on the surrogate key.

  • The fact table is joined with the composite dimension table at the leaf level using the surrogate key column.

  • The point-in-time version is super-imposed on all other possible SCD versions of the same business entity -- both backward as well as forward in time. Joining in this fashion gives the impression that the dimension is composed of only the specific point-in-time records.

  • All of the fact components for various versions add up correctly due to the super-imposition of point-in-time attributes within the dimensions.

Examples: As Is and As Was Queries

Based on the data shown in Data used for the examples illustrates the characteristics of As Is and As Was queries:

Data used for the examples

Assume that the data warehouse has DWR_SKU_ITEM , DWR_ITEM , DWR_ITEM_SBC and DWB_RTL_SL_RTRN_LI fact tables. As of January 1, 2012, these tables include the following values.

SKU Item Table

SKU_ITEM_KEY SKU_ITEM_NBR ITEM_KEY SKU_ITEM_NAME EFF_FROM_DT EFF_TO_DT
1 389338009222279 1 Antacid 1/1/2011 12/31/2099
2 41204000420118700 2 Apple Tea 1/1/2011 12/31/2099
3 3632320042915860 3 Vitamin Water 1/1/2011 12/31/2099
4 38465200581121200 4 Mineral Water 1/1/2011 12/31/2099

Item Table

ITEM_KEY ITEM_NBR SBC_KEY ITEM_NAME EFF_FROM_DT EFF_TO_DT
1 100309620 10 Antacid 1/1/2011 12/31/2099
2 100309700 1 Apple Tea 1/1/2011 12/31/2099
3 100309970 1 Vitamin Water 1/1/2011 12/31/2099
4 100309110 1 Mineral Water 1/1/2011 12/31/2099

Item Subclass Table

SBC_KEY SBC_CD ITEM_CLAS_KEY SBC_NAME EFF_FROM_DT EFF_TO_DT
1 BVGS 1 BEVERAGES 1/1/2011 12/31/2099
10 HLTH 10 HEALTH 1/1/2011 12/31/2099

Retail Sales Table

DAY_KEY SKU_ITEM_KEY SLS_AMT
20111230 1 100
20111230 2 100
20111230 3 100
20111230 4 100
20111231 1 100
20111231 2 100
20111231 3 100
20111231 4 100

Assume that the following event occurred in January 2012:

On January 2, 2012 Apple Tea and Vitamin Water, these two SKUs moved from subclass Beverages to Health. Consequently, as shown, January 3, 2012, DWR_SKU_ITEM, DWR_ITEM, DWR_ITEM_SBC and DWB_RTL_SL_RTRN_LI tables have new data.

SKU Item Table

SKU_ITEM_KEY SKU_ITEM_NBR ITEM_KEY SKU_ITEM_NAME EFF_FROM_DT EFF_TO_DT
1 389338009222279 1 Antacid 1/1/2011 12/31/2099
2 41204000420118700 2 Apple Tea 1/1/2011 1/1/2012
3 3632320042915860 3 Vitamin Water 1/1/2011 1/1/2012
4 38465200581121200 4 Mineral Water 1/1/2011 12/31/2099
1008 41204000420118700 1008 Apple Tea 1/2/2012 12/31/2099
1009 3632320042915860 1009 Vitamin Water 1/2/2012 12/31/2099

Item Table

ITEM_KEY ITEM_NBR SBC_KEY ITEM_NAME EFF_FROM_DT EFF_TO_DT
1 100309620 10 Antacid 1/1/2011 12/31/2099
2 100309700 1 Apple Tea 1/1/2011 1/1/2012
3 100309970 1 Vitamin Water 1/1/2011 1/1/2012
4 100309110 1 Mineral Water 1/1/2011 12/31/2099
1008 100309700 10 Apple Tea 1/2/2012 12/31/2099
1009 100309970 10 Vitamin Water 1/2/2012 12/31/2099

Item Subclass Table

SBC_KEY SBC_CD ITEM_CLAS_KEY SBC_NAME EFF_FROM_DT EFF_TO_DT
1 BVGS 1 BEVERAGES 1/1/2011 12/31/2099
10 HLTH 10 HEALTH 1/1/2011 12/31/2099

Retail Sales Table

DAY_KEY SKU_ITEM_KEY SLS_AMT
20111230 1 100
20111230 2 100
20111230 3 100
20111230 4 100
20111231 1 100
20111231 2 100
20111231 3 100
20111231 4 100
20120102 1 100
20120102 1008 100
20120102 1009 100
20120102 4 100

Assuming the data used for the examples, to show the sales data split by Item Subclass, the SQL statement in Example 5-5 that joins the sales fact table and the item dimensions on the SKU_ITEM_KEY surrogate key. Also snowflake tables for Item dimension DWR_ITEM is joined with DWR_SKU_ITEM by ITEM_KEY and DWR_ITEM_SBC is joined with DWR_ITEM by SBC_KEY.

Example 5-5 As Is Query for Sales Split by Item Subclass

SELECT SKU.sku_item_name
,SBC.sbc_name
,SUM(SLS_AMT) sls_amt
FROM dwb_rtl_sl_rtrn_li SLS, dwr_sku_item SKU, dwr_item ITEM, dwr_item_sbc SBC
WHERE SLS.sku_item_key = SKU.sku_item_key
AND SKU.item_key = ITEM.item_key
AND ITEM.sbc_key = SBC.sbc_key
GROUP BY SBC.sbc_name, SKU.sku_item_name
ORDER BY SBC.sbc_name, SKU.sku_item_name;

The result of the query is shown in the table Table 5-1.

Table 5-1 As Is Query Result for Sales Split by Item Subclass

SKU_ITEM_NAME SBC_NAME SLS_AMT

Apple Tea

BEVERAGES

200

Mineral Water

BEVERAGES

300

Vitamin Water

BEVERAGES

200

Antacid

HEALTH

300

Apple Tea

HEALTH

100

Vitamin Water

HEALTH

100


Assuming the data used for the examples, issue the SQL statement shown in Example 5-6 to show the sales data split by subclass using an analysis date of January 1, 2012.

Example 5-6 As Was Query for Sales Split by Item Subclass

SELECT SKU.sku_item_name
,SBC.sbc_name
,SUM(SLS_AMT) sls_amt
FROM dwb_rtl_sl_rtrn_li SLS, dwr_item_sbc SBC,
( SELECT ACT.item_key, PIT.item_nbr, PIT.sbc_key, PIT.item_name
FROM dwr_item ACT INNER JOIN
(SELECT item_key, item_nbr, sbc_key, item_name
FROM dwr_item
WHERE TO_DATE('20120101','YYYYMMDD') BETWEEN eff_from_dt AND eff_to_dt
)PIT
ON ACT.item_nbr = PIT.item_nbr
) ITEM,
( SELECT ACT.sku_item_key, PIT.sku_item_nbr, PIT.item_key, PIT.sku_item_name
FROM dwr_sku_item ACT INNER JOIN
(SELECT sku_item_key, sku_item_nbr, item_key, sku_item_name
FROM dwr_sku_item
WHERE TO_DATE('20120101','YYYYMMDD') BETWEEN eff_from_dt AND eff_to_dt
)PIT
ON ACT.sku_item_nbr = PIT.sku_item_nbr
) SKU
WHERE SLS.sku_item_key = SKU.sku_item_key
AND SKU.item_key = ITEM.item_key
AND ITEM.sbc_key = SBC.sbc_key
GROUP BY SBC.sbc_name, SKU.sku_item_name
ORDER BY SBC.sbc_name, SKU.sku_item_name

The results of this query are shown in Table 5-2. Since Apple Tea and Vitamin Water were under Beverages subclass, the sales amount for these two are accounted under the Beverages subclass.

Table 5-2 As Was Query Result for Sales Split by Item Subclass (Beverages Subclass)

SKU_ITEM_NAME SBC_NAME SLS_AMT

Apple Tea

BEVERAGES

300

Mineral Water

BEVERAGES

300

Vitamin Water

BEVERAGES

300

Antacid

HEALTH

300


Assume instead that you issued the exact same query except that the to_date phrase you specify was January 3, 2012 rather than January 1, 2012. All sales for these two products, Apple Tea and Vitamin Water, would be accounted under the Health subclass.

SELECT SKU.sku_item_name
,SBC.sbc_name
,SUM(SLS_AMT) sls_amt
FROM dwb_rtl_sl_rtrn_li SLS, dwr_item_sbc SBC,
( SELECT ACT.item_key, PIT.item_nbr, PIT.sbc_key, PIT.item_name
FROM dwr_item ACT INNER JOIN
(SELECT item_key, item_nbr, sbc_key, item_name
FROM dwr_item
WHERE TO_DATE('20120103','YYYYMMDD') BETWEEN eff_from_dt AND eff_to_dt
)PIT
ON ACT.item_nbr = PIT.item_nbr
) ITEM,
( SELECT ACT.sku_item_key, PIT.sku_item_nbr, PIT.item_key, PIT.sku_item_name
FROM dwr_sku_item ACT INNER JOIN
(SELECT sku_item_key, sku_item_nbr, item_key, sku_item_name
FROM dwr_sku_item
WHERE TO_DATE('20120103','YYYYMMDD') BETWEEN eff_from_dt AND eff_to_dt
)PIT
ON ACT.sku_item_nbr = PIT.sku_item_nbr
) SKU
WHERE SLS.sku_item_key = SKU.sku_item_key
AND SKU.item_key = ITEM.item_key
AND ITEM.sbc_key = SBC.sbc_key
GROUP BY SBC.sbc_name, SKU.sku_item_name
ORDER BY SBC.sbc_name, SKU.sku_item_name
;

The result of this query is shown in Table 5-3.

Table 5-3 As Was Query Result for Sales Split by Item Subclas (Health Subclass)

SKU_ITEM_NAME SBC_NAME SLS_AMT

Mineral Water

BEVERAGES

300

Antacid

HEALTH

300

Apple Tea

HEALTH

300

Vitamin Water

HEALTH

300


Tutorial: Creating a New Oracle Retail Data Model Dashboard

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

See:

Oracle Retail Data Model Installation Guide for more information on installing the sample reports and deploying the Oracle Retail Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance.

In this example, assume that you are creating a dashboard named "Category Manager", and you put both "Customer Sales Value by Year " and "Bottom N Customer" into this new dashboard.

To create a dashboard, take the following steps:

  1. In a 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 ordm, and provide the password.

  3. Select New, and then select Dashboard to create an Oracle Business Intelligence Suite Enterprise Edition dashboard. For example, see Figure 5-1.

    Figure 5-1 New Dashboard Start: BIEE Home

    Description of Figure 5-1 follows
    Description of "Figure 5-1 New Dashboard Start: BIEE Home"

  4. Enter a name and description, as shown in Figure 5-2. Select the save location to save the dashboard to the Dashboards folder, then click OK.

    Figure 5-2 New Dashboard: Enter Name

    Description of Figure 5-2 follows
    Description of "Figure 5-2 New Dashboard: Enter Name"

  5. In the Catalog view, expand the Customer RFMP folder, as shown in Figure 5-3. In the navigator, you can see the Customer Sales Value by Year Report; drag it from catalog view into the right panel.

    Figure 5-3 New Dashboard: Catalog View

    Description of Figure 5-3 follows
    Description of "Figure 5-3 New Dashboard: Catalog View"

  6. From the navigator, in the same folder, drag the Bottom N Customer report into the right pane, as shown in Figure 5-4.

    Figure 5-4 New Dashboard: Catalog View with New Reports Vertical

    Description of Figure 5-4 follows
    Description of "Figure 5-4 New Dashboard: Catalog View with New Reports Vertical"

  7. You can change the layout of this section to organize the two reports, either horizontal or vertical, as shown in Figure 5-4, and Figure 5-5.

    Figure 5-5 New Dashboard: Catalog View with New Reports Horizontal

    Description of Figure 5-5 follows
    Description of "Figure 5-5 New Dashboard: Catalog View with New Reports Horizontal"

  8. The page has the default name, "Page1". To change the name, do the following:

    1. Select the Dashboard, as shown in Figure 5-6.

      Figure 5-6 New Dashboard: Select Name for Page

      Description of Figure 5-6 follows
      Description of "Figure 5-6 New Dashboard: Select Name for Page"

    2. In Dashboard Properties window, click Change Name, as shown in Figure 5-7.

      Figure 5-7 New Dashboard: Enter New Page Name

      Description of Figure 5-7 follows
      Description of "Figure 5-7 New Dashboard: Enter New Page Name"

    3. Change the name to "Customer Sales Value by Year & Bottom Customer", as shown in Figure 5-8. Then click OK.

      Figure 5-8 New Dashboard: Rename Page Dialog

      Description of Figure 5-8 follows
      Description of "Figure 5-8 New Dashboard: Rename Page Dialog"

  9. Click Save on the top of the dashboard. The new dashboard is shown in Figure 5-9.

    Figure 5-9 New Dashboard: Display with Two New Reports

    Description of Figure 5-9 follows
    Description of "Figure 5-9 New Dashboard: Display with Two New Reports"

Oracle by Example:

For more information on creating dashboards see the "Creating Analyses and Dashboards 11g" OBE tutorial.

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 tutorial by name.

Tutorial: Creating a New Oracle Retail Data Model Report

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

See:

Oracle Retail Data Model Installation Guide for more information on installing the sample reports and deploying the Oracle Retail Data Model RPD and webcat on the Business Intelligence Suite Enterprise Edition instance.

In this example, assume that you are creating a report named "Vendor Sales by Channel" to show sales values by time, channel, and vendor.

To create a 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 ordm, and provide the password.

  3. Select New, and then select Analysis to create an Oracle Business Intelligence Suite Enterprise Edition report, as shown in Figure 5-10.

    Figure 5-10 Analysis Report: Welcome Page with New Menu

    Description of Figure 5-10 follows
    Description of "Figure 5-10 Analysis Report: Welcome Page with New Menu"

  4. Select Subject Area, and then select RBIAII to create a relational report, as shown in Figure 5-11.

    Figure 5-11 Analysis Report: Welcome Page with Select Subject Area Menu

    Description of Figure 5-11 follows
    Description of "Figure 5-11 Analysis Report: Welcome Page with Select Subject Area Menu"

  5. Drag and drop the dimension and fact columns into the Selected Columns panel, as shown in Figure 5-12.

    Figure 5-12 Analysis Report: Welcome Page with Selected Columns

    Description of Figure 5-12 follows
    Description of "Figure 5-12 Analysis Report: Welcome Page with Selected Columns"

  6. Select the Results tab to view the report, as shown in Figure 5-13.

    Figure 5-13 Analysis Report: Results View of Report

    Description of Figure 5-13 follows
    Description of "Figure 5-13 Analysis Report: Results View of Report"

  7. Select New View to add a chart to the report, as shown in Figure 5-14.

    Figure 5-14 New Report: Create New View

    Description of Figure 5-14 follows
    Description of "Figure 5-14 New Report: Create New View"

  8. Select Save to save this report. The report including the new values is shown in Figure 5-15.

    Figure 5-15 New Report: Final View of New Reports

    Description of Figure 5-15 follows
    Description of "Figure 5-15 New Report: Final View of New Reports"

Oracle by Example:

For more information on creating a report, see the "Creating Analyses and Dashboards 11g" OBE tutorial.

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 tutorial by name.