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