1 / 42

Leveraging NoetixViews to Migrate Custom 11i Reports to Release 12

Leveraging NoetixViews to Migrate Custom 11i Reports to Release 12. Joe Dahl, Solution Consultant . Agenda. The Challenge Applications Data Structure Changes 11i -> R12 The Solution NoetixViews What we see companies doing about reporting. About Noetix. BI solutions experts since 1994

conor
Download Presentation

Leveraging NoetixViews to Migrate Custom 11i Reports to Release 12

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Leveraging NoetixViews to Migrate Custom 11i Reports to Release 12 Joe Dahl, Solution Consultant

  2. Agenda • The Challenge • Applications Data Structure Changes 11i -> R12 • The Solution • NoetixViews • What we see companies doing about reporting

  3. About Noetix BI solutions experts since 1994 Offices in North America, Europe and South Asia Certified with all leading BI Platforms Recent Product Awards: KMWorld, eWEEK, DMReview 1400+ customers globally PARTNERSHIPS

  4. Noetix’s History of Dedication to Oracle Applications Reporting Noetix Analytics Noetix Generator for Cognos NoetixViews for PeopleSoft Noetix EUL Generator Noetix incorporated Noetix for Siebel 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 1994 Noetix Platform Noetix Generator for BusinessObjects Noetix Dashboard NoetixViews Noetix Generatorfor Oracle BI

  5. Noetix Business Intelligence

  6. Noetix Supports the Full Spectrum of Reporting Needs Enterprise Models Strategic Common Dimensions Performance Management Packaged ETL Enterprise-wide Data Integration Analytics / KPIs Near Real-time Operational Reports Summarized Data Ad hoc query Historical Trending Real-time Drillable Reports Detailed Data Database Views Application Security

  7. Operational Reporting

  8. Everything Starts with a Query • ETL routines • To replicate the data, to transform it into a different model, and usually to create stored aggregations • Reporting tool’s repository • Discoverer EUL, OBI EE Server, BO Universe, Cognos Framework Mgr. Model • Database views • Virtual transformation of data into a denormalized model without replicating the data • For direct access • The information in this presentation is applicable to all approaches

  9. Reporting Tool Repositories NoetixWebQuery Noetix Dashboard OracleDiscoverer OracleBI Suite EE Cognos8 BI Business Objects WebIntelligence ETL Business areasBusiness models Virtualtables Models /Packages Universe Other data models Oracle E-Business Suite EBS Tables

  10. Business areasBusiness models Virtualtables Models /Packages Universe Other data models Business areasBusiness models Virtualtables Models /Packages Universe Other data models Shared Metadata Common Data Access Layer NoetixWebQuery Noetix Dashboard OracleDiscoverer OracleBI Suite EE Cognos8 BI Business Objects WebIntelligence ETL Oracle E-Business Suite DatabaseViews • Common business terminology • Improved maintainability EBS Tables • Reuse complex SQL logic • Improved “time to query”

  11. Subject Areas • Funds Disbursement • Suppliers & Trading Community Architecture • Banks & Trading Community Architecture • Subledger accounting Impact of Release 12 to your 11i queries: Very little  complete rewrite

  12. Funds Disbursement

  13. Funds Disbursement • Moved from Payables to new Payment module • Creation & validation of payments • Aggregation of payments into files • Format & transmission of files • Selection & approval of invoices remains in Payables

  14. Funds Disbursement Changes

  15. Suppliers & Trading Community Architecture

  16. Suppliers & TCA • Supplier, supplier site, & contact information migrated to TCA tables • Three new AP tables containing supplier-unique data, with links to TCA tables AP_SUPPLIERS AP_SUPPLIER_SITES_ALL AP_SUPPLIER_CONTACTS • Three old PO Vendors tables obsolete • Views provided for backward compatibility

  17. Supplier Changes

  18. Banks & Trading Community Architecture

  19. Banks Data in R12 • Setup in Cash Management (CE) • Bank, bank account setup • Shared with Payables, Receivables, Treasury, & Payroll • Banks & bank branches now represented as TCA parties • 11i AP bank data moved to TCA HZ_PARTIES table • Three key CE tables • CE_BANK_ACCOUNTS for bank accounts • CE_BANK_ACCT_USES_ALL for account uses by Operating Units & Legal Entities • CE_GL_ACCOUNTS_CCID for bank account use accounting data

  20. Bank Data in TCA

  21. Subledger Accounting

  22. Subledger Accounting • All accounting performed before transfer to the GL • User-definable accounting rules • At the data level, it’s a big change for all the subledgers, though there is a first generation in 11i Payables “Accounting Events”

  23. Receivables Accounting in 11i • Final accounting data not generated prior to transfer to GL • Distribution level information GL • Three distinct distributions tables • Invoices / Credit Memos / Debit Memos / … • Accounting class & amounts, but not debits & credits • Receipts & Adjustments • Unapplied, applied • Both debits & credits • Misc. Cash Receipts • Both debits & credits • “View Accounting” is a report against distributions

  24. Payables Accounting in 11i • Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL • Run “Create Accounting” to populate accounting events tables • Accounting event tables  GL

  25. Subledger to Ledger Reportingin 11i • Complete, final accounting only available in the GL • All debits and credits • All journal entries • All balances • Challenge: Difficult to link summarized accounting data with source details

  26. Release 12 Subledger Accounting • All subledger accounting data generated and stored in shared SLA tables prior to transfer to GL • Run “Create Accounting” to populate SLA tables • User can “View Accounting” only after “Create Accounting” is run

  27. Distribution Links Sample Data Distributions SLA Je Lines Distribution Links

  28. Tie It All Together • Data flows from distributions, thru distribution links and SLA tables, to GL • Three key stages • Distributions (before accounting applied) • SLA (before transfer to GL) • GL • Three key views of the data • Distributions only • Tie distributions to SLA to GL • Tie GL back to distributions

  29. The Noetix Solution

  30. The Information Gap Query, Reporting and Analysis Tools Generic Templates Additional costs Additional resources Additional delay 70% of BI implementation costs & delay M a n u a l M a p p i n g M a p p i n g R e w o r k < The Information Gap > Custom Application Configuration Enterprise Application (v1) Enterprise Application (v2)

  31. The Information Gap Generated Content Custom Application Configuration Query, Reporting and Analysis Tools Generic Templates Generates configuration specific BI content Noetix MetaBuilder < The Information Gap > Custom Application Configuration No additional rework costs or delay Enterprise Application (v2) Enterprise Application (v1)

  32. Information About Your Setup Information about your enterprise application Business Best Practices Financials Supply Chain Configuration User Interfaces Human Resources Manufacturing Flexfields Security [Interrogated] [Handcrafted] Generated data Business Views Reports Documentation [Configuration-specific Views] [Configuration-specific Answers] [Configuration-specific Help]

  33. What is a Noetix view? Your report Noetix view of the database HZ_PARTIES HZ_CUST_ACCOUNTS HZ_CUSTOMERS Order Lines Orders ATO Flag Booked Date Customer Customer No. Item Item Desc. Line Status Open Quant. Order No. Order Quant. Project Quantity Organization Order No. OE_ORDER_HEADERS_ALL Customer Customer No. OE_ORDER_LINES_ALL PA_PROJECTS_ALL OE_ORDER_LINES_ALL Cust. Status Booked Date PA_TASKS PA_TASKS MTL_SYSTEM_ITEMS_B Order Quant. Open Quant. MTL_SYSTEM_ITEMS_TL Selling Org. Oracle database tables Flexfield column in the table [Oracle EBS upgrade] [doesn’t break] [remaps view] ATTRIBUTE1: “Customer Status” Flexfield column in the view Cust. Status

  34. Oracle Flexfields and Noetix Key FlexfieldBase Table Column Names Key FlexfieldView Column Names SEGMENT1 Company SEGMENT2 Division SEGMENT3 Cost Center Descriptive FlexfieldBase Table Column Names Descriptive FlexfieldView Column Names ATTRIBUTE1 Web Site ATTRIBUTE2 Preferred Vendor ATTRIBUTE3 Approved Until

  35. Product DemonstrationNoetixViews

  36. NoetixViews 5.8.7Financial Cross-Functional Views NoetixViews for Oracle General Ledger Payables Receivables Public Sector

  37. NoetixViews 5.8.7Financial Cross-Functional Views NoetixViews for Oracle Assets NoetixViews for Oracle Payables NoetixViews for Oracle Receivables

  38. Noetix Support for Oracle SLAPayables & Receivables

  39. The Impact? • Some 11i queries will easily migrate to Rel. 12 • Some with no changes at all • Some will need table name changes • Some 11i queries will need moderate attention • Data moved to TCA will require query re-writes, but the basic reports can remain the same • Some 11i queries won’t migrate 1:1 to Rel. 12 • SLA may require new reports, queries & ETL routines

  40. Reporting Strategies We See • Best practice: Establish a metadata layer between queries and OLTP tables • Use common business terms for query objects, data element, & their descriptions • Persist this “business metadata” between database upgrades • Remap the “technical metadata” (SQL) • OLTP database views • Common data access layer • Usable by reporting & query tools, & ETL • Reporting tools’ metadata repositories • Map to data access layer in the OLTP database

  41. Noetix Business Intelligence

  42. Q & A

More Related