Visteon Cyberquery Case Study Consolidated Reporting QAD MWUG
Visteon Cyberquery Case Study – Consolidated Reporting: QAD MWUG Spring 2017 Conference, Dearborn MI Brent Becker, Cyberscience Corporation. & Vijay Venbakkam, Visteon Corporation
Consolidated Reporting - Agenda • • Visteon overview Cyberscience overview QAD & Cyberquery at Visteon Setting up Cyberquery consolidated reporting Sample Reports Live Demo Q&A 2
Visteon Overview $3. 16 B 2016 annual sales 10, 000 Employees 19 Countries 22 Manufacturing locations 18 Technical centers Van Buren Township, Company headquarters Michigan, United States 3
Visteon - Product Portfolio 1 2 3 4 Cockpit domain controllers Head-up displays Telematics Audio Infotainment Instrument clusters Information displays 4
Visteon’s Manufacturing footprint Europe France La Ferté-Bernard Portugal Palmela Russia Vladimir Slovakia Namestovo Tunisia Bir El Bey Asia Pacific China Chongqing x 3, Shanghai x 2, Changchun x 2, Xuzhou, Shaoxing India Chennai Indonesia Jakarta Americas Japan Brazil Manaus S. Korea Yesan Mexico Chihuahua, Reynosa Thailand Rayong 21, 000 Unique Components 250 Global Suppliers 1 Million Products Per Week 5 1, 000 Customer Locations Hiroshima
Visteon’s Engineering footprint Europe Bulgaria Sofia France Cergy, Sofia Antipolis Germany Karlsruhe, Kerpen Portugal Palmela UK Chelmsford Asia Pacific Americas Brazil São Paulo U. S. Van Buren Twp. , Mich. Mexico Chihuahua More than 50% Software Engineers China Shanghai x 3 India Bangalore, Chennai, Pune Japan Hiroshima, Yokohama S. Korea 70% of Resources in Growth/Emerging Markets 9 Global Centers of Competence 6 134, 000 Lines of Code Per Week Seoul
Visteon’s customer base 7
Cyberscience Overview Cyberquery provides complete end-to-end Reporting /BI Dashboards Reporting Business Intelligence
Cyberscience Overview “Why Over 70% of Active QAD Users have Cyberquery” Top six reasons other than extremely fast speed Consolidated reporting • 15+ databases (no limit) • 5 applications (no limit) Ease of Use Trained staff to be selfsufficient Replacement for Brand X that did not work well. Ability to deliver on promise Cost
Cyberscience Overview Typical Requirements Easy Access To Data Including: • • All QAD Other applications, e. g. , legacy system Reports, Charts, Analytics, BI Also needs to be able to: • Create forms Data stored in spreadsheets • Cloud-based data sources. • Output to Excel, PDF, HTML Drill down to details Power and Functionality • Logistical reporting (e. g. BOM’s) • Dashboards Reasonable Price • KPI’s Sales and operational analysis • Data visualization • Cost of ownership • Trend reports • Financial statements • Scalability • • Data Marts Sparklines and bullet charts Rapid return on investment (ROI) Easy to use
Cyberscience Overview Who Uses Cyberquery? Cyberquery is a full-spectrum product designed with all staff and skill levels in mind 4 3 IT staff Power Users 2 Casual Developers 1 Runtime Users 4 IT Staff – technical staff that often work in IT or have BI experience 3 Power Users Casual Developers Runtime Users Power Users – departmental experts, intermediate to advanced Cyberquery skills 2 Casual Cyberquery Developers – can create their own reports 1 Runtime Users– Run reports from Launchpad
QAD at Visteon Global launch initiative – Started in 2008 Objective: Replace mainframe legacy ERP system inherited from Ford Motor Company “Core Model” definition with regional customizations for legal/statutory/customer Extensive Finance customizations Core QAD version: QAD 2008 SE (e. B 2. 1 SP 5) Bolt-on applications: Eagle RF Express & Cyberquery Integration middleware: JCAPS & extensive use of QXtend Inbound & Outbound QAD’s Master Production Scheduling Workbench (MSW/PSW) Advanced Inventory Management (AIM) 12
Cyberquery at Visteon Global Cyberquery Launch initiative started in 2011 Project Objectives – To replace legacy QAD Forms tool with an improved & integrated solution – To implement a real-time & efficient BI reporting tool for QAD Project Key Drivers – Legacy Forms solution out of support – Upgrading legacy solution too expensive – Frequency of critical production issues was high – Need to develop complex QAD forms (including supporting Cyrillic, French, Chinese & Japanese fonts, 2 D barcodes & complex NA export documentation) – Legacy BI application had performance issues – Custom browses were too slow – Need for efficient, real time QAD reporting with Excel and e-mail compatibility 13
Cyberquery at Visteon Solution options evaluated – Continue with legacy Forms solution – Upgrade the legacy Forms software version – Evaluate other QAD Forms and Reporting solutions § Cyberquery § QAD Reporting Framework § Others Key Factors influencing decision – Ability to generate QAD Forms and improve BI reporting capabilities – Cost efficiency – Replace existing (legacy) applications seamlessly Solution Decision – Cyberquery (~ June, 2011) 14
Cyberquery Forms Rolled Out (printed from QAD) Over 80 unique QAD forms developed and implemented (across 11 core model environments) Purchase Orders (Supplier Schedule / Parts procured for Inventory / Production) Delivery Notes (Shippers) & Customer Invoices – QAD Picklists (Pre-Shippers), includes 1 D barcode – NA Shippers/Invoices (US to MX Southbound & vice versa) – India Excise Invoice (pilot) – Supplier ASN (PO Shippers) Master Bills of Lading – Customer Master Bills of Lading – EU Odette Delivery Notes (generic) – EU Renault Delivery Notes – EU VDA 4939 (Germany) Delivery notes, for BMW or VW plant shipments (includes 2 D “QR” barcodes) – Russia TORG-12 (Delivery Notes) & TTN_T 1 (Invoices) – RTV (Return to Vendor) Shippers/Delivery notes – AR Customer Invoices (including EU VAT, Factura & Proforma Invoices) – Foreign languages supported: Japanese, French & Cyrillic AR Debit & Credit Memos Return documentation to Suppliers (RMA’s) & Sub-contract Shippers. 15
Visteon Cyberquery (Consolidated Reporting) Ad hoc reporting development since launch date, 2011 Formal Cyberquery key user training: March, 2016 Cyberquery applications connected to individual (plant databases), single “data sets” – Brazil, e. B 2. 1 – Korea e. B 2. 1 – China (3 databases), including e. B 2 and QAD 2011 EE – India e. B 2. 1 – Thailand e. B 2. 1 – North America (2 databases) e. B 2. 1 – Europe (6 databases) e. B 2. 1 Cyberquery applications connected to ‘consolidated’ applications – All e. B 2. 1 ‘core model’ Test environments (11 databases)** – All e. B 2. 1 ‘core model’ Production ‘shadow’ (11 databases) 16
Visteon Cyberquery (Reporting strategy) CQ. Initial Report / Ad hoc queries Development IT & Business Super Users CQ End Users DEV DB’s. (NA, EU & AP) CQ Deploy queries in Test/QA environments for UAT signoff QA DB’s. (Per Plant) UAT Approved CQ Reports Near Real Time CQ Data Replication (PROD to DR) DR DB’s. (QA server, per Plant) PROD DB’s. (Per Plant) 17
Visteon Cyberquery (Data Sets) “The Data Dictionary can define a single logical file that accesses several physical files. For example, you can divide the file containing sales records so that the sales for each year are in a different physical file: Sales_2005, Sales_2006, Sales_2007 and so on. You still have a single logical file, Sales, that refers to all these files. This means that you can generate a report across all the records, even though they are in different physical files. These kinds of files are called Data Sets. Instead of reporting across all the files, the Data Dictionary may specify that you can choose a single file, or a range of files from a set, and use just those files as the basis of a report. This selection involves entering the variable parts of the name as parameters when you run the enquiry. ” ** Source: Cyberquery Online documentation Cyberquery can be configured to scan across multiple physical Progress databases. The tables in the databases must share the same record layout (i. e. , the table definitions must match). 18
Visteon Cyberquery (Dictionary configuration) 19
Visteon Cyberquery (Dictionary configuration) 20
Visteon Cyberquery (Dictionary configuration) The Data Dictionary Physical File Definition screen must include the “pipe” symbol (“|”) in front of each DB environment variable specification. This lets Cyberquery know we are using Dataset scanning functionality. 21
Visteon Cyberquery (Configuration) “acs. cfg” file entries (“/etc/acs. cfg”) If the databases are on the same server: # Template | Application : environment : DB_NAME=[service=service 1]db 1 [service=service 2]db 2 : environment : DB_MACHINE=host 1 Application # Example : environment : DB_SERVICE=service 1 service 2 QAD : environment : DB_NAME=[service=qad_usa]qad_usa [service=qad_uk]qad_uk DB_MACHINE=alpha QAD : environment : DB_SERVICE=qad_usa qad_uk | | Continue using the “pipe” symbol (“ ”) to additional databases until the list is complete. 22
Visteon Cyberquery (Configuration) “acs. cfg” file entries (“/etc/acs. cfg”) If the databases are on different servers: # Template | Application : environment : DB_NAME=[server=host 1, service=service 1]db 1 [server=host 2, service=service 2]db 2 | Application : environment : DB_MACHINE=host 1 host 2 | Application : environment : DB_SERVICE=service 1 service 2 # Example | QAD : environment : DB_NAME=[server=alpha, service=qad_usa]qad_usa [server=beta, service=qad_uk]qad_uk | QAD : environment : DB_MACHINE=alpha beta | QAD : environment : DB_SERVICE=qad_usa qad_uk | Continue using the “pipe” symbol (“ ”) to additional databases until the list is complete. 23
Visteon Cyberquery (Combined DB’s setup on “acs. cfg”) “acs. cfg” file entries (“/etc/acs. cfg”) Our databases are on different servers: qadeb 21_combinedprd : environment : DB_NAME=[server=qadsvr 84, service=21011]r 01 prd 1|[server=qadsvr 35, service=22150]r 02 prd 1|[server=qadsvr 20, service=36101]r 14 prd 2|[server=qadsvr 19, service=21101]r 01 prd 5|[serve r=qadsvr 04, service=39100]r 17 prd 1|[server=qadsvr 43, service=39100]r 19 prd 1|[server=qadsvr 42, service=28131]r 18 prd 1|[server=qadsvr 0003, service=36101]r 16 prd 1|[server=qadsvr 40, ser vice=27075]r 08 prd 1|[server=qadsvr 45, service=30106]r 10 prd 3|[server=qadsvra 3, service=36107]r 06 prd 7 qadeb 21_combinedprd : environment : DB_MACHINE=qadsvr 84|qadsvr 35|qadsvr 20|qadsvr 19|qadsvr 04|qadsvr 43|qadsvr 42|qadsvr 0003|qadsvr 40|qadsvr 45|qadsvra 3 qadeb 21_combinedprd : environment : DB_SERVICE=21011|22150|36101|21101|39100|28131|36101|27075|30106|36107 qadeb 21_combinedprd : environment : DB 3_NAME=[server=qadsvr 84, service=21120]r 01 prd 1 adm|[server=qadsvr 35, service=22190]r 02 prd 1 adm|[server=qadsvr 20, service=36117]r 14 prd 2 adm|[server=qadsvr 19, service=21117] r 01 prd 5 adm|[server=qadsvr 04, service=39103]r 17 prd 1 adm|[server=qadsvr 43, service=39103]r 19 prd 1 adm|[server=qadsvr 42, service=28147]r 18 prd 1 adm|[server=qadsvr 0003, service=3610 3]r 16 prd 1 adm|[server=qadsvr 40, service=28080]r 08 prd 1 adm|[server=qadsvr 45, service=30116]r 10 prd 3 adm|[server=qadsvra 3, service=36117]r 06 prd 7 adm qadeb 21_combinedprd : environment : DB 3_MACHINE=qadsvr 84|qadsvr 35|qadsvr 20|qadsvr 19|qadsvr 04|qadsvr 43|qadsvr 42|qadsvr 0003|qadsvr 40|qadsvr 45|qadsvra 3 qadeb 21_combinedprd : environment : DB 3_SERVICE=21120|22190|36117|21117|39103|28147|36103|28080|30116|36117 qadeb 21_combinedprd : environment : DB 2_NAME=[server=qadsvr 84, service=21151]r 01 prd 1 cst|[server=qadsvr 35, service=22160]r 02 prd 1 cst|[server=qadsvr 20, service=36113]r 14 prd 2 cst|[server=qadsvr 19, service=21113]r 01 p rd 5 cst|[server=qadsvr 04, service=39105]r 17 prd 1 cst|[server=qadsvr 43, service=39105]r 19 prd 1 cst|[server=qadsvr 42, service=28143]r 18 prd 1 cst|[server=qadsvr 0003, service=36105]r 16 prd 1 cst|[server=qadsvr 40, service=28002]r 08 prd 1 cst|[server=qadsvr 45, service=30146]r 10 prd 3 cst|[server=qadsvra 3, service=36167]r 06 prd 7 cst qadeb 21_combinedprd : environment : DB 2_MACHINE=qadsvr 84|qadsvr 35|qadsvr 20|qadsvr 19|qadsvr 04|qadsvr 43|qadsvr 42|qadsvr 0003|qadsvr 40|qadsvr 45|qadsvra 3 qadeb 21_combinedprd : environment : DB 2_SERVICE=21151|22160|36113|21113|39105|28143|36105|28002|30146|36167 qadeb 21_combinedprd : environment : DB 4_NAME=[server=qadsvr 35, service=22190]r 02 prd 1 esbi|[server=qadsvr 20, service=36121]r 14 prd 2 esbi|[server=qadsvr 04, service=39109]r 17 prd 1 esbi|[server=qadsvr 43, service=39109]r 19 prd 1 esbi|[server=qadsvr 42, service=28151]r 18 prd 1 esbi|[server=qadsvr 0003, service=36109]r 16 prd 1 esbi|[server=qadsvra 3, service=36187]r 06 prd 7 exc qadeb 21_combinedprd : environment : DB 4_MACHINE=qadsvr 35|qadsvr 20|qadsvr 04|qadsvr 43|qadsvr 42|qadsvr 0003|qadsvra 3 qadeb 21_combinedprd : environment : DB 4_SERVICE=22190|36121|39109|28151|36109|36187 24
Visteon Cyberquery (Combined DB’s example) 25
Visteon Cyberquery (Example query 1 – Sites master list) 26
Visteon Cyberquery (Example query 1 – Sites master list) 27
Visteon Cyberquery (Example query 1 – Sites master list) 28
Visteon Cyberquery (Example query 2 – Item master counts) 29
Visteon Cyberquery (Example query 2 – Item master counts) 30
Visteon Cyberquery (Example query 2 – Item master counts) 31
Visteon Cyberquery (Demo) 32
Visteon Cyberquery (Contact Info. ) Thank You!! Questions? Brent Becker, Senior Consultant, Cyberscience Corporation bbecker@us. cyberscience. com 303 -745 -3900 Vijay Venbakkam, CPIM QAD Consultant, Detroit Engineered Products, Inc. vijay_v@depusa. com 248 -792 -1546 33
- Slides: 34