Not too long ago, we had major issues with one of our tabular models, when measures became so resource intensive, that they crashed the whole server. Needless to say that the admins weren’t too happy about that, so we tried to figure out how to improve. The problem itself was obvious: a complex calculation inside a measure was draining the CPU like there was no tomorrow. Only problem: Our procedures were so bad, that reloading all partitions would have taken ages. We fixed the problem with a calculated column and for us, everything was fine.
As the solution grew older, more data was flowing and the issues started again. Long story short, pumping data into this model was slow as hell and we were all wondering why. After all, refreshing partitions with about a million rows shouldn’t take very long, especially when it’s a simple select. Obviously there was something wrong with calculated columns and so I made a few tests.
So let’s see what happens if we use calculated columns First of all we need a small tabular based on the AdventureWorks DB. One table with four partitions, each of them one year of FactResellerSales, that’s all you need. With the version I’m using that’s about a million rows per partition, so loading the data won’t take long. SSAS will create all files and voilà, your model is ready. Before we start doing anything, let’s do a refresh of a single partition, because that’s where the problems started (I know, the dates will betray me, I forgot that step the first time). What we want to check is, which files have been changed. After all, the tabular model persists everything into multiple files on the disc.
| File | LastWritten | FileSizeKB |
|---|---|---|
| H$ResellerSales (10)$DueDate (23)$(1212).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ExtendedAmount (18)$(1127).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$OrderDate (22)$(1195).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$OrderQuantity (16)$(1093).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ProductKey (14)$(1059).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ProductStandardCost (19)$(1144).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$SalesAmount (21)$(1178).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$SalesOrderNumber (15)$(1076).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ShipDate (24)$(1229).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$TotalProductCost (20)$(1161).tbl | 01.05.2022 03:14 | |
| H$ResellerSales (10)$UnitPrice (17)$(1110).tbl | 01.05.2022 03:14 | |
| ResellerSales (10).tbl | 01.05.2022 03:14 | |
| 0.CryptKey.bin | 29.04.2022 01:02 | 1 |
| metadata.sqlitedb | 01.05.2022 03:14 | 97 |
| H$ResellerSales (10)$DueDate (23)$(1212).tbl\1215.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$DueDate (23)$(1212).tbl\1215.prt\7.H$ResellerSales (10)$DueDate (23).ID_TO_POS.0.idf | 01.05.2022 03:14 | 6 |
| H$ResellerSales (10)$DueDate (23)$(1212).tbl\1215.prt\7.H$ResellerSales (10)$DueDate (23).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$DueDate (23)$(1212).tbl\1215.prt\7.H$ResellerSales (10)$DueDate (23).POS_TO_ID.0.idf | 01.05.2022 03:14 | 6 |
| H$ResellerSales (10)$DueDate (23)$(1212).tbl\1215.prt\7.H$ResellerSales (10)$DueDate (23).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ExtendedAmount (18)$(1127).tbl\1130.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ExtendedAmount (18)$(1127).tbl\1130.prt\7.H$ResellerSales (10)$ExtendedAmount (18).ID_TO_POS.0.idf | 01.05.2022 03:14 | 24 |
| H$ResellerSales (10)$ExtendedAmount (18)$(1127).tbl\1130.prt\7.H$ResellerSales (10)$ExtendedAmount (18).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ExtendedAmount (18)$(1127).tbl\1130.prt\7.H$ResellerSales (10)$ExtendedAmount (18).POS_TO_ID.0.idf | 01.05.2022 03:14 | 24 |
| H$ResellerSales (10)$ExtendedAmount (18)$(1127).tbl\1130.prt\7.H$ResellerSales (10)$ExtendedAmount (18).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$OrderDate (22)$(1195).tbl\1198.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$OrderDate (22)$(1195).tbl\1198.prt\7.H$ResellerSales (10)$OrderDate (22).ID_TO_POS.0.idf | 01.05.2022 03:14 | 6 |
| H$ResellerSales (10)$OrderDate (22)$(1195).tbl\1198.prt\7.H$ResellerSales (10)$OrderDate (22).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$OrderDate (22)$(1195).tbl\1198.prt\7.H$ResellerSales (10)$OrderDate (22).POS_TO_ID.0.idf | 01.05.2022 03:14 | 6 |
| H$ResellerSales (10)$OrderDate (22)$(1195).tbl\1198.prt\7.H$ResellerSales (10)$OrderDate (22).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$OrderQuantity (16)$(1093).tbl\1096.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$OrderQuantity (16)$(1093).tbl\1096.prt\7.H$ResellerSales (10)$OrderQuantity (16).ID_TO_POS.0.idf | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$OrderQuantity (16)$(1093).tbl\1096.prt\7.H$ResellerSales (10)$OrderQuantity (16).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$OrderQuantity (16)$(1093).tbl\1096.prt\7.H$ResellerSales (10)$OrderQuantity (16).POS_TO_ID.0.idf | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$OrderQuantity (16)$(1093).tbl\1096.prt\7.H$ResellerSales (10)$OrderQuantity (16).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ProductKey (14)$(1059).tbl\1062.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ProductKey (14)$(1059).tbl\1062.prt\7.H$ResellerSales (10)$ProductKey (14).ID_TO_POS.0.idf | 01.05.2022 03:14 | 2 |
| H$ResellerSales (10)$ProductKey (14)$(1059).tbl\1062.prt\7.H$ResellerSales (10)$ProductKey (14).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ProductKey (14)$(1059).tbl\1062.prt\7.H$ResellerSales (10)$ProductKey (14).POS_TO_ID.0.idf | 01.05.2022 03:14 | 2 |
| H$ResellerSales (10)$ProductKey (14)$(1059).tbl\1062.prt\7.H$ResellerSales (10)$ProductKey (14).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ProductStandardCost (19)$(1144).tbl\1147.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ProductStandardCost (19)$(1144).tbl\1147.prt\7.H$ResellerSales (10)$ProductStandardCost (19).ID_TO_POS.0.idf | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ProductStandardCost (19)$(1144).tbl\1147.prt\7.H$ResellerSales (10)$ProductStandardCost (19).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ProductStandardCost (19)$(1144).tbl\1147.prt\7.H$ResellerSales (10)$ProductStandardCost (19).POS_TO_ID.0.idf | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ProductStandardCost (19)$(1144).tbl\1147.prt\7.H$ResellerSales (10)$ProductStandardCost (19).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$SalesAmount (21)$(1178).tbl\1181.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$SalesAmount (21)$(1178).tbl\1181.prt\7.H$ResellerSales (10)$SalesAmount (21).ID_TO_POS.0.idf | 01.05.2022 03:14 | 17 |
| H$ResellerSales (10)$SalesAmount (21)$(1178).tbl\1181.prt\7.H$ResellerSales (10)$SalesAmount (21).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$SalesAmount (21)$(1178).tbl\1181.prt\7.H$ResellerSales (10)$SalesAmount (21).POS_TO_ID.0.idf | 01.05.2022 03:14 | 17 |
| H$ResellerSales (10)$SalesAmount (21)$(1178).tbl\1181.prt\7.H$ResellerSales (10)$SalesAmount (21).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$SalesOrderNumber (15)$(1076).tbl\1079.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$SalesOrderNumber (15)$(1076).tbl\1079.prt\7.H$ResellerSales (10)$SalesOrderNumber (15).ID_TO_POS.0.idf | 01.05.2022 03:14 | 2651 |
| H$ResellerSales (10)$SalesOrderNumber (15)$(1076).tbl\1079.prt\7.H$ResellerSales (10)$SalesOrderNumber (15).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$SalesOrderNumber (15)$(1076).tbl\1079.prt\7.H$ResellerSales (10)$SalesOrderNumber (15).POS_TO_ID.0.idf | 01.05.2022 03:14 | 2651 |
| H$ResellerSales (10)$SalesOrderNumber (15)$(1076).tbl\1079.prt\7.H$ResellerSales (10)$SalesOrderNumber (15).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ShipDate (24)$(1229).tbl\1232.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$ShipDate (24)$(1229).tbl\1232.prt\7.H$ResellerSales (10)$ShipDate (24).ID_TO_POS.0.idf | 01.05.2022 03:14 | 6 |
| H$ResellerSales (10)$ShipDate (24)$(1229).tbl\1232.prt\7.H$ResellerSales (10)$ShipDate (24).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$ShipDate (24)$(1229).tbl\1232.prt\7.H$ResellerSales (10)$ShipDate (24).POS_TO_ID.0.idf | 01.05.2022 03:14 | 6 |
| H$ResellerSales (10)$ShipDate (24)$(1229).tbl\1232.prt\7.H$ResellerSales (10)$ShipDate (24).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$TotalProductCost (20)$(1161).tbl\1164.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$TotalProductCost (20)$(1161).tbl\1164.prt\7.H$ResellerSales (10)$TotalProductCost (20).ID_TO_POS.0.idf | 01.05.2022 03:14 | 4 |
| H$ResellerSales (10)$TotalProductCost (20)$(1161).tbl\1164.prt\7.H$ResellerSales (10)$TotalProductCost (20).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$TotalProductCost (20)$(1161).tbl\1164.prt\7.H$ResellerSales (10)$TotalProductCost (20).POS_TO_ID.0.idf | 01.05.2022 03:14 | 4 |
| H$ResellerSales (10)$TotalProductCost (20)$(1161).tbl\1164.prt\7.H$ResellerSales (10)$TotalProductCost (20).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$UnitPrice (17)$(1110).tbl\1113.prt | 01.05.2022 03:14 | |
| H$ResellerSales (10)$UnitPrice (17)$(1110).tbl\1113.prt\7.H$ResellerSales (10)$UnitPrice (17).ID_TO_POS.0.idf | 01.05.2022 03:14 | 5 |
| H$ResellerSales (10)$UnitPrice (17)$(1110).tbl\1113.prt\7.H$ResellerSales (10)$UnitPrice (17).ID_TO_POS.0.idfmeta | 01.05.2022 03:14 | 1 |
| H$ResellerSales (10)$UnitPrice (17)$(1110).tbl\1113.prt\7.H$ResellerSales (10)$UnitPrice (17).POS_TO_ID.0.idf | 01.05.2022 03:14 | 5 |
| H$ResellerSales (10)$UnitPrice (17)$(1110).tbl\1113.prt\7.H$ResellerSales (10)$UnitPrice (17).POS_TO_ID.0.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\25.prt | 01.05.2022 03:14 | |
| ResellerSales (10).tbl\26.prt | 01.05.2022 03:14 | |
| ResellerSales (10).tbl\27.prt | 01.05.2022 03:14 | |
| ResellerSales (10).tbl\28.prt | 01.05.2022 03:14 | |
| ResellerSales (10).tbl\8.ResellerSales (10).DueDate (23).dictionary | 01.05.2022 03:14 | 12 |
| ResellerSales (10).tbl\8.ResellerSales (10).ExtendedAmount (18).dictionary | 01.05.2022 03:14 | 48 |
| ResellerSales (10).tbl\8.ResellerSales (10).OrderDate (22).dictionary | 01.05.2022 03:14 | 12 |
| ResellerSales (10).tbl\8.ResellerSales (10).OrderQuantity (16).dictionary | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\8.ResellerSales (10).ProductKey (14).dictionary | 01.05.2022 03:14 | 2 |
| ResellerSales (10).tbl\8.ResellerSales (10).ProductStandardCost (19).dictionary | 01.05.2022 03:14 | 2 |
| ResellerSales (10).tbl\8.ResellerSales (10).SalesAmount (21).dictionary | 01.05.2022 03:14 | 33 |
| ResellerSales (10).tbl\8.ResellerSales (10).SalesOrderNumber (15).dictionary | 01.05.2022 03:14 | 7890 |
| ResellerSales (10).tbl\8.ResellerSales (10).ShipDate (24).dictionary | 01.05.2022 03:14 | 12 |
| ResellerSales (10).tbl\8.ResellerSales (10).TotalProductCost (20).dictionary | 01.05.2022 03:14 | 7 |
| ResellerSales (10).tbl\8.ResellerSales (10).UnitPrice (17).dictionary | 01.05.2022 03:14 | 10 |
| ResellerSales (10).tbl\25.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).0.idf | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\25.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).0.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\26.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).1.idf | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\26.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).1.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\27.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).2.idf | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\27.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).2.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).DueDate (23).3.idf | 01.05.2022 03:14 | 1291 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).DueDate (23).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ExtendedAmount (18).3.idf | 01.05.2022 03:14 | 223 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ExtendedAmount (18).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).OrderDate (22).3.idf | 01.05.2022 03:14 | 1291 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).OrderDate (22).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).OrderQuantity (16).3.idf | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).OrderQuantity (16).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ProductKey (14).3.idf | 01.05.2022 03:14 | 219 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ProductKey (14).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ProductStandardCost (19).3.idf | 01.05.2022 03:14 | 35 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ProductStandardCost (19).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).3.idf | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).SalesAmount (21).3.idf | 01.05.2022 03:14 | 170 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).SalesAmount (21).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).SalesOrderNumber (15).3.idf | 01.05.2022 03:14 | 3081 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).SalesOrderNumber (15).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ShipDate (24).3.idf | 01.05.2022 03:14 | 1291 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).ShipDate (24).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).TotalProductCost (20).3.idf | 01.05.2022 03:14 | 44 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).TotalProductCost (20).3.idfmeta | 01.05.2022 03:14 | 1 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).UnitPrice (17).3.idf | 01.05.2022 03:14 | 192 |
| ResellerSales (10).tbl\28.prt\8.ResellerSales (10).UnitPrice (17).3.idfmeta | 01.05.2022 03:14 | 1 |
So these are a lot of files that have been changed, but let’s split that into categories. First of all, the system hierarchies – starting with H$ – have all been rebuilt. This shouldn’t be too much of a surprise, since the system needs to know which values have been added. Same is true for the dictionaries. What surprised me a bit was that although only one partition was loaded (28.prt), all row numbers were updated. The last part is the partition itself. The above is what the regular processing of a single partition will look like.
So let’s proceed to adding a calculated column. Personally I’m using PowerShell to add them, but use whatever you like best. This is the code I’m using:
Import-Module SqlServer;
$server = New-Object Microsoft.AnalysisServices.Tabular.Server;
$server.Connect("APOC\DEV_TAB");
$database = $server.Databases["CalculatedColumnTest"];
$model = $database.Model;
$table = $model.Tables["ResellerSales"];
$table.Columns.Add(
(New-Object Microsoft.AnalysisServices.Tabular.CalculatedColumn -Property @{
Name = "CalcCol";
Expression = "[OrderQuantity] * [UnitPrice]";
})
);
$model.SaveChanges();Now that we added the column, let’s see which files have been changed.
| File | LastWritten | FileSizeKB |
|---|---|---|
| metadata.sqlitedb | 29.04.2022 01:32 | 90 |
| ResellerSales (10).tbl\25.prt | 29.04.2022 01:32 | |
| ResellerSales (10).tbl\26.prt | 29.04.2022 01:32 | |
| ResellerSales (10).tbl\27.prt | 29.04.2022 01:32 | |
| ResellerSales (10).tbl\28.prt | 29.04.2022 01:32 | |
| ResellerSales (10).tbl\25.prt\2.ResellerSales (10).CalcCol (533).0.idf | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\25.prt\2.ResellerSales (10).CalcCol (533).0.idfmeta | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\26.prt\2.ResellerSales (10).CalcCol (533).1.idf | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\26.prt\2.ResellerSales (10).CalcCol (533).1.idfmeta | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\27.prt\2.ResellerSales (10).CalcCol (533).2.idf | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\27.prt\2.ResellerSales (10).CalcCol (533).2.idfmeta | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\28.prt\2.ResellerSales (10).CalcCol (533).3.idf | 29.04.2022 01:32 | 1 |
| ResellerSales (10).tbl\28.prt\2.ResellerSales (10).CalcCol (533).3.idfmeta | 29.04.2022 01:32 | 1 |
This is pretty much what you would expect, given that the column has only been created and no refresh has been done. The folders have been changed and the files for the calculated column have been created. You can see they’re still empty, since their file size is 1. Of course we don’t want an empty column, so let’s refresh that thing:
$model.RequestRefresh("Calculate");
$model.SaveChanges();
The outcome we get now is not really surprising, as well.
| File | LastWritten | FileSizeKB |
|---|---|---|
| H$ResellerSales (10)$CalcCol (533)$(554).tbl | 29.04.2022 01:54 | |
| ResellerSales (10).tbl | 29.04.2022 01:54 | |
| metadata.sqlitedb | 29.04.2022 01:54 | 93 |
| H$ResellerSales (10)$CalcCol (533)$(554).tbl\557.prt | 29.04.2022 01:54 | |
| H$ResellerSales (10)$CalcCol (533)$(554).tbl\557.prt\0.H$ResellerSales (10)$CalcCol (533).ID_TO_POS.0.idf | 29.04.2022 01:54 | 26 |
| H$ResellerSales (10)$CalcCol (533)$(554).tbl\557.prt\0.H$ResellerSales (10)$CalcCol (533).ID_TO_POS.0.idfmeta | 29.04.2022 01:54 | 1 |
| H$ResellerSales (10)$CalcCol (533)$(554).tbl\557.prt\0.H$ResellerSales (10)$CalcCol (533).POS_TO_ID.0.idf | 29.04.2022 01:54 | 26 |
| H$ResellerSales (10)$CalcCol (533)$(554).tbl\557.prt\0.H$ResellerSales (10)$CalcCol (533).POS_TO_ID.0.idfmeta | 29.04.2022 01:54 | 1 |
| ResellerSales (10).tbl\25.prt | 29.04.2022 01:54 | |
| ResellerSales (10).tbl\26.prt | 29.04.2022 01:54 | |
| ResellerSales (10).tbl\27.prt | 29.04.2022 01:54 | |
| ResellerSales (10).tbl\28.prt | 29.04.2022 01:54 | |
| ResellerSales (10).tbl\4.ResellerSales (10).CalcCol (533).dictionary | 29.04.2022 01:54 | 51 |
| ResellerSales (10).tbl\25.prt\4.ResellerSales (10).CalcCol (533).0.idf | 29.04.2022 01:54 | 18 |
| ResellerSales (10).tbl\25.prt\4.ResellerSales (10).CalcCol (533).0.idfmeta | 29.04.2022 01:54 | 1 |
| ResellerSales (10).tbl\26.prt\4.ResellerSales (10).CalcCol (533).1.idf | 29.04.2022 01:54 | 207 |
| ResellerSales (10).tbl\26.prt\4.ResellerSales (10).CalcCol (533).1.idfmeta | 29.04.2022 01:54 | 1 |
| ResellerSales (10).tbl\27.prt\4.ResellerSales (10).CalcCol (533).2.idf | 29.04.2022 01:54 | 218 |
| ResellerSales (10).tbl\27.prt\4.ResellerSales (10).CalcCol (533).2.idfmeta | 29.04.2022 01:54 | 1 |
| ResellerSales (10).tbl\28.prt\4.ResellerSales (10).CalcCol (533).3.idf | 29.04.2022 01:54 | 223 |
| ResellerSales (10).tbl\28.prt\4.ResellerSales (10).CalcCol (533).3.idfmeta | 29.04.2022 01:54 | 1 |
The hierarchy has been updated for the new column and the column itself has been filled on all four partitions. Now onwards to the final step. If you model is very large, you will try everything to keep the load as short as possible without touching too many tables or partitions. This is why partitions exist and this is how our initial scenario started. So let’s go and do exactly that, refresh a single partition.
$table.Partitions["2013"].RequestRefresh("Full");
$model.SaveChanges();
What happens now was a surprise to me (well I guess not only to me, otherwise the problem would’ve never arisen). Let’s see which files have been changed now. For easier navigation I have added a column to allow filtering.
| File | LastWritten | FileSizeKB | Partition |
|---|---|---|---|
| H$ResellerSales (10)$CalcCol (533)$(763).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$DueDate (23)$(729).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ExtendedAmount (18)$(644).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$OrderDate (22)$(712).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$OrderQuantity (16)$(610).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ProductKey (14)$(576).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ProductStandardCost (19)$(661).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$SalesAmount (21)$(695).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$SalesOrderNumber (15)$(593).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ShipDate (24)$(746).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$TotalProductCost (20)$(678).tbl | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$UnitPrice (17)$(627).tbl | 29.04.2022 01:57 | Hierarchy | |
| ResellerSales (10).tbl | 29.04.2022 01:57 | Hierarchy | |
| metadata.sqlitedb | 29.04.2022 01:57 | 95 | Hierarchy |
| H$ResellerSales (10)$CalcCol (533)$(763).tbl\766.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$CalcCol (533)$(763).tbl\766.prt\3.H$ResellerSales (10)$CalcCol (533).ID_TO_POS.0.idf | 29.04.2022 01:57 | 26 | Hierarchy |
| H$ResellerSales (10)$CalcCol (533)$(763).tbl\766.prt\3.H$ResellerSales (10)$CalcCol (533).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$CalcCol (533)$(763).tbl\766.prt\3.H$ResellerSales (10)$CalcCol (533).POS_TO_ID.0.idf | 29.04.2022 01:57 | 26 | Hierarchy |
| H$ResellerSales (10)$CalcCol (533)$(763).tbl\766.prt\3.H$ResellerSales (10)$CalcCol (533).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$DueDate (23)$(729).tbl\732.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$DueDate (23)$(729).tbl\732.prt\3.H$ResellerSales (10)$DueDate (23).ID_TO_POS.0.idf | 29.04.2022 01:57 | 6 | Hierarchy |
| H$ResellerSales (10)$DueDate (23)$(729).tbl\732.prt\3.H$ResellerSales (10)$DueDate (23).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$DueDate (23)$(729).tbl\732.prt\3.H$ResellerSales (10)$DueDate (23).POS_TO_ID.0.idf | 29.04.2022 01:57 | 6 | Hierarchy |
| H$ResellerSales (10)$DueDate (23)$(729).tbl\732.prt\3.H$ResellerSales (10)$DueDate (23).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ExtendedAmount (18)$(644).tbl\647.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ExtendedAmount (18)$(644).tbl\647.prt\3.H$ResellerSales (10)$ExtendedAmount (18).ID_TO_POS.0.idf | 29.04.2022 01:57 | 24 | Hierarchy |
| H$ResellerSales (10)$ExtendedAmount (18)$(644).tbl\647.prt\3.H$ResellerSales (10)$ExtendedAmount (18).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ExtendedAmount (18)$(644).tbl\647.prt\3.H$ResellerSales (10)$ExtendedAmount (18).POS_TO_ID.0.idf | 29.04.2022 01:57 | 24 | Hierarchy |
| H$ResellerSales (10)$ExtendedAmount (18)$(644).tbl\647.prt\3.H$ResellerSales (10)$ExtendedAmount (18).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$OrderDate (22)$(712).tbl\715.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$OrderDate (22)$(712).tbl\715.prt\3.H$ResellerSales (10)$OrderDate (22).ID_TO_POS.0.idf | 29.04.2022 01:57 | 6 | Hierarchy |
| H$ResellerSales (10)$OrderDate (22)$(712).tbl\715.prt\3.H$ResellerSales (10)$OrderDate (22).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$OrderDate (22)$(712).tbl\715.prt\3.H$ResellerSales (10)$OrderDate (22).POS_TO_ID.0.idf | 29.04.2022 01:57 | 6 | Hierarchy |
| H$ResellerSales (10)$OrderDate (22)$(712).tbl\715.prt\3.H$ResellerSales (10)$OrderDate (22).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$OrderQuantity (16)$(610).tbl\613.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$OrderQuantity (16)$(610).tbl\613.prt\3.H$ResellerSales (10)$OrderQuantity (16).ID_TO_POS.0.idf | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$OrderQuantity (16)$(610).tbl\613.prt\3.H$ResellerSales (10)$OrderQuantity (16).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$OrderQuantity (16)$(610).tbl\613.prt\3.H$ResellerSales (10)$OrderQuantity (16).POS_TO_ID.0.idf | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$OrderQuantity (16)$(610).tbl\613.prt\3.H$ResellerSales (10)$OrderQuantity (16).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ProductKey (14)$(576).tbl\579.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ProductKey (14)$(576).tbl\579.prt\3.H$ResellerSales (10)$ProductKey (14).ID_TO_POS.0.idf | 29.04.2022 01:57 | 2 | Hierarchy |
| H$ResellerSales (10)$ProductKey (14)$(576).tbl\579.prt\3.H$ResellerSales (10)$ProductKey (14).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ProductKey (14)$(576).tbl\579.prt\3.H$ResellerSales (10)$ProductKey (14).POS_TO_ID.0.idf | 29.04.2022 01:57 | 2 | Hierarchy |
| H$ResellerSales (10)$ProductKey (14)$(576).tbl\579.prt\3.H$ResellerSales (10)$ProductKey (14).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ProductStandardCost (19)$(661).tbl\664.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ProductStandardCost (19)$(661).tbl\664.prt\3.H$ResellerSales (10)$ProductStandardCost (19).ID_TO_POS.0.idf | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ProductStandardCost (19)$(661).tbl\664.prt\3.H$ResellerSales (10)$ProductStandardCost (19).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ProductStandardCost (19)$(661).tbl\664.prt\3.H$ResellerSales (10)$ProductStandardCost (19).POS_TO_ID.0.idf | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ProductStandardCost (19)$(661).tbl\664.prt\3.H$ResellerSales (10)$ProductStandardCost (19).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$SalesAmount (21)$(695).tbl\698.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$SalesAmount (21)$(695).tbl\698.prt\3.H$ResellerSales (10)$SalesAmount (21).ID_TO_POS.0.idf | 29.04.2022 01:57 | 17 | Hierarchy |
| H$ResellerSales (10)$SalesAmount (21)$(695).tbl\698.prt\3.H$ResellerSales (10)$SalesAmount (21).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$SalesAmount (21)$(695).tbl\698.prt\3.H$ResellerSales (10)$SalesAmount (21).POS_TO_ID.0.idf | 29.04.2022 01:57 | 17 | Hierarchy |
| H$ResellerSales (10)$SalesAmount (21)$(695).tbl\698.prt\3.H$ResellerSales (10)$SalesAmount (21).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$SalesOrderNumber (15)$(593).tbl\596.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$SalesOrderNumber (15)$(593).tbl\596.prt\3.H$ResellerSales (10)$SalesOrderNumber (15).ID_TO_POS.0.idf | 29.04.2022 01:57 | 2651 | Hierarchy |
| H$ResellerSales (10)$SalesOrderNumber (15)$(593).tbl\596.prt\3.H$ResellerSales (10)$SalesOrderNumber (15).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$SalesOrderNumber (15)$(593).tbl\596.prt\3.H$ResellerSales (10)$SalesOrderNumber (15).POS_TO_ID.0.idf | 29.04.2022 01:57 | 2651 | Hierarchy |
| H$ResellerSales (10)$SalesOrderNumber (15)$(593).tbl\596.prt\3.H$ResellerSales (10)$SalesOrderNumber (15).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ShipDate (24)$(746).tbl\749.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$ShipDate (24)$(746).tbl\749.prt\3.H$ResellerSales (10)$ShipDate (24).ID_TO_POS.0.idf | 29.04.2022 01:57 | 6 | Hierarchy |
| H$ResellerSales (10)$ShipDate (24)$(746).tbl\749.prt\3.H$ResellerSales (10)$ShipDate (24).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$ShipDate (24)$(746).tbl\749.prt\3.H$ResellerSales (10)$ShipDate (24).POS_TO_ID.0.idf | 29.04.2022 01:57 | 6 | Hierarchy |
| H$ResellerSales (10)$ShipDate (24)$(746).tbl\749.prt\3.H$ResellerSales (10)$ShipDate (24).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$TotalProductCost (20)$(678).tbl\681.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$TotalProductCost (20)$(678).tbl\681.prt\3.H$ResellerSales (10)$TotalProductCost (20).ID_TO_POS.0.idf | 29.04.2022 01:57 | 4 | Hierarchy |
| H$ResellerSales (10)$TotalProductCost (20)$(678).tbl\681.prt\3.H$ResellerSales (10)$TotalProductCost (20).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$TotalProductCost (20)$(678).tbl\681.prt\3.H$ResellerSales (10)$TotalProductCost (20).POS_TO_ID.0.idf | 29.04.2022 01:57 | 4 | Hierarchy |
| H$ResellerSales (10)$TotalProductCost (20)$(678).tbl\681.prt\3.H$ResellerSales (10)$TotalProductCost (20).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$UnitPrice (17)$(627).tbl\630.prt | 29.04.2022 01:57 | Hierarchy | |
| H$ResellerSales (10)$UnitPrice (17)$(627).tbl\630.prt\3.H$ResellerSales (10)$UnitPrice (17).ID_TO_POS.0.idf | 29.04.2022 01:57 | 5 | Hierarchy |
| H$ResellerSales (10)$UnitPrice (17)$(627).tbl\630.prt\3.H$ResellerSales (10)$UnitPrice (17).ID_TO_POS.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| H$ResellerSales (10)$UnitPrice (17)$(627).tbl\630.prt\3.H$ResellerSales (10)$UnitPrice (17).POS_TO_ID.0.idf | 29.04.2022 01:57 | 5 | Hierarchy |
| H$ResellerSales (10)$UnitPrice (17)$(627).tbl\630.prt\3.H$ResellerSales (10)$UnitPrice (17).POS_TO_ID.0.idfmeta | 29.04.2022 01:57 | 1 | Hierarchy |
| ResellerSales (10).tbl\25.prt | 29.04.2022 01:57 | 25.prt (2010) | |
| ResellerSales (10).tbl\26.prt | 29.04.2022 01:57 | 26.prt (2011) | |
| ResellerSales (10).tbl\27.prt | 29.04.2022 01:57 | 27.prt (2012) | |
| ResellerSales (10).tbl\28.prt | 29.04.2022 01:57 | 28.prt (2013) | |
| ResellerSales (10).tbl\5.ResellerSales (10).CalcCol (533).dictionary | 29.04.2022 01:57 | 51 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).DueDate (23).dictionary | 29.04.2022 01:57 | 12 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).ExtendedAmount (18).dictionary | 29.04.2022 01:57 | 48 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).OrderDate (22).dictionary | 29.04.2022 01:57 | 12 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).OrderQuantity (16).dictionary | 29.04.2022 01:57 | 1 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).ProductKey (14).dictionary | 29.04.2022 01:57 | 2 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).ProductStandardCost (19).dictionary | 29.04.2022 01:57 | 2 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).SalesAmount (21).dictionary | 29.04.2022 01:57 | 33 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).SalesOrderNumber (15).dictionary | 29.04.2022 01:57 | 7889 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).ShipDate (24).dictionary | 29.04.2022 01:57 | 12 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).TotalProductCost (20).dictionary | 29.04.2022 01:57 | 7 | Dictionary |
| ResellerSales (10).tbl\5.ResellerSales (10).UnitPrice (17).dictionary | 29.04.2022 01:57 | 10 | Dictionary |
| ResellerSales (10).tbl\25.prt\5.ResellerSales (10).CalcCol (533).0.idf | 29.04.2022 01:57 | 18 | 25.prt (2010) |
| ResellerSales (10).tbl\25.prt\5.ResellerSales (10).CalcCol (533).0.idfmeta | 29.04.2022 01:57 | 1 | 25.prt (2010) |
| ResellerSales (10).tbl\25.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).0.idf | 29.04.2022 01:57 | 1 | 25.prt (2010) |
| ResellerSales (10).tbl\25.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).0.idfmeta | 29.04.2022 01:57 | 1 | 25.prt (2010) |
| ResellerSales (10).tbl\26.prt\5.ResellerSales (10).CalcCol (533).1.idf | 29.04.2022 01:57 | 207 | 26.prt (2011) |
| ResellerSales (10).tbl\26.prt\5.ResellerSales (10).CalcCol (533).1.idfmeta | 29.04.2022 01:57 | 1 | 26.prt (2011) |
| ResellerSales (10).tbl\26.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).1.idf | 29.04.2022 01:57 | 1 | 26.prt (2011) |
| ResellerSales (10).tbl\26.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).1.idfmeta | 29.04.2022 01:57 | 1 | 26.prt (2011) |
| ResellerSales (10).tbl\27.prt\5.ResellerSales (10).CalcCol (533).2.idf | 29.04.2022 01:57 | 218 | 27.prt (2012) |
| ResellerSales (10).tbl\27.prt\5.ResellerSales (10).CalcCol (533).2.idfmeta | 29.04.2022 01:57 | 1 | 27.prt (2012) |
| ResellerSales (10).tbl\27.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).2.idf | 29.04.2022 01:57 | 1 | 27.prt (2012) |
| ResellerSales (10).tbl\27.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).2.idfmeta | 29.04.2022 01:57 | 1 | 27.prt (2012) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).CalcCol (533).3.idf | 29.04.2022 01:57 | 223 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).CalcCol (533).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).DueDate (23).3.idf | 29.04.2022 01:57 | 1807 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).DueDate (23).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ExtendedAmount (18).3.idf | 29.04.2022 01:57 | 223 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ExtendedAmount (18).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).OrderDate (22).3.idf | 29.04.2022 01:57 | 1807 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).OrderDate (22).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).OrderQuantity (16).3.idf | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).OrderQuantity (16).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ProductKey (14).3.idf | 29.04.2022 01:57 | 219 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ProductKey (14).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ProductStandardCost (19).3.idf | 29.04.2022 01:57 | 35 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ProductStandardCost (19).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).3.idf | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).RowNumber 2662979B 1795 4F74 8F37 6A1BA8059B61 (11).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).SalesAmount (21).3.idf | 29.04.2022 01:57 | 170 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).SalesAmount (21).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).SalesOrderNumber (15).3.idf | 29.04.2022 01:57 | 3081 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).SalesOrderNumber (15).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ShipDate (24).3.idf | 29.04.2022 01:57 | 1807 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).ShipDate (24).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).TotalProductCost (20).3.idf | 29.04.2022 01:57 | 44 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).TotalProductCost (20).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).UnitPrice (17).3.idf | 29.04.2022 01:57 | 192 | 28.prt (2013) |
| ResellerSales (10).tbl\28.prt\5.ResellerSales (10).UnitPrice (17).3.idfmeta | 29.04.2022 01:57 | 1 | 28.prt (2013) |
Obviously the partition we requested the refresh for (28.prt = 2013) was fully loaded, no surprise here. Additionally, all hierarchies and all row numbers were rebuilt. We saw that already, it’s required since there is new data incoming. Now to the costly part. As you can see, not only were the row numbers rebuilt for each partition, but so was the calculated column. In my opinion a strange behaviour. Yes, the formulas might contain references to data that has just been loaded, but it’s kind of strange that the engine doesn’t do a probe first, like checking whether the calculated column contains aggreation formulas.
Fact is, the more complex your calculated column gets and the more partitions you have, the longer it will take to perform a refresh, even if only a single partition one. At the end of the day you’re compromising a mechanic that you thought would give you an advantage in terms of refresh speed.
Does this mean that calculated columns are bad and should be avoided? My personal opinion: it depends, but rather yes. In the end it boils down to what you’re trying to achieve.
- Do you need to change the model without reloading partitions? Then yes, this might be a good way to go, maybe even just for a while.
- Is your model small enough and loads quickly? No problem, but then again, you might as well change the SQL and reload the whole thing, since it’s so fast.
What do you think about calculated columns. Are they a blessing of heaven or are they a product of hell? Feel free to leave me a comment.