Business Intelligence integration API

The Business Intelligence (BI) integration API can be implemented for integrating Service Management with a third party business intelligence system. The record types and fields integrated are defined in the BI Integration user interface. For more information, see Business Intelligence integration.

The API enables you to configure and manage the integration process using the following operations:

• Initial data sync. Begins the process by taking a snapshot of all occurrences of the specified record types and their fields and relationships and stores the results in CSV files which are identified by a generated syncId. This API should be run once the first time you perform an integration.
• Ongoing data sync. Produces the list of all updates or only the last updates that occurred since a previous sync (up to a maximum of 7 days from the starting time) and stores the results in CSV files identified by a generated syncId. This API should be run on a regular basis to keep the external integration in sync with Service Management updates.
• Get status. Returns the status information for each record type specified. Use the fileId returned to generate a File Repository Service (FRS) URL to download the exported CSV files.

• Get All Syncs. Returns the results of all recent syncs.

Every tenant is given a total integration capacity per calendar month, known as a quota. Currently, the value is 3 GB per tenant. When a BI sync job (initial data or ongoing data) is run and produces output, it is counted toward the BI quota. The quota usage is based on the bytes of output written to disk (in unzipped form). When your quota is exceeded, all BI jobs will fail with an error indicating the reason. To overcome this issue, you will need to wait for the next calendar month or contact Support if you have a business need to raise the quota limit for your tenant.

Initial data sync

Enter the following POST request URI:

There are no parameters for this URI.

The POST request JSON is {"BiSyncConfigurationName":"<configuration name>"}.

The BiSyncConfigurationName is the name of the synchronization configuration. If a new BI configuration has been added, use the custom name. Otherwise, use the default BI configuration profile name: EmptyConfiguration.

The following is an example of the structure of the response JSON:

{
    "SyncId": "f9e2c239-6e44-4e1e-aa18-be56f03f6605",
    "Status": "SUCCESS"
}

The syncId is the generated unique ID for this sync operation.

Ongoing data sync

Enter the following POST request URI:

The POST request JSON is: {"BiSyncConfigurationName":"<configuration name>","LastUpdateTime":"<timestamp>","SyncEndTime":"<timestamp>"}.

An example of a POST request JSON is:

{
     "BiSyncConfigurationName: "EmptyConfiguration"
     "LastUpdateTime": "1442446258123",
     "SyncEndTime": "1442456781207"
}

where

• the BiSyncConfigurationName is the name of the synchronization configuration. If a new BI configuration has been added, use the custom name. Otherwise, use the default BI configuration profile name: EmptyConfiguration.
• the LastUpdateTime parameter is the timestamp returned from the Get Status API for a previous initial or ongoing sync operation.
• the SyncEndTime parameter indicates the end of the sync period. The SyncEndTime parameter is optional. If you do not enter it, the default value for the sync period is 7 days.

The maximum value for the sync period is 7 days. If you enter a value for the SyncEndTime parameter that is more than 7 days later than the LastUpdateTime parameter, the sync period is cut off at 7 days beyond the LastUpdateTime value.

The following is an example of the structure of the response JSON:

{
    "SyncId": "1c6387bd-7d89-44d7-be6f-d6adbf71a77c",
    "Status": "SUCCESS"
}

The SyncId is the generated unique ID for this sync operation.

Get status

Enter the following GET request URI:

where the <syncID> parameter is the generated unique ID returned by an initial or ongoing sync operation.

The following is an example of the structure of the response JSON:

