Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Update Isolation Level connection string property

leave a comment »

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 :

Important:

When updated cells do not overlap, the Update Isolation Level connection string property can be used to enhance performance for UPDATE CUBE. For more information, see ConnectionString.

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

Update Isolation Level

None

Specifies whether the cells in a single UPDATE CUBE statement are isolated from each other. The default is not isolated.

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.

Written by Chris Webb

April 14, 2009 at 12:07 pm

Posted in Analysis Services

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,240 other followers

%d bloggers like this: