Using CS.SPARK_XCALL() UDF

Introduction

By connecting Spark services together, users can use data from different sources and better externalize calculations and data. The Xcall is used through a user-defined function, and has 4 components, meant to help discover options for, build, and implement an Xcall:

• CS.SPARK_XCALL()

  • Use this to discover folders a user has access to, and to find specific services. Include a folder name in the brackets to return a list of services in that folder.

  • Note: only the most recent active version of a given service will be displayed. To see a list of versions for a given model, include “folder name/service name[]” in the brackets.

• CS.SPARK_XCALL("folder name/service name")

  • Use this to return the input template, used to set up inputs for the downstream service you want to call.

• CS.SPARK_XCALL("folder name/service name", input_location)

  • Use this function to get a response from the downstream service after setting up an input template range. It is recommended to set up an output template (see below) rather than use the full output in an upstream service.

  • You can also use the CS.SPARK_XCALL("folder name/service name", input_location, output_template, input_name_or_values) function to add additional inputs that may be located in a separate area of your workbook from the rest of your input_location, or if you want to use a combination of fixed and/or variable inputs for scenarios/simulations. See below for more information and examples.

• CS.SPARK_XCALL("folder name/service name", input_location, output_template)

  • Use this function to change the structure of the output received from the downstream service. This includes changing the order of outputs, or restricting which outputs of a service are received. It is recommended to use this in an upstream model to prevent issues if changes are made to downstream services (such as new outputs being added).

• CS.SPARK_XCALL("folder name/service name", input_location, output_template, input_name_or_values, input_name_or_values)

  • Use name/value pairs to supplement (or replace) your input template for increased flexibility in using your Xcall. Name/value pairs allow you to list an input name as a parameter, followed by the value for that input as the next parameter. These inputs can be single inputs or tables.

  • Using name/value pairs allows you to use the Xcall array functionality, where you can make multiple Xcalls from one UDF.

To reference a specific version, you can reference the service version using "folder name/service name[version]", e.g. myfolder/myservice[0.4.0]. If you include a partial version number, Spark will use the latest service version with the matching reference.

input_name_or_values parameters require name/value pairs, meaning that for each input_name you provide, there must be a value following it. You can list multiple name/value pairs in a single UDF.

If you include the same input with different values in both the input_location parameter and the name/value pairs, only the value included in the name/value pair parameters will be used in your Xcall.

All Xcall functions are spill functions and may return results in multiple neighboring cells. Leave ample space around (and especially below) Xcall functions to avoid #SPILL! errors.

Prerequisites

• Two Spark services are required. One is upstream, to make an API request and receive the response. The other one is downstream, to give an API response depending on the request from upstream.

• Both Spark services must be in the same tenant and accessible with the same credentials.

Downstream service

This is a normal Spark service that is used to provide data to the upstream service when an Xcall is executed in the upstream service. This file will contain the usual Xinputs and Xoutputs, which will be used as parameters when called from the upstream service. This file does not need to contain any custom or user-defined functions.

Upstream service

This Spark service uses Xcall to call an API to get data from the downstream service by passing the necessary parameters. The Excel file for this service would contain the Xcall block as well as custom/user-defined functions to execute the API call.

Coherent Assistant

With the help of the Coherent Assistant, users can connect an Excel file to Spark without having to open their browser. Coherent Assistant is required to access both the user-defined functions needed to use Xcall, and the tenant where services are located. By using Coherent Assistant, users do not need to manually create User Defined Functions (UDF) to run Xcall.

Build an Xcall

Search functions

• CS.SPARK_XCALL()

  • Use this to discover folders with accessible services, and to find specific services, include a folder name in the brackets to return a list of services in that folder.

  • Note: only the most recent active version of a given service will be displayed. To see a list of versions for a given model, include folder name/service name[] in the function parameter.

Input templates

