MS SQL Server OLAP - Cumulative Sum backward in time

Author

Cumulative Sum backward in time

Gianluca

2005-11-10, 7:23 am

Hi all,
i'm in trouble with the following mdx issue...

I've a time dimension with level Year, Qtr, Month and Days and two measures
"Sales" and "Receivables"
The fact table is something like that:

TimeID Sales Receivables
20051109 10000 30000
20051108 10000 11000
20051104 12000 10000
20051101 10000 0

My goal is to sum backwards in time to calculate how many days are necessary
to have Sales >= Receivables
For example in 20051109 the result is 5 (10000+10000+12000 > 30000).
"20051109" must be considered as a "current member" because it's selected by
users using the dimension

Thanks for your help!

mike

2005-11-10, 9:23 am

here is an example you might be able to look at, sorry that it's NOT based on
FoodMart 2000 but another example db i had from reference book i have:

/* COUNTING BACKWARDS IN TIME uses waremart 2000 */
with
SET [Week Set] as '{
OpeningPeriod([Time].[ByWeek].[Week],Head([Time].[ByWeek].[Week].Members,1).Item(0).Item(0)
) : ClosingPeriod([Time].[ByWeek].[Week],
Tail([Time].[ByWeek].[Week].Members,1).Item(0).Item(0)) }'

member [Measures].[Accum New Count] as 'Sum(
{ [Time].[ByWeek].CurrentMember : ClosingPeriod([Time].[ByWeek].[Week],
Tail([Time].[ByWeek].[Week].Members,1).Item(0).Item(0))},
[Measures].[Count Added]
)'

SET [Times Until Sum] as
'Filter(
{[Week Set] },
[Measures].[Accum New Count] <= 50000000
)'

select
{
[Measures].[Accum New Count]
} on columns,
{
[Times Until Sum]
} on rows
from Inventory

"Gianluca" wrote:

> Hi all,
> i'm in trouble with the following mdx issue...
>
> I've a time dimension with level Year, Qtr, Month and Days and two measures
> "Sales" and "Receivables"
> The fact table is something like that:
>
> TimeID Sales Receivables
> 20051109 10000 30000
> 20051108 10000 11000
> 20051104 12000 10000
> 20051101 10000 0
>
>
> My goal is to sum backwards in time to calculate how many days are necessary
> to have Sales >= Receivables
> For example in 20051109 the result is 5 (10000+10000+12000 > 30000).
> "20051109" must be considered as a "current member" because it's selected by
> users using the dimension
>
> Thanks for your help!
>

Darren Gosbell

2005-11-13, 8:23 pm

I have one possible solution using Foodmart 2000 that is probably closer
to what you are after.

I found it easier to reverse the logic and find the set of members whose
sum was less than the receivables amount, the next member after the last
one in this set is the one you are after. I used the count function to
calculate an offset and added one so that I could then use the lag
function in another calculation to the actual member.

In my example I used the [Sales Count] measure to represent your [Sales]
measure and [Unit Sales] to represent [Receivables]. You may need to
adjust the "Result" measure to work with your dimension. In Foodmart
2000 the month key is just the month number so my example will not cross
year boundaries properly.

[color=darkred]

/* Getting the set of members less than the threshold and adding 1 */
WITH

MEMBER Measures.Offset as
'Count(
Filter(
Union({[Time].CurrentMember} as cMth,
Time.CurrentMember.level.members.item(0):[Time].CurrentMember
)
, sum(cMth.item(0):Time. CurrentMember,measur
es.[Sales Count])
< sum(cMth,measures.[unit sales])
)
) +1'

MEMBER Measures.Result as
'IIF(IsEmpty(Time.Currentmember.Lag(Measures.Offset))
Or IsEmpty(Measures.[Unit Sales])
,0
,CInt(Time.CurrentMember.Properties("Key"))
- Cint(Time.CurrentMember.Lag(Measures.Offset ).Properties("Key"))
)'

SELECT
{
[Measures].[Sales Count]
,[Measures].[Unit sales]
,[Measures].Offset
,[Measures].Result
} ON COLUMNS,
{
[Time].month.members
} ON ROWS
from [Sales]
[color=darkred]

You will also need to consider what results you want to see for the
first few members in the time dimension. At the moment it calculates an
Offset that goes past the start of the dimension and I am setting these
members to "0" in the [Result] calculation.

