Need Data Update Option to Update the Historic Data in Sisense Data Model

Comments

1 comment

  • Avatar
    Rapid Bi

    Hi Naresh,

            Have you looked at accumulative builds? Out of the box this will only process new records into those tables that are marked as accumulate, and each record needs an accumulate key (https://documentation.sisense.com/latest/managing-data/build-settings.htm#gsc.tab=0) . As your historic data may change I'd suggest using an 'update date' as your accumulate key. I'd also split your data into multiple tables. Say everything older than 1 month in historic table(s), and a ThisMonth table for any new/changed records.

    For historic data set the tables to only build on 'changes only' that means if the schema doesn't change then the table's data is just used without any processing.

    You then use custom sql to combine the two sets of data together

    select * from [This Month]

    union

    select * from [Historic] where Id not in (select id from [This Month])

    We also have tools to extract all data from a cube and have used that to create staging tables which is useful if your data has many calculated columns.

    1. Use Sisense to configure calculated fields in historic tables in a staging cube
    2. build the data as usual (can be scheduled)
    3. run the extract tool to copy that data to a Staging DB or CSV file
    4. use historic data in from step 3 in any downstream cubes

    This is faster as the calculation step is only done once and then stored, which is the equivalent of 'Changes Only' in Sisense, but the data from that step can then be shared with multiple cubes.

    Cheers

    Ian

    0
    Comment actions Permalink

Please sign in to leave a comment.