Understanding the application schema

This table shows the database tables that contain high volumes of data:

Table Query subject in the metadata model Details
WORK_SUMMARY [Work Day] Always have date filters on the WORK_SUMMARY work date column (found in the model as [Work].[Work Day].[Work Date]) as that column is indexed.

Never use [Work].Work].[Work Date] to filter by date as this maps to the WORK_DETAIL table, in which the column is not indexed and, therefore, results in a full table scan.
WORK_DETAIL [Work] Never filter anything on the WORK_DETAIL table as the only column indexed on that table is WRKS_ID, which is used for fast joins with the WORK_SUMMARY table.
SO_RESULTS_DETAIL [Actual Results] For performance purposes, use these metadata model items to ensure that all elements of the index are in your query:
  • [Location Name], which is column SKDGRP_ID.
  • [Date], which is column RESDET_DATE.
  • [Volume], which is column RESDET_VOLUME.
  • [Volume Type], which is column VOLTYP_ID. Volume Type is always NULL for LFSO data, but valid for Labor Budgeting data. Always be aware of this so that your report does not include extraneous data.
SO_INTERVAL_REQ [Staffing Requirements (SO)] To take advantage of the index on SKDGRP_ID and INTREQ_DATE, always filter on the [Staffing Date] query item and include the [Location Name] query items.
EMPLOYEE_SCHED_DTL [Scheduled Shift] To take advantage of the index on WBT_ID and ESCHD_WORK-DATE, always filter on the [Date] query item and include either the [Employee] query items or [Team] query items.

In addition, consider these tips:

  • All work date filtering should be done using [Work].[Work Day].[Work Date]. Although [Work].[Work].[Work Date] is functionally equivalent to [Work].[Work Day].[Work Date], the WORK_SUMMARY.WRKS_WORK_DATE column in the database that populates [Work].[Work Day].[Work Date] is indexed.
  • All filtering should be done using the appropriate primary key fields provided in the Keys folder of most query subjects.
  • Indexes improve performance and should be used.
  • Ensure that the Duplicates property for query relationships is set to Preserve. A setting of Remove equates to the SQL UNION command. The SQL UNION command performs a sort of the database to remove duplicates, thus affecting performance.