How to Get the Most from a Database

Databases help record, analyze, and relay plant-floor information, often behind the scenes. Data arrive from manufacturing, controls, instrumentation, automation software, human-machine interface software, execution systems, and even clipboard-wielding personnel, who may still manually collect and enter information.

By Mark T. Hoske June 1, 2002

ON-LINE EXTRAS:

Six tips for designing a manufacturing database

Look inside a Hershey database effort

Database connections provide ways and means

KEY WORDS
  • Software and information integration

  • Database

  • Human-machine interface (HMI)

  • Data acquisition

Sidebars:
End-users provide input on databases

Databases help record, analyze, and relay plant-floor information, often behind the scenes. Data arrive from manufacturing, controls, instrumentation, automation software, human-machine interface software, execution systems, and even clipboard-wielding personnel, who may still manually collect and enter information.

Without proper consideration of the process, database design, and implementation, however, a database can become a monster to be fed, rather a source of intelligence and value for users. Think about the databases used in your facility-are they working for you, or are you working for them?

Almost all software uses some type of database to store and retrieve data. Arguably, an effective database is organized so users unfamiliar with the underlying structure can get information in a useful form without a lot of difficulty.

The software language providing the interface between the user and the data can vary. Understanding how to organize and retrieve the information in a standard way obviously can help.

Structured query language

Structured query language (SQL or database query language)-some say Satan’s query language because of its complexity-is a standard established by the American National Standards Institute (ANSI) and International Standards Organization (ISO). IBM originally developed it in the 1970s.

SQL is described as a declarative language; users tell it what to do, rather than how to do it. The resulting relational database, designed to organize large quantities of data, is usually a collection of tables with relationships among them.

Tables’ records are in rows and fields are in columns. Field types vary widely according to content needs: numbers, text, currency, dates, objects and others.

Courses and books are available on database design and organization, to help establish and understand the relationships between each table, each collection, of information.

Compatible software can retrieve its own information from databases. If information exists once, in theory, it’s easier to maintain and manage, because an update in a one location updates information in related forms or reports in many places. This provides one ‘truth.’ (See related reading in Online Extra box and at www.controleng.com .)

Users retrieve a set of information from SQL via a query or request. Queries can update, modify, and calculate data. They also can be automated to feed standard reports and accept information from various sources.

According to Microsoft Corp. (Redmond, Wa.), software programs do most database access, in regularly scheduled reports, statistical analyses, and data entry programs. While Microsoft’s SQL Server offers online query tools and other wizards, Ronald Sielinski, senior industry technical strategist within the Industry Solutions Group at Microsoft, says most end-users will likely want an application interface between them and a database, because writing queries is like writing code. And, he says, ‘The cheapest line of code is one you can buy… not many companies are interested in writing their own applications.’

Microsoft Developer Network Library at https://msdn.microsoft.com explains that software programs access SQL three ways:

  • Embedded SQL, in which SQL statements are embedded in a host language, such as C or COBOL;

  • SQL modules, in which SQL statements are compiled on the database management system (DBMS) and called from a host language; and

  • Call-level interface (CLI), which consists of functions called to pass SQL statements to the DBMS, and to retrieve results from the DBMS.

Information from data

Over time, users may spend too much time serving their databases (because that’s the way they’ve always done it), without questioning what the data’s used for or the return on investment. Stored data needs to serve the changing needs of users by becoming information when and where it’s needed.

Terry Sutter, Honeywell International’s president of Industry Solutions (Phoenix, Ariz.), was previously a customer, an engineer running Honeywell’s chemical businesses. He says there are too many unsynchronized, inconsistent databases containing lots of meaningless data. A recent customer visit, he adds, showed how less than 10% of data collected drives any action. Mr. Sutter spoke at the ARC Advisory Group Manufacturing Strategies Forum meeting in Orlando, Fla., on Feb. 11.

Donna Fulenwider, marketing director, Analytic Intelligence, SAS Institute (Cary, N.C.) notes there’s a difference between gathering data and doing something with it. SQL can operate underneath a process, but there’s a need to navigate according to business knowledge, rather than by SQL queries, she says. And just storing data doesn’t mean it can be analyzed. There needs to be an analytical layer. Making metadata (information about the data) helps users understand the data usage patterns, and how to best access information regardless of where it resides, Ms. Fulenwider explains, in a March 20 conversation with Control Engineering at National Manufacturing Week.

Microsoft’s Mr. Sielinski says, that despite complexities, databases serve a simple function, and strategies exist to optimize performance. Online analytical processing (OLAP) tools, included in some Microsoft software, can help package and analyze data. Microsoft’s tools for OLAP include SQL Server 7.0; Office 2000; SQL Server 2000 Analysis Services; and Microsoft XP, particularly Excel 2002. OLAP data structures start with a cube (data universe or theme), which includes dimensions (categories or areas of interest), hierarchies (subcategories), and measures (actual data values).

Info to intelligence

The current buzz-phrase is ‘transforming data into business intelligence,’ Mr. Sielinski says. Doing so requires having information in the right form, when and where it’s needed in a cost-effective manner. SQL databases and the persons and tools that interact with them can help.

Executive Manufacturing Technologies (EMT, London, Ontario, Canada) offers what it calls enterprise manufacturing intelligence through its VisualPlant 3.1 software. This scalable software allows manufacturers to collect, display, and analyze all production activity, now or any point in the past, via secure browser, according to John Dyck, EMT’s vp of marketing and business development. Installation is said to take days, compared to custom software programs that often require six months to a year. VisualPlant is in more than 50 plants, presently.

All major software vendors provide interfaces to database infrastructure; typing ‘database’ as a keyword at Control Engineering Buyer’s Guide returns more than 80 vendors at www.controleng.com/buyersguide .

Comments? E-mail mhoske@cahners.com

End-users provide input on databases

What should end-users consider regarding databases?

The right info has to be in the right places, usable, and updated;

Education and training… systems need to be easy to work with;

Databases have to be driven from work processes to help people do their jobs. Generally databases are part of an application or solution;

Think about a central design. Databases are often disconnected and difficult or impossible to integrate into one location. This may be the unattainable nirvana of information management, but it still can be useful to aggregate data collection;

Clearly determine what functions are needed;

Data structures may differ depending on the application and technologies: batch, continuous, semi-continuous, a programmable logic controller on the packaging side, control systems, manufacturing execution systems, enterprise resource planning, or data for a process that needs to conform to U.S. FDA 21 CFR Part 11 requirements; and

Think electronically. ‘Get information to go with the parts of the product instead of recreating it at every step. There needs to be more direct collaboration between machine tool builders and end-users,’ according to Dick Mathias, consultant for Boeing Corp. (Chicago).

Source: Control Engineering, with input from end-users.


Author Bio: Mark Hoske has been Control Engineering editor/content manager since 1994 and in a leadership role since 1999, covering all major areas: control systems, networking and information systems, control equipment and energy, and system integration, everything that comprises or facilitates the control loop. He has been writing about technology since 1987, writing professionally since 1982, and has a Bachelor of Science in Journalism degree from UW-Madison.