March 13, 2024 at 2:42 pm
We're running SQL Server 2017 Enterprise. We have a Tabular Data Model. One TDM table is very large. I want to reprocess the partition on that TDM table with only the data that has changed in the underlying database table - so, add new rows into the TDM, and update rows that have changed. How can i best do that? Currently I'm performing a full reprocess refresh on this large table which takes a long time and only a small part of the data has actually changed.
In the underlying database table, there is a LoadedOn column that changes when either a new row is inserted, or an existing row is changed. I'd like to have the TDM table partition select rows from the database table where the LoadedOn value in the database is greater than the maximum loaded on value in the TDM table. I can't work out how to query the max LoadedOn value in the table in the TDM in order to know which rows to select out of the database table.
March 13, 2024 at 3:53 pm
Analysis Services partitions have to be refreshed as a unit, meaning that you cannot selectively update certain rows of a partition. If your large cube only has one partition for a given table/entity, your first step should be to create partitions so that you can perform an incremental refresh of certain partitions in the future.
The "LoadedOn" date you mention might be a good attribute to use to identify which partitions have to be refreshed, if you want to do that programmatically...but it's not a good partition key. You'll need a date that doesn't change after the initial insert as a partition key (i.e. to determine what partition the data needs to go to.
Here's a reference you can use to get started: https://learn.microsoft.com/en-us/analysis-services/tabular-models/partitions-ssas-tabular?view=asallproducts-allversions
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply