Dashboard creation fails with error code CONTEXTUAL_UNKNOWN_SYMBOL during validation

Question & Answer
,
1

Hi QS team!

I am facing a strange issue deploying a CloudFormation stack that contains a custom resource for creating QuickSight Dashboard from an exported analysis definition.

The error I am getting is:

{
  "Type": "INVALID_CALCULATED_COLUMN_EXPRESSION",
  "Message": "Calculated field delta_volume failed with error code CONTEXTUAL_UNKNOWN_SYMBOL during validation",
  "ViolatedEntities": [
    {
      "Path": "calculated-field/delta_volume"
    }
  ]
},

In the QS console, for the analysis, everything works fine. And so does publishing the analysis to a dashboard.

This is what the calculated field looks like on QS:

Screen Shot 2023-01-19 at 12.09.11
Screen Shot 2023-01-19 at 12.09.111090×781 97.2 KB

Any idea what might be the issue here?

2

Hi @donatoaz!
There was a similar case noted in this Community. What could cause CONTEXTUAL_UNKNOWN_SYMBOL error?. In this case it was an ordering issue of the calculations in the .json file.

With this being an error, I would recommend opening a support case. It is best to have it investigated by the support/engineering teams by looking at the logs. It is possible there is a bug the team needs to fix (or they can at least pinpoint the problem and perhaps suggest a workaround).

Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.

3

I was aware of the similar issue, however in my case I have a single calculated field, so in theory there is no reason for a dependency issue to be happening. The calculated fields property of the definition has a single field, and it references a regular data set field (not a calculated one).

CalculatedFields: [
    {
      DataSetIdentifier: dataSetIdentifier,
      Name: "delta_volume",
      Expression: "{flow_avg_1d} * 60 * 60 * 24",
    },
  ],
4

For posterity:

We ended up not creating the calculated fields in the CfnDashboard’s definition, but pre-creating the calculated fields on the Data Set that is used by the Dashboard.

This solved the issue.

As an example, this was the LogicalTableMap used on DataSet creation:

{
    "table": {
        "Alias": "DailyAggrData",
        "DataTransforms": [
            {
                "CreateColumnsOperation": {
                    "Columns": [
                        {
                            "ColumnName": "consumption_daily",
                            "ColumnId": "consumption_daily",
                            "Expression": "{flow_avg_1d} * 24 * 60 * 60"
                        }
                    ]
                }
            },
            {
                "ProjectOperation": {
                    "ProjectedColumns": [
                        "customer_id",
                        "site_name",
                        "device_id",
                        "measure_name",
                        "time",
                        "flow_avg_1d",
                        "consumption_daily"
                    ]
                }
            }
        ],
        "Source": {
            "PhysicalTableId": "table"
        }
    }
}