Supporting Updates and Deletions in DB when building accumulate

[intapiuser]

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 7.4

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.

In order to track the deleted records, you need to create the logic on the data source side that will allow identifying the deleted records. You can add a new column in the database, and whenever the record should be removed, instead of performing DELETE operation, use UPDATE which sets:

• the data columns to null
• Id of the record should remain the same
• Deleted column = True.

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:

1. 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 **NOTE: The partitioning key for the rankdesc/rankasc functions do not currently support int or bigint data types unless you are using the Long Index configuration.  An alternative approach is to convert int and bigint to string values for your partitioning keys.

The SQL should look something like this:

SELECT 
a.ShipID, 
a.StatusChangeDate, 
rankdesc(a.ShipID,a.StatusChangeDate) as RankFilter 
FROM [dbo.ShipFact] a

3. Next, Connect the RankTable to the fact table
   
4. Load the initial data into the ElastiCube. Run an ElastiCube build and select Build Entire

First Data Load – Build Entire

 

4. 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

 

5. 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