CURSOR is driving me insane!

We have a tree structure containing section names. Each node is asection name and each section can have subsections. I have to copy thetree structure but need to maintain the parent-child relationshipestablished within the id / parent_id fields. How do i acheive this?

For example i have the tree
Section 1
|-Section 1.1
Section 2
|-Section 2.1

The"Section" table contains 3 fields: id, parent_id, and caption. ID isthe identity of the section record and parent_id contains NULL or theID of this record's parent to create a child. So "Section 1" (id=1,parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1"(id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).

I wouldlike to copy this sucture to create 4 new sections but they need tomaintain their id/parent_id relationships BUT with new IDs. For this icreated the following stored procedure:
------
CREATE PROCEDURE [dbo].[CopySection]
AS
-- Declare a temporary variable table for storing the sections
DECLARE @.tblSection TABLE
(
id int,
parent_id int,
caption varchar(max),
)

DECLARE @.newAgendaID int, @.newSectionID int;
DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max);

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table
INSERT INTO @.tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID
DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FORUPDATE OF parent_id;
OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Insert the new Section and record the identity
INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@.tparent_id, @.tcaption);
SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection
-- Update all temp variable records to point to the new parent_id
UPDATE @.tblSection SET parent_id = @.newSectionID WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
END

CLOSE c1
DEALLOCATE c1

END
------

Thecritical "UPDATE @.tblSection" part doesnt seem to update the tempvariable table with the @.newSectionID (the actual section identityobtained after inserting a real record into the tblSection table). Soin the end the inserted records into tblSection still point to theincorrect parent_id instead of the copied record's parent_id.

Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset? I've tried using both table variables and temp tables but no luck.

I've only quickly scanned your code, but it makes no sense that the UPDATE statement does not work. It should. And since it's not, that means your WHERE condition is not being met.

So, backtracking and looking at your INSERT statement, you have 3 columns listed yet are inserting only 2 values. I am guessing that this is the cause of your problem. You are assigning the @.tcaption value to the parent_id column:

INSERT INTO tblSection (agenda_id,parent_id, caption) VALUES (@.tparent_id,@.tcaption);

|||Great stuff. Thanks Terri. All is good.

Cubes and Cost-Forecast

Hi,

I'm experiencing trouble with the following requirement:

We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table:

projekt, date_of_forecast, date, costs

PR-A, 2007-06-01, 2007-07-01, 2000

PR-A, 2007-06-05, 2007-07-01, 3000

PR-A, 2007-06-10, 2007-07-01, 2500

PR-A, 2007-06-10, 2007-07-15, 2000

For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15.

The customer wants a cube wich allows an answer to the following question:

Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] ?

So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ...

How to accomplish the using SQL Server 2005 STANDARD-EDITION ?

Do I need two Time-Dimensions or just one?

Is there a way to create a Cube-Skript for this requirement?

Thanks and best whishes

Manfred

Dear Friend,

You must have a Time dimension and a Project Dimension and you FactTable that could be as you wrote...

Look for the follow post in my blog that could help you... (see the MDX query... I think you can apply to your case, but only seing more carefully)

http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html

I hope this helped you!

regards!

|||

Hi,

I've tried this. But SSAS seems to get into an infinity-recursion ...

How to work around this issue?

Best Whishes,

Manfred

|||Can you post the calculation code? The usual problem with an infinite recursion is a missing measure reference somewhere.|||

Here it is:

iif ( not isEmpty([Measures].[Gewinn]),

[Measures].[Gewinn],

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)

)

)

Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ...

Best wishes,

Manfred

|||

You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following

Code Snippet

iif ( not isEmpty([Measures].[Geplanter Gewinn]),

[Measures].[Geplanter Gewinn],

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Gewinn], [Time].[Date].PrevMember)

)

)

In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following:

Code Snippet

iif ( not isEmpty([Measures].[Geplanter Gewinn])

,([Measures].[Geplanter Gewinn])

,([Time].[Date].PrevMember)

)

Which says

1. If Geplanter Gewinn is not empty return that

2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn.

|||

Hi,

I think, there is a missunderstanding.

[Gewinn] is the Measure in the Cube and has a value for some days.

[Geplanter Gewinn] should return the last non empty value of [Gewinn]

So, the right syntax should be:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Geplanter Gewinn]

AS

iif ( not isEmpty([Measures].[Gewinn]), -- If there is a [Gewinn] for the current day

[Measures].[Gewinn], -- return it

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember) -- Go back one day and retry it

)

)

But this ends up in an infinity-recursion ...

Wishes,

Manfred

|||

Dear ManfredSteyer,

