Comparison of data changes between data loads

Question

We are getting more and more requests from clients to be able to see the data changes between data refreshes.&nbsp; &nbsp;My idea was to firstly load both the old and new data into the same elasticube, and then union the data into single tables with a flag for "old" and "new", and then it will be easy to switch between the old and new data.&nbsp; However, any ideas on how to flag the data changes without having to write variable by variable comparisons and flagging?&nbsp; Looking forward to some creative ideas.

liliia_devx · Answer

francoisvv&nbsp;hi!&nbsp;Please check if the Accumulative build type could help to store the historical data in the easier way for you. Please refer to the 'Accumulate By' section on this guide:&nbsp;https://docs.sisense.com/main/SisenseLinux/elasticube-build-settings.htm#AccumulateBy&nbsp;

liliia_devx · Answer

francoisvv&nbsp;Got it! Thanks for the additional details. If you find a SQL solution for your use-case, please share it with us!Please also take a look at the post following the link below. It provides an example on how to see the&nbsp;historical data per the dimension and not just the current state:&nbsp;https://community.sisense.com/t5/knowledge/slowly-changing-dimension/ta-p/8925

liliia_devx · Answer

francoisvv&nbsp;you're welcome! Hope it helps to achieve your goal 🙂To make the accumulative build work properly, you need to&nbsp;select a specific integer or date column to act&nbsp;as an index, and if the index value is greater in a subsequent build, then data is accumulated for the table.&nbsp;Is there any id or date column available in your dataset that could be used as an index for accumulation?&nbsp;

francoisvv · Answer

Thanks so much for this valuable information. I just checked on our instances, and were able to enable it, however, from what I see, it does not take into consideration records that are unique.&nbsp; Do you perhaps know how it identifies the new records and is this done on a dataset level or can it be specified by a criteria, for example new records added or changed by an employee or subject?

francoisvv · Answer

Hi Lily,Thanks so much, I played around with it a bit, but I think my use case requires me to be able to dive a bit deeper into the changes itself.&nbsp; &nbsp;Although the accumulation does help, I want to be able to identify not only new / changed records in a dataset level, but by a row level, drilled down to a subject level. So if there is 10 subjects in the first data load, each with 2 records, and with the next load, 5 of the subjects have 2 new records loaded each, each of the new records should be flagged as new, and if 2 of the original subjects have had 3 variable values changed or updated, to be able to flag that record as changed based on all the fields that changed, not just a date.&nbsp; Hope this makes sense!&nbsp; I am busy playing around with flagging with SQL as I can use more defined group by values so will let you know what I manage with that.&nbsp;&nbsp;

Forum Discussion

Comparison of data changes between data loads

6 Replies

Recent Discussions

How to Dynamically Switch Between Multiple Data Models in a Single Dashboard?

How to make a histogram widget

Edit filter dipends on license?

Two calculations displaying separately in a single field

JavaScript does not work for Tabber

Sisense

Support

Resources