Configuring denormalized tables in Field Service Management
Summarize
Summary of Configuring denormalized tables in Field Service Management
Field Service Management (FSM) typically stores data in normalized databases, where information is split across multiple related tables for easier data management. However, this structure can slow down data retrieval. To optimize performance, FSM uses denormalized tables that consolidate relevant data from multiple tables into a single table, significantly improving load times and query efficiency.
Show less
How Denormalized Tables Work in FSM
When you launch applications like Dispatcher Workspace, FSM pre-queries all necessary data and stores it in denormalized tables. Subsequent data requests query these single tables instead of multiple tables, speeding up information delivery. If some data is missing in the denormalized table, FSM retrieves it from the original normalized tables to ensure completeness. Importantly, the way you add or update data in FSM remains unchanged; denormalization affects only data retrieval performance.
Managing Denormalized Data
Data processing for denormalized tables is handled in multiple parallel jobs to improve efficiency. For example, data for 1600 agents can be processed in four jobs of 400 agents each. The number of processing jobs and the span of historical data stored can be adjusted, but changes require contacting ServiceNow support. Key features leveraging denormalized tables include Appointment Booking, Dynamic Scheduling, and Dispatcher Workspace.
Important Considerations
- Never modify data directly in denormalized tables, as this can disrupt FSM functionality.
- Denormalization is enabled by default and controlled via three system properties: snfsm.wmweeklyresourcespan, snfsm.wmweeklyresourcespan.numberofweeksinpast, and snfsm.wmweeklyresourcespan.numberofweeksinfuture.
- Only system administrators should modify these properties, and disabling denormalization requires turning off snfsm.wmweeklyresourcespan.
Practical Impact for ServiceNow Customers
By utilizing denormalized tables, FSM customers can expect faster application load and query times, particularly in Dispatcher Workspace and scheduling features. This optimization helps dispatchers access critical information more efficiently without changing how data is managed. Adjustments to processing jobs or data retention periods should be coordinated with ServiceNow support to maintain optimal performance and data integrity.
Denormalized databases collapse information stored in multiple tables into a single table. This collapsing of relevant data improves Field Service Management load time.
Data in Field Service Management
The data for Field Service Management is typically stored in a normalized database. In normalized databases, tables can extend other tables, creating parent tables and child tables. The Field Service Management normalized databases use tables and records to manage data and processes, such as work orders, agents, and equipment. This normalized data structure that stores and organizes information is helpful for humans to add and manage the data in the tables, but it’s not the quickest or most efficient way for computers to process the data in the tables.
Denormalized tables
Denormalized databases collapse multiple tables into a single table, taking what was once in multiple table cells across multiple tables and puts them into one table. This collapsing of relevant data from multiple tables rows allows Field Service Management to query and return information faster since the data being fetched can be accessed from a single table instead of multiple tables.
Denormalized tables in Field Service Management
The way that denormalized tables work for Field Service Management, is when you load an application like Dispatcher Workspace, all of the tables used to retrieve data from are queried up front and the information is stored in a denormalized table. Then when you’re working in Dispatcher Workspace, when information is needed to execute a request, the denormalized table is queried, instead of the multiple tables that were necessary to query before. If the necessary data is not present in the denormalized table, then the data is retrieved from the original table source.
Denormalized tables just make it easier for information to be delivered to dispatchers. The way you add data and manage tables in Field Service Management remains the same. There is no change to how you update or add information to tables.
When data is added to denormalized tables, the process is divided into 4 jobs. For example, if you have 1600 agents the processing of those agent's data will be divided into four jobs each with 400 agents. You can change the number of jobs that process data, but you must contact ServiceNow to do this.
You can change the number of weeks' worth of data that's stored in denormalized tables. For more information, see Update the data stored in denormalized tables. For information related to the data model used to create denormalized tables, see Data model for denormalized tables in Field Service Management.
- Appointment booking
- Dynamic scheduling
- Dispatcher Workspace
Never change any of the data directly in a denormalized table. This can cause Field Service Management to break and should never be performed, even by a professional developer.
Denormalized table properties
There are three system properties that control table denormalization for Field Service Management. For more information, see the properties listed below on Properties installed with Field Service Management.
Denormalized tables are enabled by default. You must disable the property sn_fsm.wm_weekly_resource_span to turn off denormalized tables.
- sn_fsm.wm_weekly_resource_span
- sn_fsm.wm_weekly_resource_span.number_of_ weeks_in_past
- sn_fsm.wm_weekly_resource_span.number_of_weeks_in future