Cursor Process Coding Problem

Hi guys,

It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @.@.FETCH_STATUS = 0 to @.@.FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

WHILE @.@.FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancialsyour update needs to be inside the WHILE Loop

But aren't you worried about datatypes...and what's wrong with nulls annyway

(Here we go again)|||Ok,

I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?

Here is the updated code:

DECLARE @.FieldName char (25)

DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

update rpt_Scr_B0000_MiniFinancials
set @.FieldName = 0
where @.FieldName is null

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END|||Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
GO

INSERT INTO myTable99(Col1,Col2,Col3)
SELECT 1 , null, '2006-01-01' UNION ALL
SELECT null, 'b' , '2006-01-02' UNION ALL
SELECT 3 , 'c' , null
GO

SELECT * FROM myTable99
GO

DECLARE @.sql varchar(8000), @.collist varchar(8000), @.TABLE_NAME sysname

SET @.TABLE_NAME = 'myTable99'

SELECT @.collist = COALESCE(@.collist+', ','') + COLUMN_NAME + ' = '
+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ') THEN ''''+'0'+'''' ELSE '0' END
+ ' ELSE ' + COLUMN_NAME + ' END'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @.TABLE_NAME

SELECT @.sql = 'UPDATE ' + @.TABLE_NAME + ' SET ' + @.collist

SELECT @.sql

EXEC(@.sql)

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||OK,

I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.

Dave|||We resolved it in-house. Here is the answer! Thanks again.

================================================== =====

DECLARE @.FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'

open cursor_update_rpt_Scr_B0000_MiniFinancials

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

WHILE @.@.FETCH_STATUS = 0
BEGIN

execute('
update rpt_Scr_B0000_MiniFinancials
set '+@.FieldName+' = 0
where '+@.FieldName+' is null
')

FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @.FieldName

END

CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials|||Well use mine anyway and blow their minds...cursors...ech

Besides mine will be faster|||WOuld setting default = 0 on the table structure accomplish what you want.|||I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?

Davesql

current row no?

i know there is a function to count number of rows in a group, but is there anyway of numbering each row?

So that I can use =Last(ReportIems("norow") to find the last row on the page?

Thanks

Try putting this into your detail row (where GroupName is the name of your group):

=RowNumber("GroupName")

Hope this helps.

Jarret

|||Thank you SO much.... I used this to a Page no for each group ;)

current row no?

i know there is a function to count number of rows in a group, but is there anyway of numbering each row?

So that I can use =Last(ReportIems("norow") to find the last row on the page?

Thanks

Try putting this into your detail row (where GroupName is the name of your group):

=RowNumber("GroupName")

Hope this helps.

Jarret

|||Thank you SO much.... I used this to a Page no for each group ;)

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Cube/Rollup without Group By?

MS has been nice enough to add the Cube and Rollup operators so I can
have totals with my results.

But out of shear meanness they won't let me use them unless I use a
Group By clause in my select. I have a proc with 25 fields, and have
no desire to Group By anything- when a certain field changes, I just
want a row with the total of that field.

Is this possible?

Thanks,

BurtNot totally sure what you are asking, but maybe COMPUTE will do what you need.

SELECT col1, col2
from t1
COMPUTE SUM(col1)

Non-standard SQL I believe but occasionally useful.

Mike John

"Burt" <burt_5920@.yahoo.com> wrote in message news:19e5f39f.0309190842.50760c96@.posting.google.c om...
> MS has been nice enough to add the Cube and Rollup operators so I can
> have totals with my results.
>
> But out of shear meanness they won't let me use them unless I use a
> Group By clause in my select. I have a proc with 25 fields, and have
> no desire to Group By anything- when a certain field changes, I just
> want a row with the total of that field.
>
> Is this possible?
>
> Thanks,
>
> Burt|||You might find it necessary to put all the columns in the "select" clause
also into the "group by" clause, where you can then add a "rollup" or "cube"
clause. You might also find it useful to add a suitable "order by" clause.

Goetz Graefe
Microsoft SQL Server development -- Comments given as personal opinions and
suggestions only.

"Burt" <burt_5920@.yahoo.com> wrote in message
news:19e5f39f.0309190842.50760c96@.posting.google.c om...
> MS has been nice enough to add the Cube and Rollup operators so I can
> have totals with my results.
> But out of shear meanness they won't let me use them unless I use a
> Group By clause in my select. I have a proc with 25 fields, and have
> no desire to Group By anything- when a certain field changes, I just
> want a row with the total of that field.
> Is this possible?
> Thanks,
> Burt

Cube Process Error : The version of the linked Measure group xxxxx has changed.

I have migrated all my AS 2000 to 2005. But 2 cubes A and B have a processing

issue.

Cube B shares data from Cube A.

When I process Cube A I get this message that the version of the linked Measure Group in B has changed.

I have no problem processing Cube B which actually does share data with Cube A.

Any ideas

Thanks

Are both cubes in the same database? Try processing the whole database. Does that work?|||

Yes both cubes are in the same database.

As part of the 2005 migration, I processed all the cubes in the database successfully.

It is only when I am processing these 2 cubes as part of the posting process for these cubes that I experience the problem.