Using SQL Databases to Grow Your Enterprise System

2. Using SQL Databases to Grow Your Enterprise System Daniel Clark The Sherwin-Williams Company Jeremy Kiffer

3. The Sherwin-Williams Company engages in the development, manufacture, distribution, and sale of paints, coatings, and related products to professional, industrial, commercial, and retail customers throughout the globe.

4. Daniel Clark Lead Software Developer MFG Controls / MES Database Lead Software Developer Data Reporting / Enterprise Historian Jeremy Kiffer

5. The Power of a SQL Database Common point of data storage Multiple systems can access the same data • Both Ignition based and non-Ignition based • Data organization • Ability to query data directly • Ability to summarize data • Ability to use analytical function

6. The Power of Ignition and SQL Databases Ignition can bring islands of data from traditionally offline equipment into a database (data pump) Ignition can visualize the islands of data to bring multiple systems and databases together in one common dashboard form (client) Ignition can present this information without the need of a full PC • Raspberry Pi to drive big screen TV dashboards Ignition has flexibility with how to use SQL Ignition has the ability to merge real-time data and transactional data together in a dynamic and modern view

7. Ignition Platform

8. Enterprise Systems

9. Business Data Acquisition Ignition as Data Pump LIMS MES EAM ERP Ignition Python Web Services SQL Transaction Groups Historian

10. Business Data Acquisition Database Processes LIMS MES EAM ERP Database Database Links API’s Db Jobs Web Services Historian

11. Routinely Used SQL • Tables • Indexes • Allow for optimized methods for data storage • Keys Primary / Foreign • Allow for unique and uniquely linked data • Triggers • Allow for transactions to automatically happen as information is loaded or updated on tables • Views • Real-time execution of stored query • Materialized Views • Cached view executed on demand or via schedule

12. Routinely Used SQL • Functions • Collection of code that returns a value or table of values • Procedures • Collection of code that can just execute or return multiple values • Packages • Collection of functions and procedures

13. View Examples

14. Materialized View Examples Central Database Safety Codes Safety Codes Plant 2 Database Plant 1 Database

15. Procedure Example Label Shared Printer Procedure Unix Function

16. Function Example Order Info Packaging Info External System Function

17. Package Example Package Wood Package Furniture Procedure Walnut Procedure Vanity Procedure Oak Function Maple Procedure Vanity Function Table Function Cherry Function Table Function Dresser Function Cherry Procedure Walnut Function Dresser Function Maple Procedure Oak

18. Deployment Considerations • Logistical Considerations • Number of concurrent clients ( 500+ ) • Site gateways vs. centralized gateways • Multiple geographical sites • Localization

19. Design Considerations • Information is not Intelligence • Design to create operational and business intelligence • How information is presented = utility • Create dashboards intuitive to user role • Reduce learning curve: training, turnover, support • Minimize need to scan for information (example: scanning trend for event vs. displaying indicators. Clock face vs. digital time format)

20. Customizable Status Displays • Centralize • Ignition Application Gateways • Client Configuration • Automated Startup • Display Status • Raspberry Pi • Drive TV Displays • Low Cost (<$100)

21. Challenge: Conversion of Business Data Ignition Solution Create central database table to pull all mappings (to -> from) Vision table with filters All business stake holders can access Vision client • Lab • Purchasing • Manufacturing Ease transition for sites Challenge • Converting formula and raw materials from an acquisition to standard enterprise formats • Many sites and business units required access to frequently updated data

22. Challenge: Legacy Batch System Ignition Solution Extend all critical data mappings from previous example to now automatically convert RS batch formulas New table to log all conversions Use python on SQL procedures to copy RS Batch formulas with converted raw materials and formulas Site estimated 30+ minutes to manually convert. Bulk conversion: 2 hours Ad-hoc single formula conversion: 1 minute Challenge • Legacy RS Batch system • Verify batch execution and status with new Ignition dashboards to ensure mistakes are not made while learning new codes and names

23. Shift Schedules from External System Challenge Schedules set up in legacy database Central database suspect to WAN disruption and maintenance • Cache upcoming shift schedules • Ignition 7.9+ -> Dataset Memory Tag • Pre 7.9 -> CSV data in string memory tag • Automatically load current shift assignment

24. Shift Schedules from External System Ignition Solution Model in UDT • Add to equipment UDTs • Trigger events, reset real-time shift summary data Load upcoming schedule (cache) • Timer Script Load current shift • Tag Change Script

25. Enterprise Historian Architecture Central Database WAN Site (40+) Store & Forward Long Term Historian (years) Summary Events Statistics Analysis Data Science Customizable Dashboards Real time trends Short Term (weeks) High availability High Load

26. Centralize Trend Configurations Abstract Sensor and Data Based on Availability Differences between equipment Manufacturing areas Site to site Make the most of what is available Design Once Hybrid dashboards with real-time and historical data Only need to update database Minimize deployment time Minimize client updates and interruptions Store configuration in db table

27. Centralize Trend Configurations Ignition Setup

28. Centralize Trend Configurations The Power of SQL Extend Ignition Configuration Classify by equipment Classify by manufacturing area Classify by application Benefit from Database Views Allow views to do statistical analysis Modify underlying queries once – use everywhere

29. Customizable Dashboards Design for Different Roles and Users in the Enterprise User Defined Operator • Equipment Status • Order Info Supervisor • Shift Status • Work Order Sttatus Manager • Daily Plant Summary Shipping / Receiving

30. Design in Ignition Layout Main Window Functional Template uses • Functional Data • indirect tag binding • queries • scripting • business logic uses Component Templates Functional Template

31. Layout Templates

32. Store Configuration in Database Query configuration from database based on user selection Use python to parse csv data Load Ignition template configuration • Template paths • Template parameters

33. Tips: Ignition Database Connectoins Separate Connections by Application or Priority Security for read-only applications Prioritize for mission critical applications (SCADA) Historian may be considered low priority (Store & Forward) Optimize max connections setting

34. Tips: WAN Network Plan for WAN Constraints Quality of Service • JDBC socket timeout setting Outages • Store & Forward • Cache data in Ignition Bandwidth • S&F Settings

35. Tip: Troubleshoot Client Queries Windows with queries driving other queries can have unintended consequences Assess at design time Monitor on Ignition database connection status page