SQL SERVER DATABASE TABLES | CNCNETPDM MANUAL

Top

This section describes the steps to setup an MS SQL Server database and required as well as optional tables for CNCnetPDM.

• Note: Starting with CNCnetPDM Ver. 6.3.3.1 the names of all database tables can be configured on a per-service basis, see chapter Connect of CNCnetPDM INI file for details.

Although not absolutely necessary for testing it is recommended to setup database tables and database connectivity before starting CNCnetPDM the first time. Without a database CNCnetPDM writes all collected data into a text file in subfolder \offline of the program directory.

• MTH_RAW_DATA Stores all data received from devices
• MTH_ONLINE_DATA Contains only the most recent data received from devices
• MTH_USER_CONTEXT Enables you to combine machine-data with work-order, operation and part-number information
• MTH_EQP_SHIFT_AVAIL_V Enables you to acquire data only during an active shift

MTH_RAW_DATA

For storing all data collected in every reading cycle from devices CNCnetPDM uses table MTH_RAW_DATA. This is ideal if you want to build reports with historical data from your equipment. You can create it manually by using the following code or use file MTH_RAW_DATA_SQLSERVER.sql from this zip archive. In this description tables in database MDA are used. To use this table please make sure that you have set WriteRawData = 1 in section [Connect] of CNCnetPDM.ini.

/****** Object:  Table [dbo].[MTH_RAW_DATA] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTH_RAW_DATA]
(
	[GROUP_ID] [float] NULL,
	[READING_TIME] [datetime] NOT NULL,
	[DEVICE_NUMBER] [smallint] NOT NULL,
	[TAG_CODE] [varchar](255) NOT NULL,
	[TAG_DATA] [varchar](255) NULL,
	[CREATION_DATE] [datetime] NULL,
	[PROCESSING_FLAG] [tinyint] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MTH_RAW_DATA] ADD  CONSTRAINT [DF_MTH_RAW_DATA_PROCESSING_FLAG]  DEFAULT ((1)) FOR [PROCESSING_FLAG]
GO

FIG 1:    Setup script (SQL Server) for database table MTH_RAW_DATA

MTH_ONLINE_DATA

To only store the most recent data from your machines you can use table MTH_ONLINE_DATA. CNCnetPDM updates all existing records for every device in this table when new data is queried. The update interval depends on the value for PollInterval in section [GENERAL] of CNCnetPDM.ini. To use this table please make sure that you have set WriteOnlineData = 1 in section [Connect] of CNCnetPDM.ini. You can create table MTH_ONLINE_DATA manually by using the following code or file MTH_ONLINE_DATA_SQLSERVER.sql from this zip archive.

/****** Object:  Table [dbo].[MTH_ONLINE_DATA] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTH_ONLINE_DATA]
(
	[GROUP_ID] [float] NULL,
	[READING_TIME] [datetime] NOT NULL,
	[DEVICE_NUMBER] [smallint] NOT NULL,
	[TAG_CODE] [varchar](255) NOT NULL,
	[TAG_DATA] [varchar](255) NULL,
	[CREATION_DATE] [datetime] NULL,
	[PROCESSING_FLAG] [tinyint] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MTH_ONLINE_DATA] ADD  CONSTRAINT [DF_MTH_ONLINE_DATA_PROCESSING_FLAG]  DEFAULT ((1)) FOR [PROCESSING_FLAG]
GO

FIG 2:    Setup script (SQL Server) for database table MTH_ONLINE_DATA

MTH_USER_CONTEXT

OPTIONAL: CNCnetPDM is able to use work-order, operation and part-number collected by a separate solution for Operational Data Acquisition. In this case CNCnetPDM looks up this information after every reading cycle and inserts 3 additional records for the current work-order, operation and part-number. If you plan to use this feature you also need table MTH_USER_CONTEXT. You can create it by using the following code or file MTH_USER_CONTEXT_SQLSERVER.sql from this zip archive.

/****** Object:  Table [dbo].[MTH_USER_CONTEXT] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MTH_USER_CONTEXT](
	[USER_CONTEXT_PK_KEY] [numeric](5, 0) IDENTITY(1,1) NOT NULL,
	[SCAN_TIME] [varchar](255) NOT NULL,
	[EQUIPMENT_FK] [varchar](255) NOT NULL,
	[WORKORDER_FK] [varchar](255) NOT NULL,
	[SEGMENT_FK] [varchar](255) NOT NULL,
	[ITEM_FK] [varchar](255) NOT NULL,
 CONSTRAINT [PK_MTH_USER_CONTEXT] PRIMARY KEY CLUSTERED 
(
	[USER_CONTEXT_PK_KEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

FIG 3:    Setup script (SQL SERVER) for database table MTH_USER_CONTEXT

If you’re using an external program for Operational Data Acquisition the table should contain one row for every device with the 4 digit device number in column EQUIPMENT_FK, SCAN_TIME is optional. The solution has to update WORKORDER_FK (work order), SEGMENT_FK (operation) and ITEM_FK (part number) with data about the current activity of the device.

MTH_EQP_SHIFT_AVAIL_V

OPTIONAL: If your devices are switched on all the time but you only want to acquire data within active shifts only you need an additional view named MTH_EQP_SHIFT_AVAIL_V that just returns the 4 digit device number in case there is an active shift for the device at the moment. If nothing is returned CNCnetPDM assumes that there is no active shift. For testing you can also create a table MTH_EQP_SHIFT_AVAIL_V that just contains column EQUIPMENT_FK. To build the table you can use the following code or file MTH_EQP_SHIFT_AVAIL_V_SQLSERVER.sql from this zip archive.

/****** Object:  Table [dbo].[MTH_EQP_SHIFT_AVAIL_V] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MTH_EQP_SHIFT_AVAIL_V](
	[EQUIPMENT_FK] [numeric](5, 0) NOT NULL
) ON [PRIMARY]
GO

FIG 4:    Setup script (SQL SERVER) for database table MTH_EQP_SHIFT_AVAIL_V

For testing you can insert the 4 digit device number into field EQUIPMENT_FK and adjust the .ini file for CNCnetPDM to only collect data within a shift. In a production environment MTH_EQP_SHIFT_AVAIL_V should be a view that looks up if there’s an active shift for the equipment in a shift table and, if yes, returns the 4 digit device number.