If you are interested, I have started writing an article for my blog
breaking down the various pieces of this MDX expression and going
through the process of how I developed it. I hope to post it in the next
day or so. :)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <7170E2B5-3F87-4AB0-BB49- 1463398305B5@microso
ft.com>,
mike@discussions.microsoft.com says...[color=darkred]
> here is an example you might be able to look at, sorry that it's NOT based on
> FoodMart 2000 but another example db i had from reference book i have:
>
> /* COUNTING BACKWARDS IN TIME uses waremart 2000 */
> with
> SET [Week Set] as '{
> OpeningPeriod([Time].[ByWeek].[Week],Head([Time].[ByWeek].[Week].Members,1).Item(0).Item(0)
> ) : ClosingPeriod([Time].[ByWeek].[Week],
> Tail([Time].[ByWeek].[Week].Members,1).Item(0).Item(0)) }'
>
> member [Measures].[Accum New Count] as 'Sum(
> { [Time].[ByWeek].CurrentMember : ClosingPeriod([Time].[ByWeek].[Week],
> Tail([Time].[ByWeek].[Week].Members,1).Item(0).Item(0))},
> [Measures].[Count Added]
> )'
>
> SET [Times Until Sum] as
> 'Filter(
> {[Week Set] },
> [Measures].[Accum New Count] <= 50000000
> )'
>
>
> select
> {
> [Measures].[Accum New Count]
> } on columns,
> {
> [Times Until Sum]
> } on rows
> from Inventory
>
> "Gianluca" wrote:
>

Giorgio

2005-11-30, 7:23 am

Hi Darren, Mike
and many thanks for your reply (sorry if the first post was with the name of
my colleague Gianluca).
Darren, i've choosen your approach with just some changes to use days
instead of month and to manage cross-period days (end of month, quarter or
year).
I will not use the result member because what i need it seems to be the
offset (as a result i obtain a difference betwwen days)
I will test again but i think final expression will be

Count(
Filter(
Union(
{DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).count-1).item(0)} as cDay
,
--[Time].[Standard].[Day].&[-1].NextMember
DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).count-1).item(0).lag(730)
:
DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).count-1).item(0)
)
,
sum(cDay. item(0):DESCENDANTS(
[Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).count-1).item(0), [Measures].[Vendite])
<
sum(cDay, [Measures].[Crediti])
)
)

Now my main problem is query performance. There are no so many rows in fact
table but this formula evaluate a measure for each day and it has very long
response time. As you can see i've tried to start 2 years before the current
time member and it seems to be quicker but if user makes a different
selection (ie not country list but customers), the query hangs...
Is it possible to count starting from the end (the time current member) and
stop when a certain condition is met? maybe using recursive calculated
members?

Many thanks for your help!!!

Darren Gosbell

2005-11-30, 8:24 pm

I was a bit afraid that the performance might be an issue, I will have a
think about the recursive calc, but I cannot see an obvious solution at
the moment.

I do have one suggestion that may help. You should be able to avoid
recalculating the nested descendants functions by referring to the named
set. Also the call to descendants in the sum function would be redundant
because we are already down at the day level because of the descendants
call that creates the set that we are filtering.

eg.

Count(
Filter(
Union(
{DESCENDANTS([Time].[Standard].CurrentMember,
[Time].[Standard].[Day]).item(DESCENDANTS([Time].
[Standard].CurrentMember,
[Time].[Standard].[Day]).count-1).item(0)} as cDay
,
--[Time].[Standard].[Day].&[-1].NextMember
cDay.item(0).lag(730)
:
cDay.item(0)
)
,
sum(cDay.item(0):[Time].[Standard].CurrentMember, [Measures].
[Vendite])
<
sum(cDay, [Measures].[Crediti])
)
)

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <23C81058-4C34-49FC-8F10- 6BC8F3DE3B40@microso
ft.com>,
Giorgio@discussions.microsoft.com says...
> Hi Darren, Mike
> and many thanks for your reply (sorry if the first post was with the name of
> my colleague Gianluca).
> Darren, i've choosen your approach with just some changes to use days
> instead of month and to manage cross-period days (end of month, quarter or
> year).
> I will not use the result member because what i need it seems to be the
> offset (as a result i obtain a difference betwwen days)
> I will test again but i think final expression will be
>
> Count(
> Filter(
> Union(
> {DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).count-1).item(0)} as cDay
> ,
> --[Time].[Standard].[Day].&[-1].NextMember
> DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).count-1).item(0).lag(730)
> :
> DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).count-1).item(0)
> )
> ,
> sum(cDay. item(0):DESCENDANTS(
[Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).item(DESCENDANTS([Time].[Standard].CurrentMember,
> [Time].[Standard].[Day]).count-1).item(0), [Measures].[Vendite])
> <
> sum(cDay, [Measures].[Crediti])
> )
> )
>
> Now my main problem is query performance. There are no so many rows in fact
> table but this formula evaluate a measure for each day and it has very long
> response time. As you can see i've tried to start 2 years before the current
> time member and it seems to be quicker but if user makes a different
> selection (ie not country list but customers), the query hangs...
> Is it possible to count starting from the end (the time current member) and
> stop when a certain condition is met? maybe using recursive calculated
> members?
>
> Many thanks for your help!!!
>
>
>
>
>

Sponsored Links