If you are having Full extract then Tableau will take care of the changes since it will refresh all the rows. (So it will take care of both Update and Delete )
Datasource Update to
Numbers Numbers ------------Full Tableau Refresh ----------> Numbers
1 1 1
2 0 0
When you select incremental refresh at tableau level then you might some differences ? What are you using ?
Thanks for your time Ritesh. I am yet to implement the solution, so full load is not recommendable for our project architecture.
we have to stick with incremental refresh by dealing updated and deleted records.
I can use datetime field for incremental data but not sure how to replace the existing data if we received historical data updates.
Any suggestions or thoughts? I believe someone might have implemented the solution, so looking for their solutions.
You can try something like this
If its daily transaction data then create one backup table which will contains data till yesterday. And once your daily data updated into the main table then do some checks with your backup table, like how many records has been inserted/updated/deleted. You can get the records details which are affected with DML.
Add a FLAG field in your backup table and update the field with values like 0=New records, 1=Updated, 2=Deleted
Then connect this updated backup table in tableau for analysis.
You can automate this process writing any stored procedure. I feels in this way you can achieve.
Note: In tableau when you're using extract as data connection on your daily transaction data source then occasionally you should run full refresh the extracts to get the updated information of your historical data set. Because in tableau only insert/append (DML) happens, no update/delete until you use full refresh.
Thanks for your inputs Mahfooj. Looks like this is a limitation in tableau.
I don't say its a limitation of Tableau though every tool has their own capabilities of doing things. Tableau is a data visualization tool not an ETL tool.
True, let me take my words back.
We can do it in Qlikview or Qlik Sense.
This is a huge limitation that have always been present.