{
    "SyncId": "0dd6d4c9-d98d-4731-8d49-6c286f61fe3e",
    "Type": "INITIAL",
    "LastUpdateTime": "1438884895176",
    "TotalProcessingTime": "11.638",
    "Status": "COMPLETE",
    "RemainingQuota": 1870957325,
    "TotalByteCount": 179782,
    "Files": [
        {
            "Status": "COMPLETE",
            "TotalByteCount": 39010,
            "Segments": [
                {
                    "Name": "Person",
                    "FileId": "7c006bac-448d-4fc1-b38d-aead728bb35a",
                    "Status": "COMPLETE",
                    "TotalByteCount": 39010,
                    "RecordCount": 500
                }
            ]
        },
        {
            "Status": "COMPLETE",
            "TotalByteCount": 21087,
            "Segments": [
                {
                    "Name": "Incident",
                    "FileId": "96100dd5-abd1-4044-9b36-0fb1575651e8",
                    "Status": "COMPLETE",
                    "TotalByteCount": 21087,
                    "RecordCount": 300
                }
            ]
        },
        {
            "Status": "COMPLETE",
            "TotalByteCount": 3953,
            "Segments": [
                {
                    "Name": "ActualService",
                    "FileId": "301c94af-f8f8-42d2-aca9-d2975ee5d01a",
                    "Status": "COMPLETE",
                    "TotalByteCount": 3953,
                    "RecordCount": 100
                }
            ]
        },
        {
            "Status": "COMPLETE",
            "TotalByteCount": 1877,
            "Segments": [
                {
                    "Name": "IncidentCausedByRequest",
                    "FileId": "986ce4cb-5fe4-4069-9ece-5162bde0a80c",
                    "Status": "COMPLETE",
                    "TotalByteCount": 1877,
                    "RecordCount": 50
                }
            ]
        },
        {
            "Status": "COMPLETE",
            "TotalByteCount": 45941,
            "Segments": [
                {
                    "Name": "ServiceLevelTarget",
                    "FileId": "cacf92a3-57c1-4d03-af51-bc8178380e71",
                    "Status": "COMPLETE",
                    "TotalByteCount": 45941,
                    "RecordCount": 500
                }
            ]
        },
        {
            "Status": "COMPLETE",
            "TotalByteCount": 64438,
            "Segments": [
                {
                    "Name": "Request",
                    "FileId": "9aace1a5-40bc-42f3-ae74-c35c2ccb94d7",
                    "Status": "COMPLETE",
                    "TotalByteCount": 64438,
                    "RecordCount": 300
                }
            ]
        },
        {
            "Status": "COMPLETE",
            "TotalByteCount": 3476,
            "Segments": [
                {
                    "Name": "PersonGroup",
                    "FileId": "d17e6528-7c6d-4419-b0fb-46cf1a849726",
                    "Status": "COMPLETE",
                    "TotalByteCount": 3476,
                    "RecordCount": 50
                }
            ]
        }
    ]
}
 

The parameters in the JSON are as follows:

• SyncId. Same as input parameter

• Status. One of the following:

  • PROCESSING. The job is in progress.
  • COMPLETE. The job is finished.
  • FAILED_TO_PROCESS. The job has failed.
• ErrorMessage. Message displayed if an error occurred during the job (only when status is FAILED_TO_PROCESS)
• LastUpdateTime. Timestamp to use in the next ongoing sync operation (only when status is COMPLETE)
• RecordCount. The number of records already processed
• TotalByteCount. Size of all generated files (in bytes)
• TotalProcessingTime. Job processing time (in seconds)
• RemainingQuota. Remaining space in the quota (in bytes)
• Files. The list of generated files in FRS with the fileId, name and size (only when status is COMPLETE)

To access the output files, construct a URL as follows: <host>:<port>/rest/<tenantId>/frs/file-list/<fileId> where <fileId> is the FileId for each of the generated files. Paste the URL into a browser to execute the FRS REST call and automatically download the file to your downloads directory.

Get all syncs

Enter the following GET request URI:

There are no parameters for this URI.

The following is an example of the structure of the response JSON:

[
   {
        "SyncId": "838e1923-ed5f-4ddc-b14c-545b5a7646b7",
        "Type": "INITIAL",
        "Status": "COMPLETE",
        "LastUpdateTime": "1442543211350",
        "TotalProcessingTime": "8.042",
        "RemainingQuota": 1870957,
        "TotalByteCount": 34464,
        "Files": [
            {
                "Status": "COMPLETE",
                "TotalByteCount": 16385,
                "Segments": [
                    {
                        "Name": "Incident_0",
                        "FileId": "cd98f886-5f8e-4881-8aac-c4ab70883c11",
                        "RecordCount": 68,
                        "Status": "COMPLETE",
                        "TotalByteCount": 16385
                    }
                ]
            }
        ]
    },
   {
        "SyncId": "ce7daa77-2b77-4229-8a9a-ad275481d188",
        "Type": "ON_GOING",
        "Status": "COMPLETE",
        "LastUpdateTime": "1442612404950",
        "TotalProcessingTime": "2.552",
        "RemainingQuota": 1731239,
        "TotalByteCount": 3210,
        "Files": [        
            {
                "Status": "COMPLETE",
                "TotalByteCount": 479,
                "Segments": [
                    {
                        "Name": "Incident_0",
                        "FileId": "5465cbd7-765e-48b9-b672-e4ac3b44c3c2",
                        "RecordCount": 10,
                        "Status": "COMPLETE",
                        "TotalByteCount": 479
                    }
                ]
            }
        ]
    }
]

