Old McDonald had a farm…O-L-E D-B. Not the words you had in mind? It’s okay, by the end of this entry you might never go back to the original!
We all see and use acronyms all the time, but we don’t always think about the meaning and power behind them. Two of my all-time-favorites are DMS and DCS. DMS stands for Data Management System and DCS stands for Data Collection Service. These are a couple of very powerful tools that you should have in your InfinityQS toolbox.
DMS uses providers to interact and communicate with many types of devices. Just like Old McDonald, who has several different crops and animals on his farm, you have data in many different places at your workplace. If we were to sit down and eat a meal at Old McDonald’s table it might consist of milk from his cow, tomatoes from his garden, corn from his fields, and even an egg or two from one of his hens. The thing is, all of these different items come from different sources, but end up on the same table. DMS has several types of providers that can be used to bring data into one specific location from many types of devices. The nice thing about DMS is that it allows these different providers to be used at the same time. This can be very helpful, especially when building subgroups similar to a meal on Old McDonald’s table.
One of my favorite providers for DMS is the OLE DB (Object Linking and Embedding, Database) provider. This provider will allow anyone to view data from an OLE database in the Data Management System. The OLE DB provider allows for your database administrator to write SQL queries that can search a database for specific tables and then pull the fields that you want to return and view in the Data Management System. That data can then be added to other data from additional providers and then bundled into a subgroup and stored into the ProFicient database. Just like Old McDonald’s table, this data is neatly in one place and you can decide what needs to be combined to create the subgroup needed. This data can be just about anything – from Production Order Numbers, Lot Numbers, etc. – from an ERP or MES system. This provider can also extract information from Microsoft Access databases used in some custom homegrown applications.
In the SQL tab of the provider (pictured below), a query can be entered in the field A. Processing to search a database for specific values from a desired table.
A. Processing:
- SELECT TOP 10 * FROM sgrp_ext
- WHERE f_sgrp > %f_sgrp%
- ORDER BY f_sgrp ASC
The OLE DB provider also allows you to create a token to generate a starting point for each database query, so the provider does not have to query the entire database each time a search is initiated. By utilizing the B. Startup and C. Preserve Argument.
B. Startup:
- SELECT DISTINCT TOP 1 f_sgrp FROM sgrp_ext
- ORDER BY f_sgrp ASC
The data requested will now be displayed to the right and is viewable using the Data Collection Service (DCS). Again, in DCS the data can be bundled with data from other providers to create a single subgroup to be stored in the ProFicient database.
Another use for this tool is to pull production data from Industrial SQL databases. If you have a manufacturing network where you store information from your Program Logic Controllers (PLC) in a Historian database, this tool might be the one for you. Some managers want to sample frequently because they don’t want to miss anything, but for SPC purposes, you don’t necessarily want to sample that frequently just because you can. Sampling plans should be used to identify when and where data should be gathered. Use the Historian database and capture that information for historical purposes, then use the OLE DB provider in DMS/DCS to query that Historian database and collect at the appropriate interval for your SPC sampling strategy. It’s a win-win scenario. Also, if the data tells you something utilizing SPC tools, the option to go back and look into the historian database is still there.
Using the DMS tool, and its many different providers, you can build a subgroup from your ProFicient database that combines a Production Order Number from your ERP system using the OLE DB provider and process parameters from various equipment PLCs using the OPC provider, with product testing data from your QC lab testing equipment using Grid Data provider, and readings from a gage using the MUX provider. You just used four separate providers to pull data from four distinct sources and combined them into one subgroup. That is very powerful; especially if you use Lean Six Sigma and want to do correlation studies. Now you won’t have to spend countless hours matching data with different parameters due to WIP (work in Progress) or other time delays in the process. Just like Old McDonald, you can have many different data sources and providers on your "farm" and they can all serve a purpose.