SQL or SQL? What the Heck Is It, and Why Does It Matter?
“Ess-Q-El” or “Seek-quell” - the debate rages, maybe not quite as long as “Toh-MAY-Toh” vs “Tah-MAH-Toh” or “Que-Pon” vs “COO-Pon”. So why does it matter, and, more importantly, what is it and what does it matter to me?
Structured Query Language, abbreviated as SQL, is a programming language used to query and manipulate data stored in relational databases. Relational databases consist of many tables and fields of information – we will get more into that in a bit. UKG Pro©/UltiPro© uses relational databases for its core HR/payroll application, as well as many other modules and applications.
OK, OK so I understand the basics of SQL, but why is it important to my work, and how in the world does it relate to HR and Payroll data?
Believe it or not, HR and Payroll data is one of the most complex data sets in existence. When we step back and think of all the necessary information needed to hire, pay, and manage an employee’s information, in addition to Federal, State, and even local compliance, in addition to company level configuration, it becomes a bit more clear why the databases need to be so massive to accommodate all this information.
A standard UKG Pro©/UltiPro© core database contains about 3,000 tables and over 60,000 fields!
A “table” is a subset of a database that contains “fields”, which contain specific data types of information. Tables are “related” to each other using Primary and Foreign Keys.
Consider two of the key employee-related tables in UKG Pro©/UltiPro© - EmpComp (Employee Company) and EmpPers (Employee Personal). Each employee is one, and only one person so has only one “record”, that is set of information in the EmpPers table. However, that employee may be hired into more than one company, so may have multiple records in the EmpComp table. These tables are “joined”, or linked, together by the EEID – that is the primary key, or link between the two.
So, why not keep this data all in one table for simplicity? The answer is that the amount of data would increase exponentially. Consider that each employee may have five tax codes, ten earning codes, and twenty deduction codes. If all the employee level data was contained in one table, the amount of stored data would literally become unmanageable.
The importance of understanding SQL as well as the UKG Pro©/UltiPro© database comes into play in the following areas:
Understanding database relationships and data management
Custom BI Report Development – IBM Cognos© is essentially a visualization of SQL. SQL queries can be written within IBM Cognos© as well.
Developing and troubleshooting expressions – Expressions in UKG Pro©/UltiPro© are written using a form of SQL to accomplish custom calculations that are not system delivered
Interface (import/export) logic
Whether you are planning to, or are currently implementing UKG Pro©/UltiPro©, or have been a customer of UKG for years, JT Tech Consulting can help in the following ways. Of course, please reach out if you need any assistance with items not in this list!
Development of custom reporting/expressions
Training on database schema/key data tables in the application
Troubleshooting or review of customizations and expressions
Interface development and maintenance
Data “check-up” – review of current data to identify data issues that may impact transactions in the application
