Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
Todd
Can you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd
|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of th
e
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:

> I have a table that I am auditing by having a trigger insert the Deleted r
ow
> to an audit table. .Net is calling the same stored procedure to update th
e
> same row in the base table 4 times. The stored procedure subtracts a pass
ed
> in value from a column in the base table. After the code runs, the value
in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of
the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger t
o
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],
[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[Tota
lAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
>

Concurrent updates

I have a table that I am auditing by having a trigger insert the Deleted row
to an audit table. .Net is calling the same stored procedure to update the
same row in the base table 4 times. The stored procedure subtracts a passed
in value from a column in the base table. After the code runs, the value in
the base table is correct, but the audit rows appear to show the the first
update subtracted first two amounts. The other weird thing is the time
stamp, which is generated from a getdate() is exactly the same for two of the
rows.
How tightly is the trigger code tied to the code that causes the trigger to
fire? We have tried playing with isolation levels on the .Net transaction
and this hasn't helped. Have a tripped on a bug, or am I doing something
wrong. Thanks for the help.
ToddCan you post the trigger?
AMB
"pralnwuf" wrote:
> I have a table that I am auditing by having a trigger insert the Deleted row
> to an audit table. .Net is calling the same stored procedure to update the
> same row in the base table 4 times. The stored procedure subtracts a passed
> in value from a column in the base table. After the code runs, the value in
> the base table is correct, but the audit rows appear to show the the first
> update subtracted first two amounts. The other weird thing is the time
> stamp, which is generated from a getdate() is exactly the same for two of the
> rows.
> How tightly is the trigger code tied to the code that causes the trigger to
> fire? We have tried playing with isolation levels on the .Net transaction
> and this hasn't helped. Have a tripped on a bug, or am I doing something
> wrong. Thanks for the help.
> Todd|||/*
* TRIGGER: [EFTAuditTrig]
*/
CREATE TRIGGER EFTAuditTrig ON EFT FOR UPDATE
as
Set NOCOUNT on
INSERT
EFTAudit([EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate])
SELECT
[EFTID],[CreateDate],[SubmitDate],[SubmitedUserID],[TotalAmount],[CreateUserID],[UpdateUserID],[UpdateDate] FROM Deleted
"Alejandro Mesa" wrote:
> Can you post the trigger?
>
> AMB
> "pralnwuf" wrote:
> > I have a table that I am auditing by having a trigger insert the Deleted row
> > to an audit table. .Net is calling the same stored procedure to update the
> > same row in the base table 4 times. The stored procedure subtracts a passed
> > in value from a column in the base table. After the code runs, the value in
> > the base table is correct, but the audit rows appear to show the the first
> > update subtracted first two amounts. The other weird thing is the time
> > stamp, which is generated from a getdate() is exactly the same for two of the
> > rows.
> >
> > How tightly is the trigger code tied to the code that causes the trigger to
> > fire? We have tried playing with isolation levels on the .Net transaction
> > and this hasn't helped. Have a tripped on a bug, or am I doing something
> > wrong. Thanks for the help.
> >
> > Todd

Concurrent Cube Processing

Is it possible to have multiple incremental updates to the same cube or partition? We have imports into the same cube that would occurr at different times as the data from multiple business areas is processed and becomes available to the cube for processing.

Further to this, is it possible to query the cube when processing is taking place? If so, what data is visible? Is the data visible as each row is added to the cube or is it only available one the transaction is 'commited'.

This is quite important for us since it will define the partitioning and processing strategy.

No replies yet! I can't believe no one knows the answer to this...|||I haven't dealt with incremental updates, but (if you have Enterprise) you can have multiple measures/partitions/dimensions etc processing in parallel. (Via the UI you can click "Change Settings" to configure this. Is this what you're asking?

Re: whether you can query the cube while processing is occurring - in Harinath & Quinn's book, I vaguely remember a section where it stated that the existing measure groups stay online until the new one is created, though I can't say I've noticed this behavior (typically when I fully process a cube the old one is immediately thrown out :-( )|||

I was actually wondering if you can have multiple independent processes doing updates to the same cube, but in different partitions, whilst still being available for querying of the existing data.

Here is my scenario:

The company is a financial company with multiple business areas with independent systems. Each day a whole series of batch ETL processes are carried out to consolidate the data into a DW/Reporting Database. This happens at different times for different business areas and the time at which these happen are not predictable nor is the order set. The users want the data asap so we don't want to wait for the last data import to complete before starting to process the cubes/partitions. Hence, we want to create and process partitions on an ad hoc basis and at any time and concurrently with any other processing. I want to know if Analysis Services could handle this without barfing.

|||

If you design your partition strategy correctly such that a batch update of a given partition occurs and then you kick off an incremental update for that paritition and it completes before you need to kick off another incremental update for that same partition, then yes, SSAS can handle multiple partition processing transactions concurrently (in parallel). What I don't believe you can do is kick off multiple incremental update processes agains the SAME partition at the same time...

As for whether data is available or not, that depends on the type of processing you are doing. For incremental updates and for refresh data processing of a given partition, that partition's data remains online during the processing (as the processing is wrapped in a transaction that does not affect the existing partition structure until it commits). However, if you do a rebuild processing action on a partition, that partition (and its associated measure group) becomes unavailable as soon as the transaction starts and it is not available until the transaction completes. Here's a link to a white paper describing the various processing types available for various objects and the ramifications of each:

http://msdn2.microsoft.com/en-us/library/ms345142.aspx

HTH,

Dave Fackler

|||

Hi Dave,

Your last paragraph is not entirely accurate. Full processing can also be done in a transaction (in fact processing a single object is done in an implied transaction) and the old data will be available for querying while the processing is taking place and then at the end of the transaction the old data is swapped out and the new data is then made available. The difference with full processing is that dependant objects will become unprocessed.

So if you full process a dimension, all the cubes that use that dimension will become unprocessed at the end of the transaction (you could manage this by full processing the dimension and relevant cubes in a single transaction).