Processing data transactionally in AMO
There are a few processing commands that leave the tabular model in an unqueryable state until a ProcessRecalc is done at the database level. These commands are ProcessData and ProcessClear. Also, merging partitions in code or script leaves the model unqueryable until a ProcessRecalc occurs. Be mindful of this when implementing your automation for processing. One common processing pattern is to create a new partition, ProcessData on the partition, then ProcessRecalc on the database. If you want your model to be queryable at all times (and you probably do), you must perform this task transactionally.
I will show this transactional processing pattern in AMO on the Hans Rosling project database. Recall the previous demonstration of creating a partition and processing it. Imagine now we want a partition for the year 1802, created and populated in a single transaction. To do this in AMO, we capture the commands in a capture log, then execute the commands in one shot. This technique was illustrated in a code sample by Adrian Dumitrascu in 2006 in a Usenet post (remember Usenet?). The technique still works with tabular. Now let’s look at the code to use it with the ProcessData/ProcessRecalc pattern.
//connect to server, substitute the server/instance name with yours as necessary Server s=new Server(); s.Connect(".\\TABULAR"); //navigate to the Demographic Facts measure group, we must add the partition to the measure group Database db = s.Databases.FindByName("hans_rosling_project"); DataSourceView dsv = db.DataSourceViews.FindByName("Sandbox"); Cube c = db.Cubes.FindByName("Model"); MeasureGroup mg = c.MeasureGroups.FindByName("Demographic Facts"); //enter capture mode s.CaptureXml = true; //log the commands we want to perform - create partition, Process Data, Process Recalc Partition p = mg.Partitions.Add("Partition 1802"); p.Source = new QueryBinding(dsv.DataSourceID, "SELECT [dbo].[DemographicFacts].* FROM [dbo].[DemographicFacts] WHERE ([Year] = 1802)"); p.StorageMode = StorageMode.InMemory; p.Update(UpdateOptions.ExpandFull); p.Process(ProcessType.ProcessData); db.Process(ProcessType.ProcessRecalc); //stop logging s.CaptureXml = false; //execute commands transactionally but not in parallel XmlaResultCollection results = s.ExecuteCaptureLog(true, false);
We’ve seen the code to navigate to the measure group several times before. The new code, which starts logging the commands, begins with the s.CaptureXml line. When we finally go to execute the captured commands by calling ExecuteCaptureLog, we make sure we set the first parameter to true so that the commands are transactional. In this case, it doesn’t really matter if you set the second parameter to true or false – the engine will look at this particular processing job and schedule it in sequence anyway. I set the parameter to false to make it clearer that the code is sequential.
If you are using this code, be sure to leave lots of comments. This particular piece of code is not self-documenting. Nowhere here is there any method or enum called something helpful like “Transaction” or “Batch”. AMO is what it is at this point, so we just need to learn to live with its quirks.
See also: Process Add example. Although Process Add does not require a Recalc, the query binding and the Process Add should happen in a single transaction as well. I will leave improving that example as an exercise for the reader.
From → BI Semantic Model