Using Discoverer To Create a Reporting Data Mart

1
Using Discoverer To Create a Reporting Data Mart
Are you in the right session?

• Do you need additional reports from Oracle
  Applications?
• Do you need reports that combine data from Oracle
  Applications with other sources such as
• Legacy data
• In-house systems other than Oracle Applications
• Vendor and/or customer information not supported
  in Oracle Applications
• Have you been in a meeting where several people
  had the same report but with different totals or
  had multiple definitions for the same data item?
• If you answered YES to any of these questions,
  you are in the right session.

2
Using Discoverer to Build a Reporting Data
Mart Barry Markovic
3
Using Discoverer To Create a Reporting Data Mart
Agenda

• Why build a Reporting Data Mart
• Building a Reporting Data Mart
• Using Discoverer to extend analytical and
  reporting capabilities

4
Using Discoverer To Create a Reporting Data Mart
Why Build a Reporting Data Mart?

• Provide reports with both Oracle and non-Oracle
  Applications data
• Improve overall system performance
• Standardize reporting formats and terminology
• Reduce cost and time to develop customer analysis
  and reports

5
Using Discoverer To Create a Reporting Data Mart

• Why Build a Reporting Data Mart?
• Provide reports with both Oracle Applications and
  non-Oracle Applications data

• Integrating legacy data
• Integrating non-Oracle Applications data
• Partners
• Market research
• Competitors
• Date Transformation
• Data Cleansing
• Data Summarization
• Data Categorization

6
Using Discoverer To Create a Reporting Data Mart

• Why Build a Reporting Data Mart?
• Improve overall system performance

• Create custom tables designed for reporting
  requirements
• Reduce the number of tables needed in queries
• Filter data when loading tables based on
  reporting requirements
• Create indexes on data elements for quicker data
  retrieval
• De-normalize data for quicker response time
• Create summary tables for management level
  reporting
• Avoid using Oracle Applications tables while they
  are being updated on-line
• Move Data Mart tables to another server
• Schedule extracts from Oracle Applications tables
  during non-peak times

7
Using Discoverer To Create a Reporting Data Mart

• Why Build a Reporting Data Mart?
• Standardize Reporting Formats and Terminology

• Provide a snapshot of the data for reporting
• All reports run throughout the day provide the
  same results.
• Create standard calculations for Key Performance
  Indicators
• Profit Margin
• Net Sales
• Etc.
• Create time sensitive data elements
• Weekly, Monthly, Quarterly
• Production Planning, Sales Forecasts, Cash Flow,
  Etc.
• Use Business terminology rather than Oracle data
  column names
• Create a user-friendly data dictionary

8
Using Discoverer To Create a Reporting Data Mart

• Why Build a Reporting Data Mart?
• Reduce cost and time for developing custom
  analysis and reports

• Simplify procedures for accessing business data
• Minimize Information Technology staff time
• Allow users to do their own ad hoc analysis
• Allow users to create standard production reports
• Allow super-user to perform administrative
  tasks

9
Using Discoverer To Create a Reporting Data Mart
Building a Reporting Data Mart

• Basic Steps
• Plan and Launch the Project
• Define Business Requirements
• Define the Technical Architecture
• Selecting Data Warehouse Tools
• Design Dimensional Data Models
• Create Users Analytical Applications
• Build the Data Staging Applications
• Maintain and Sustain the Data Warehouse

10
(No Transcript)
11
(No Transcript)
12
Using Discoverer To Create a Reporting Data Mart
Building Data Mart Tables

• Based on reporting requirements
• Standardize technical elements
• Table and column naming conventions
• Use defined prefix to identify the Oracle Source
  table
• Use defined prefix to identify calculated columns
  
• Standardize business terminology and calculations
• Net Sales Include/exclude samples, promotions,
  returns, recalls
• Identify quantity versus dollar amount
• Time dimensions Year-to-date, Prior year,
  monthly, etc.
• Calculations customized for each organization
• Forecast that includes year-to-date net shipments
  plus sales forecast for the remainder of the year.