The statment in my blog works perfectly... are you sure you saw it right?

Check this:

Code Snippet

'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),
[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)'

I hope you get it!!!

Regards!

|||

Sorry, without the CREATE MEMBER clause I could not tell which was the measure in the cube. In that case it looks OK. I'm wondering if there is something else in your calculation script that might be conflicting.

Are you able to use this type of calculation in an MDX query using the "WITH MEMBER" clause?

|||

for you both:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_PRM_ENT_Racio

AS 'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),

[Measures].[ENT_Racio]

,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]

,[DimTime].[Dia].PrevMember)

)

)',

VISIBLE = 1;

Helped?

This for me works!

Regards!

|||

Hi,

I exactly used this pattern - I also looked up "MDX Solutions" (Wiley) ...

And if I use it directly within a mdx-query, it works too:

with member [Measures].[Geplanter Gewinn]

as

iif ( not isEmpty([Measures].[Gewinn]),

[Measures].[Gewinn],

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)

)

)

select [Measures].[Geplanter Gewinn] on 0

from [Kostenrechnung Sample Db]

where [Time].[Date].&[2007-06-15T00:00:00]

But when I used it as calculated member or within a cube-script, I get an inifinity-recursion ...

Have you used this pattern within cube-script/ as calc. meber or "just" as mdx-query ?

Regards,

Manfred

|||

I use it in a CM...

|||

I can't see anything wrong with the implementation of this CM. I think the fact that it works inline in a query proves that there is nothing with it on it's own. There must be a circular dependancy somewhere in the calculation script.

In order to figure this out you could either set a breakpoint in the MDX Script and use the debugger. Stepping through the script until the calc does not work. If it does not work as soon as you hit it with the debugger then there must be something earlier in the script that is upsetting it, but my guess is that it might be something after it in the script. The other approach which you could either use on it's own or in conjunction with the debugger would be to comment out blocks of the script until you isolate what is causing the issue.

|||

Hi,

Now, I figured out, that there is not an inifinity-recursion but a realy time consuming recursion. But I can not imagine why this takes that long, cause it's just a proof-of-conecpt project with very few data (~ 15 rows) and a small time-dimension (Jan/2006 - Dec/2007).

Best Whishes,

Manfred

ps.: Perhaps I sould try enterprise edition ...

|||

Yeah, sometimes depending in the projectsm could take lot of time! :-(

mark your answer to resolved!

Kind Regards!

Cubes and Cost-Forecast

Hi,

I'm experiencing trouble with the following requirement:

We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table:

projekt, date_of_forecast, date, costs

PR-A, 2007-06-01, 2007-07-01, 2000

PR-A, 2007-06-05, 2007-07-01, 3000

PR-A, 2007-06-10, 2007-07-01, 2500

PR-A, 2007-06-10, 2007-07-15, 2000

For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15.

The customer wants a cube wich allows an answer to the following question:

Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] ?

So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ...

How to accomplish the using SQL Server 2005 STANDARD-EDITION ?

Do I need two Time-Dimensions or just one?

Is there a way to create a Cube-Skript for this requirement?

Thanks and best whishes

Manfred

Dear Friend,

You must have a Time dimension and a Project Dimension and you FactTable that could be as you wrote...

Look for the follow post in my blog that could help you... (see the MDX query... I think you can apply to your case, but only seing more carefully)

http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html

I hope this helped you!

regards!

|||

Hi,

I've tried this. But SSAS seems to get into an infinity-recursion ...

How to work around this issue?

Best Whishes,

Manfred

|||Can you post the calculation code? The usual problem with an infinite recursion is a missing measure reference somewhere.|||

Here it is:

iif ( not isEmpty([Measures].[Gewinn]),

[Measures].[Gewinn],

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)

)

)

Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ...

Best wishes,

Manfred

|||

You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following

Code Snippet

iif ( not isEmpty([Measures].[Geplanter Gewinn]),

[Measures].[Geplanter Gewinn],

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Gewinn], [Time].[Date].PrevMember)

)

)

In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following:

Code Snippet

iif ( not isEmpty([Measures].[Geplanter Gewinn])

,([Measures].[Geplanter Gewinn])

,([Time].[Date].PrevMember)

)

Which says

1. If Geplanter Gewinn is not empty return that

2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn.

|||

Hi,

I think, there is a missunderstanding.

[Gewinn] is the Measure in the Cube and has a value for some days.

[Geplanter Gewinn] should return the last non empty value of [Gewinn]

So, the right syntax should be:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Geplanter Gewinn]

AS

