IDEA Integrations using ODBC - PDF Document

Presentation Transcript

  1. IDEA Integrations using ODBC ….and Some Other Interesting News - London User Group: October 2018 - James Loughlin - Head of Technical & Training AuditWare Systems

  2. Agenda • IDEA News • IDEA Integrations Using ODBC • Q & A

  3. IDEA News • IDEA 10.3 ▪ Character Field Statistics – # of blanks, # of categories ▪ Stratified Random Sampling - You can now specify a percentage of records as a sample size ▪ UTF-8 delimited files are now supported ▪ Visualization - Charts now contain scrolling and zooming controls that let you focus on a specific area of data ▪ Duplicate Key Detection field statistic (# of Duplicates) is now available in the Field Statistic panels ▪ Improved PDF file support in Report Reader ▪ IDEA is now supported on Windows Server 2016 ▪ IDEA is now compatible with CaseWare Working Papers 2016 and later.

  4. IDEA News • IDEA 10.3 Python is an interpreted, object-oriented programming language that is becoming one of the most popular programming languages used for analytics/data mining and machine learning. Python support has been integrated into IDEA to let users execute their existing Python scripts to perform analytic tasks. http://ideascripting.com/

  5. IDEA News: SmartAnalyzer–Financials Apps General Ledger Accounts Payable Accounts Receivable • Out of Balance Journal Entries • Duplicate Journal Entries • Missing Journal Entries • Journal Entries Posted on Weekends • Journal Entries Posted on Specific Dates • Journal Entries Posted at Specific Times • Journal Entries by User • Journal Entries by Period and Journal Source • Journal Entries by Period • Journal Entries with Large Amounts • Journal Entries with Rounded Amounts • Journal Entries with Amounts that End in 999 • Journal Entries with Specific Comments • Summary by Account Number • Summary by Account Combinations • Account Balances by Journal Source • Account Balances by Period • Aging by AP Invoice Date • Duplicate Invoices or Payments • Creditors with Net Debit Balances • Creditors with Total Invoice Amount Greater than Approved Limit • Creditors with Balances Greater than Approved Limit • Creditor Transaction Summary • Invoices without Purchase Order Numbers • Transactions Around a Specified Date • Transactions Posted on Specified Dates • Transactions Posted at Specified Times • Transactions by User ID • AP Transactions Posted on Weekends • AP Transactions with Rounded Amounts • AP Duplicate Field Search • Aging by Due Date and Invoice Date • Debtors with Balances Greater than Credit Limit • Debtors with Total Amount Greater than Credit Limit • Debtors with Net Credit Balances • Debtor Transaction Summary • Transactions Around a Specified Date • AR Duplicate Field Search

  6. IDEA News: SmartAnalyzer–Financials Apps Fixed Assets Inventory • Aging by Receipt Date and Ending • Inventory Balance • Aging by Receipt Date and Unit Cost • Recalculate Inventory Balance • Calculate Inventory Turnover Ratio • Calculate Unit Turnover Ratio • Zero or Negative Unit Cost • Negative Quantity on Hand • Inventory Location Summary • Large Inventory Amounts • Inventory Received Around Specified Date • Last Sales Price Lower than Unit Cost • Compare Sales Price with Unit Cost • Duplicate Field Search • Fixed Assets Additions • Asset Category Summary • Recalculate Straight Line Depreciation • Recalculate Declining Balance Depreciation • Depreciation Exceeding Cost • Duplicate Field Search

  7. IDEA News: Smart Analyzer-IA App Internal Audit Apps – A collection of 183 one click Internal Audit tests Due for Release Late 2018/Early 2019

  8. IDEA Integrations using ODBC j

  9. What is ODBC? • ODBC stands for Open Database Connectivity • ODBC is a specification for a database API ▪ API stands for Application program interface and is a set of routines, protocols, and tools for building software applications. An API specifies how software components should interact. • ODBC embedded within both server and desktop Microsoft operating systems • ODBC uses Database Management System (DBMS) specific drivers to interact with core software systems and platforms • Microsoft Operating Systems come complete with generic drivers to access Excel, Access, Oracle and SQL Databases • Most drivers created are system specific e.g. Sage, Quickbooks etc

  10. Importing Data Into IDEA Using ODBC j j j j j j j j j

  11. Importing Data Into IDEA Using ODBC

  12. Pros & Cons of Importing Data Using ODBC • Pros ▪ Access data directly to core systems or data warehouses without needing to request extracts ▪ Allow access to system related information very rarely included in reports generated by the clients / stakeholders ▪ No need for data manipulation – normalising extracted data ▪ Easier to build into an automated macro process for Continuous Auditing/Monitoring • Cons ▪ When accessing core systems an awareness of tables and table relationships is needed

  13. Finding ODBC in Microsoft Windows

  14. Creating a Connection

  15. Creating a Connection

  16. Importing Data Into IDEA Using ODBC

  17. Importing Data Into IDEA Using ODBC

  18. Importing Data Into IDEA Using ODBC

  19. Importing Data Into IDEA Using ODBC

  20. Importing Data Into IDEA Using ODBC

  21. Importing Data Into IDEA Using ODBC

  22. Exporting Data from IDEA Using ODBC j j

  23. IDEA ODBC EXPORT

  24. IDEA ODBC Drivers • Available in both 32 bit and 64 variants • Allows software which can reference ODBC sources access to an IDEA Project • Ideal for integrating with advanced data visualisation tools • Enables automated processes and data sharing with clients or stakeholders • Available to clients with a current maintenance contract • Download links provided upon request to support@auditware.co.uk.

  25. Questions & Answers

  26. Thank you for your time.