13
Cross-Reference of Prefix to Oracle Table
Prefix Oracle Table or View 1 Co
ra_contacts 2 Dl wsh_deliveries 3
Dp wsh_departures 4 Fu
fnd_users 5 Ho so_order_holds 6 Mc
mtl_categories 7 Oh so_headers 8
Ol so_lines 9 Op
hr_operating_units
Prefix Oracle Table or View 10 Pd
so_picking_line_details 11 Ph
so_picking_headers 12 Pl so_picking_lines 13
Pr so_price_lists 14 Ra ra_addresses 15
Rc ra_customers 16 Ro ra_contacts 17
Rr mtl_so_rma_receipts 18 Rs ra_site_uses
14
Order Entry Items - Business Definitions
Report Column Transaction(s) / Comments 1
Entered Orders Entered Orders Entered Orders
which have not been booked in Oracle's Order
Entry cycle. 2 Booked Orders Booked
Orders Booked Orders. May or may not be
shipped. 3 Cancel Cancelled Orders Cancelled
Orders cannot have been shipped. 4
Bookings Entered Orders Booked Orders -
Cancelled Orders - Returns Bookings include
any item on order less cancelled orders less
items returned. This is the same as Open
orders plus net shipments. It is essentially
what has been or will be sold. Note Open Orders
at the end of a year are automatically
included in Bookings for the new year. 5
Gross Ship Total Shipped Items shipped which may
or may not be returned. 6 Net Ship Total Shipped
Returns Shipped items which have not been
returned. 7 Net Open Order Entered Orders
Booked Orders - Cancelled Orders Total
Shipped Items which are currently on Order but
have not been shipped. Returns will not
affect Net Open Orders (If an item is returned,
it will not automatically become an open
order). 8 Return Returns Returns are not
recognized until items received or customer
documents the items were destroyed.
15
Standard Reporting Prefixes and Suffixes
Prefix Business Meaning CY Current
Year CYTD Current Year To Date CM Current
Month CMTD Current Month To Date CM1 Next Month
PY Prior Year PYTD Prior Year To Date
Suffix Business Meaning QTY Quantity (Units)
AMT Currency (Dollars) PCT Percent AVG Average
16
Using Discoverer To Create a Reporting Data Mart
Building Data Mart Tables - continued

• Extract Programs
• Primarily SQL scripts with some PL/SQL
• Ease of development and maintainability primary
  factors
• Transformations
• De-normalized data for ease of reporting
• Embed common calculations
• Load Programs
• Designed for ease of loading on a nightly basis
• Used multiple load steps rather than more
  complicated logic
• Designed to run concurrent processes
• Scheduled in off-peak time 200 AM through
  500 AM

17
Oracle Application Tables
Non-Oracle Application Tables
SQL Scripts
Temporary Tables
SQL Scripts
Discoverer Tools and Reports
Data Mart Tables
18
(No Transcript)
19
Folder loaded directly from an Oracle table
Folder created from joining other folders using
foreign keys
Folder created using SQL to extract directly from
Oracle table(s). This occurs at run time.
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
Using Discoverer To Create a Reporting Data Mart
Create Users Analytical Applications

• Oracles description of Discoverer Plus
• End-user data access and analytical reporting
  tool
• Discoverer provides
• On-line access to pre-defined reports via
  Corporate Network or Internet/Intranet
• Batch printed reports
• Reports not available in Oracle Applications
• More data or less data
• Different sort
• Filtered data
• Different totals and/or different page breaks
• Etc.

31
Using Discoverer To Create a Reporting Data Mart
Create Users Analytical Applications

• Discoverer Plus Reporting Strengths
• Good user interface for creating reports and
  ad-hoc analysis
• Drill down and up between summarized and detail
  data
• Easy to move data elements between columns and
  rows
• Easy to create exception reporting
• Can include graphical reports
• Can define run time parameters to allow users
  flexibility in selecting data
• Easy to transfer data to other tools such as Excel

32
Selecting data for analysis
33
Formatting report layout
34
Set conditions for filtering data
35
Using run time parameters
36
Setting sort fields and properties
37
Creating Custom Calculations
38
Available Functions
39
Entering Run Time Parameters
40
Checking Query Run Time
41
Query Results
42
Drop Down Selection of Values
43
Swapping Row and Column Items
44
Using Exceptions for Specified Values
45
Using Exceptions for Specified Values
46
Cross Table Report Format
47
Drill Up / Down Based on Hierarchies
48
Drilling to Specific Data Items
49
Graphical Reporting
50
Scheduled Reports
51
More Complex Reports
52
Barry Markovic [phone] Barrym_at_iodatasphere.
com www.iodatasphere.com