• CS.SPARK_XCALL("folder name/service name”)

  • Use this to return the input template that can be copied to set up inputs for the downstream service.

Subservices

To identify specific subservices to execute with your Xcall, you can use the #service_category metadata field (found in the request_meta section of API requests and responses). There are several ways to reference this field in your Xcall formula, as explained below. If you don't provide a subservice name, Spark will assume you wish to use the default subservice.

Specify a single subservice

=CS.SPARK_XCALL("SERVICE_URI",D6:E13,,"#service_category","Subservice1")

Using the above syntax, we're passing a single subservice as an inline argument in our function, first identifying the field, then the value. If the subservice name specified is incorrect, the function will result in an error.

Specify multiple subservices

=CS.SPARK_XCALL("SERVICE_URI",D6:E13,,"#service_category","Subservice1,Subservice2,Subservice3")

To specify more than one subservice, we use the same syntax as above, and add multiple values to the field. If one or more of the subservice names is incorrect, Spark will return the results for the correct subservices and will ignore the invalid subservices.

Specify subservice(s) by cell reference

=CS.SPARK_XCALL("SERVICE_URI",D6:E13,,"#service_category",C4:C6) Rather than entering subservice names directly into the formula, you can also use cell reference (make sure the subservice name is spelled correctly and the cells are formatted as text). In the example above, cells C4 to C6 contain the names of the subservices.

Call a Spark service via Xcall

Outputs

• CS.SPARK_XCALL("folder name/service name”, input_location)

  • Use this to call a Spark API from an upstream file. The downstream service folder and name can be retrieved from the Spark tenant or the search function described above.

  • The input template can also be retrieved from the search function listed above –copy the template returned and input the values to use in an Xcall.

  • In this format, the call will return the entire response from the API based on the specified inputs. This function should be used to discover all available output formats and names, which can then be selected as part of an output template range (see below).

  • It is best to set an output template to ensure changes to the downstream service do not inadvertently impact an upstream service call (see below).

Output templates

• CS.SPARK_XCALL("folder name/service name", input_location, output_template)

  • Similar to the above function, this will return a response from the downstream service but will return the output in the specified format in the output template range.

  • Output templates are recommended but not required when uploading a service containing an Xcall to Spark. If you upload a service to Spark without an Output template, Spark will use the Xcall spill range size and structure at the time of upload as the Output template. It is recommended to set an output template in your Xcall, as not all values returned from the API may be needed in an upstream service. Additionally, without an output template, changes to the service being called can inadvertently affect how an Xcall is executed.

  • If your Xcall includes returning ranges, it is strongly recommended to use an output template. For each of the ranges, allocate an output range equivalent to the size of the Xoutput in the downstream service. If this is not done, there may be instances where the Xcall retains residual values that were previously saved in the upstream file.

Name-value pairs

• CS.SPARK_XCALL("folder name/service name”, input_location, output_template, input_name_or_values, input_name_or_values)

  • Using name/value pairs allows you to add additional inputs to your Xcall. Specify an input name followed by another parameter specifying the value for that input. This can be for a single input or a table.

  • Input name/value pairs can be used to supplement or replace inputs in your input_location. You can also skip the input_location parameter and use only the name/value pairs in your Xcall.

  • If a given input is listed in both the input_location and the name/value pairs, only the value in the name/value pairs will be used in the Xcall.

  • Name/value pairs also enable you to use Xcall as an array function to make multiple Xcalls within one UDF. If you are making more than one Xcall to a given service in your upstream file, Xcall arrays provide performance enhancements over individual Xcall UDFs when calling the same service multiple times in uploaded services.

Single column input tables

If a single column array of inputs nx1 is included in an Xcall, Xcall will assume this is an array of n Xcalls with distinct single inputs for the associated input parameter. If a single column table is passed in without additional instruction, your Xcall may return an error for that associated input.

To denote that your Xcall includes inputs that are single column nx1 tables, you can use the !treat_as_table input instruction:

1. First, set up your Xcall and include your input name and value for the single column table.

2. Next, add an additional input parameter to your Xcall: !treat_as_table.

3. The values associated with this name should be the name of the input that you want to be treated as a single column table - it can also be a comma separated list of input names if your request includes multiple single column tables.

Example: CS.SPARK_XCALL(..., "!treat_as_table", "SingleColumnInput")

Please note that if your Xcall includes a multi-column table, or intended to be an array of single inputs, you do not need to include the parameter for these inputs.

Async batch

Please note that async batch is billed separately from other Spark API usage. Please contact Support for more information.

Xcall can be combined with the capability of the Batch APIs asynchronous (async) processing for faster response times in processing a block of many Xcalls to one service. This is useful for situations such as processing a table of data or simulations. This option is not recommended for small quantities of Xcalls due to high startup time.

To use async batch with Xcall, simply follow the steps below:

1. Setup your Xcall using the array spec to create 1 Excel formula that makes multiple Xcalls (seeName-value pairs).

2. In your Xcall, include the input name #async_batch with the text of value "true".

3. Optionally, you can include a parameter to manage the chunk size of your async batch to optimize processing by including the input name !chunk_size with a value of how many requests you wish to process per chunk.

   • Chunk size allows you to set the size of the number of requests you send at a given time.

   • For example, if your batch is 50000 records, and you set !chunk_size = 1000, you would create 50 chunks with a size of 1000 records each.

Here is a sample formula of using Xcall with async batch:

• =CS.SPARK_XCALL("Async Batch Demo/Async_Batch_Example_Service",,N2:T2,A2:M2,A3:M50002, "#async_batch", "true", "!chunk_size", 1000)

Sample files

In this example, we are using two files (Downstream and Upstream file) to show the functionality of the Xcall using Coherent Assistant.

Downstream service

• The downstream Excel file contains 2 Xinputs (Input1 and Input2) and 2 Xoutputs (Output1 and Output2), where Output1 is a single value and Output2 is a separate JSON object having its own outputs.

Downstream Service

Upstream service

• The upstream Excel file contains the input and output templates, CS.SPARK_XCALL() user-defined functions (UDFs), and responses.

Table JSON objects will be spilled as Excel tables

Example of an upstream service

You can use the files below to follow along!

12KB

DownstreamService.xlsx

Open

17KB

UpstreamService_example.xlsx

Open

Preparation

Downstream service

1. You must be logged into the tenant containing your downstream service via Spark Assistant

2. You will need the downstream folder name, service name (version number optional), and input template.

   • You can view the folder name and service name of the downstream service on the folder page. Note down the name on the folder card to perform the following steps.

   • Remember you can also query the folder and service names using the methods described above.

Folder View

Upstream service

Setup input template

1. See Input templates section above about how to retrieve an input template for a given service, including an example for the downstream service.

2. The upstream service must be uploaded to the same tenant as the downstream service it is calling.

Command: =CS.SPARK_XCALL("Test_SPARK_Xcall/DownstreamService")

Request

Create an Xcall function using the CS.SPARK_XCALL() UDF in the upstream service to make an API call. The Xcall function can be anywhere in your workbook, but you should leave ample blank cells beneath your Xcall function, as this utilizes spill functions to return data – leaving cells filled in below the block can result in #SPILL! errors.

You must be logged in to Coherent Assistant to make an Xcall, and you must be logged into the same tenant where the service you are calling is located. The function needs the following fields to make an Xcall:

• Service name (including folder name)

  • Note: if no version number is included with your service name, the Xcall will default to the most recent effective version of the service downstream service you are calling. This will also allow any updates to the downstream service to be applied to your Xcall once they are available.

  • If you include a version number in your service name, the Xcall will call that specific version of the service. Use this if you want to fix your Xcall to a particular version of a service.

  • Examples:

    • CS.SPARK_XCALL("Test_SPARK_Xcall/DownstreamService") – will retrieve the most recent effective version of an input template for this service every time it is run.

    • CS.SPARK_XCALL("Test_SPARK_Xcall/DownstreamService[0.1.0]") – will retrieve the input template for version 0.1.0 of this service every time it is run.

• Input template

  • Note: the first column of the input template must only include the input names for the downstream service you are calling. Refer to the input template returned by the UDF and/or the example shown here to see how to set up the input template for both single and table inputs.

• Output template

  • Note: this is optional to receive a response but is recommended to reduce potential errors. If you are uploading your upstream service to Spark, an output template is required.)

  • Note: The first column of the output template must only include the output names for the downstream service you are calling. Refer to the full output response returned by the UDF and/or example shown here to see how to set up the output template for both single and table outputs.

    • For single outputs, if you pass only the output name as your output template, the response will only contain the values of the single outputs you request.

    • You can request metadata from the downstream service in your output by including # + metadata field name(s) in your output template (for example, #call_id).

• Optional: including additional input name/value pairs separated by commas. Use this if you want a more flexible input template, or if you want to run scenarios/testing/simulations. You can add up to 125 name/value pairs in your Xcall UDF.

Please note that folder, service, input, and output names are case-sensitive when using Xcall.

Response

The CS.SPARK_XCALL() UDF will return a spill with the response from the API.

• The spill will start at the same cell as the UDF.

• If no output template is selected, then the entire API response will be returned.

• If an output template is selected, then the API response will be returned in that format.

• If you receive an error, review the messages associated with the error to adjust your request.