Update Isolation Level connection string property

I recently came across a new connection string property, Update Isolation Level, mentioned briefly in BOL but with so few accompanying details that I had no idea how it could be used. Here’s what is currently documented:

From http://technet.microsoft.com/en-us/library/ms145488.aspx :

From http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdconnection.connectionstring.aspx :

Luckily Akshai Mirchandani and Marius Dumitru from the dev team were able to provide me more details on it…

Basically, Update Isolation Level is useful when you are using writeback and you are writing values back to multiple cells within the same UPDATE CUBE statement. This point is important: you won’t see a benefit if you’re just writing back to one cell in your UPDATE CUBE statement, or if you’re issuing multiple single-cell UPDATE CUBE statements within a transaction. What it allows you to do is to say that each tuple within the SET clause in the UPDATE CUBE statement doesn’t overlap with any of the others; if that’s true, AS doesn’t need to do as much work at execution time and so the UPDATE CUBE statement runs faster.

Let’s take a look at some examples (I’ve used a simple cube I built based on the Adventure Works database, but it’s not Adventure Works). First of all, the following UPDATE CUBE statement contains two tuple assignments in the SET clause which overlap:

UPDATE CUBE [Sales]
SET
([Measures].[Sales Amount], [Order Date].[Date].&[20010701]
, [Product].[Product].&[1], [Customer].[Country – Customer].&[21383]) = 10,
([Measures].[Sales Amount]) = 10

The first tuple assigns the value 10 to Sales Amount for a particular Date, Product and Customer; the second assigns the same value to a tuple that contains just Sales Amount, and this tuple overlaps with the previous tuple because its value is the aggregation of all Sales Amounts for all Dates, Products and Customers. However, in the following example the two tuples do not overlap because they assign to Sales Amount for different Products:

UPDATE CUBE [Sales]
SET
([Measures].[Sales Amount], [Order Date].[Date].&[20010701]
, [Product].[Product].&[1], [Customer].[Country – Customer].&[21383]) = 2,
([Measures].[Sales Amount], [Order Date].[Date].&[20010701]
, [Product].[Product].&[486], [Customer].[Country – Customer].&[21383]) = 3

The Update Isolation Level connection string property has two possible values:

• Update Isolation Level=1 means that each tuple update is guaranteed to be Isolated, ie the tuples don’t overlap
• Update Isolation Level=2 means that each tuple update is not guaranteed to be Isolated

So, in theory, for the second example above if you set Update Isolation Level=1 in the connection string, the statement should run faster. I couldn’t see any differences in my limited testing but I’m assured that in more complex scenarios there are improvements – how much will depend on the amount of overlap, the structure of the cube and so on.