Cubes and Cost-Forecast

SQL Server Analysis Services

ManfredSteyer


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

Top

Re: Cubes and Cost-Forecast

PedroCGD


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!






Top

Re: Cubes and Cost-Forecast

ManfredSteyer

Hi,

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

How to work around this issue

Best Whishes,

Manfred



Top

Re: Cubes and Cost-Forecast

Darren Gosbell

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



Top

Re: Cubes and Cost-Forecast

ManfredSteyer

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


Top

Re: Cubes and Cost-Forecast

Darren Gosbell

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.





Top

Re: Cubes and Cost-Forecast

ManfredSteyer

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


Top

Re: Cubes and Cost-Forecast

PedroCGD

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!





Top

Re: Cubes and Cost-Forecast

Darren Gosbell

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





Top

Re: Cubes and Cost-Forecast

PedroCGD

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!





Top

Re: Cubes and Cost-Forecast

ManfredSteyer

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


Top

Re: Cubes and Cost-Forecast

PedroCGD

I use it in a CM...





Top

Re: Cubes and Cost-Forecast

Darren Gosbell

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.





Top

Re: Cubes and Cost-Forecast

ManfredSteyer

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 ...


Top

Re: Cubes and Cost-Forecast

PedroCGD

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

mark your answer to resolved!

Kind Regards!





Top