Ctrlk
About CoherentAPI referenceSupportYouTube
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Execute API (v3)

Execute the calculation from a Spark service.

Returns: Service execution response.

Each Spark service can be called to perform calculations by calling the Execute API. The v3 specification of the Execute API is the version that is currently being used in the API Tester.

## Depending on the desired reference parameter, different URI are available.
POST /{tenant}/api/v3/folders/{folder}/services/{service}/execute
POST /{tenant}/api/v3/service/{serviceId}
POST /{tenant}/api/v3/version/{versioId}
POST /{tenant}/api/v3/execute
POST /{tenant}/api/v3
POST /{tenant}/api/v3/public/folders/{folder}/services/{service}/execute
POST /{tenant}/api/v3/public/service/{serviceId}
POST /{tenant}/api/v3/public/version/{versionId}
POST /{tenant}/api/v3/proxy/{urlsuffix}

Authorization

  • Bearer {token} accessible from Authorization - Bearer token or systematically via Client Credentials.

    • The request headers should include a key for Authorization with the value Bearer {token}.

  • API key created from Authorization - API keys.

    • The request headers should include the keys x-synthetic-key and x-tenant-name with the values of the API key and tenant name respectively.

  • No Authorization is required for Authorization - Public APIs.

  • /{tenant}/api/v3 does not work with x-synthetic-key .

Path parameters

Key
Value

tenant *

Tenant is part of your Log in to Spark URL and also available in the User menu.

folder *

Folder associated with the request.

service *

Service associated with the request.

serviceId *

service_id of the requested service. The latest service version will be referenced.

versionId *

version_id of the requested service version.

urlsuffix *

Custom endpoints path.

Request body

Content-Type: application/json

The easiest way to understand the request_data for your Spark service is experimenting in the API Tester.

request_data

inputs is an object that contains key value pairs that will be passed to the fields identified as Xinput in the workbook.

Representation of Spark inputs

Key
Value

null

A null value is represented by null.

Single input

Example 1: Xinput_Single_number:

"Single_number": 1234.567

Example 2: Xinput_Single_string:

"Single_string": "Hello World!"

Range input

Example: Xinput_Ranged_input 2 column table of E and F

E F

A 6

B 8

Would be referenced as:

"Ranged_input":

[

{"E": "A", "F": 6},

{"E": "B", "F": 8}

] or alternatively using array notation: "Ranged_input":

[["E", "F"], ["A", 6], ["B", 8]]

inputs data types

Type
Formatting description

null

A null value is represented by null.

boolean

Boolean values are either true or false.

number

Use digits 0123456789 and with an optional . decimal separator as part of the API request only. Would not recommend using any strings enclosed symbols or separators such as $ % , as these may potentially affect the calculation and interpretation of any Data Validation defined in the Excel file, however numeric strings are cast to numbers by Spark.

Example 1: Instead of "$1,234.567" use 1234.567

Example 2: Instead of "8.5%" use 0.085

string

Strings are enclosed with double quotation marks. An empty string is denoted as "".

Example: "Hello world"

date

Dates are entered in YYYY-MM-DD format enclosed with double quotation marks.

Example: "2021-12-31"

time

Times are entered in hh:mm:ss format enclosed with double quotation marks.

Example: "23:59:59"

date and time

Dates and times are entered in YYYY-MM-DD hh:mm:ss format enclosed with double quotation marks. This format is consistent with Excel.

Example: "2021-12-31 23:59:59"

request_meta

Parameters to resolve Spark service versions

Spark will use these parameters in order to resolve down to the most appropriate version_id to use for an API call. These parameters are not necessary if the URI contains sufficient information. Learn more about the version resolution in the section Manage service versions and effective dates.

version_id will be resolved by Spark the quickest.

Execute API will always resolve the version_id parameter first, service_id parameter second, and then service_uri last. It is possible that these 3 may refer to different services entirely but Spark will resolve the reference in the same order.

Key
Value

service_uri

