I need to create a snapshot of my profile snapshot dataset in AJO so I can access values stored in aggregate fields in PowerBI.
I have tried scheduling a query to select the columns and counters I need and write them to a dataset.
The problem I have is that the scheduled query either adds rows to the table (thus I get duplicates), using the "Use Existing Dataset" option or schedule never runs when I use the "Use New Dataset". I've tried adding the SQL DELETE FROM tablename at the start of the query only to have my query fail with
delete from tool_extract_layers_and_locations;
6:52:08 PM > Running query...
6:52:46 PM > Query failed in 38.285 seconds.
6:52:46 PM > ErrorCode: 42601 Syntax error encountered. Reason: [Invalid command!]
How can I run a scheduled query (eg run every second day) , create a dataset with a fixed name, and only have it contain the results of the last time the query executed?