Configuring denormalized tables in Field Service Management
Summarize
Summary of Configuring denormalized tables in Field Service Management
Field Service Management (FSM) typically uses normalized databases, where data is spread across multiple related tables. While this structure is good for data management and input, it is less efficient for data retrieval, which can slow down application performance. Denormalized tables improve FSM performance by collapsing related data from multiple tables into a single table, enabling faster query response times especially in high-demand areas such as Dispatcher Workspace.
Show less
How Denormalized Tables Work in FSM
When an FSM application like Dispatcher Workspace loads, it queries all relevant tables upfront and stores the combined data in a denormalized table. Subsequent data requests are served from this single denormalized table rather than querying multiple tables repeatedly. If data is missing from the denormalized table, it falls back to the original normalized tables. Importantly, the process for adding or updating data remains unchanged for users and administrators; data entry and management continue to operate via the normalized structure.
Data Processing and Configuration
The creation and updating of denormalized tables are handled through a batch job process, which divides data processing into multiple jobs. For example, agent data can be processed in four separate jobs to optimize performance. Customers can request changes to the number of processing jobs or the time span of data stored in denormalized tables by contacting ServiceNow support.
Features Using Denormalized Tables
- Appointment booking
- Dynamic scheduling
- Dispatcher Workspace
Important Considerations
- Never modify data directly in denormalized tables as this can cause system failures.
- Denormalized tables are enabled by default in FSM.
- System administrators control denormalization behavior through three key system properties:
- snfsm.wmweeklyresourcespan – Enables or disables denormalized tables.
- snfsm.wmweeklyresourcespan.numberofweeksinpast – Configures how many weeks of past data is stored.
- snfsm.wmweeklyresourcespan.numberofweeksinfuture – Configures how many weeks of future data is stored.
- Only system administrators should modify these properties to ensure system stability.
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