@SumitSingh - Currently, as you've said, Aggregate and State-Oriented data marts are full rebuilds only when using Compose's data mart automation. Aggregates aren't incrementally refreshed becuase when refreshing an aggregate incrementally, Compose would have to ensure it knows current and prior values of any attribute you are aggregating on which is not possible for any Type 1 attribute (for example you are aggregating by ProductType and ProductType changes for a number of records, Compose would have to know and aggregate the prior ProductType values and the current ProductType values to know which records in the aggregated fact change).
Having said that, you can do the below to work-around this.
- Create your Aggregated Data mart
- Apply a filter using a date / year / month etc. range that encompasses the data set you wish to refresh (typically when rebuilding aggregates we only need to rebuild the last month / 6 months / year etc.).
- I like to use a control table to manage this filter as it allows me to change the values without requiring a code deployment.
- eg - Create table Agg_Filters (FactTable varchar(100), MinFilterValue varchar(50), MaxFilterValue(varchar(50))
- Then the filter would be something like -
- YEAR(${DateColumn}) >= (SELECT MinFilterValue FROM AggFilters WHERE FactTable ='MyFactTable') AND YEAR(${DateColumn}) <= (SELECT MaxFilterValue FROM AggFilters WHERE FactTable = 'MyFactTable')
- Create a copy of the FactTable structure and provide it a new name (MyFactTable_XXX)
- Create a Post-Load step to delete from MyFactTable_XXX for the date range being processed and then INSERT into MyFactTable_XXX select * from MyFactTable
Using this method allows you to leverage the automation in Compose to recreate whatever segment of the aggregation that is required to be reloaded and refresh just that section of the manually managed table
On the first run - have Compose load the entire aggregate (set the MinFilterValue and MaxFilterValue to the entire range of values) . On secondary runs set appropriate Min and Max filter values so you refresh just the segment of the aggregate that you wish to refresh.
Of course this does require knowing which "ranges" of data you wish to refresh each day. If you don't, and you want the data set rebuilt with each run - you can follow the same process and leverage renaming / truncating / cloning (depending on your target DW env.) in the Post-Load step in order to reduce any data downtime for consumers.
Hope this helps!
