Transcript Set up a successful reporting solution with Oracle

Setting up a Successful
Reporting Solution with Oracle
Discoverer and Other Tools
The Top 7 Reasons Why Oracle Users Rely on NoetixViews for Reporting
Joe Dahl, Sales Consultant
Agenda
•
•
•
•
•
About Noetix
Reporting Challenges
The Noetix Solution
Product Demonstration
Q&A
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
About Noetix
• Enterprise Application Metadata
experts since 1994
• Offices in Europe, US and Asia
• Oracle Certified Partner
• Siebel Validated Software
Partner
• Microsoft Gold Partner
• 1,300+ Customers Worldwide
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Industry
AWARDS
Reporting Challenges
Reporting Challenges
Packaged Business Intelligence Tools
 Manual mapping of data equates to
significant cost and delay of most BI
tools
Tailored
Templates
Generic
Reporting
Templates
With Noetix you have
™
Reporting
Gap
– Manual Mapping
Noetix
MetaBuilder
 Reduced time to information
 Reduced cost of implementation
Customto
Application
Configuration
Changes
Application
Configuration
Oracle Applications
 Reduced total cost of ownership
 Minimum project risk
 No additional rework costs or delay
with upgrades
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Performance at a Price
• RDBMS and underlying Database design allows
optimal Online transaction processing (OLTP)
performance
Oracle
Applications 11i
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
The DB Consists of 1000s of Objects
Tables
Packages (Procedures &
Functions)
Grants/Synonyms
Views (Limited use)
Database Users
(Application User Security)
Roles
(Limited use in HR modules)
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Querying Oracle Applications
Using SQL
Columns
Tables
(or Views)
Conditions
& Data
Relationships
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Overview of NoetixViews
7 Reasons Oracle Users Rely on
NoetixViews for Reporting
•
•
•
•
•
•
•
Information All in One Spot
Auto-Generated Content
Open Systems Flexibility
Automatic Upgrade Protection
Performance Optimization
Index Column Identification
Customized Help File System
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
#
1 Information All in
One Spot
Oracle changes the database...
HZ_PARTIES
Flexfield
column
in the table
HZ_CUSTOMERS
HZ_CUST_ACCOUNTS
ATTRIBUTE1:
“Customer Status”
OE_ORDER_HEADERS_ALL
PA_PROJECTS_ALL
PA_TASKS
PA_TASKS
Version 2
1
OE_ORDER_LINES_ALL
MTL_SYSTEM_ITEMS
MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_TL
Sample Oracle database tables
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Simplify access
Configuration specific
Upgrade protection
Noetix remaps
the View…
Order Lines
ATO Flag
Booked Date
Customer
Customer No.
Cust. Status
Item
Item Desc.
Line Status
Open Quant.
Order No.
Order Quant.
Project
Quantity
Organization
Noetix “View” of
the database
Doesn’t
break
Orders
Order No.
Customer
Flexfield column in
the view
Cust.
Status
Booked Date
Order Quant.
Open Quant.
Selling Org.
Report
#
2 Auto-Generated Content
• NoetixViews creates a comprehensive set of
views which “simplify” the underlying
database design of Oracle Applications
Views mask the
complexity
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
What is Metadata?
Your configuration…
Users/Roles
Noetix MetaBuilder
Organizations
Noetix Metadata
Repository
Oracle
Applications 11i
Setups

Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Business Rules

How Noetix Uses Metadata
Oracle
Applications 11i
Noetix Metadata
Repository
Security
KPIs and metrics

NoetixViews
Transaction data
Copyright © June 2006 by Noetix Corporation – All Rights Reserved

Query & reporting
Flexfields as Configured
Key Flexfield
Base Table Column Names
Key Flexfield
View Column Names
SEGMENT12
Account$Company
SEGMENT6
Account$Division
SEGMENT3
Account$Cost_Center
Descriptive Flexfield
Base Table Column Names
Descriptive Flexfield
View Column Names
ATTRIBUTE2
VEND$WWW_Site
ATTRIBUTE9
VEND$Preferred_Vendor
ATTRIBUTE11
VEND$Approved_Until
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
List of Values (QuickPicks Decoded)
Stored Oracle Values
Retrieved Noetix Values
STATUS_LOOKUP_CODE
Status
A
C
I
P
Active
Closed
In Process
Printed
TERMS_ID
1000
1001
1002
1020
4
5
Payment_Terms
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
2/10, Net 30
30/60/90
Due 10th
Net 30
Net 15
Immediate
#
3 Open Systems Flexibility
Audience
Business Users/Managers
Unique Reporting Needs
Pre-built Reports
Ad hoc Access







Discoverer
Oracle Reports
Crystal
Noetix WebQuery
Business Objects
Cognos
Brio
Senior Mgmt
Dashboards
Analysts
Controllers
Excel
Formatted/Ad hoc
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
#
4 Automatic Upgrade Protection
• Insulates queries
against physical
changes
–
–
–
–
Application upgrades
Changing business
requirements
Different delivery
methods
Changes to
Application
Configuration
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
v10.7
v11i
#
5 Performance Optimization
• Each View references many tables
– Complex Queries
– Union Relationships
• Noetix Development involves Tuning
– “Explain Plan”
– TKProf
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
#
6 Indexed Columns Identified
View Columns
which use an index
(indicated by ‘A$’ prefix)
PO_Purchase_Orders
Search by hints
tell how to build
fast queries
A$BUYER_NAME
A$BUYER_NUMBER
A$PO_CREATION_DATE
A$PO_NUMBER
A$VENDOR_NAME
A$VENDOR_NUMBER
PRINTED_DATE
VENDOR_NAME
STATUS
TERMS
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
#
7 Customized Help File System
• MS Windows Help File Format
• HTML Help File Structure Format
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Noetix EUL Generator for Discoverer
Supports Discoverer versions 3.1, 4.1, 9i & 10g
•
•
•
•
•
•
•
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Business Areas
Folders
Items
Descriptions
List of Values
Data Drills
Automatic Joins
NoetixViews for
Oracle Applications
•
•
•
•
•
•
•
•
Financials — AP, AR, FA & GL
Supply Chain — INV, OM & PO
Manufacturing — MRP, BOM, WIP, CST and OPM
Human Resources — HR, PAY, BEN
Project Accounting — PC & PB
Grants
Cross Operations Extension
Custom Modules
Through Oracle Applications versions 11.5.10
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
NoetixViews Cross Operations
Extension
SIRA Inc.
Set of Books
Operating
Units
Inventory
Orgs
Set of Books
Set of Books
Airplane Group
Automobile Group
Operating
Unit
Operating
Unit
Operating
Unit
Operating
Unit
Commercial
Military
Automotive
Motorcycles
INV / Man.
Organization
INV / Man.
Organization
Washington
California
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
INV / Man.
Organization
New York
INV / Man.
Organization
Texas
INV / Man.
Organization
Virginia
Noetix Solution — Components
•
•
•
•
•
•
NoetixViews®
Provides hundreds of views of the underlying
data structures organized by functional area.
Noetix Help File
Efficiently instructs users in report creation by
documenting your Oracle Application specific
configurations
Noetix QueryServer®
Integrates disparate data sources into a
secure query routing & management platform.
Noetix WebQuery®
Empowers decision makers to create and
modify reports without IT involvement.
Noetix AnswerPoint™
Delivers hundreds of click-on, customizable
answers to business questions.
Noetix Dashboards
KPIs for various functional areas presented
via a graphical display
Copyright © June 2006 by Noetix Corporation – All Rights Reserved
Through Oracle Applications v. 11.5.10
– Financials – AP, AR, FA & GL
– Supply Chain – INV, OM & PO
– Manufacturing – MRP, BOM, WIP, CST, &
OPM
– Human Resources – HR, PAY, BEN
– Project Accounting – PC & PB
– Grants
– Cross Operations Extension
– Custom Views for other Modules
– DW Views – GL, AP, AR, OM
– EUL Generator for Discoverer
Product Demonstration
Why We are Successful
Pre-generated Content

– Views and reports that pick up configuration
End User Independence
– Ease of use
Faster Time to Market
– Predictable, proven implementation
Open Architecture
– Easily integrate with other data sources
Performance
– Don’t have to sacrifice real-time data
Change Management
– Upgrade protection
Copyright © June 2006 by Noetix Corporation – All Rights Reserved




