I’m facing a problem with scheduled incremental refresh. I’ve seen similar questions asked several times before. I carefully studied the answers to other users’ questions, but unfortunately, I still did not find a solution to my problem. I’m creating a dataset using a MySQL database as a data source. The dataset is created successfully. Because the database is updated with data every minute and I need to visualize them close to real time, I’m trying to set up a schedule update every 15 minutes, but instead of adding new data in a 1 hour window, the number of rows in my dataset decreases and I don’t understand the reason.
Data once written to the database is no longer changed, only new records are added. My database has a timestamp column with data type “timestamp” of the following form 2023-10-02 00:09:19 (UTC time). In the Quicksight dataset, this data is already displayed in the format 2023-10-02T00:01:19.000Z. There may be some conflict in the date and time format, but I don’t understand how to fix it.
Please share your ideas on how to solve this problem.
Thank you in advance
Hello @prohogiy, are you still experiencing this issue with your incremental refreshes or were you able to resolve it? If it is still not working how you expected, my first question is how long are the refreshes taking if they are happening every minute? It may be worth increasing your refresh window slightly if you are still losing data. Let me know how things are going and we can work to resolve it if necessary!
Hello @DylanM! Thank you for your response. Currently, data updates in SPICE Quicksight occur every 15 minutes, and in the source - every 10 minutes. I tried setting the window longer - 30 minutes, 1 hour. In this case, with the first incremental refresh, even more rows are lost, and with each subsequent incremental refresh no longer changes, as with refreshes with an interval of 15 minutes. Unfortunately, it doesn’t help.
Hello @prohogiy, I think the issue may be related to the datetime field you are using the manage the incremental refresh. Are you using a field like Modified Date/Last Updated Date, or are you using a Created Date? Also, what is the format of the date field? Has an incremental refresh ever increased the number of rows?
Hello @DylanM, I’m using the “timestamp” field to update, which contains the UTC date and time of the corresponding measurements. This field has the data type “timestamp” in the data source - mysql database, in Quicksight SPICE the data type is “Date”. The date and time format in the source “YYYY-MM-DD HH:MM:SS” and in SPICE “YYYY-MM-DDTHH:MM:SS.sssZ”. The number of rows during an incremental refresh never increased; it decreases during the first incremental refresh and remains unchanged thereafter.
Hello @prohogiy, is that timestamp field that you are referencing with the incremental refresh being updated any time a value on an existing row is updated or when a new row is added? It seems like the issue might be occurring within your datasource. If that timestamp is not being updated correctly, the incremental refresh will not be able to properly manage which rows are supposed to be ingested.
Hello @DylanM, 10 rows with measurement values for the past 10 minutes are added to my database every 10 minutes . All previous entries, including timestamp, are never changed, only 10 new rows are added each time. The row looks like this: timestamp - measurement1 - measurement2 -…
Hello @prohogiy, at this point I would recommend filing a case with AWS Support where we can
dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.
I can’t think of any other reason this problem would be occurring so I think this is your best option to try and find a solution for the error you are facing. I hope this helps!
