Transcript 5_02_dahl
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 • Offices in North America, Europe and South Asia • Certified with all leading BI Platforms • Recent Product Awards: KMWorld, eWEEK, DMReview • 1400+ customers globally PARTNERSHIPS Noetix’s History of Dedication to Oracle Applications Reporting Noetix Analytics Noetix Generator for Cognos Noetix EUL Generator Noetix incorporated 1995 NoetixViews for PeopleSoft Noetix for Siebel 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 1994 NoetixViews Noetix Platform Noetix Dashboard Noetix Generator for BusinessObjects Noetix Generator for Oracle BI Noetix Business Intelligence Noetix Supports the Full Spectrum of Reporting Needs Enterprise Models Common Dimensions Strategic Performance Management Packaged ETL Enterprise-wide Analytics / KPIs Data Integration Near Real-time Operational Reports Summarized Data Ad hoc query Historical Trending Real-time Drillable Reports Detailed Data Database Views Application Security Operational Reporting 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 Reporting Tool Repositories Noetix WebQuery Oracle Discoverer Noetix Dashboard Oracle BI Suite EE Virtual tables Business areas Business models Cognos 8 BI Business Objects Web Intelligence Models / Packages Universe Oracle E-Business Suite EBS Tables ETL Other data models Common Data Access Layer Noetix WebQuery Oracle Discoverer Noetix Dashboard Oracle BI Suite EE Virtual tables Business areas Business models Cognos 8 BI Business Objects Web Intelligence Models / Packages Universe ETL Other data models Shared Metadata Oracle E-Business Suite Improved maintainability Database Views Improved “time to query” EBS Tables Common business terminology Reuse complex SQL logic 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 Funds Disbursement 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 Funds Disbursement Changes Co-existing Tables 11i Release 12 Invoice payments AP_INVOICES_ALL AP_PAYMENT_SCHEDULES_ALL IBY_DOCS_PAYABLE_ALL Payments AP_CHECKS_ALL IBY_PAYMENTS_ALL Instructions, batches AP_INV_SELECTION_CRITERIA_ALL IBY_DOCS_PAYABLE_ALL IBY_PAY_SERVICE_REQUESTS IBY_PAY_INSTRUCTIONS_ALL Obsolete Tables 11i Release 12 Banks AP_BANK_ACCOUNT_USES_ALL CE_BANK_ACCT_USES_ALL Payment documents AP_CHECK_STOCKS_ALL CE_PAYMENT_DOCUMENTS AP_BANK_ACCOUNTS_ALL IBY_EXTERNAL_PAYEES_ALL IBY_EXT_BANK_ACCOUNTS IBY_PMT_INSTR_USES_ALL Supplier and customer bank accounts Suppliers & Trading Community Architecture Suppliers & TCA • Supplier, supplier site, & contact information migrated to TCA tables • Three new AP tables containing supplierunique 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 Supplier Changes Co-existing Tables Release 12 Payables Release 12 TCA Suppliers AP_SUPPLIERS HZ_PARTIES Supplier Sites AP_SUPPLIER_SITES_ALL HZ_PARTY_SITES HZ_LOCATIONS AP_SUPPLIER_CONTACTS HZ_PARTIES HZ_PARTY_SITES HZ_RELATIONSHIPS HZ_ORG_CONTACTS 11i Obsolete Tables Release 12 Suppliers PO_VENDORS AP_SUPPLIERS Supplier Sites PO_VENDOR_SITES_ALL AP_SUPPLIER_SITES_ALL Supplier Contacts PO_VENDOR_CONTACTS AP_SUPPLIER_CONTACTS Supplier Contacts Banks & Trading Community Architecture 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 Bank Data in TCA Release 12 TCA Bank, bank branch, account attributes, contact persons HZ_PARTIES Bank sites and locations HZ_PARTY_SITES HZ_LOCATIONS Change history and additional attributes HZ_ORGANIZATION_PROFILES Contact details and methods HZ_CONTACT_POINTS Contact titles HZ_ORG_CONTACT Contact purpose or role HZ_ORG_CONTACT_ROLES Subledger Accounting 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” 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 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 Subledger to Ledger Reporting in 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 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 Distribution Links Sample Data Distributions Trx # Line Type Line # Ext Amt Currency CCID Acct. Class Amt SLA Je Lines 11984 Line 1 100 CAD 12871 Rev 100 Trx # 11984 Freight 1 20 CAD 13053 Freight 20 11984 13053 Receivable 158.2 123.93 11984 Freight 1 5 CAD 13053 Freight 5 11984 12833 Rounding 0 0.01 11984 Tax 1 3 CAD 18170 Tax 3 11984 12871 Revenue 120 94.01 11984 Tax 1 0.6 CAD 18170 Tax 0.6 11984 24718 Freight 25 19.59 11984 Line 2 20 CAD 12871 Rev 20 11984 18170 Tax 13.2 10.34 11984 Tax 2 2 CAD 18170 Tax 2 11984 Tax 2 0.4 CAD 18170 Tax 0.4 11984 Tax 3 6 CAD 18170 Tax 6 11984 Tax 3 1.2 CAD 18170 Tax 1.2 11984 CAD 12833 Rec 158.2 11984 CAD 24718 Round 0 SLA Entered DR SLA Entered CR CCID Acct. Class Entered DR Entered CR Acct. DR Distribution Links Trx # CCID Acct. Class Curr 11984 13053 Freight CAD 25 19.59 5 3.92 11984 13053 Freight CAD 25 19.59 20 15.67 11984 12833 Receivable CAD 11984 12871 Revenue CAD 120 94.01 20 15.67 11984 12871 Revenue CAD 120 94.01 120 78.34 11984 24718 Rounding CAD 11984 18170 Tax CAD 13.2 10.34 1.2 0.94 11984 18170 Tax CAD 13.2 10.34 .04 0.31 11984 18170 Tax CAD 13.2 10.34 0.6 0.47 11984 18170 Tax CAD 13.2 10.34 6 4.7 11984 18170 Tax CAD 13.2 10.34 2 1.57 11984 18170 Tax CAD 13.2 10.34 3 2.35 158.2 SLA Acct. DR SLA Acct. CR 123.93 0 Acct. CR Unrounded Entered DR Unrounded Entered CR 158.2 0.01 Unrounded Acct. DR Unrounded Acct. DR 123.93 0 0.01 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 The Noetix Solution The Information Gap Query, Reporting and Analysis Tools Generic Templates Additional costs M Ma ap nInformation p u ianl g MRa ep w po iGap nr g k > < The Custom Application Configuration (v1) Enterprise Application (v2) 70% of BI Additional implementation resources costs & delay Additional delay The Information Gap Query, Reporting and Analysis Tools Generic Templates Generated Content < TheNoetix Information MetaBuilderGap > Generates configuration specific BI content Custom Application Configuration Enterprise Application (v2) (v1) No additional rework costs or delay Information About Your Setup Information about your enterprise application Business Best Practices Configuration User Interfaces Financials Supply Chain Flexfields Security Human Resources Manufacturing [Interrogated] [Handcrafted] Generated data Business Views [Configuration-specific Views] Reports [Configuration-specific Answers] Documentation [Configuration-specific Help] What is a Noetix view? Oracle database tables Flexfield column in the table [Oracle EBS upgrade] HZ_PARTIES HZ_CUSTOMERS HZ_CUST_ACCOUNTS ATTRIBUTE1: “Customer Status” OE_ORDER_HEADERS_ALL PA_PROJECTS_ALL PA_TASKS OE_ORDER_LINES_ALL MTL_SYSTEM_ITEMS_B MTL_SYSTEM_ITEMS_TL Noetix view of the database Your report [remaps view] [doesn’t break] Order Lines Orders ATO Flag Booked Date Customer Customer No. Cust. Status Item Item Desc. Line Status Open Quant. Order No. Order Quant. Project Quantity Organization Flexfield column in Order No. the view Customer Customer No. Cust. Status Booked Date Order Quant. Open Quant. Selling Org. Oracle Flexfields and Noetix Key Flexfield Base Table Column Names Key Flexfield View Column Names SEGMENT1 Company SEGMENT2 Division SEGMENT3 Cost Center Descriptive Flexfield Base Table Column Names Descriptive Flexfield View Column Names ATTRIBUTE1 Web Site ATTRIBUTE2 Preferred Vendor ATTRIBUTE3 Approved Until Product Demonstration NoetixViews NoetixViews 5.8.7 Financial Cross-Functional Views Public Sector Receivables Payables NoetixViews for Oracle General Ledger Accounting Subject Area GL-to-Subledger Cross-Functional View Legacy GL-to-Subledger Cross-Functional View Built in R12? Payables invoices GL_Je_SLA_AP_Inv_Dist GL_AP_Inv_Je_Line_Details GL_Je_AP_Inv_Dist_Details Yes Payables invoice payments GL_Je_SLA_AP_Pmt_Dist GL_Je_AP_Pmt_Dist_Details Yes Receivables adjustments GL_Je_SLA_AR_Adj_Dist GL_Je_Tran_AR_Adj_Dist Yes Receivables miscellaneous receipts GL_Je_SLA_AR_Misc_Rcpt_Dist GL_Je_Tran_AR_Misc_Dist Yes Receivables standard receipts GL_Je_SLA_AR_Std_Rcpt_Dist GL_Je_Tran_AR_Pymt_Dist Yes Receivables transactions GL_Je_SLA_AR_Tran_Dist GL_Je_Tran_AR_Tran_Dist Yes Purchase order and requisition encumbrances GL_Je_SLA_PO_All_Encumb GL_Je_Tran_All_PO_Encumb Yes Blanket purchase agreements GL_Je_SLA_PO_Blnkt_Encumb GL_Je_Blanket_PO_Encumb Yes Purchase order encumbrances GL_Je_SLA_PO_Encumb GL_Je_Tran_PO_Encumb Yes Requisition encumbrances GL_Je_SLA_PO_Req_Encumb GL_Je_Tran_PO_Req_Encumb Yes NoetixViews 5.8.7 Financial Cross-Functional Views NoetixViews for Oracle Assets Accounting Subject Area FA GL Cross-Functional View Legacy FA GL Cross-Functional View Built In R12? Adjustments FA_Adjustments_SLA_GL_Je FA_GL_Journal_Adjustments Yes Depreciations FA_Deprn_Dtls_SLA_GL_Je FA_Depreciation_Details (value-added) Yes Depreciation histories FA_Deprn_Dtl_Hist_SLA_GL_Je FA_Depr_Detail_History (basic) Yes Invoice transactions FA_Invoice_Trx_SLA_GL_Je FA_GL_Invoice_Trans_Je Yes Transactions FA_All_Trans_SLA_GL_Je FA_GL_Journals Yes NoetixViews for Oracle Payables Accounting Subject Area AP GL Cross-Functional View Legacy AP GL Cross-Functional View Built in R12? Invoices AP_Inv_Dist_SLA_GL_Je AP_Inv_GL_Tr_Details Yes Payments AP_Pmt_Dist_SLA_GL_Je AP_Pmt_Dist_GL_Tr_Details Yes Trial balance AP_Trial_Balance_Details AP_Trial_Balance (value-added) Yes NoetixViews for Oracle Receivables Accounting Subject Area AR GL Cross-Functional View Legacy AR GL Cross-Functional View Built in R12? Adjustments AR_Adj_Dist_SLA_GL_Je AR_Adjustment_GL_Je_Dist Yes Miscellaneous receipts AR_Misc_Rcpt_Dist_SLA_GL_Je AR_Misc_Pymt_GL_Je_Dist Yes Standard receipts AR_Std_Rcpt_Dist_SLA_GL_Je AR_Cust_Pymt_GL_Je_Dist Yes Transactions AR_Tran_Dist_SLA_GL_Je AR_Cust_Tran_GL_Je_Dist Yes Noetix Support for Oracle SLA Payables & Receivables Accounting Subject Area GL Subledger Cross-Functional View (Final accounting data only) Subledger GL Cross-Functional View (Final and draft accounting data) Payables invoices GL_Je_SLA_AP_Inv_Dist AP_Inv_Dist_SLA_GL_Je Payables invoice payments GL_Je_SLA_AP_Pmt_Dist AP_Pmt_Dist_SLA_GL_Je Receivables adjustments GL_Je_SLA_AR_Adj_Dist AR_Adj_Dist_SLA_GL_Je Receivables miscellaneous receipts GL_Je_SLA_AR_Misc_Rcpt_Dist AR_Misc_Rcpt_Dist_SLA_GL_Je Receivables standard receipts GL_Je_SLA_AR_Std_Rcpt_Dist AR_Std_Rcpt_SLA_GL_Je Receivables transactions (all) GL_Je_SLA_AR_Tran_Dist AR_Tran_Dist_SLA_GL_Je Payables invoices GL_Je_SLA_AP_Inv_Dist AP_Inv_Dist_SLA_GL_Je 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 rewrites, 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 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 Noetix Business Intelligence Q&A