Tutorial: Define CS.SPARK_XCALL UDF

Syntax: CS.SPARK_XCALL([folder name/service name], [input_location], [output_template, input_name_or_values], [input_name_or_values])

Spark services can be set up to call other Spark services. Any Spark service can become a function that can be called from any other Spark service. This feature is known as Xcall and is defined as a function within an Excel workbook.

This enables encapsulation and reuse of calculations and data. Instead of having all the formulas and data in one workbook, calculations can be split and managed separately, while being referenced and reused multiple times across Spark services.

Use cases include:

• Model insurance product structures where each coverage is defined by separate Spark services. A "traffic director" upstream service assembles the coverage together for the policy.

• Model insurance product structures that have variances across distribution channels or product versions across time. Each product can be a separate Spark service and a "traffic director" upstream service manages them with a common API interface.

• Spark services where a complex part of a calculation like a solve needs to be run multiple times. The solve can be put into a separate file.

Prerequisites

• Two Spark services are required:

  • The downstream service that has the encapsulated calculations and data. This can be any Spark service that includes Inputs and outputs (Xinput and Xoutput).

  • The upstream service that makes the API request and receives the response. This workbook must include the CS.SPARK_XCALL user-defined function to call the downstream service.

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

• Coherent Assistant is required to define CS.SPARK_XCALL user-defined functions in Excel.

Use the right Xcall definition

There are many ways that the CS.SPARK_XCALL function can be defined. To understand how best to use this function, follow these steps.

1. Review the target downstream service. It should already be uploaded to Spark.

2. Identify the inputs you need to send to this service and the outputs that you want to receive. Depending on your use case you may not need all the inputs and outputs.

3. Categorize the inputs and outputs as either being single value or tables.

4. Assess the best CS.SPARK_XCALL definition using the table below.

5. Follow the step-by-step example for the definition.

Downstream inputs to send	Downstream outputs to receive	Recommended CS.SPARK_XCALL definition
Any number of inputs and types	1 single value output	Xcall without output template
Any number of inputs and types	2+ single value output OR 1+ table outputs	Xcall with output template
Make multiple Xcall at a time with single value inputs only	1+ single value outputs	Array Xcall for scaling

Xcall without output template

Downstream inputs	Downstream outputs
Any number of inputs and types	1 single value output

Example of Xcall without output template

1. In Spark, create a folder called xcalls if it does not exist.

2. Upload the Excel files for the downstream service and upstream service into the folder.

3. Use the API Tester to interact with the upstream service matrix_math_single xcall. The key calculations are performed by the downstream service matrix_math_single. The provisioned endpoint can be used for integration.

4. Open the upstream file matrix_math_single xcall.xlsx in Excel.

5. Load Coherent Assistant and sign in to the same environment and tenant. Change the input fields and observe that the CS.SPARK_XCALL function will recalculate.

6. If you would like to make any changes to the upstream service, edit the Excel file and re-test the CS.SPARK_XCALL function with Coherent Assistant. Upload the Excel file again to update the service.

Downstream service

Review all of the mappings in the Name Manager, either:

• Select the Formulas tab and select Name Manager OR,

• Use the keyboard shortcut Ctrl+F3.

Input or Output	Description
Xinput_scalar	Single value input.
Xinput_matrix	Table input with headings c1 and c2.
Xoutput_scalar_det	Single value output of the determinant =det[scalar⋅matrix].

matrix_math_single.xlsx

10KB

Open

Upstream service

Review all of the mappings in the Name Manager, either:

• Select the Formulas tab and select Name Manager OR,

• Use the keyboard shortcut Ctrl+F3.

Input	Description	Cell address
Xinput_up_scalar	Single value input sent downstream as scalar.	C3
Xinput_up_matrix	Table input with headings c1 and c2 sent downstream as matrix.	C5:D7
Xoutput_up_double_det	Single value output derived from downstream response scalar_det then ×2.	C20

matrix_math_single xcall.xlsx

12KB

Open

Upstream service function definition

CS.SPARK_XCALL("xcalls/matrix_math_single", , "scalar_det", "scalar", C3, "matrix", C5:D7)

Function parameters