Reference to service location in the format of folders/{folder}/services/{service}.

This does not specify the version of the service returned. This can be done so by including transaction_date or version.

Example: folders/API V3/services/Sample API

service_id

service_id of the requested service. This does not specify the version of the service returned. This can be done so by using transaction_date or version.

Example: 0c9deaad-bd33-49dd-9404-14f9303c531e

version

Semantic version number which corresponds to the version number shown in the Spark user interface: {Major revision}.{Minor revision}.{Patch revision}.

Used on conjunction with service_uri or service_id.

Example 1: 4.15.2

Example 2: 1.4 take the latest version starting with 1.4.

Example 3: 1 take the latest version starting with 1.

version_id

version_id of the requested service version.

This takes precedence over service_uri, service_id, transaction_date, version.

Example: e5a86ccb-b53b-47c7-86e7-25c228b211f4

transaction_date

Timestamp to resolve the service version for Manage service versions and effective dates.

Use Timestamp Converter to convert dates to ISO 8601.

  • When call to a service is made using the service_uri or service_id this is used to determine the latest version of a service which have effective start and effective end dates that bound transaction_date.

    • If omitted, the current date and time is used.

    • If the transaction_date does not fall within the effective start and effective end dates of any versions, then the response will be an error.

  • When calling a specific version using version or version_id this field has no effect.

Example: 2023-10-31T01:30:00.000-05:00

Parameters for Neuron evaluation

Key
Value

debug_solve

Boolean to return outputs.additional_outputs.solve_log which contains the iterations for each Solve functions (Xsolve) in the service. Example: true

debug_xcall

Boolean to return outputs.additional_outputs.xcall_log which contains the tracing for the Call Spark service APIs (Xcall) used in the execution of the service. Example: true

manual_now

Default behavior for Excel NOW() and TODAY() functions are to return the value based upon server time. If a Unix epoch time is provided, then that will be used to derive the current time and date instead.

Example: 1650593119 would represent 2024-10-29T02:44:48

profile

Boolean to enable performance tracing.

See Profile Neuron calculations for more information. Example: true

random_seed

Number for random number generation seed. This ensures that each API call generates the same sequence of random numbers. Example: 123

requested_output_regex

Regular expression string to denote the outputs to keep in the response. This is an advancement of requested_output. If both requested_output and requested_output_regex are specified, then an error will be raised.

Example 1: solve will return all outputs that contain solve.

Example 2: ^price_ will return all outputs that begin with price_

Example 3: a|c matches outputs that are a or c.

show_max_calc_chain

When the debug build is used, this is used to identify the longest calculation chain.

See Profile Neuron calculations for more information. Example: true

top_n_profile

When the debug build is used, this is used to adjust the of number records returned from the performance tracing output.

See Profile Neuron calculations for more information. Example: 25

Parameters to control the response outputs

Key
Value

requested_output

Array of strings to denote the outputs to keep in the response. * can be used to identify wildcard characters. Example 1: ["price*","*nut","Boeing7*7", "calc"]

Alternatively direct references to Excel ranges are possible.

For Neuron 1.7.0 to 1.16.0 Service details Direct addressing outputs enabled when TRUE allows any cell of the Excel workbook to be returned as part of the Execute API (v3) response by specifying the request_meta requested_outputs.

For Neuron_v1.17.0 and later this can be enabled In Excel by adding a Named Range referenced to a cell called xBuildParams. The cell contents must contain '--direct_addressing=true. Example 2: ["@Sheet1!B2:D4"]

Example 3: ["@MyNamedRange"]

Furthermore if #SparkFormulaValueVector is added to these requests, only the calculated results would be returned.

For compatibility, the requested elements can also be provided in a comma separated list, Example 4: "first,second,third".

Type3 behavior:

Single string that applies a “starts with” filter on the output objects.

Example 5: price_ will only return all the outputs that begin with price_.

service_category

Comma separated string of subservice names. Reference How to: Work with subservices.

