Direct Query vs. Spice Engine

Hi Everyone,

How can I keep my dataset from automatically switching to use the SPICE engine?

I work with large datasets (10+million) records and I want my redshift cluster to perform the queries instead of the SPICE engine.

Thanks in advance

What do you mean automatically? How are you creating these?

If you are doing it via the console you can click on direct query.

1 Like

Thanks @Max … Yes when you created the data set, you can change the query mode. As always @Max the solutions are really useful.

Regards - San

1 Like

Hi Max, When I add dimensions to my fact table. the model automatically switches to SPICE instead of remaining in DIRECT mode. See the attached image.

@ROBERT_J_ESTEVES : Do we have used any custom sql as part of the data set? If yes, we observed the same issue and raise a post for the same as well. I am awaiting response from the community, planning to raise a ticket to AWS product team soon.

For now, please check whether custom sql have been used or not. if yes, you can create a view and put those condition at database level and expose the view in QuickSight as work around solution.

Regards - San

1 Like

Thanks @Sanjeeb2022. I look forward to an answer soon.

1 Like

Thanks @ROBERT_J_ESTEVES . Hopefully this will be a feature request for Quicksight down the line and we will wait. For now, you can marked the view approach as solution.

Regards - San


Please look at the details in the below post and then compare the scenario at your end.

Could you paste a screenshot which shows how a model looks like ? ( eg : screenshot of the model I have in the above link including the query mode capability ) .

@Sanjeeb2022 : what was the issue with custom sql ?

Athena : custom sql from the same Athena datasource which is joined with another Athena dataset which is also in direct query mode.

Kind regards,

Thanks @Koushik_Muthanna . You are correct, I can see custom sql with nomal database, we can see SPICE now. Any recent changes.

@Biswajit_1993 - Can you check this as well. Now if we have a custom sql data set and normal data set, the result can be stored in Direct Query. See the screenshot below.

However the below case is still valid.

Case -1: When you have 2 data sets, data set -1 is spice and dataset no spice, when you join, by default the custom data set is going SPICE and there is no direct query option.

Regards - San

1 Like

Thanks for taking the time to answer my question @Koushik_Muthanna, based on your diagram I should be able to use direct query, because all my data sources come from Redshift (dimensions and Fact).

Yet. this is what I see in Quicksight.

1 Like

Thanks @ROBERT_J_ESTEVES . If you see my comment case -1, this is what I exactly put… Thanks for detail information.

Regards - San

I believe that QuickSight considers your dataset 1 and dataset 2 as different sources even though the underlying source is the same for both of them. Each parent dataset used in your child dataset is treated as a data source.

It’s what they refer to as “dataset-as-a-source”.

Hi @Sanjeeb, As you mentioned both of the scenario is valid I checked with my end.

If one Datasets is Custom Sql and another is Direct Query Datasets then joining can be Direct Query or SPICE mode.

But in second scenarios if any of your dataset is in SPICE mode then the joining between the datasets is automatically going to the SPICE mode.

Thanks & Regards
Biswajit Dash

1 Like