Build a service for API_CALL

Goal

You will have an HTTP endpoint that the API_CALL formula function can call during a model calculation, and that returns a result Valsight turns into a node.

The examples use Python with FastAPI and pandas. Nothing about the contract is specific to either: it is one JSON POST in and one JSON body out, so any language and framework will do.

Prerequisites

• A Valsight account with administrator permissions
• Network access to your Valsight instance

Steps

1. 1. Understand the contract

   A model node calls your endpoint like this:

   API_CALL("https://your-service/optimize", ['Volume', 'Capacity'], ["Country", "Site"])

   Valsight POSTs the values of Volume and Capacity, and expects a cube back on the levels Country and Site. Your service receives:

   Key	Value
   inputs	The input nodes, keyed by position in the formula as a string: "0", "1". Each value is a cube.
   result_levels	The requested level names as one string, for example "[Country, Site]".
   message	The formula’s message argument, passed through untouched.
   version	The formula’s version argument, passed through untouched.
   format	Always "JSON".
   maximum_mbs_to_return	The instance’s response size limit, as a string.

   A cube carries its columns as metadata and its values as rows:

   {
     "version": "1",
     "columnMetadata": [
       {"name": "Country", "columnType": "level",   "levelId": 118},
       {"name": "Site",    "columnType": "level",   "levelId": 101},
       {"name": "measure", "columnType": "measure"}
     ],
     "tableMetadata": {"tableType": "FACT", "tableName": "Input 0"},
     "rowData": [["DE", "S-100", "1200"], ["DE", "S-101", "900"]]
   }

   You must answer with HTTP 200–299 and this envelope:

   {
     "response_status": "SUCCESS",
     "error_message": "",
     "output": { ... a cube in the same shape ... }
   }

   Valsight reads only those three keys. See API_CALL for the full reference.

2. 2. Convert a cube to a DataFrame and back

   These two helpers are the whole translation layer. Everything else in your service is ordinary application code.

   import pandas as pd
   
   def cube_to_frame(cube, measure_name):
       """Turn one Valsight cube into a DataFrame with a numeric measure column."""
       columns = [column["name"] for column in cube["columnMetadata"]]
       frame = pd.DataFrame(cube["rowData"], columns=columns)
       frame = frame.rename(columns={"measure": measure_name})
       frame[measure_name] = pd.to_numeric(frame[measure_name], errors="coerce")
       return frame
   
   
   def frame_to_cube(frame, level_names, measure_column, source_cube, table_name):
       """Turn a DataFrame back into the cube Valsight expects in `output`."""
       by_name = {column["name"]: column for column in source_cube["columnMetadata"]}
       metadata = [
           by_name.get(name, {"name": name, "columnType": "level"})
           for name in level_names
       ]
       metadata.append({"name": "measure", "columnType": "measure"})
   
       ordered = frame[level_names + [measure_column]]
       return {
           "version": 1,
           "columnMetadata": metadata,
           "tableMetadata": {"tableType": "FACT", "tableName": table_name},
           "rowData": ordered.values.tolist(),
       }

   Two details in there earn their place:

   • pd.to_numeric is not optional. Every value in rowData arrives as a string, numbers included.
   • frame_to_cube copies columnMetadata from an input cube rather than writing its own. Those entries carry the levelId values Valsight uses to map values back onto its dimensions; an invented id fails the conversion.

   On the way out you may return real JSON numbers. There is no need to turn your results back into strings.

3. 3. Start with an echo endpoint

   Wire up the simplest possible endpoint first. It proves the allow-list, the network path and the envelope are right, with no modelling questions attached.

   from fastapi import FastAPI, Request
   
   app = FastAPI()
   
   @app.post("/echo")
   async def echo(request: Request):
       body = await request.json()
       cube = body.get("inputs", {}).get("0")
       if cube is None:
           return failure("No input cube received at position 0.")
       return {"response_status": "SUCCESS", "error_message": "", "output": cube}

   Call it from a model with API_CALL("https://your-service/echo", ['Some Node'], ["Year"]). The node should show the input node’s own values.

4. 4. Report errors so the user can see them

   Return HTTP 200 with response_status set to ERROR, not a 4xx status:

   def failure(message):
       return {
           "response_status": "ERROR",
           "error_message": message,
           "output": None,
       }

   On a status outside 200–299 Valsight discards the body and shows a generic transport error, so your text never reaches the user. With ERROR it is displayed on the node, which is where the person who wrote the formula will look.

5. 5. Handle the four cases that actually occur

   These are the ones that turn up in real models rather than in tests.

   Case	What to do
   The input cube is empty	Return an empty cube of the right shape, not an error. A modeller who filtered everything out in a scenario legitimately sends nothing, and the node should calculate to zero.
   The solver drops rows	Return the dropped members at 0 rather than leaving them out. A missing row reads as no value, so downstream nodes fall back to a previous figure instead of registering the change.
   A requested level is not in the cube	Fail with a clear error_message naming the level. Returning a cube of the wrong shape is far harder to diagnose from inside a model.
   The cube carries levels you did not ask for	Ignore them. Input nodes are sent with full dimensionality, so a node on Site also arrives with its Country column. Read levels by name.

6. 6. Get the endpoint allow-listed

   API_CALL refuses every URL until an administrator sets featureFlags.apiCall.allowedUrlsRegex on the instance — a Java regular expression that your endpoint must match. Empty is the default and allows nothing.

   Two other instance limits are worth knowing before you design the payload:

   Setting	Default	Meaning
   featureFlags.apiCall.maxRowsToSend	150000	Maximum rows across all input nodes combined, per request.
   featureFlags.apiCall.maxMbsToReturn	24	Maximum response size read, in megabytes.

   Requests can additionally be restricted to the instance’s outbound IP address. Ask your Valsight support team to set both up.

7. 7. Keep it fast

   The default timeout is 30 seconds, set per formula. Aim far below it: the node recalculates on every user interaction, so anything past roughly two seconds makes the model feel unresponsive long before the timeout matters.

   If the underlying computation is genuinely slow, that is a sign the work belongs in an uploaded data table via DATA rather than in a live call.

Verify

Test the endpoint before pointing a model at it. The request body is plain JSON, so no Valsight instance is needed:

curl -s localhost:8000/optimize \
  -H 'Content-Type: application/json' -d '{
  "format": "JSON",
  "version": "1",
  "message": "",
  "maximum_mbs_to_return": "24",
  "result_levels": "[Country, Site]",
  "inputs": {
    "0": {
      "version": "1",
      "columnMetadata": [
        {"name": "Country", "columnType": "level", "levelId": 118},
        {"name": "Site",    "columnType": "level", "levelId": 101},
        {"name": "measure", "columnType": "measure"}
      ],
      "tableMetadata": {"tableType": "FACT", "tableName": "Input 0"},
      "rowData": [["DE","S-100","1200"],["DE","S-101","900"]]
    }
  }
}'

You are done when all four of these hold:

• The response is HTTP 200 and response_status is SUCCESS.
• output.columnMetadata names exactly the levels you asked for, plus measure.
• A node using the formula calculates and shows those values.
• Deliberately breaking the service — stopping it, or returning response_status: "ERROR" — puts a readable message on the node rather than a generic failure.

Related