When this is not specified or set as #Default#, outputs not related to subservices are shown.

Example 1: subservice1 will only show the the outputs from subservice1.

Example 2: subservice1,subservice2 will only show the outputs from subservice1 and subservice2.

Example 3: #Default#,subservice2 will show outputs not relating to subservices and from subservice2.

compiler_type

Calculation engine for the request. By default this uses the setting defined in Change the Default Service Type. Example: Neuron

array_outputs

This is a string list of Xoutput ranges to return in the JSON array format. Using * can be used to denote all of the outputs. See Return tables as JSON arrays.

Example 1: "*"

Example 2: "list1, list2"

response_data_inputs

Boolean to enable the echo of request_data.inputs into response_data.inputs. This is useful for consuming systems that want to store and analyze the API request and response data together.

excel_file

When set to true, this returns a copy of the source Excel file with the request_data inputs populated into the Xinput cells. The output is the same as the API Call History Download as Excel (Legacy).

For security, the use of this feature with API keys can managed through Assign permissions through Features permissions and access to the Spark.DownloadServiceByCallId.json feature permission. This is not a feature that is recommended for extensive use. Response times when using this feature will be inconsistent call to call due to the need to generate and populate the Excel file.

xreport_options

Parameters to control how Produce PDF reports (Xreport) are produced. See the above documentation link for more information.

xreport_options.produce_pdfs

Boolean to indicate whether to return the Xreport sheets as PDFs. Default is true.

xreport_options.multiple_docs

Boolean to indicate whether to return each Xreport sheet as a separate PDF.

  • If no xreport_options are used in the request, the default is true .

  • If xreport_options are used in the request, the default is false .

xreport_options.page_numbers

Boolean to indicate whether to include page numbers. Default is true.

xreport_options.render_chart_texture_bars_as_images

Boolean to resolve an issue with borders appearing for dynamic chart images. Default is false.

Parameters to tag a request

Key
Value

call_purpose

Tag API Call. Can be queried in API Call History.

Example: admin system p

source_system

Tag API Call. Can be queried in API Call History.

Example: mycicd

correlation_id

Tag API Call. Can be queried in API Call History.

Example: 456

Submit tabular inputs as a JSON array

Extending upon Map a single/multiple cell range to an input/output and CSV Inputs / Outputs, JSON arrays can also be used to pass in values for multiple cell table inputs. The array structure is more compact and can be easier to read for larger data sets.

Example:

If an Xinput table is defined as Xinput_EMP with the following structure:

Name
Age
Location

Dinesh

32

Gota

Sagar

25

Nikol

Then, the Execute API (v3) request_data can be formed using two methods:

Default "records" JSON request format
JSON array format

This is the default table representation shown in the API Tester.

Composing the tabular data using JSON arrays is more compact and easier to read.

Submit tabular inputs as a JSON array with multiple hierarchies

Merged cells in header rows are used to define additional hierarchy above the standard column headers shown in the previous example.

Extending the previous example of Xinput_EMP, the previous headings can be grouped into a subheading. Personal and Professional are merged cells that are included in the definition of Xinput_EMP.

  • The merged cell is used to define a heading and subheading hierarchy. The Personal heading includes Age and Location subheadings. The Professional heading includes Job and Industry subheadings.

  • Headings without subheadings should be defined in the row immediately above the first data row. In this example, the Name heading will be on the same hierarchy as Personal and Professional.

  • The API Tester also supports the display of multiple row headers in the user interface.

The request_data can be formed using two methods:

Default "records" JSON request format
JSON array format

This is the default table representation. Heading objects include the nested subheadings.

The tabular data represented using JSON arrays is more compact and easier to read. Subcolumn headings are represented using Heading.Subcolumn notation.

Sample request

Response headers

Response headers of note

Key
Value

spark_total_time

The time it took for Spark to process the inputs, perform the calculation, execute any Ximage, Xreport, Xsolve parameters in ms. This does not include any transit time.

Example: 50

