10.4 Data Dictionary

Overview of Database Tables

FactoryWiz is an open system, meaning we give users full access to all data for the purpose of writing custom applications and reporting solutions, as well as integrating with other business applications such as ERP systems, FactoryWiz has the ability to import and export data to and from other systems either in real-time, or scheduled periodically to refresh historical data. This means the other systems should have the ability to import and/or export data as well.

Possible data exchange methods include:

1. FactoryWiz API DLL for creating applications in dotNet Framework 4.8 or dotNet Core 6 and above
   
2. FactoryWiz SOAP API for communicating with FactoryWiz over HTTP network connection 
3. ODBC Connector direct database connection, either locally or over network connection, for Applications like Excel, Microsoft Access, or Power BI.
   
4. Direct MySQL database connection (Advanced)

The two main FactoryWiz database tables for retrieving/exporting production data are the CycleData and CycleLiData tables.

The main FactoryWiz database table for importing production data, such as Job and Cycle Time data from ERP systems, is the RefTimeData table.

For additional information on the RefTimeData table and details regarding importing data into FactoryWiz,
please refer to KB article 8.2 Cycle Times

List of tables available in the database:

Table Name	Purpose
AlarmData	History of alarms
AlarmState	Internal Use Only
BlobData	Large object storage (such as images)
CncGroups	Groups of machines
CncOperatorConfig	Internal Use Only
CommandQueue	Internal Use Only
CycleData	Data for each Cycle / Part
CycleLIData	Event detail line items per Cycle
DNCLog	N/A
EmailQueue	Internal Use Only
FWConfig	All system settings
MntBackupState	N/A
MntHistData	Preventive Maintenance History
MntHistLIData	Preventive Maintenance History line item details
MntSchedData	Preventive Maintenance Schedules
MntSchedLIData	Preventive Maintenance Schedule line item details
MntTemplateData	Preventive Maintenance Schdule Templates
NotificationData	Collaborative Messageing data
OffsetState	Live machine offsets
ParameterState	N/A
PmcBitState	Live PMC (PLC) bit data
ProbeData	N/A
RealtimeState	Live machine data
RefTimeData	Data imported from ERP system or other external source
SchedAvailDate	Schedule of Unavailable Time
SerialNumberState	Internal Use Only
SettingState	Internal Use Only
StickyNoteData	Collaborative Note data
StickyNoteUserState	Collaborative Note tracking
StreamData	Machine event stream
SummaryData	Precalculated historical data for reporting
SwarmData	Collaborative Assistance Request / Response data
SystemLog	Internal Use Only
TimelineData	N/A
ToolLoadData	Data from tool load
VariableState	Live Macro Variable data

Every time a new part is produced on the CNC, a new record is created in the CycleData table.

Every time a CNC event occurs, such as stops, starts, mode changes, etc., a new record is created in the CycleLiData table.
These two tables are joined by their corresponding Serial field, specifically where CycleData.CDSerial = CyclLiData.CDLISerial .

CycleData Fields:

