Leveraging IoT for Process Improvement

What is IoT?

• Network of physical objects with IP Addresses

• Passes data back to the Internet

• Connects to devices that ARE NOT normally considered to be computers (not laptops, desktops, or servers)

And What is Op-Smart?

• Manage production schedule• Capture inputs• Collect data from shop floor• Recognize outputs• Provide reporting

• Productivity• Process improvement

Op-Smart Provides

Process & Profit ImprovementOpportunities

30,000Validated

FactsPer Shift, Per Line

ResultsEfficiencyQualityProfitability

CustomersQuality, ValuePartnership

ShareholdersIncreased Earnings Per Share

CommunityStability, Job Growth

The Business Opportunity

85%

20 30

55% WorldClass

AveragePlant

Opportunity: to% %

… Uptime

Building Products

Pharmaceuticals

Data Collection System

PC

PLC

Data Collection

Data Collection PC

PLC

Switch

Switch

Switch

Switch

Third Party System

· Reading from local systems· Pushing data to cloudIncorporates· SQL Server (Community edition)· C# Applications· Stored Procedures

Azure

PLC for Data Collection

Ladder Logic

Aggregating the Data

PLC PC

1

2

3

4

Log

HTTP

Local SQL Cloud SQLTSQL

L1 C# 1 C# 2

Core Application Architecture

Microsoft SQL Server

Entity Framework

Repository

Service

Controller

ViewJSON

Database Server

Web Server

Browser

HTMLAJAX

Javascript

Kendo

Built on the Microsoft Stack

MicrosoftMVC

We’ve Got Tables!

SQL Server Management Studio DiagramsMoved to lots of mini-diagrams…

Entity Framework to access DB

• Entity Framework– Microsoft’s ORM

• Eliminates code writing

• Provides transaction management

– Has some issues• Requires hierarchical

structure for best results

• Join operator only does equijoins

• OPTIMIZATION CAN BE HARD!

Access Methods?

• Embedded SQL

• Stored Procedures

• Object Relational Mapping

(ORM)

Example 1private int[] GetProcessUnitEquipments(int processUnitId)

{

var equipmentIds = _context.ProcessUnitEquipments

.Where(x => x.processUnitID == processUnitId)

.Select(x => x.equipmentID);

return equipmentIds.ToArray();

}

BecomesSELECT

[Extent1].[equipmentID] AS [equipmentID]

FROM [dbo].[ProcessUnitEquipment] AS [Extent1]

WHERE [Extent1].[processUnitID] = @p__linq__0

• No need to bind variables• Column names are

checked as code is entered, validated at compile time

Example 2

var propertyQuery = from p in _context.EquipmentProperties

where p.PropertySetMember.memberName == "ShowProcessTimeTablet" &&

p.equipmentPropertyValue==1

select new

{

p.equipmentID

};

See the join?

Example 3Complex 50 line query

var activityQuery = (from a in _context.Activities

join act in myActivityIds on a.activityID equals act.ActivityId

select new Model.ActivityTypes.Activity

{

ActivityId = a.activityID,

…

SuppressOnTablet = a.ActivityType.suppressOnTablet,

ActivityHistories = (from ah in _context.ActivityHistories

where ah.activityID == a.activityID &&

(equipmentId == 0 || ah.equipmentID == equipmentId)

select new ActivityHistoryShortForm

{

ActivityHistoryStart = (DateTime)(ah.actualStart ?? ah.actualEnd),

…}).OrderBy(x => x.ActivityHistoryStart),

EquipmentName = a.Equipment.equipmentCode,

ActivitySegments = from s in a.ActivityScheduleSegments

where

(equipmentId == 0 || a.equipmentID == equipmentId)

select new ActivitySegment

{

ActivityScheduleSegmentId = s.activityScheduleSegmentId,

…},

GotActivityReportingProperties =

a.ActivityProperties.Any(x => x.PropertySetMember.PropertySet.propertySetDescription == "ActivityReporting")

}).OrderBy(o => o.WorkOrderStartDate).ThenBy(o => o.WorkOrderId).ThenBy(o => o.Start).AsEnumerable();

416 lines of SQL- Sometimes with Performance Problems

- Very hard to debug or optimize!

SQL OptimizationTurn on

graphical plan

display

Hover over item

to see details

Examine suggested

indexes (carefully!)

Old School Style

Peek inside the black boxActivity Monitor…

Peek inside the black boxThe Profiler…

PARTS

MAINTENANCEHISTORY

MATERIALS

MOLDS

YARD

NEWMetrics & KPIs

Anytime - Anywhere

Define why the plant is not functioning

Identify Largest Opportunities

Visualize Business Impact

Tools for Planning and Visualizing Work

Production Reporting

Barriers to Success• Finding the right customer

• Must be quality focused

• Must be committed to continuous improvement

• Must have stomach to face failures every day

• Must have commitment from shop floor to executive suite

Thank youQuestions?