Database Manager
(v. 5.4.4008)
Using Database Manager, you can create, configure, view, and print your ProFicient databases, as well as add, edit, delete, import, and export records within the databases.
ProFicient requires an ODBC compliant database to store all of its information, including part numbers, machine numbers, processes, gages, test characteristics, specification limits, control limits, subgroup values, comments, process events, employee information, and security levels. Even if you are using many different projects throughout your facility, all the data resides in this single, centralized database.
Database Overview
The database is like a filing cabinet with many drawers. The cabinet represents the database, the drawers in the cabinet represent the database tables, the folders in the drawers represent the groups, and the papers in the folders represent the items within the groups.
Database structure requires that you think in terms of company-wide rather than department-wide, since this approach will easily accommodate change and growth as additional departments and product lines may require the ability to add data. InfinityQS strongly recommends that you develop a company-wide naming convention, and publish these conventions so that employees clearly understand how to add new items to the database.
When creating groups, you must determine logical classifications for each family of items. For example, you could organize your impeller parts in an Impellers group, and your shaft parts in a Shafts group, or you could decide not to separate the different parts, and instead organize the impeller parts and shaft parts in a Global Parts group.
Within a single table (drawer), new groups (folders) can be added, moved, or deleted at any time. Existing groups, however, cannot be moved to another table. In addition, items (papers) within a group (folders) can be added, moved to other groups or deleted, but cannot be moved to another table.
For example, an item initially created in the Part table can be moved around to different part groups, but cannot (and should not) be moved to the Process table.
Types of Databases
The database can range from low-end single-tier databases, such as Microsoft Access, to the high-end client-server databases, such as Oracle, Sybase, SQL Server, Informix, and Progress, and InfinityQS provides the Microsoft ODBC drivers for a variety of databases in the installation package.
- Single-Tier Databases. Most single-tier databases can be created directly in Windows without having the database software installed. For example, you can create a Microsoft Access database without having Microsoft Access installed on the computer.
However, for security and scalability reasons, single-tier databases are not recommended for multi-user network installations.
- Multi-Tier Databases. ProFicient does not include a database engine for multi-tier databases, and some database systems, such as Oracle or Sybase, do not allow Windows to create the database shell. Before creating the database shell, you must be logged in as the database owner (DBO), which is NOT the same as a user with administrative rights or full permissions. The DBO is the original creator of the database. If you are not logged in as the DBO, other users will not have permissions to new tables, and will not be able to see or use those tables. Once you create the database shell, ProFicient can create all of the required database tables.
Database ODBC
The ODBC data source is not the database, but rather the shortcut containing information about how to connect to a data provider, including the path to the database and the required driver necessary for communication. You must create an ODBC data source at each ProFicient workstation.
Open Database Connectivity (ODBC) is a standard established by Microsoft that enables Windows applications to communicate with any database engine using a standard protocol.
ProFicient Database Overview
ProFicient stores everything, including part numbers, machine numbers, processes, gages, test characteristics, specification limits, control limits, subgroup values, comments, process events, employee information, and security levels, in a centralized ODBC compliant database.
In a multi-user network installation, the database typically resides on a shared server, allowing users across the enterprise to access and maintain the data. In a stand-alone installation, the database typically resides on the local workstation.
Database Structure
ProFicient is built around a Part-Process-Test structure, which means that every variable or attribute value written to the database must have values for the part item, the process item, and the test item, plus a date/time stamp. You can associate additional information with a data value in user-defined or fixed descriptor tables, such as Job, Lot, Component Lot, Shift, or Employee.
-
Part
A part is the item that you are examining, observing, or evaluating. Typically, parts are physical objects with measurable characteristics and different specifications. You can organize parts of similar shape, size, weight, color, material, or function in part families or groups, allowing you to consolidate all valve part numbers in a group called Valves.
For example, the part could be an Aluminum Engine Mount, a Blueberry Breakfast Cereal Bar, Wing Skin A, Frozen Juice Product B, a 32 oz Plastic Bottle, John's Green Ketchup, an Italian Port Wine, or a Silicon Wafer 99-A.
However, if you are collecting data on process parameters, the physical object that you are producing may not be relevant, and the part is the value that, when changed, causes the specification limits to change. For example, if you are monitoring pressure, the part could be the list of pressure set points. In this case, you can still store the name of the physical object as a user defined descriptor.
-
Process
A process is the item that creates, treats, or prepares the test being measured, and can be a machine, a line, a mold, or a cavity within a mold, and is often uniquely defined with a machine identification number. When a test falls outside of the expected range, you would investigate and adjust the process to improve the test result. You can organize processes of similar function in process families or groups, allowing you to consolidate all lathes in a group called Turning Center.
For example, for the Outside Diameter test, the process is the lathe that created the outside diameter; for the Temperature test, the process is the oven that created the temperature; and for the chicken breast weight test, the process is the line that cut the chicken breast fillet to the desired weight.
NOTE: The process is never the device used to capture the measurement. For example, even if a sophisticated coordinate measuring machine (CMM) captures the measurements, the CMM is not the process since it did not create the test.
-
Test
A test, also known as a characteristic or a feature, is data being measured or evaluated, and is typically specified on drawings, inspection sheets, or specifications. You can organize tests of similar function in test families or groups, allowing you to consolidate all outside diameter measurements (Outside Diameter A, Outside Diameter B, and Outside Diameter C) for a part in a group called Outside Diameters.
The three types of tests are as follows:
- Variable. Measured test values such as diameter, temperature, weight, height, etc. Variable tests are monitored using the following: Xbar-Range Chart, Individual X-Moving Range (IX-MR) Chart, Xbar-Sigma Chart, Pre-Control Chart, Moving Average - Moving Range Chart, and Median-Range Chart.
- Defects. Total number of defect occurrences during an inspection. Multiple defects may occur on a single piece. Defect tests are monitored using the following: c Chart, u Chart, DPMO Chart, and DPTO Chart.
- Defectives. Number of pieces that failed a particular inspection. A single piece is either defective or not. Defective tests are monitored using the following: p Chart, np Chart, PPM Chart, and PPT Chart.
For example, the Outside Diameter test measures the outside diameter of a part; the Viscosity test measures the thickness of oil; and the End of Line Audit test measures counts visual defects at the end of a production line.
For example, if you wanted to monitor the number of chocolate chips in individual cookies, you would create the Part, or product name of the cookie (for example, Classic cookie or Super Chipper cookie); the Process, or device that inserts the chocolate chips into the cookie (for example, Chip Injector #1, Chip Injector #2, etc.); and the Test, or number of chocolate chips in a cookie (for example, 10-12 for Classic or 20-25 for Super Chipper).
Consider the following scenarios:
-
The Classic cookie (Part) must contain 10-12 chocolate chips (Test). If Chip Injector #1 (Process) regularly inserts 15 - 20 chocolate chips, you must adjust the process to reduce the number of chocolate chips.
-
The Super Chipper cookie (Part) must contain 20 - 25 chocolate chips (Test). If Chip Injector #2 (Process) regularly inserts 8 - 12 chocolate chips, you must adjust the process to increase the number of chocolate chips.
Database Security
InfinityQS strongly recommends using the ProFicient internal security to ensure the integrity of your data, allowing you to base your decisions on accurate and reliable information. While your database provides security features, only ProFicient's security offers audit tracking logged with each user's identity. If you enable both database security and ProFicient security, you might create conflicting permissions that interfere with the software operation.
Not only can you secure the data, you can use corporate hierarchy to limit visibility of part and process data, granting designated users access to the information they need. This access control applies to menus and ribbon buttons, as well as layout and format, including colors, fonts, bar widths, etc.
Database Permissions
This database is not created in a ProFicient application or utility. Instead, you must create the database using your database configuration software, and then ProFicient will run an automated script to create all the necessary tables, indexes, and referential integrity constraints through Database Manager.
Before creating the tables, you must be logged in as the database owner (DBO), which is NOT the same as a user with administrative rights or full permissions. The DBO is the original creator of the database.
If you are not logged in as the DBO, other users will not have permissions to new tables, and will not be able to see or use those tables.
NOTE: To ensure proper database permissions, please contact your database administrator for assistance.
Database Tables
In the ProFicient database, there are three critical tables that sit at the highest level in the database structure:
- Part Table. Part items are stored in the PART_DAT table, and grouped into logical classifications in the PART_GRP table.
- Process Table. Process items are stored in the PRCS_DAT table, and grouped into logical classifications in the PRCS_GRP table.
-
Test Table. Test items are stored in the TEST_DAT table, and grouped into logical classifications in the TEST_GRP table.
Most of the other ProFicient tables are related to the Part, Process, and Test tables, and include the following:
- Descriptor Tables. These are the User-Defined Descriptors, Employees, Job, Lot, Component Lot and Shift tables. Subgroup data may have additional associated tag fields, in addition to Part, Process, and Test data, which can be used to filter the data during analysis.
- Limit Tables. These are Control Limits, Specification Limits, Defect Opportunities, and Alarm Notification Rules tables.
- Event Tables. These are ACC, CAC, Defects, E-mail, and Event tables.
- Roles and Security Tables. These are Privileges and Passwords tables.
- Log Tables. These are Database Access Logs and Sign In Failure Logs tables.
- Audit Tables. These are Change History and Reason For Change tables.