Direct Query vs. Spice Engine

Question & Answer
, , ,
1

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

2

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

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

image
image554Ă—946 25 KB

1 Like
3

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
4

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.
spice_mode_only

5

@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
6

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

1 Like
7

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

8

Hi @ROBERT_J_ESTEVES ,

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.

Screenshot 2023-03-30 at 18.13.57
Screenshot 2023-03-30 at 18.13.571898Ă—931 106 KB

Kind regards,
Koushik

1 Like
9

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.

image
image1054Ă—684 39.7 KB

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
10

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).

image
image664Ă—507 32.6 KB

Yet. this is what I see in Quicksight.

image
image1456Ă—605 79.2 KB

1 Like
11

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

Regards - San

12

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”.

1 Like
13

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

2 Likes