Introduction
When using the "accumulate by field" (see below), Sisense will only import "new" records based on the selected field.
The logic will be similar to adding a Where clause to the SQL Query: "WHERE Date > Last Value"
In some cases, data bases support deletions and updates, usually, updates will be inserted again with a new timestamp.
This article describes how to manage those records in your Elasticbue.
The Accumulation build option can only be applied to the entire table and specific integer or date fields., you can find more information on accumulative build options here.
Environment
Sisense version 6.6
Resolution
This implementation requires you to manipulate the manual query option on the data table. You can find more information on using Custom SQL to Import Data here.
An initial build is required to import our first set of data. This initial data will be used as our baseline to determine if additional records are new, deleted, or updated. The first build will use the Full build option, then subsequent will use the Accumulation option.
Two filters, Rank and Deleted, will be added to every dashboard that will filter out the deleted and updated records.
Example initial table -
** Important Assumption **
The StatusChangeDate of records is correctly set in the first insertion – no future/ fault dates can be set (original record should not have a bad date)
Steps to implement:
- From the ElastiCube Manager, create the Rank filter by selecting the Custom SQL table option. The ranking will be descending, so that the most recent record will always be ranked 1.
The SQL should look something like this:
SELECT
a.ShipID,
a.StatusChangeDate,
rankdesc(a.ShipID,a.StatusChangeDate) RankFilter
FROM [dbo.ShipFact] a
- Next, Connect the RankTable to the fact table
- Load the initial data into the ElastiCube. Run an ElastiCube build and select Build Entire
First Data Load – Build Entire
- Subsequently every build after should use the Accumulation option. You can either use the accumulate by field option and select StatusChangeDate as your field or accumulate the entire table. If you choose to accumulate the entire table it is recommended to update your manual query to only bring in the new records from your source.
Second Load – Build Accumulate
- Every Dashboard should contain filters on both Rank and Deleted. Rank should always be equal to 1 and Deleted should filter out 1.
Dashboards will filter out deleted rows (filter our Deleted=1) and will only show date of Rank=1 rows
