I am trying to create an aggregate table in QuickSight and have brought in the four string columns and one decimal column that I want to aggregate. When I try to add the Aggregate step, it is disabled. Since it was disabled, I started removing the string columns to get down to a single column and also have the integer column on which I want to aggregate, yet the step is still disabled. Is there a step that needs to be completed before the Aggregate step will become enabled?
UPDATE - I was in Direct Query mode and it became enabled when I changed to SPICE. Not sure why it cannot be done in Direct Query, but I have to live with this for now.
This behavior is expected as QuickSight limits certain actions in Direct Query, which includes aggregations. Direct Query runs queries in real time against the source, which restricts complex transformations and is subject to database capabilities and timeouts. SPICE is not the same way as SPICE does not run queries in real time against the source but rather stores the data in QuickSight’ in-memory engine.
Based on this documentation (Features not supported in the new data preparation experience - Amazon Quick), I interpreted it as aggregation support may be expanded for Direct Query in the future. For now, I’d recommend using SPICE when you need to perform those types of aggregations, as you know it will work there.
@TraderRef (cc: @JacobR ) The net new functionalities (Pivot, Unpivot, Append, Aggregate, Divergence etc.) are available only in SPICE mode. There is a reason why DirectQuery is not supported for these features. Transformations like Pivot is an advanced prep functionality and can cause timeouts when using DirectQuery. Documentation below talks about this.
So, I am trying to get the DISTINCT claim_number where the anomaly_flag = 1. I am only selecting the two columns in the Select Columns step and filtering out the ones with anomaly_flag = 1 in the Filter step. I then am trying to use the Aggregate step to group by the claim_number to only get the DISTINCT ones, but the SPICE refresh keeps failing. It brings in the correct ones before the Aggregate step, although the preview in the new data prep experience fails. I pull them into a table visual and see them in an analysis. If I do a count and count distinct in a KPI in the analysis, I get the numbers I expect, but the new data prep experience is not giving me those distinct claim_number(s). How can I make this work? I have even tried the ListAgg and ListAgg distinct, but that is failing, too, but I really don’t understand how to use this step and could not find videos explaining it.