Field Name	Type	Max Size	Purpose
CDID	Integer	4294967295	Unique autoincremented primary key field
CDSerial	Integer	4294967295	Serial number for the cycle
CDCnc	String	50	CNC Name
CDPartId	String	100	Part ID, taken from CNC program, ERP or tablet
CDCustText1	String	255	For storing custom defined string value
CDCustText2	String	255	For storing custom defined string value
CDCustText3	String	255	For storing custom defined string value
CDCustText4	String	255	For storing custom defined string value
CDCustText5	String	255	For storing custom defined string value
CDCustText6	String	30	For storing custom defined string value
CDCustText7	String	30	For storing custom defined string value
CDCustText8	String	30	For storing custom defined string value
CDCustText9	String	30	For storing custom defined string value
CDCustNum	Integer	2147483647	For storing custom defined integer value
CDCustDbl	Double		For storing custom defined decimal value
CDIdleSecs	Integer	4294967295	Idle seconds for that cycle
CDJob	String	100	Job / Works order number
CDJobOpNo	String	10	Job / Works order OP number
CDBatch	String	50	Batch number
CDRejected	Integer	4294967295	Quantity of parts rejects that cycle
CDIncomplete	Tiny Int	1	0 if complete, 1 if incomplete (basically treated as boolean)
CDStart	DateTime		DataTime when Cycle started
CDFinish	DateTime		DataTime when Cycle finished
CDRunSecs	Integer	4294967295	Running seconds for that cycle (cutting material)
CDPausedSecs	Integer	4294967295	Feed hold seconds for that cycle
CDPartsPerCycle	Small Int	65535	Quantity of parts produced in that cycle
CDAvailable	Tiny Int	1	If the machine was set as being available
CDUnavailableReason	String	50	If unavailable, the reason (if provided)
CDRejectedReason	String	50	If the part was rejected, the reason (if provided)
CDShifts	String	60	Shifts this cycle spanned (can be multiples)
CDProjectedRuntime	Integer	4294967295	The projected running time of the cycle in seconds (if provided)
CDProjectedIdleTime	Integer	4294967295	The projected idle time of the cycle in seconds (if provided)
CDSetupSecs	Integer	4294967295	The projected setup time of the cycle in seconds (if provided)
CDHVCycles	Integer	4294967295	N/A

CycleLiData Fields:

