The Oracle Retail Reporting Landscape

Oracle Retail has two primary reporting tools, each serving a fundamentally different purpose:

Understanding when to use which tool is critical. Using BIP for analytics wastes time building static reports that should be interactive. Using OBIEE for formatted documents produces ugly, non-printable output.

BIP vs. OBIEE/OAS: When to Use Which

BI Publisher (BIP) Deep Dive

Oracle BI Publisher generates formatted, document-style reports. It separates the data query from the presentation template, allowing non-technical users to design report layouts.

Architecture

┌──────────────┐     ┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│  DATA MODEL  │────▶│  TEMPLATE    │────▶│  BIP ENGINE  │────▶│  OUTPUT      │
│              │     │  (RTF/XSL)   │     │              │     │  (PDF/Excel/ │
│ SQL Query    │     │              │     │ Merges data  │     │   HTML/CSV)  │
│ or           │     │ Layout with  │     │ with template│     │              │
│ Web Service  │     │ placeholders │     │              │     │ Delivered    │
│ or           │     │              │     │              │     │ via email,   │
│ OBIEE Data   │     │              │     │              │     │ FTP, or UI   │
└──────────────┘     └──────────────┘     └──────────────┘     └──────────────┘

Data Model

The data model defines where report data comes from. Common sources:

• SQL Query: Direct SQL against RMS, ReSA, or RI tables
• Web Service: REST or SOAP API calls to external systems
• OBIEE Subject Area: Reuse an existing OBIEE analysis as the data source

Template Types

• RTF (Rich Text Format): Designed in Microsoft Word with BIP's Template Builder plugin. Most common for formatted documents.
• XSL-FO: XML-based templates for complex layouts requiring pixel-perfect control
• Excel: Native Excel templates for spreadsheet-style reports
• HTML/CSS: Web-based templates for portal embedding

Common Oracle Retail BIP Reports

OBIEE / OAS Deep Dive

Oracle Analytics Server (OAS) — the successor to OBIEE (Oracle Business Intelligence Enterprise Edition) — is an interactive analytics platform that enables business users to explore data through dashboards, charts, and ad-hoc queries.

Key Concepts

• Analyses: Interactive queries with charts and tables. Users can drill down, filter, pivot, and export.
• Dashboards: Collections of analyses, prompts, and KPIs arranged on a page.
• Agents: Automated alerts that monitor conditions (e.g., "Notify me if any store's daily sales drop below $5,000").
• Prompts: Interactive filters that users apply to analyses (date range, department, region).

Building an Analysis

1. Select Subject Area — Choose the pre-built subject area (e.g., "Sales Performance")
2. Drag Columns — Drag measures (Sales Amount) and dimensions (Store, Week) to the query canvas
3. Add Filters — Restrict data (last 4 weeks, Department 100 only)
4. Choose Visualization — Bar chart, line chart, pivot table, heat map
5. Add to Dashboard — Place the analysis on a dashboard page

RPD Architecture

The RPD (Repository) is the brain of OBIEE/OAS. It is a metadata layer that maps physical database tables to business-friendly concepts.

Three RPD Layers

1. Physical Layer: Defines connections to the actual database tables (RI star schema). Maps physical table names, column names, and join relationships.

2. Business Model Layer: The critical middle layer where all business logic lives:

• Logical table mappings — maps multiple physical tables to a single logical concept (e.g., "Sales" maps to both daily and weekly fact tables)
• Calculated metrics — gross margin % = (retail - cost) / retail × 100
• Hierarchies — product, location, and calendar drill paths
• Security filters — restrict data by user role (regional managers only see their region)

3. Presentation Layer: Defines the subject areas that business users see. Organizes logical columns into folders with user-friendly names.

Physical Layer          Business Model Layer     Presentation Layer
┌─────────────────┐    ┌──────────────────┐     ┌──────────────────┐
│ W_RTL_SLS_TRX_F │───▶│ Logical: Sales  │────▶│ Subject Area:    │
│ W_RTL_INV_F     │───▶│ Logical: Inv    │────▶│ "Sales           │
│ W_PRODUCT_D     │───▶│ Logical: Product│────▶│  Performance"    │
│ W_INT_ORG_D     │───▶│ Logical: Store  │────▶│                  │
└─────────────────┘    └──────────────────┘     └──────────────────┘

Building Custom Reports

When to Build Custom

Oracle Retail ships pre-built reports for common scenarios, but every retailer has unique reporting needs:

• Custom KPIs: Metrics specific to the retailer's business model
• Cross-System Reports: Combining data from multiple sources not covered by pre-built subject areas
• Operational Reports: Store-level operational reports tailored to the retailer's processes
• Regulatory Reports: Government-mandated reporting formats

Custom BIP Report Steps

1. Create Data Model — Write the SQL query, define parameters, and test
2. Design Template — Create an RTF template in Word with placeholders for data fields
3. Upload to BIP Server — Deploy the data model and template
4. Configure Security — Assign roles that can run the report
5. Schedule (optional) — Set up automated execution and delivery

SELECT 
    d.dept,
    d.dept_name,
    cal.fiscal_week,
    cal.fiscal_year,
    SUM(CASE WHEN cal.fiscal_year = :P_YEAR 
             THEN f.sales_retail ELSE 0 END)    AS ty_sales,
    SUM(CASE WHEN cal.fiscal_year = :P_YEAR - 1 
             THEN f.sales_retail ELSE 0 END)    AS ly_sales,
    ROUND(
        (SUM(CASE WHEN cal.fiscal_year = :P_YEAR 
                  THEN f.sales_retail ELSE 0 END) 
       - SUM(CASE WHEN cal.fiscal_year = :P_YEAR - 1 
                  THEN f.sales_retail ELSE 0 END))
       / NULLIF(SUM(CASE WHEN cal.fiscal_year = :P_YEAR - 1 
                         THEN f.sales_retail ELSE 0 END), 0)
       * 100, 1
    )                                            AS comp_pct
FROM 
    w_rtl_sls_trx_f f
JOIN 
    w_product_d d ON f.product_key = d.product_key
JOIN 
    w_mcal_period_d cal ON f.day_key = cal.day_key
WHERE 
    cal.fiscal_year IN (:P_YEAR, :P_YEAR - 1)
    AND cal.fiscal_week = :P_WEEK
GROUP BY 
    d.dept, d.dept_name, cal.fiscal_week, cal.fiscal_year
ORDER BY 
    ty_sales DESC;

Report Bursting & Scheduling

Bursting is BIP's powerful feature for generating and distributing personalized reports automatically:

1. Single Query, Multiple Outputs: Run one SQL query that returns data for all regions
2. Split by Dimension: BIP automatically splits the data by region
3. Apply Templates: Each region's data is merged with the report template
4. Deliver: Each region's report is emailed to the respective regional manager

Example: A weekly sales report is burst by region — the Northeast VP gets Northeast data, the Southeast VP gets Southeast data, all from a single scheduled job.

Scheduling Options

• Time-Based: Run daily at 6 AM, weekly on Monday, monthly on the 1st
• Event-Based: Run after the nightly ETL completes
• On-Demand: User-triggered via the BIP web UI

Reporting in the Cloud

As retailers move to Oracle Retail Cloud Services:

• BIP is available as a cloud service (BIP Cloud) with the same functionality
• OAS is being replaced by Oracle Analytics Cloud (OAC) with enhanced self-service analytics
• Pixel Streams: Pre-built retail analytics content delivered as cloud-managed dashboards
• Data Visualization: OAC includes Tableau-like drag-and-drop visualization capabilities

Best Practices