Parameter	Description	Value
[service]	Service URI expressed in {folder}/{service}[version] format.	"xcalls/matrix_math_single"
[input_location]	Leave this empty.
[output_template]	Name or reference to the name of the single value output from the downstream service.	"scalar_det"
[input_name_or_values]	Name or reference to the name of the first input to the downstream.	"scalar"
[input_name_or_values]	Value or reference to the values of the first input to the downstream.	C3 Take the value of Xinput_up_scalar and use in the downstream call.
[input_name_or_values]	Name or reference to the name of the second input to the downstream.	"matrix"
[input_name_or_values]	Value or reference to the values of the second input to the downstream.	C5:D7 Take the values of Xinput_up_matrix and use in the downstream call.

Xcall with output template

Downstream inputs	Downstream outputs
Any number of inputs and types	2+ single value output OR 1+ table outputs

Example of Xcall with output template

The example is similar to Example of Xcall without output template except the downstream also includes a table response. An [output_template] is recommended for the CS.SPARK_XCALL function to properly layout the outputs returned by the downstream service.

1. In Spark, create a folder called xcalls if it does not exist.

2. Upload the Excel files for the downstream service and upstream service into the folder.

3. Use the API Tester to interact with the upstream service matrix_math_w_table xcall. The key calculations are performed by the downstream service matrix_math_w_table. The provisioned endpoint can be used for integration.

4. Open the upstream file matrix_math_single xcall.xlsx in Excel.

5. Load Coherent Assistant and sign in to the same environment and tenant. Change the input fields and observe that the CS.SPARK_XCALL function will recalculate.

6. If you would like to make any changes to the upstream service, edit the Excel file and re-test the CS.SPARK_XCALL function with Coherent Assistant. Upload the Excel file again to update the service.

Downstream service

Review all of the mappings in the Name Manager, either:

• Select the Formulas tab and select Name Manager OR,

• Use the keyboard shortcut Ctrl+F3.

Input or Output	Description
Xinput_scalar	Single value input.
Xinput_matrix	Table input with headings c1 and c2.
Xoutput_scalar_det	Single value output of the determinant =det[scalar ⋅ matrix].
Xoutput_scalar_mult	Table input with headings c1 and c2 =scalar ⋅ matrix.

matrix_math_w_table.xlsx

10KB

Open

Upstream service

Review all of the mappings in the Name Manager, either:

• Select the Formulas tab and select Name Manager OR,

• Use the keyboard shortcut Ctrl+F3.

Input	Description	Cell address
Xinput_up_scalar	Single value input sent downstream as scalar.	C3
Xinput_up_matrix	Table input with headings c1 and c2 sent downstream as matrix.	C5:D7
Xoutput_up_double_det	Single value output derived from downstream response scalar_det then ×2.	C20
Xoutput_up_double_matrix	Table output with headings c1 and c2 derived from downstream response scalar_mult then ×2.	C22:D24

matrix_math_w_table xcall.xlsx

12KB

Open

Upstream service function definition

CS.SPARK_XCALL("xcalls/matrix_math_w_table", B9:D12, "scalar", C3, "matrix", C5:D7)

Function parameters

Parameter	Description	Value
[service]	Service URI expressed in {folder}/{service}[version] format.	"xcalls/matrix_math_w_table"
[input_location]	Leave this empty.
[output_template]	Output template is defined by a range reference. Inside this range: The left most column is for the output names. The columns to the right define the space where the outputs will be placed. Single values need only one cell. Tables should have the table headings and the maximum size of the output response allocated.	B9:D12 where scalar_det B9:C9 scalar_mult B10:B12
[input_name_or_values]	Name or reference to the name of the first input to the downstream.	"scalar"
[input_name_or_values]	Value or reference to the values of the first input to the downstream.	C3 Take the value of Xinput_up_scalar to send the downstream call.
[input_name_or_values]	Name or reference to the name of the second input to the downstream.	"matrix"
[input_name_or_values]	Value or reference to the values of the second input to the downstream.	C5:D7 Take the values of Xinput_up_matrix to send the downstream call.

Array Xcall for scaling

Downstream inputs	Downstream outputs
Make multiple Xcall at a time with single value inputs only	1+ single value outputs

Example of array Xcall for scaling