Field Name	Type	Max Size	Purpose
CDLIID	Integer	4294967295	Unique autoincremented primary key field
CDLISerial	Integer	4294967295	Serial number for the cycle
CDLICnc	String	50	CNC Name
CDLIStart	DateTime		DateTime when cycle event started
CDLIFinish	DateTime		DateTime when cycle event finished
CDLISecs	Integer	2147483647	duration of the event in seconds
CDLIShift	Integer	2147483647	The shift number when event started
CDLIShiftDate	Date		Date of shift when event started
CDLIOperator	String	50	CNC Operator name
CDLIEmpNo	String	20	Employee ID of CNC Operator (if configured)
CDLIAvgFeed	Small Int	32767	Average Feed rate during event
CDLIAvgSpindle	Small Int	32767	Average Spindle rate during event
CDLIAvgRapid	Small Int	32767	Average Rapid rate during event
CDLIFirstChunk	Tiny Int	1	1 if first event, 0 if subsequent event
CDLIIncomplete	Tiny Int	1	0 if complete, 1 if incomplete
CDLIType	Tiny Int	2	Event type (Idle = 0, Running = 1, Paused = 2)
CDLIType2	Tiny Int	5	Idle = 0, Running = 1, Paused = 2, Alarm = 3, Disconnected = 4, MDI = 5
CDLIMode	String	50	Mode during even (Production, Setup, etc.)
CDLIExtendedIdleReason	String	255	If event Type was not 1 (running) and reason was given, then this will be the full idle reason string
CDLIIdleReason	String	50	If the given Idle Reason string was nested (contained any '\' characters), then the string after last \ from the Extended Idle Reason (else just full reason text)
CDLIBreakSecs	Integer	2147483647	integer
CDLIStartSequence	Integer	2147483647	integer
CDLIFinishSequence	Integer	2147483647	integer
CDLIMaxSpindleLoad	Integer	2147483647	Maximum value of Spindle load during event
CDLIAvailable	Tiny Int	1	If the machine was set as being available during the event

RefTimeData Fields:

Field Name	Type	Max Size	Purpose
FTID	Integer		Unique primary key field (IGNORED IF PRESENT IN ANY IMPORT DATA)
FTCnc	String	50	Optional
FTPartID	String	100	Optional, but mandatory if FTJob is not set
FTJob	String	45	Optional, but mandatory if FTPartID is not set -OR- Mandatory if you want to use the "Show ERP Data" option on the "New Job"  widget in OperatorConnect Tablet Interface
FTJobOpNo	String	10	Optional Job OP Number if FTJob is set
FTCycleTime	Integer	2147483647	Optional Planned Runtime in Seconds
FTSetupSecs	Integer	2147483647	Optional
FTLoadTime	Integer	2147483647	Optional
FTUpdated	DateTime		The last Date and Time that the record was imported or updated (IGNORED IF PRESENT IN CYCLETIME CSV IMPORT DATA)
FTSite	String	20	Optional
FTDepartment	String	20	Optional
FTPartsPerCycle	Integer	2147483647	Optional
FTBatchQuty	Integer	2147483647	Optional
FTCustText1	String	255	Optional
FTCustText2	String	255	Optional
FTCustText3	String	255	Optional
FTCustText4	String	255	Optional
FTCustText5	String	255	Optional
FTCustText6	String	30	Optional
FTCustText7	String	30	Optional
FTCustText8	String	30	Optional
FTCustText9	String	30	Optional
FTCustText	Integer	2147483647	Optional
FTCustDbl	Double		Optional
FTDescription	String	100	Optional
FTMode	String	50	Optional

CncGroups Fields:

Field Name	Type	Max Size	Purpose
ID	Integer		Unique autoincremented primary key field
MachineName	String	50	Name of a machine in the FactoryWiz system - always inner joined to a related to a related CNC field in another table such as RTCnc, CDCnc, CDLICnc, FTCnc, SDCnc, or UDCnc
GroupName	String	50	Name of a group in the FactoryWiz system. n:n relationship between machines and groups. All machines will also appear in a group called "ALL_FW" which encompases every machine.

SummaryData Fields:

(note that the records in this table are split by date, shift, part id, operator, idle reason, scrap reason, job+job op, and mode. This means that any selection or grouping NOT via one of those fields may need to be performed against CycleData / CycleLIData)

Field Name	Type	Max Size	Purpose
UDID	Integer		Unique autoincremented primary key field
UDCnc	String	50	CNC Name
UDPartID	String	100	Part Name
UDJob	String	100	Job
UDJobOpNo	String	10	Operation Number
UDShiftDate	DateTime		Shift date (which can be slightly different from recorded time since the entirety of shifts which begin before midnight take the date from when the shift starts)
UDShiftNum	Integer		Shift number. Generally for OEE calculations, ignore any numbers below 1
UDTotParts	Integer		Total parts produced (good and bad)
UDBadParts	Integer		Only parts marked as bad (see UDScrapReason column for why)
UDTotSecs	Integer		Total seconds (including seconds not part of OEE calculation)
UDTotAvailSecs	Integer		Only available seconds used in OEE calculation (total)
UDTotAvailRunSecs	Integer		Only available seconds used in OEE calculation (running)
UDCycAllSecs	Integer		Only for completed cycles - the total running and idle time
UDCycRunSecs	Integer		Only for completed cycles - the total running time
UDCycRefRunSecs	Integer		Only for comlpeted cycles - the benchmark running time (if none was available, will match the UDCycRunSecs so that the performance number defaults to 100% in the event of missing data)
UDCycles	Integer		Cycle count (which can differ from the part count) used to back out total running seconds or total parts to the time or parts per cycle
UDOperator	String	50	Operator name
UDMode	String	50	Operation mode
UDIdleReason	String	50	Idle Reason (only filled in if this record represents
UDScrapReason	String	50	Scrap code (only filled in if this record represents scrapped parts)

• Related Articles

• 8.3.2 Data Retention

  Data Retention The Data Retention tab allows you to select how long FactoryWiz™ Monitoring stores the data it collects from your machines. You may set different retention periods for productivity data and event data. Productivity data is anything ...

• 7.2 Data Entry - History

  Under the History view, you can see previous cycles and information associated with each cycle, including each status change recorded in that cycle. By using the date picker at the top, you can filter your results by date. The History view is split ...

• 7.3 Data Entry - Quality

  The Quality tab of the Data Entry screen allows you to reject multiple parts historically. You must filter parts by Date and Part ID. Once a date has been selected, the Part Id will be populated with the Part IDs produced by the selected Machine on ...

• 8.3.3 Manual Data Deletion

  Manual Data Deletion The Manual Data Deletion tab allows you to manually delete all older data collected from all the machines or delete the data collected from specific machines, thus removing them from the FactoryWiz Database. If you want to purge ...

• 7.1 Data Entry - Current State

  The Data Entry screen is where you can alter the current state of certain variables per machine. When you arrive at the Data Entry screen by using the left side menu, the page will display all available machines. Click on a machine to see information ...