Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.sql

Handling Transaction

hi friends,

I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.

When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.

Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.

I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...

JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,

thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.

But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?

Jake

Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.

Handling errors when using ExecuteNonQuery

Let's say that I have a StringCollection named scripts that contains a bunch of DDL statements I want to execute. So I execute the statements using ExecuteNonQuery as follows:

ExecuteNonQuery(scripts)

If one of the DDL statements contained in the StringCollection returns an error, it means that all subsequent statements will not be executed. Even if I use try catch as follows:

try

{

db.ExecuteNonQuery(scripts);

}

catch (Exception ex)

{

Console.WriteLine("Error while recreating objects. See errorlog for more details\r\n");

tw.Write("Error while recreating objects: {0}\r\n", ex.GetBaseException().ToString());

tw.Flush();

}

Is there any way to keep executing the subsequent statements in the StringCollection even if some of the DDL statements return an error?

If you send them one at a time, handle the error that occurs, then send the next script, you could then continue after an error. DDL errors, however, always make me want to stop, because DML usually depends on the DDL being correct.

|||

Thanks for your answer Allen,

I tried what you suggested but I noticed that none of my stored procs kept their settings. For example:

I script my proc as follows:

if (u.Type == "StoredProcedure")

{

ScriptingOptions so = new ScriptingOptions();

StoredProcedure o = (StoredProcedure)svr.GetSmoObject(u);

so.SchemaQualify = true;

so.SchemaQualifyForeignKeysReferences = true;

so.Permissions = true;

so.ExtendedProperties = true;

so.EnforceScriptingOptions = true;

so.NoCollation = true;

foreach (string os in o.Script(so))

{

sc.Add(os.ToString());

}

}

If I try to execute my statement as follows:

foreach (string s in scripts)

{

db.ExecuteNonQuery(s);

}

Then two statementswill be sent to SQL Server and the ANSI_NULL Settings will not necessarily be maintained:

first statement:

SET ANSI_NULLS ON

Second statement:

CREATE PROCEDURE...

The problem being that if the setting for my second connection is SET ANSI_NULLS OFF then my proc will be created with the wrong settings.