iif ( not isEmpty([Measures].[Gewinn]), -- If there is a [Gewinn] for the current day

[Measures].[Gewinn], -- return it

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember) -- Go back one day and retry it

)

)

But this ends up in an infinity-recursion ...

Wishes,

Manfred

|||

Dear ManfredSteyer,

The statment in my blog works perfectly... are you sure you saw it right?

Check this:

Code Snippet

'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),
[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)'

I hope you get it!!!

Regards!

|||

Sorry, without the CREATE MEMBER clause I could not tell which was the measure in the cube. In that case it looks OK. I'm wondering if there is something else in your calculation script that might be conflicting.

Are you able to use this type of calculation in an MDX query using the "WITH MEMBER" clause?

|||

for you both:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_PRM_ENT_Racio

AS 'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),

[Measures].[ENT_Racio]

,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]

,[DimTime].[Dia].PrevMember)

)

)',

VISIBLE = 1;

Helped?

This for me works!

Regards!

|||

Hi,

I exactly used this pattern - I also looked up "MDX Solutions" (Wiley) ...

And if I use it directly within a mdx-query, it works too:

with member [Measures].[Geplanter Gewinn]

as

iif ( not isEmpty([Measures].[Gewinn]),

[Measures].[Gewinn],

iif ( [Time].[Date].PrevMember IS NULL,

null,

([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)

)

)

select [Measures].[Geplanter Gewinn] on 0

from [Kostenrechnung Sample Db]

where [Time].[Date].&[2007-06-15T00:00:00]

But when I used it as calculated member or within a cube-script, I get an inifinity-recursion ...

Have you used this pattern within cube-script/ as calc. meber or "just" as mdx-query ?

Regards,

Manfred

|||

I use it in a CM...

|||

I can't see anything wrong with the implementation of this CM. I think the fact that it works inline in a query proves that there is nothing with it on it's own. There must be a circular dependancy somewhere in the calculation script.

In order to figure this out you could either set a breakpoint in the MDX Script and use the debugger. Stepping through the script until the calc does not work. If it does not work as soon as you hit it with the debugger then there must be something earlier in the script that is upsetting it, but my guess is that it might be something after it in the script. The other approach which you could either use on it's own or in conjunction with the debugger would be to comment out blocks of the script until you isolate what is causing the issue.

|||

Hi,

Now, I figured out, that there is not an inifinity-recursion but a realy time consuming recursion. But I can not imagine why this takes that long, cause it's just a proof-of-conecpt project with very few data (~ 15 rows) and a small time-dimension (Jan/2006 - Dec/2007).

Best Whishes,

Manfred

ps.: Perhaps I sould try enterprise edition ...

|||

Yeah, sometimes depending in the projectsm could take lot of time! :-(

mark your answer to resolved!

Kind Regards!

Cube Design - Number Max of Dimensions

Hi all,

Some cubes into my project have been designed containing 11 dimensions ? Is there a number max of dimensions? The performances look fine so far but I was wondering if it will be a good idea to split these cubes to have a limited number of dimension per cube.

Thanks a lot for your support

Juan

I think you're a long way off hitting the maximum:
http://msdn2.microsoft.com/en-us/library/ms365363.

The only thing you need to look out for when you start adding lots of dimensions to a cube is that aggregation design will take longer and the you're less likely to get good results using the Storage Design Wizard alone - you'll probably need to do Usage Based Optimisation to get the aggregation design you really need. It's also gradually emerging that putting all your data in one cube with multiple measure groups might not be the most efficient way of designing your cubes - see
http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH,

Chris

|||

Chris,

Many Thanks for your feedback.

The tests of performance (processing of cubes) I have made meet our requirements.

Do you think that there will be an impact in the restitution of these cubes (with a lot of dimensions) via Excel (add-in) ?

Does the cube design with a lot of dimensions have an impact in the restitution performances ?

Thanks again.

Juan

|||

What do you mean by 'restitution', sorry? Do you mean query performance? If so, no there's no reason why a cube with many dimensions should perform worse than a cube with few dimensions, although as I said you need to be more careful with your aggregation design when you have many dimensions in order to get good performance.

Chris

|||

Sorry for my english...

I meant 'browse' the cube thanks to Excel (Add_in).

My tests of performance have been done to measure :

- performance to process cubes

- performance to access cubes with Excel

If I understood well, the design has a direct impact in the processing of the cube but not in the browsing of the cube.

Hope to be clearer...

Cheers

Juan

|||

Well, what I was trying to say was that adding dimensions to the cube doesn't necessarily cause worse query performance so long as you pay attention to your aggregation design. But it would not be true to say that cube design in general has no effect on query performance - it does.

Chris