SAP Field Service Management offers regular users the option to query the FSM company database (provided the users have been granted access). This feature is available under Analytics and Reporting → Queries. With queries, you can do the following:
- Get a list of data according to a query
- Save queries for later use
- Share queries with other users within the same company
When setting a query as a favorite (Add to menu), a heart appears in the query list.
Queries are written in CoreSQL. This is the same query language as is being used for FSM Query API. It is derived from PostgreSQL and limited to READ operations. It allows for complex SELECT statements across multiple tables in the FSM cloud database. The resulting output can be exported in CSV or JSON formats.
Data access is governed by the settings in the Admin Policy Group.
The data on the FSM Cloud database is structured into tables, one for each object type. The object types or tables are referred to as Data Transfer Objects, or DTOs. Some examples of DTOs include Activity, Service Call, and WorkTimeTask. Each DTO consists of a number of different data fields, and each DTO can have can have many individual data records. In total, there are hundreds of these DTOs.
DTOs are not only designated by their name, but also by their version number. Version numbers incrementally increase over time, as the definition of each table changes. This can happen, for example, when a new field is added to a table.
The image above is a simplified diagram of several important data transfer objects. The diagram is not complete, but it shows several commonly used entities and their relations.
Note
This diagram might also be helpful for the units covering business rules and master data to recall information regarding the main data transfer objects.it is important to understand how different DTOs relate to each other: which DTOs can be linked in the first place, and if they can have a link. If tables are linked, it's important to understand the nature of these relationships - for example:
- Every single service call (SC) is assigned to exactly one service call type
- A service call type can be referred to by many Service Calls
- Any single activity is assigned to exactly one Service Call, but can be linked to only 1 or 0 Service Assignments at a time
While it is not possible to give a complete overview of all DTOs and their relationships here, using FSM queries can help you explore and understand the data model.
The core of the logic and data in Field Service Management is referred to as the "monolith". The monolith includes the FSM Cloud database of your FSM company. Not all data however is part of the FSM company database: Field Service Management product development is implementing more and more microservices because this makes software development more agile. These microservices operate outside of the monolith and usually have their own databases outside of the main company database, as well as having specific dedicated APIs. Therefore, data from microservices (for example OrgLevel details) must be retrieved by the corresponding dedicated API, instead of using Query API or Data API.
Next to the Queries function in the Analytics and Reporting module, a similar functionality is available to administrators within the admin console for an FSM company. There, it is available under the Query API tab. Clients outside of FSM can also read date by using FSM Query API.
CoreSQL queries are also important components of report templates and business rules. Understanding Query API is an invaluable skill for debugging and when setting up integrations.
CoreQL supports many but not all SQL statements for reading data. For a complete guide to the supported statements, please refer to the Help documentation.
When using Queries on the end user UI or in the Admin module, the DTO version defaults to the latest version. In Business Rules however, it is mandatory to specify the desired DTO version.
When writing a query, it is mandatory to use an alias when referring to a DTO. In the following query for example, we refer to the Activity DTO with the Alias "act": SELECT act FROM Activity act
Here are some example queries:
- Getting all the fields from all service assignments:
SELECT sa FROM ServiceAssignment sa
- Getting the code and subject fields from the latest 10 activities:
SELECT act.code, act.subject FROM Activity act ORDER BY act.createDateTime DESC LIMIT 10
- selecting Business Partner- and Service Call data for Service Calls with high priority, by joining the 2 tables:
SELECT bp.code, bp.name, sc.code, sc.subject FROM ServiceCall sc LEFT JOIN BusinessPartner bp ON bp.id = sc.businessPartner WHERE sc.priority = 'HIGH'
Query API in the Admin module will use the latest version of the DTO by default. If you would like to use a specific DTO version, you can select the settings option which will then enable you to declare a specific version of a supported data object. You can also select whether to show timestamps as date times or as a Unix timestamp. To simulate what a specific user would see, you can specify some under Execute As.
To increase user-friendliness, the system suggest relevant commands, tables, and field names as you type. You can bookmark your queries to save them for later use. The most recent queries are visible under the History button