If you need to perform multiple Xcall to a service at a time and downstream inputs and outputs are only single values, then you can make use of array Xcall. Array Xcall are easier to define many Xcall together and can be configured for higher synchronous Xcall performance.

1. In Spark, create a folder called xcalls if it does not exist.

2. Upload the Excel files for the downstream service and upstream service into the folder.

3. Use the API Tester to interact with the upstream service ab_operations xcall. The key calculations are performed by the downstream service ab_operations. The provisioned endpoint can be used for integration.

4. Open the upstream file ab_operations_xcall.xlsx in Excel.

5. Load Coherent Assistant and sign in to the same environment and tenant. Change the input fields and observe that the CS.SPARK_XCALL function will recalculate.

6. If you would like to make any changes to the upstream service, edit the Excel file and re-test the CS.SPARK_XCALL function with Coherent Assistant. Upload the Excel file again to update the service.

Downstream service

Review all of the mappings in the Name Manager, either:

• Select the Formulas tab and select Name Manager OR,

• Use the keyboard shortcut Ctrl+F3.

Input or Output	Description
Xinput_a	Single value input.
Xinput_b	Single value input.
Xoutput_sum	Single value output of SUM(a,b).
Xoutput_product	Single value output of PRODUCT(a,b).
Xoutput_sumsq	Single value output of SUMSQ(a,b).

ab_operations.xlsx

11KB

Open

Upstream service

Review all of the mappings in the Name Manager, either:

• Select the Formulas tab and select Name Manager OR,

• Use the keyboard shortcut Ctrl+F3.

Input	Description	Cell address
Xinput_table	Table with headings a and b . The table records will be sent downstream as a and b.	C3
Xoutput_table1	Table with columns concat, sum, product, sumsq derived from downstream response sum, product, sumsq. The source CS.SPARK_XCALL references the data in Xinput_table as a single contiguous range	I10:L13
Xoutput_table2	Table with columns concat, sum, product, sumsq derived from downstream response sum, product, sumsq. The source CS.SPARK_XCALL references the data in Xinput_table as separate columns.	I15:L18

ab_operations xcall.xlsx

13KB

Open

Upstream service function definition

For array Xcall, the input data can be referenced as a contiguous range or as separate columns depending on the layout of your worksheet. Both will work the same way.

Reference data as contiguous range

CS.SPARK_XCALL("xcalls/ab_operations", , C10:E10, C3:D3, C4:D6)

Function parameters

Parameter	Description	Value
[service]	Service URI expressed in {folder}/{service}[version] format.	"xcalls/ab_operations"
[input_location]	Leave this empty.
[output_template]	Output template is defined by using a range. For array xcall, this should be arranged as a 1 row × n column range.	C10:E10
[input_name_or_values]	Name or reference to the name of the first input from the downstream.	C3:D3 Take the headings of a and b from Xinput_table to send the downstream call.
[input_name_or_values]	Value or reference to the values of the first input from the downstream.	C4:D6 Take the data for a and b from Xinput_table to send the downstream call.

Reference data as separate columns

CS.SPARK_XCALL("xcalls/ab_operations", ,C15:E15, C3, C4:C6, D3, D4:D6)

Function parameters

Parameter	Description	Value
[service]	Service URI expressed in {folder}/{service}[version] format.	"xcalls/ab_operations"
[input_location]	Leave this empty.
[output_template]	Output template is defined by a range reference. For array xcall, this should be arranged as a 1 row × n column range of output names.	C15:E15
[input_name_or_values]	Name or reference to the name of the first input from the downstream.	C3 Take the heading of a from Xinput_table to send the downstream call.
[input_name_or_values]	Value or reference to the values of the first input from the downstream.	C4:C6 Take the data for a from Xinput_table to send the downstream call.
[input_name_or_values]	Name or reference to the name of the second input from the downstream.	D3 Take the heading of b from Xinput_table to send the downstream call.
[input_name_or_values]	Value or reference to the values of the second input from the downstream.	D4:D6 Take the data for b from Xinput_table to send the downstream call.

Next steps

Before making your first Xcall, review the CS.SPARK_XCALL UDF function reference. Many CS.SPARK_XCALL configurations are available. Review these configurations before implementing Xcall into your own upstream Spark service.