Calculated Field Works in SPICE but not Direct Query?

Q&A
, , , , , ,
1

I created a calculated field in an analysis with a SPICE dataset. After setting the dataset to Direct Query (our preferred method), that field now breaks graphs with this error:

Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.

Why would this happen?

Context if this matters:

It’s a calculated field Most Recent 5 Years using a single value string parameter. I use the calculated field in a filter Most Recent 5 Years = 1 which changes the years shown in the visualization. Screen caps below

ifelse(
    ${ParamYearsToDisplay} = 'Show Full FYs Only',
    ifelse(
        {_YearDenseRank} <= 6 AND {_YearDenseRank} > 1,
        TRUE,
        FALSE    
    ), 
    ifelse(
        {_YearDenseRank} <= 5,
        TRUE,
        FALSE    
    )
)

This works as expected in SPICE. Showing tables with the values, but we’re actually using it in bar charts.

image
image709×491 19.4 KB

image
image722×496 16.9 KB

In Direct Query it throws this error on all visualizations if the field is in the visualization or the filter is enabled:

Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.

Also, I’m also open to retooling this field/parameter set up if there’s a more efficient way to accomplish this. I don’t love having to use the denserank thing but I needed a way to calculate the difference between the absolute max value of the entire Year field vs the value in the given row.

2

Hi @C_B

  1. What is your dataset volume (how many rows)
  2. If it is in millions then the boolean is being computed based on each row from the database which may be causing this issue.
  3. Are you applying any filtering to reduce the data that is being queried from the database through parameters at the dataset level? You may need to look at this

Regards,

Giri

3

Thanks @Giridhar.Prabhu

I applied a year filter at the dataset level which cut it back to ~33,000 rows, and it still doesn’t work. Some of the datasets for this analysis will be in the millions after filtering, so that is good to know.

Want to reiterate though, I only have the problem in direct query. It works fine in spice. Is the row limit due to direct query or booleans?

Is there another way to go about this without using the boolean calculated field? I don’t really care how it’s set up (parameters, calculations, booleans, non-booleans, etc, etc, etc), it just needs to have a control that allows the user to change which year range is shown.

4

Hi @C_B

The issue is not the boolean but the direct query. If your purpose is to show data for the 5 years from the date selected you can probably determine the date range in two calculated years and use those fields as parameters in your dataset so that you only get the data that you want in the visual and possibly may still be able to use direct query.

But please remember that everytime the user makes a change in selection there will be a query going back to the database. That may not give a good user experience.

Share some additional details of your case and I may be able to provide some sample to work with

Regards,
Giri

5

@Giridhar.Prabhu Thanks for your help!

The final dashboard visualizations will only show 5 years. That is predetermined. The user can use a control (set as parameter) whether that includes the most recent fiscal year to date. Here its 2023-2027:

image
image484×439 8.92 KB

Or, they choose full fiscal years only, which would be 2022-2026.

image
image493×443 7.29 KB

This is based on the highest year number in the year field, not subtracting from the current date because this is fiscal years. I couldn’t figure out how get the highest number value in an entire column using quicksight’s native calculated field functions like max() etc, because they seem to require grouping or will be affect by grouping at some point, and that is not helpful for what i need to do.

6

Hi @C_B

You can actually shift your following condition as part of your Direct Query. You can use the parameter ${ParamYearsToDisplay} and construct the where clause as neeed.

A simple example from my work:

  1. In the dataset I created two parameters
  3. I then used the same in my queries where clause
  4. image
  5. In the analysis I have the same parameters
  6. image
  7. Then you link the analysis parameter to the dataset parameter
  8. image
    image242×214 5.54 KB
  9. So basically if you think about your case ${ParamYearsToDisplay} parameter’s value you want passed from your analysis to the dataset when it issues the query to the database. Since you only want the 5 full years or 5 years with the latest YTD which I assume are database columns in your source you can create the equivalent of the where clause in the datasets query
  10. This way you filter the records at source than bringing all the data and then trying to do the computation in QuickSight
7

Hi @C_B

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with archive this topic.

Thank you!

8

Hi @C_B

Since we have not heard back from you, I’ll go ahead and close this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!