Is there a approach to delta load to SSAS Tabular model ?

Question

I have a Scenario , Where i refresh the cube (Process FULL or Process DATA) at certain interval, However my goal is to achieve incremental delta load to the partitions by allowing the partition to be refreshed with newly added data . The process ADD method does not work good if there had been an update in the transactional table . It basically duplicates the same Key Value with the new updated Value without replacing the OLD value and eventually the partition ends up with duplicate record. What is the best way to Achieve incremental Load in SSAS TABULAR ?

Answer 1

Hello, this is Gulshan Negi.

Well, accomplishing a steady delta load in SSAS should be possible by utilizing segment handling choices, for example, cycle add, interaction update, or cycle update with add. However, in order to avoid duplicating the same key value with the new updated value, it is recommended to use Process Update with Append whenever there are updates in the transactional table. Change Data Capture (CDC) is another option for applying the changes in the transactional table to the cube. Overall, the best strategy will be determined by the project's particular requirements and constraints.

Thanks

Answer 2

@swabhiman das - Thanks for the question and using MS Q&A paltform.

Yes, there are several approaches to achieve incremental delta load in SSAS Tabular models. Here are some of the common approaches:

Partition switching: Partition switching is a technique that allows you to switch out an old partition and switch in a new partition with the updated data. This approach requires that you have a staging table that contains the updated data, and that you can switch the partition to the new data. This approach is best suited for scenarios where you have a large amount of data that needs to be updated frequently.

DirectQuery mode: DirectQuery mode allows you to query the data directly from the source database, rather than importing it into the SSAS Tabular model. This approach is best suited for scenarios where you have a large amount of data that needs to be updated frequently, and where the data is too large to be imported into the SSAS Tabular model.

Incremental processing: Incremental processing is a technique that allows you to process only the data that has changed since the last processing. This approach requires that you have a way to identify the changed data, such as a timestamp or a flag, and that you can use this information to process only the changed data. This approach is best suited for scenarios where you have a moderate amount of data that needs to be updated frequently.

Merge partitions: Merge partitions is a technique that allows you to merge two or more partitions into a single partition. This approach requires that you have two or more partitions that contain the same data, and that you can merge them into a single partition. This approach is best suited for scenarios where you have a small amount of data that needs to be updated frequently.

The best approach for your scenario will depend on the specific requirements of your data and your SSAS Tabular model. You may need to experiment with different approaches to find the one that works best for your needs.