We are getting the below error (and attached) in a visual that contains a periodOverPeriodPercentDifference function for a YoY calculation. Previously, this has worked without any issue and we have not made any changes.
Dataset is a custom SQL, limiting the data resolves it on one refresh, but issue reappears in next refreshes.
“something went wrong during publishing or querying SPICE dataset. Please check the dataset is published successfully and try again. If you continue to get this error, contact AWS support.”
Is this a known issue? How can we root-cause or troubleshoot, are there logs?
At first glance, my initial guess is that this error is being caused by a timeout issue or something is happening that is causing your load time to be too much for the system. But with that said, it is weird that the worked before and now you are experiencing problems with it even after no changes have been made.
What data prep experience are you using? There have been a couple community instances where using the new data prep experience compared to the legacy data prep experience resulted in higher ingestion times.
One other workaround is limiting your data which as you said resolves the issue but then set it to incremental refresh. That way the dataset is not doing a full ingestion every time it refreshes and that could help.
Hi @Arjan Checking in. We have not heard back from you regarding your question. We’d still like to help. If we do not hear back in the next 3 days, we will archive the question
Thank you for your reply. Indeed, this only started recently, has there been an update?
Limiting the data loaded does not structurally solve the issue as it happens sporadically.
Using a custom SQL query, with about 100-150m rows per full refresh, which takes up to 30 minutes.
Are there any logs I can look into too root-cause the issue? The mentioned visual is using periodOverPeriodPercentDifferencefunctions, which is the only common denominator among all the broken visuals.
Another idea I can think of, if the periodOverPeriodPercentDifference function is what seems to be what is causing this problem, is to perform this calculation outside of your sql and ideally in the analysis level. I am unsure why this error is happening sporadically, but I think the function is most likely putting too much strain to run the query in quick; moreover, a period over period calculation is very doable to achieve on the analysis level. Let me know if you need any additional help on how to formulate this calculation in quicksight.
To your logs question, there is unfortunately not a way to view logs to get a different look into what the root-cause of this issue is. In some cases, I have used the web browser console and that can give further insight into the errors as they happen but that is pretty much it.
Even after drastically reducing the data being loaded into the SPICE dataset (as can be seen attached), this issue still persists sporadically. Furthermore, the error message points to either publishing or querying the SPICE dataset. However, I can see that the SPICE dataset has loaded successfully and never failed (as can be seen attached). Lastly, no change in our end has been made that could explain this.
All in all, given the above, I don’t think it is related to the volume of the data (44m rows, 30gb). Is there a way we can loop in AWS support as the error proposes?
Given all that you have said, and the dataset is loading successfully but the error message is still persisting, I am unsure on what is causing this behavior. I would suggest creating a support ticket and the support team will be able to further investigate into what is the cause. To create a support ticket, please refer to this resource (Case management - AWS Support).
I’ve been able to resolve the issue by reducing one level of nesting in the calculated field that was being used in the visual (calculated field using periodOverPeriod function which referenced another calculated field using periodOverPeriod function).
Most likely this was putting too much strain on the visual.
