Documentation of snapshot isolation level in data source

Hello all,

I was wondering if there's somewhere good documentation of the semantics of isolation level in a data source in an Analysis Services project and how the different isolation levels affect the processing of Analysis Services database. I don't mean the semantics of differerent isolation levels in SQL server, those are well documented, but what the isolation level actually means in context of an Analysis Services data source.

I'm interested in using snapshot isolation level so that when processing the Analysis Services database all the data for the Analysis Services objects would be got using a snapshot of the SQL Server database. I wonder if this is possible. There's additionally the "Maximum number of connections"-property. I assume that when processing the Analysis Services multiple connections are made to SQL server and if each of those connections use snapshot isolation level, there's no guarantee the snapshots contain the same data.

My actual problem is that the data may change in the SQL server database while processing Analysis Services database. This causes Analysis Services processing failures sometimes, since a value in the database may be found in one dimension, but not in another one.

Any ideas?

r,

J

This doesn't directly answer your question, but it might be easier to support and maintain. Try setting a flag on the records in the data warehouse tables. The flag would indicate whether or not the record was available for upload into SSAS. The flag would be set to 1 (where it was currently set to 0) at the top of the run. Data entering the tables during the run would have the flag set to the default of 0. Your queries would have logic to select just those records with the flag set to 1.

Otherwise, you can follow the steps provided in this KB article to setup/configure/use snapshot isolation:

http://support.microsoft.com/kb/919160

Good luck,

Bryan

Do you need to be a programmer to use SSIS?

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

If i understand your question correctly, the Script Component may be overkill. You could just drag over the OLE DB Dest and Source. Between the two items in the data flow, you could use a Derrived Column transform or Data Conversion based on what the nuts and bolts of your data conversion. It looks like you just want to do a conversion by your example and you could do that in the Data Conversion transform.

-- Brian

|||

Worf wrote:

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

Absolutely not. You have already done the required transformation in your SQL statement above. If you can do your required transforms in the source then invariably that is what you should do - there's no point in having unnecassary transformations in your data-flow. If you wanted to carry out those same transformations in the data-flow then you would require a Data Conversion component (or possibly the Derived Column component) and there is no "programming" (in the common sense of the word) involved in using those.

-Jamie

|||Good call Jaimie.