Using CloudFormation to add dataset calculated fields

Q&A
, ,
1

I have three environments, DEV, QA, and PRD where we develop and get things working in the DEV instance, then move it to the QA instance for user acceptance validation of our efforts, before then moving on to PRD for all reporting users. I recently found articles on how to get a “dataset refresh time stamp” added to my dashboards … creating a calculated field on the dataset, then a maximum date calculated field on the Analysis. Works great! Now I need to move all that over into the QA environment and hit several issues - dataset calculated field is not there and not moved with the CLI-based migration of Analyses/Dashboards; manually creating dataset calculated field in QA gets a different ID (I think) than the one in DEV, so the migrated Analysis/Dashboard is still giving a “Missing Field” error on the visual

We use a Cloud Formation Template to create our datasets, consistently across the environments - is there a clear documented method to allow me to develop a solution with a dataset Calculated Field, and then repeat that creation in QA and PRD such that my export/transfer/import deployment sequence will tie all the pieces correctly and result in a Visual that does not get an error, and I won’t step on myself and wipe out my calculated fields with the next dataset configuration change via that CFT??

Any pointers to documentation or posts I have missed in my search, or advice on how this can be accomplished will be appreciated. Thanks in advance for your time in reading and responding.

2

Hello @jnkline

Have you tried Asset bundle api’s for Quicksight asset migration? If not please refer to this blog, this should help you.

Some of the helpful responses here -

Please let me know if you need any help.

Thank you,

Shravya

3

Thank you @shravya . That gives us a set of items to look at to see what helps us out or not. That first link for using API’s, I have passed off to a team mate to compare what we are already doing to see if that is what we use (currently only doing Analysis and Dashboard assets). The second and third I need to review to see if it helps me understand how to keep the development environment where I can “develop, test, and validate” before defining what goes on to the QA and PRD instances, and ensure that whatever that practice becomes will NOT wipe out a calculated field when a CFT is updated to add another field, remove a field, or whatever … since it appears I cannot just add a LogicalMap to my CFT to retain those calculated fields (if I correctly understand what I have found so far). I’ll update and mark solutions, or not, once we digest what you have provided. Really do appreciate the quick response you have given!

4

OK - again, thanks for the reference links. After reading through and discussing the problem statement internal with other team members, I find that we are:

Fully following best practices in that we have DEV, QA, and PRD as three different accounts and instances, with each their own data sources to pull from

As it relates to assets Analyses and Dashboards, utilizing a pipeline with API calls to export these assets, transfer them from instance to instance in S3 buckets, then import them into the receiving environments (DEV to QA and/or QA to PRD) and that works perfectly well when we are ready to initiate those pipeline schedules for one, several, or all of our Analysis/Dashboard sets

This leaves me with the development piece and ensuring I don’t wipe out those calculated fields when we have to add one, that so far is not addressed at all that I can tell.

Scenario:

CFT creates a dataset from a source that is our SQL Server database (either a table or a View of a table-join selection)

A dashboard visualization request comes in and the solution (as in my example of adding the dataset refresh timestamp) requires a calculated column be added at the dataset level …

Manual creation within the console UI - calculated field refreshDateTime is created using now() as the expression

Analysis gets a calculated field added as maxRefreshTime using datediff(maxover(refreshDateTime ….) and a new visual Narrative is added to represent the newest refresh time of the filtered data on the Page …

Another reporting request comes in that requires a change to the underlying Table/View to add two new selected columns and delete one that turns out never gets used in all reporting

To do this, we change the table/view as required, edit the CFT to make appropriate adds/deletes of the physical map input columns, then update the stack with the modified template file

Result of this is my manually created calculated field is GONE, wiped out, and that results in “missing field” errors on the narrative visuals because the Analysis-level calculated field no longer has the dataset calculated field to compute against

All indications of searching and AI generated “how to” implies that calculated field can be added to a dataset via the templates, but every combination of parameters and keys I have tried results in a validation error and my stack update is rolled back.

How do I protect my calculated fields at the dataset when dataset assets are created/managed via CloudFormation using a Template?

5

@jnkline - Thank you for the detailed response. I hear you. Quick Sight does not let you define or preserve dataset-level calculated fields within CloudFormation templates. Calculated fields added manually get wiped out on any CFT-driven dataset update, and automating their recreation is not natively supported.

Suggested Workaround -

Use the Quick Sight API (via boto3 or CLI) to script calculated field creation by including them as CalculatedColumn objects in your dataset definition. Store these definitions in configuration files or source control, and run a post-CFT stack update script that applies them consistently across environments. Alternatively, implement calculated fields in your database views or SQL queries to make them part of the physical schema, preserving them automatically during CFT updates.

This approach is not yet fully automated by AWS but is the practical workaround used in many multi-environment Quick Sight setups.

Here are key resources and guidance for scripting and managing Quick Sight dataset calculated fields:

  1. Quick Sight API supports creating/updating datasets with calculated fields via CalculatedColumn objects in the CreateDataSet or UpdateDataSet API calls. You specify a unique ColumnId and an expression formula to recreate calculated fields programmatically after CloudFormation changes:

  2. Quick Sight boto3 create_data_set API

  3. Quick Sight boto3 docs overview

Just fyi - https://community.amazonquicksight.com/t/dashboard-creation-fails-with-error-code-contextual-unknown-symbol-during-validation/9250/2

If nothing works, I would suggest you to raise a support ticket. You could look to opening up a case with AWS and see if they can provide a solution or other workarounds.

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. Hope this helps!

Thank you,

Shravya

6

Thanks for the dialog and references, @shravya . From this I am working on moving the calculation back up into the SQL query, to then still have the Analysis level calculated field used in the Narrative visualize the timestamp.