Spice vs Direct
In Analytics Builder, what is the difference between a SPICE and DIRECT query? Are there cases where one is better than the other?
Comments
-
My understanding is Spice is a captured moment in time that the query's run against, whereas the Direct would be a pull against the full (as up to date) data everytime. Spice = quicker processing, Direct could be longer processing.
-
Spice vs Direct Query is a good question.
So SPICE is basically a fast memory and there is a limitation on the maximum size of data that can be stored so the results set can influence the decision. ( currently a max of around 9GB )
If you use SPICE then you also have to ensure that you setup the refresh scheduled such that the SPICE refresh occurs after the last Dataset is uploaded to Analytics Builder.
eg Diff Dataset is generated at 2:30AM and it is uploaded to the Analytics Builder Datastore at 3:50 AM then you need to schedule your SPICE for say 4;30am to ensure you have the previous days data.
However the benefit of SPICE is that it can populate the Dashboard data faster than Direct Query and so from a user point of view the Dashboard is more performant when filtering or just loading the data into the visual.
Direct Query is less of an issue as the Analysis query runs directly on the AB Datastore so will show the last data that was automatically uploaded to the the Analytics Builder Datastore.
The downside is it takes longer to update the data in the Dashboard especially if you are looking at a long query.
Hope this helps make the choice ( I would recommend using Direct Query if you can and the performance is acceptable. ) -
In addition to what others have said, if you want to introduce (complex) calculations in your dataset/dashboard SPICE is better than Direct Query, as it is engineered to perform calculations faster. I have found that SPICE is also best for the datasets (and analysis/dashboards) that require additional security like RLS or CLS.
Overall, I prefer SPICE for some things, especially because it has that feature that emails you if a refresh fails. However, Direct Query is also great for the less 'high stakes' analyses.