X-RateLimit-Limit

Spark rate limiter value. Example: 600

X-RateLimit-Remaining

Remaining calls for the rate limiter. Example: 590

X-RateLimit-Reset

Unix epoch time representing when the rate limit is reset. Use Timestamp converter to convert to ISO 8601. Example: 1768925455

Response body

Content-Type: application/json

status

success is returned when the API is able to provide a calculated response

error is returned if there is an issue providing an API response.

response_data

inputs

Inputs from the request will be returned in the response if the request_meta response_data_inputs is true.

outputs

Outputs generally follow the same conventions as inputs described in request_data. It is recommended to test and evaluate the response outputs before finalizing any integration.

  • Ranges are dynamically sized on the output. For any ranged values, if a row is completely empty (null) at the end of the dataset, it will not be returned. For example, if an Xoutput has been defined as 6 rows but only the first 4 rows have data, then only 4 rows will be returned as part of the API response.

Type
Formatting description

null

For any Xjoutput this will return as "".

date and time

Dates and times are returned in YYYY-MM-DDThh:mm:ss format enclosed with double quotation marks. This format is consistent with ISO 8601.

Example: "2021-12-31T23:59:59"

warnings

Warnings are issues that may potentially affect the correct calculation of the response.

A common reason for warnings is that an input value was not provided as part of the request_data and therefore a default value was used.

errors

Common error messages:

  • "message": "INVALID_TOKEN" accompanied by HTTP status code 401 Unauthorized. This likely refers to a security token that is expired.

  • "message": "unknown error. reason: INVALID_REQUEST_PAYLOAD." occurs if the API response body is not structured in the correct manner (e.g. using the older V1 request format).

  • "message": "unknown error. reason: NO_ACTIVE_VERSION." occurs when calling a service version beyond its effective start and effective end dates.

  • "message": "unknown error. reason: Object reference not set to an instance of an object.." may arise when using an incorrect combination of service_uri service_id version version_id.

Errors may also relate to issues from data validation where the request_data includes values that fail the data validation as defined in Excel or raised using the Xvalidate formula. These are set as errors as they indicate that the calculated response data may not be correct.

response_meta

These are response metadata used to provide information on how the request was processed.

Key
Value

system

System that processed the request.

Example: SPARK

request_timestamp

Request timestamp.

Example: 2020-10-29T09:53:18.8314052Z

service_id

service_id of the resolved service.

Example: 48a953cc-ed34-4454-b5c7-1141912f2f8d

version_id

version_id of the resolved service version. Example: 6bf49dca-5bc2-4144-9710-5988a0f44163

version

Resolved service version number.

Example: 4.15.2

process_time

Time for Spark to process the API call in ms.

Example: 30

call_id

Universally unique identifier for this API Call. This can be referenced in the API Call History.

Example: f6123393-d33f-4ff6-86a4-a663111afd82

compiler_type

Calculation engine used by Spark to process the request.

Example: Neuron

compiler_version

Compiler version of this service version.

Example: 1.25.0

source_hash

null

engine_id

Hash generated for this service.

Example: 64AFC7C321E5B97BAB090371DF1E9580

correlation_id

Value from Request body.

response_data_inputs

Value from Request body.

array_outputs

Value from Request body.

random_seed

Value from Request body.

show_max_calc_chain

Value from Request body.

profile

Value from Request body.

trace

Value from Request body.

top_n_profile

Value from Request body.

parameterset_version_id

Value from Request body.

debug_xcall

Value from Request body.

debug_solve

Value from Request body.

manual_now

Value from Request body.

Return tables as JSON arrays

Multiple cell table outputs defined as Xoutput can also be returned using JSON arrays as part of the response_data. To output all tables in the JSON array format, include the parameter "array_outputs": "*" in the request_meta.

Below is an example of a multiple-cell range defined as Xoutput_Packaging and a comparison between the default and JSON array formats.

Sample response

HTTP 200 OK Content-Type: application/json

Last updated