3366253 - WT&A, SQL, and the Database

 This KB article is intended for users that have completed the WFS Database Schema Training. The topics are more advanced and receiving the certification from this training is required to receive database access. Please reach out to your Account Relationship Manager or appropriate representative if you need more details about taking this training.

For WT&A, the database can be a very useful resource to work with when used properly. All WT&A data is stored in the database, and so a user familiar with querying the database can often find information faster than someone just working through the web application. The reports in the web application run directly against the WT&A database, as does the Analytics tool.

WFS makes use of both Oracle and SQL Server databases. While Oracle is the standard for new systems, different ones can be in use depending on the specific needs of the system and time of creation. The version in use will also have some variation. Standard documentation about these databases, their differences, and more can be found by searching online.

There are two main ways to review information from the database: through Tenant Manager (Internal and Partners only) or through a direct database connection. Both of these require an active VPN connection to the proper datacenter. The utility in Tenant Manager provides Read Only access through the browser. It has limits on the number of columns and rows it can display, and can only run one query at a time. Updates, Inserts, and Deletes are not possible. Direct database access requires a query tool, with the most common one used by WFS being DBVisualizer. As such, KB articles will show or mention DBVisualizer by default, though other tools can work similarly. When set up, DBVisualizer allows for more options when running queries, and more control over what is displayed and how information is seen. Depending on the access granted, Updates, Inserts and Deletes may be possible for non-PROD regions. Only WFS Cloud Services and DBAs will have any sort of write access to PROD databases.

IMPORTANT: Generally speaking, direct SQL inserts should not be run against the database while a system is up and running. This is because of the need to generate unique IDs for fields like SYSTEM_RECORD_ID. The counters move too frequently while the system is up do to other actions being taken to be able to safely create an appropriate value, and so the system must be taken down for newly inserted data and to move counters appropriately. Note that restoring records from audit is different, since those inserts are simply recreating counters that were already generated, so that can be done while the system is still running.

In general, the best practice is to include notes on key tables and sample queries in relevant articles. General articles with useful queries can be created as well, but it is suggested to add this to appropriate areas when possible rather than just building a large list of queries.

SAP Time and Attendance Management by WorkForce Software (All versions)

ORA, Database, SQL, Query , KBA , XX-PART-WFR-TAM , SAP Time and Attendance Management by Workforce Software , Problem