The parameters are the same as those in the Get status API.

To access the output files, construct a URL as follows: <host>:<port>/rest/<tenantId>/bi/file-list/<fileId> where <fileId> is the FileId for each file from the relevant sync.

Output format

The sync output is stored as multiple zipped CSV files in the FRS in one of the following formats:

• One file per record type. The maximum size of each file is 2 MB unzipped. If the file exceeds this size, it is split into multiple chunks.

  The first row in each file is a header as follows:

  BiSyncOperation, <field1>, <field2>…<field-n>

  where <field-1>…<field-n> are the record type field names specified in the sync configuration. Note that one to one and one to many relationships are treated as regular fields.

  Subsequent rows specify values for some of the columns depending on the operation. The operation can be one of the following:

  • ADD_ENTITY. Creation of a new record, with initial values for every specified field
  • REMOVE_ENTITY. Deletion of a record, no field value is provided
  • UPDATE_ENTITY. Only the field values that have changed are specified

  The output format for both the initial and ongoing sync is the same. The only difference is that initial sync files contain only ADD_ENTITY records, while ongoing sync files can contain ADD_ENTITY, UPDATE_ENTITY, and REMOVE_ENTITY records.

  The first column of the file is called RowId. The unique RowId is used to reference the specific row of the main file from the complex type file (comments or user options).

  For each selected complex field for a record type, the following additional columns are also included in the output file:

  • <complex-field-name>_File. The name of the CSV file containing the complex field value for the operation in that row.
  • <complex-field-name>_Rows. The row IDs of the relevant rows in the complex field file.

  For a sample output file, see Output example.

  Note The ongoing data sync output displays a blank space for an unchanged field and displays <NULL> for a field whose value has been deleted.

• One file per many to many relationship type. The maximum size of each file is 2 MB unzipped. If the file exceeds this size, it is split into multiple chunks.

  The first row in each file is a header as follows:

  BiSyncOperation, LastUpdateTime, <first-record-type>, <second-record-type>

  Subsequent rows specify values for some of the columns depending on the operation. The operation can be one of the following:

  • ADD_RELATION. Addition of a many to many relationship between two records.
  • REMOVE_RELATION. Deletion of a many to many relationship between two records.

  The output format for both the initial and ongoing sync is the same. The only difference is that initial sync files contain only ADD_RELATION records, while ongoing sync files can contain ADD_RELATION and REMOVE_RELATION records.

• Complex type fields. If you selected a complex type field in the sync configuration, the data is exported in a separate file. For static complex type fields, such as comments, there is one file for each record type. For dynamic complex type fields, such as user options, there are separate files for each user option for each record type.

  The following columns are included in the output file:

  • RowId. The row number (a unique value). This is the row ID referenced from <complex-field-name>_Rows in the main record type file.
  • ParentFile. Reference to the original record type file name.
  • ParentRowId. Reference to the row ID of the original record type transaction in the main record type file.

  • Index. The index of the complex field row within each complex field entry. The first row of each entry has an index of 1.

    Only static complex fields can have multiple rows per field entry. Dynamic complex fields have only one row per entry with an index of 1.

  For sample output files, see Output example.

Output example

The following example demonstrates output data including a record type file and complex type field files.

For the request record type, the Request output file is the main record type file:

The Request_Comments file is the complex type field file for comments:

Note that the ParentRowId for the first two rows is the same, which indicates that both of these comments relate to the first row of the main record type file. The first row has an index of 1, meaning it is the first comment relating to the row of the main file. The second row has an index of 2, meaning it is the second comment relating to that row. This information can also be found in the main file, where the Comments_Rows column in the first row contains the row IDs of these two comments rows.

A similar pattern occurs for the third and fourth rows; they relate to the second row of the main file. The Comments_Rows column in that row contains the row IDs of the third and fourth comments rows.

There are two complex type field files for user options, one for each user option exported:

Request_PrinterUserOptionType_c

Request_SelectPCUserOptionsType_c

In the first row of the first user options file, the ParentRowId column contains the row ID of the first row of the main file. In the first row of the main file, Request_PrinterUserOptionType_c is specified as the UserOptions_File and the UserOptions _Rows column contains the row ID of the first row in this user options file.

Related topics

• Overview of the REST API

• How to connect to the REST API

• Authentication endpoint service

• Record bulk update and collection APIs

• REST API queries

• REST API collection query protocol

• Analytics REST API

• REST API use case scenario - import REST API