WinSPC Tips & Tricks: Setting Up WinSPC to Use An External Database As A Data Source | WinSPC Tips & Tricks

November 2, 2014

WinSPC makes it easy to capture your existing data for actionable SPC analysis.

In WinSPC Version 8, you can configure a device to collect data from multiple fields in your external database and store those values into WinSPC variables. This is done much like you would have configured an ODBC device in previous versions, however, the “Location” field can now be used to selectively map any field that is available to you. This enables the use of one device to feed multiple variables in a collection plan.

Let’s walk through an example to show how this is done. We will assume that you have a database table containing the following information:

  • A field containing a unique record ID
  • Three fields containing characteristics that you measure (Length, Width, Height)
  • A field for your Lot Number (which will be an identifying “Tag” in WinSPC).
  • A field for your Serial Number (which will also be an identifying “Tag” in WinSPC).
  • A field for your Production Date.

So your source database table might look something like this:

sampledb.jpg

In WinSPC, you would setup a part that contains three variables and two tags that look something like this:

odbc_partsetup.jpg

NOTE: Windows must be configured to see your database as an ODBC DSN. It is recomended that you set the database up as a System DSN if possible. Microsoft provides instructions in the following

Next, we need to configure an ODBC device. To do so:

  1. Click on the “New Device” button in the toolbar to create a new device and give it a name. In this example, we will use “Production Access Database” as the device name.
  2. Edit the properties of the device (right click on the new device, select “Properties“).
  3. Select “ODBC Data Source” from the “Device Type” drop down at the top of the window.odbcdevice_connectionproperties.jpg
  4. Select the configured data source from the “ODBC Data Source Name” drop down. This data source is configured from within your Windows Control Panel and varies based upon the specific operating system you are using. If needed, also fill in the “User Name” and “Password” fields to provide the necessary credentials to connect to the database.
  5. Once the above items are filled out, move to the second tab (“ODBC Settings“).odbcdevice_odbc1properties.jpg
  6. On the “ODBC Settings” tab, select the table or view from the “Table / View” drop down. Alternatively, you can write your own SQL command using the “SQL Text” option. Once you have done either of these, move to the third tab (“ODBC Settings (Cont.)“).
  7. On the “ODBC Settings (Cont.)” tab, we need to select the fields from the database that WinSPC will use for the different options for the ODBC Device.odbcdevice_odbc2properties.jpg
  8. In this example, we will not select a “Data field“, as that configuration will be done later, in the “Collection Plan Setup” window. We will, however, select a “Date/Time field” (to control the date/time of the data in WinSPC), the “Ordering field” (to control the order in which the data is read into WinSPC), and a “Sequence number field” (to tell WinSPC to remember what data it has already collected). So now our “Device Setup” might look something like this:odbcdevice_odbc2properties_dbexample.jpg
  9. Note that we do not need to configure the “Data field” option here. Now we can save the device setup by clicking “OK“.
  10. Next, we need to point each of the variables to this device in the “Collection Plan Setup” window. Edit the properties for the collection plan containing the variables (by right clicking on the collection plan and selecting “Properties“).cp_sampledb.jpg
  11. For each variable that you want to use the ODBC device for in data collection, click on the “Browse…” button underneath the “Collect With” option to select the device.
  12. Next, select the field you want to use from the “Location” drop down.
  13. Repeat this process for each variable that needs to be associated with the device, selecting the appropriate database field from the “Location” drop down.cp_sampledb_withdeviceandlocation.jpg
  14. The collection plan is now configured to use one ODBC Device to collect data to multiple variables from your external datasource.

 

About DataNet Quality Systems

DataNet Quality Systems delivers trusted and capable real-time statistical solutions that allow manufacturers to create the highest quality product for the lowest possible cost. DataNet’s flagship product, WinSPC, provides statistical decision-making at the point of production while delivering real-time, actionable information to where it is needed most. With thousands of installations worldwide and distributors across the globe, DataNet is dedicated to delivering the highest level of customer service, shop-floor expertise, and support for your Continuous Improvement, Six Sigma, and Lean Manufacturing goals.

Leave a Reply