Introduction to IDSA 4xgs - Overview Introduction to...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Overview Introduction to Industrial Data and Systems Analysis • Operational Improvement Cycle • Overview of Topics in ORIE3120 Peter L. Jackson Professor School of O.R. and I.E. 1/23/2012 Industrial Data and Systems Analysis • Structured Query Language 1/23/2012 1 An Operational Improvement Cycl le Industrial Data and Systems Analysis 2 An Operational Improvement Cycl le • Measure the current performance of the current performance of the system Measure Act 1/23/2012 • Analyze the current system and identify ways to improve it • Act on the improvement ideas by implementing them • Measure the new performance of the system • Repeat the cycle Analyze Industrial Data and Systems Analysis 3 1/23/2012 Industrial Data and Systems Analysis 4 Example: A Chronic Yield Problem Chronic Yield Problem What the Team Found the Team Found • Story by Steve Crom, Valeocon Management • Every pet theory is a ‘hypothesis’ • Tested against data • Many favorite theories failed Consulting, published at http://europe.isixsigma.com/library/content/c051005b.asp • Pharmaceuticals manufacturer manufacturer • Looking at the data, the operators on the team (!) at the data the operators on the team (!) spotted correlation of content uniformity with agglomerates in base material • Then, the team noticed that agglomerates had the team noticed that agglomerates had more than expected amounts of flavor additive • Conclusion: process of mixing flavor additives was root cause for lumps, lumps resulted in too little vitamin D3 in some tablets, variations in D3 resulted in rejected batches • For 15 years, seemingly random batch failures due to variations in production process • Everyone had a pet theory had pet theory • Particle sizes, time of year humidity, equipment settings, etc. • Cross-functional team formed to study problem • Quality manager, operators, lab technicians, process engineers 1/23/2012 Industrial Data and Systems Analysis 5 Impact of Study of Study 1/23/2012 Industrial Data and Systems Analysis 6 An ‘Academic’ Perspective Academic Perspective • Control measures were implemented to monitor the critical variables identified in the study • Batch failures reduced from 12 per year to only two (and company continues efforts to eliminate them entirely) • Savings estimated at €500,000 per year with estimated at per year with additional saving downstream in tablet compression and packaging • Success of study led to general acceptance of ‘fact-based decision-making’ 1/23/2012 Industrial Data and Systems Analysis Measure Analyze Act 7 1/23/2012 Industrial Data and Systems Analysis 8 An Academic Perspective An ‘Academic’ Perspective The Business Perspective Business Perspective • Analysis is the ‘hard’ part • Analysis requires mathematical Measure sophistication hi • The really interesting problems are mathematical in nature mathematical in nature • Analysis provides the greatest value in the improvement cycle the improvement cycle 1/23/2012 Industrial Data and Systems Analysis Act Analyze 9 1/23/2012 Industrial Data and Systems Analysis A Practitioner’s Perspective: Measure The Business Perspective Business Perspective • I can always hire a ‘wonk’ to do the Measure analysis • Getting the data is difficult • Implementation is the hardest part • Making sure we solve the right problem is the most important thing 1/23/2012 Industrial Data and Systems Analysis 10 Act 11 1/23/2012 Request data Gather data Organize data Clean data data Aggregate data Explore data Identify opportunities opportunities Analyze Industrial Data and Systems Analysis 12 A Practitioner’s Perspective: Analyze Measure Act 1/23/2012 A Practitioner’s Perspective: Act Formulate model Fit data to model Select algorithm Solve model Interpret results Analyze Industrial Data and Systems Analysis 13 1/23/2012 Analyze Industrial Data and Systems Analysis 14 Industrial Data and Systems Analysi is •structured query language (SQL) query language (SQL) •procedural event-based programming •geographical information systems • Operational Improvement Cycle •multiple linear regression •classification •logistic regression •time series forecasting Database and statistical techniques for data mining, graphical display, and predictive analysis in the context of industrial systems analysis in the context of industrial systems (manufacturing and distribution). • Overview of Topics in ORIE3120 • Structured Query Language Industrial Data and Systems Analysis Measure Act Overview 1/23/2012 Listen to people Engage people Inform people people Persuade people Empower people Reward people •factory scheduling and simulation •materials planning •cost estimation •inventory planning •lean manufacturing manufacturing 15 1/23/2012 Industrial Data and Systems Analysis 16 Faculty Faculty • Prof. Peter Jackson • • • • • (Weeks 1-9.5) Operations Research and Information and Information Engineering 218 Rhodes Hall 607-255-9122 pj16@cornell.edu Office Hours Spring Hours 2012 : T,Th. 1 :30 2 :30 pm 1/23/2012 • Prof. Peter Frazier • • • • • (Weeks 9.5-14) Operations Research and Information Engineering 232 Rhodes Hall Rhodes Hall 607-254-5243 pf98@cornell.edu Office Hours Spring 2012: TBA Industrial Data and Systems Analysis 17 Origin of SQL (Structured Query Language) Overview • 1970: “A Relational Model of Data for Large Shared Data Banks” E.F. Codd • Operational Improvement Cycle • Definitive model for relational databases • 1970’s: Chamberlin and Boyce at IBM publish Ch IBM SEQUEL language • 1978: IBM successfully tests prototype with IBM successfully tests prototype with customers • 1980’s: commercial implementations of RDBMS (relational database management systems) • 1986: SQL adopted as standard by American National Standards Institute (ANSI) National Standards Institute (ANSI) • Overview of Topics in ORIE3120 • Structured Query Language 1/23/2012 Industrial Data and Systems Analysis 19 1/23/2012 Industrial Data and Systems Analysis 20 Relational Database Management System (RDBMS) (RDBMS) Relational database stores data in Tables provide data for view by for view by SQL Overview Overview • Tables • Sample Table with Key • Use Keys to Make Relationships • Tables are Related by Keys • Database Design Principle • Change the Design • Queries • Query Result • Select Only the Fields You Procedures execute flow of changes to execute changes to Queries 1/23/2012 Procedures “SELECT * FROM Products” Tables organized sequentially sequentially into formatted into are Interested In • Selective Query Result Reports Queries • Save the Query • Join Query to Table to Get Additional Fields • Join Query Result • Aggregate Data Query Data Query • Aggregate Query with Sub-Totals • Make a New Table • Modify Data in the th Database • Organize Executable Queries into Procedures • Select Which Records Are Reports 21 Industrial Data and Systems Analysis Interesting • Selective Query Result Query Result 1/23/2012 22 Industrial Data and Systems Analysis Procedures Tables Tables Reports Queries Procedures Sample Table with Key Table with Key Tables Reports Queries Fields: a fixed number of fixed number of columns, each column having a prescribed data type (i (integer, single, double, text, …) and length Records: an unlimited number of rows each row containing of rows, each row containing data in each column of the prescribed type 1/23/2012 Industrial Data and Systems Analysis Key: field, or ordered set of fields, whose data uniquely identify a record 23 1/23/2012 Industrial Data and Systems Analysis 24 Use Keys to Make Relationships 1/23/2012 Procedures Tables Reports Queries 25 Industrial Data and Systems Analysis Procedures Tables Tables are Related by Keys are Related by Keys 1/23/2012 Reports Queries 26 Industrial Data and Systems Analysis Procedures Database Design Principle Design Principle Tables Reports Queries • Avoid duplicating data duplicating data Procedures Change the Design the Design Tables Reports Queries • Suppose each customer can have more each customer can have more than one contact person • Don’t put customer phone number into Orders table • Ex. Mary handles meat orders, John handles everything else • Same phone number then occurs in hundreds or thousands of records • Wasted storage • Subject to error: person recording new order may make typing mistake and enter phone number incorrectly • How would you change the schema? • Set up Customers table (one record per customer) and include phone number field 1/23/2012 Industrial Data and Systems Analysis 27 1/23/2012 Industrial Data and Systems Analysis 28 Procedures Tables Queries Queries Reports Queries Procedures Query Result Result Tables Reports Queries English-like statement describing statement describing data request “SELECT * FROM Products” Non-procedural: does not describe HOW to get data (looping, testing) Small set of keywords: SELECT, FROM, AS, WHERE, ORDER BY, … Looks like a table, can be treated like a table (sometimes), but exists only in temporary memory but exists only in temporary memory Prescribed syntax: “KEYWORD expression KEYWORD expression …” 1/23/2012 29 Industrial Data and Systems Analysis Select Only the Fields You are Interested In 1/23/2012 30 Industrial Data and Systems Analysis Procedures Tables Reports Queries Procedures Selective Query Result Query Result Tables Reports Queries “SELECT ProductID, ProductName, SupplierID, UnitsInStock,UnitsOnOrder, ReorderLevel, IIf([ReorderLevel]>[UnitsInStock]+[UnitsOnOrder],[ReorderLevel][UnitsInStock]-[UnitsOnOrder],0) AS SuggestedOrder FROM Products” Use formulas to calculate new quantities formulas to calculate new quantities Name the new quantities as though they were new fields 1/23/2012 Industrial Data and Systems Analysis 31 1/23/2012 Industrial Data and Systems Analysis 32 Select Which Records Are Interesting Procedures Tables Reports Queries Procedures Selective Query Result Query Result Tables Reports Queries “SELECT ProductID, ProductName, SupplierID, UnitsInStock,UnitsOnOrder, ReorderLevel ReorderLevel, IIf([ReorderLevel]>[UnitsInStock]+[UnitsOnOrder],[ReorderLevel][UnitsInStock]-[UnitsOnOrder],0) AS SuggestedOrder FROM Products WHERE ReorderLevel>UnitsInStock+UnitsOnOrder ” WHERE clause describes selection criteria 1/23/2012 Now, you can use this query as the basis thi th for a report or for automated action (place orders with suppliers) 33 Industrial Data and Systems Analysis 1/23/2012 34 Industrial Data and Systems Analysis Procedures Procedures Save the Query the Query Tables Reports Queries Only the English-like statement is saved, not the query result. Tables SQL Overview Overview • Tables • Sample Table with Key • Use Keys to Make Relationships • Tables are Related by Keys • Database Design Principle • Change the Design • Queries • Query Result • Select Only the Fields You are Interested In • Selective Query Result Reports Queries • Save the Query • Join Query to Table to Get Additional Fields • Join Query Result • Aggregate Data Query Data Query • Aggregate Query with Sub-Totals • Make a New Table • Modify Data in the th Database • Organize Executable Queries into Procedures • Select Which Records Are Interesting • Selective Query Result Query Result 1/23/2012 Industrial Data and Systems Analysis 35 1/23/2012 Industrial Data and Systems Analysis 36 Join Query to Table to Get Additional Field Additi Fi lds Procedures Tables Reports Queries Procedures Join Query Result Query Result Tables Reports Queries formatted into Suppliers “SELECT Q01SuggestedOrders.ProductName, Suppliers.CompanyName, Q01SuggestedOrders Q01SuggestedOrders.SuggestedOrder FROM Q01SuggestedOrders INNER JOIN Suppliers ON Q01SuggestedOrders.SupplierID = Suppliers.SupplierID Report (non-interactive) (non 1/23/2012 37 Industrial Data and Systems Analysis Procedures Aggregate Data Query Data Query Tables Reports Queries Aggregate functions include: SUM(), functions include: SUM() COUNT(), MAX(), MIN(), AVG(), STDEV(), FIRST(), LAST() 1/23/2012 38 Industrial Data and Systems Analysis Aggregate Query with Sub-Totals l Procedures Tables Reports Queries “SELECT Orders.ShipVia, Sum(Orders.Freight) AS SumOfFreight FROM Orders Orders GROUP BY Orders.ShipVia” “SELECT Count(ProductID) AS CountOfProductID, Sum(SuggestedOrder) AS SumOfSuggestedOrder FROM Q01SuggestedOrders” Q01SuggestedOrders results in results in 1/23/2012 Industrial Data and Systems Analysis 39 1/23/2012 Industrial Data and Systems Analysis 40 Relational Database Management System (RDBMS) (RDBMS) Relational database stores data in Tables Reports Queries “SELECT ProductID, SupplierID,SuggestedOrder AS OrderQuantity INTO SupplierOrders FROM Q01SuggestedOrders” execute flow of changes to Queries 1/23/2012 Make a New Table New Table Procedures execute changes to provide data for view by for view by Procedures Tables “SELECT * FROM Products” organized sequentially sequentially into formatted into Reports 41 Industrial Data and Systems Analysis Procedures Tables Modify Data in the Database in the Reports Queries 1/23/2012 42 Industrial Data and Systems Analysis Organize Executable Queries into Procedures Procedures Tables Reports Queries Products table “UPDATE Products INNER JOIN SupplierOrders ON Products.ProductID = SupplierOrders.ProductID SET Products.UnitsOnOrder = SET [Products].[UnitsOnOrder]+[SupplierOrders].[OrderQuantity]” Products table Permanently changed in table changed in table 1/23/2012 Industrial Data and Systems Analysis 43 1/23/2012 Industrial Data and Systems Analysis 44 Relational Database Management System (RDBMS) (RDBMS) Relational database stores data in Tables provide data for view by for view by Procedures execute flow of changes to execute changes to Queries 1/23/2012 SQL Reference Manual Reference Manual “SELECT * FROM Products” organized sequentially sequentially into formatted into Industrial Data and Systems Analysis Reports 45 In Your Reading Packet Your Reading Packet 1/23/2012 Industrial Data and Systems Analysis 46 Overview • Operational Improvement Cycle • Overview of Topics in ORIE3120 • Structured Query Language 1/23/2012 Industrial Data and Systems Analysis 47 1/23/2012 Industrial Data and Systems Analysis 48 ...
View Full Document

This note was uploaded on 02/09/2012 for the course ORIE 3120 taught by Professor Jackson during the Spring '09 term at Cornell University (Engineering School).

Ask a homework question - tutors are online