Cursor

I have to perform a number of calculations on a table but these
calculations need intermediate values ( like for example I have to
first calculate one value before I can use it in the next step of the
calcualtion). There are two ways of doing this:
1. Have columns for intermediate values ( it is possible as my
calculations are on a temp tbl) and use update statements. One update
statement for every step in the calculation.
2. Have a cursor, loop through the resultset and do the calculations
like we would in a programming language like C#.
Which one is better? Are updates ( a number of them) faster than a
cursor?
Thanks.John
A set based solution is much faster that cursors, however since you have
not posted DDL+ sample data it is hard to suggest something
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1139492787.074798.293960@.g14g2000cwa.googlegroups.com...
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||Cursors are usually slower than an SQL operation which updates all the rows
in
one go.
I would only use a cursor for this sort of operation if there were any
locking issues.
Are Riksaasen
"John Smith" wrote:

> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
>|||John Smith wrote:
> I have to perform a number of calculations on a table but these
> calculations need intermediate values ( like for example I have to
> first calculate one value before I can use it in the next step of the
> calcualtion). There are two ways of doing this:
>
> 1. Have columns for intermediate values ( it is possible as my
> calculations are on a temp tbl) and use update statements. One update
> statement for every step in the calculation.
>
> 2. Have a cursor, loop through the resultset and do the calculations
> like we would in a programming language like C#.
>
> Which one is better? Are updates ( a number of them) faster than a
> cursor?
>
> Thanks.
Neither of the above if you can avoid them. Create derived columns in
your queries or updates rather than using temp tables. Cursors are the
method of last resort for anything in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Cube Calculations and Report Model Problems

Hello all,

I understand this is an Analysis Services board, but due to the topic, I thought it would be as good of a place as any. I have also cross-posted to the reporting services group.

I've been working with SSRS for a year and a half now, but I am brand new to SSAS. I've gone through tutorials and did as much reading as I could.

The underlying data I am using is similar to the following:

Shift_Start Shift_End Machine_ID Breakdown_Min ... Quality_Pct
1/1/2005 7am 1/1/2005 5pm 535 120 .55

As you can tell, we have one record for each shift. This stores breakdown minutes (as well as many other additive measures). As it stands now, we've also stored percentages in the table (although this could be removed and calculated within the cube).

Because the percent won't add up across dimensions, I removed that measure and created a calculation. This calculation does the work necessary to come up with the percentages. I have no problems using the calculations in the cube browser.

This is where SSRS comes into play. I created a data source to point to the cube and generated a data model. I then used the Report Builder to create a report based on this model.

My problem is that I cannot see the calculations I defined. As I see it, mabye the report model isn't completely integrated with analysis services cubes yet? Has anyone else noticed this behavior and/or has anyone found a solution? I hope it is as simple as me missing a setting...

Thank you for taking the time...

Regards,

Dan

I hate to break it to you Dan but I think you will need to figure out a different way of doing it... here is some discussion about using Report Designer but I think the same would apply to Report Builder:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64253&SiteID=1

The gist, I believe, is that you can use Calculated Measures but not Calculated Members. The other limitations on the Query, ie only measures on the columns is a real pain too. Oh well maybe it will be fixed in a service pack one of these years...

Sorry 'bout that =/

Cube Calculations and Report Model Problems

Hello all,

I understand this is an Analysis Services board, but due to the topic, I thought it would be as good of a place as any. I have also cross-posted to the reporting services group.

I've been working with SSRS for a year and a half now, but I am brand new to SSAS. I've gone through tutorials and did as much reading as I could.

The underlying data I am using is similar to the following:

Shift_Start Shift_End Machine_ID Breakdown_Min ... Quality_Pct
1/1/2005 7am 1/1/2005 5pm 535 120 .55

As you can tell, we have one record for each shift. This stores breakdown minutes (as well as many other additive measures). As it stands now, we've also stored percentages in the table (although this could be removed and calculated within the cube).

Because the percent won't add up across dimensions, I removed that measure and created a calculation. This calculation does the work necessary to come up with the percentages. I have no problems using the calculations in the cube browser.

This is where SSRS comes into play. I created a data source to point to the cube and generated a data model. I then used the Report Builder to create a report based on this model.

My problem is that I cannot see the calculations I defined. As I see it, mabye the report model isn't completely integrated with analysis services cubes yet? Has anyone else noticed this behavior and/or has anyone found a solution? I hope it is as simple as me missing a setting...

Thank you for taking the time...

Regards,

Dan

I hate to break it to you Dan but I think you will need to figure out a different way of doing it... here is some discussion about using Report Designer but I think the same would apply to Report Builder:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64253&SiteID=1

The gist, I believe, is that you can use Calculated Measures but not Calculated Members. The other limitations on the Query, ie only measures on the columns is a real pain too. Oh well maybe it will be fixed in a service pack one of these years...

Sorry 'bout that =/

Cube calculation error

Upon building a cube in analysis services, i go to the calculations tab and i get an an error on the page... "Unexpected error occurred: Error in the application".!!!!

Thus, i can't do any calculated measures. Anyone, know what this is ?

Thanks LP

Check out http://support.microsoft.com/default.aspx/kb/926421.|||There are 2 DLLs msmdlocal.dll and msmgdsrv.dll in these folders:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
C:\Program Files\Common Files\System\Ole DB

The ones in the PrivateAssemblies folder are older versions so you copy the ones from the OLE DB folder to the PrivateAssemblies folder and the problem goes away.