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!
chemas-microsoft-com:xml-analysis">
attern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
ateTime" minOccurs="0" />