Hi,

I have a calculated measure which count the number of "active activities".
1 activity has a start date and an end date, I have to track day by day how
many activities I have and the number of employees.
My fact table is my dimension table (activity)
So my cubes has 3 times the time dimension:
* start date
* end date
* date

my formula is :
aggregate(

exists(

LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,

[Activity - Start Date].[Calendar by Fiscal Year]).level.item(0)

:LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,

[Activity - Start Date].[Calendar by Fiscal Year])

,

LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,

[Activity - End Date].[Calendar by Fiscal Year])

: LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,

[Activity - End Date].[Calendar by Fiscal
Year]).parent.lastsibling.lastchild)

,[Measures].[No of Activities])


So, I take all the activities with a start date <= selected date (or month
or year) and where the end date >= selected date.

This works fine, but sometimes when my users play with the cube the server
become unresponsive.
the CPU is used at 100% and nothing is returned to the end user after 10
minutes.
I have aggregated the cube at 99%

so, how can I write my formula?
there is any simple way to do a "between" in AS2005?

jerome.

Hi Jerome,

Are you sure your current calculation even returns the correct results? For
example, imagine you have three activities with start and end dates as
follows:
1) Start Date Jan 2005, End Date Dec 2005
2) Start Date Jan 2005, End Date Feb 2005
3) Start Date Mar 2005, End Date Dec 2005

As far as I can see with your calculation (although I may be wrong), if your
user then selects August 2005 then the algorithm aggregates all the members
on [Activity - Start Date] up to and including August 2005, which exist with
members on [Activity - End Date] from August 2005 to the last available
month. In which case, all the above three activities would be counted because
the start date Jan 2005 does exist with the end date Dec 2005 (for activity
#1), even though you actually didn't want to count activity #2. Does this
make sense?

If I'm right, then I think something like

aggregate(
nonempty(
crossjoin(
LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - Start Date].[Calendar by Fiscal Year]).level.item(0)
:LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - Start Date].[Calendar by Fiscal Year])
,
LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - End Date].[Calendar by Fiscal Year])
: LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - End Date].[Calendar by Fiscal
Year]).parent.lastsibling.lastchild
))
,[Measures].[No of Activities])

might be what you're after. No idea whether this will suffer from the same
bizarre problem as your existing calculation though.

Regards,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Jéjé" wrote:

my tests return the expected result.
welll, also I have some other calculations like the one here.

The problem I have is, sometimes there is no answer from the server and
sometimes there is no problems!
Specially when I play with an attribute of the employee dimension. (like the
year of hiring)
The result could appears in 1 seconds or no results!

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

Well, since I don't know enough about the structure of your cube you're
probably right. I would double-check your tests though! In any case it does
sound as though you've come across some kind of bug here - it might be worth
opening a call with PSS and start looking for a workaround yourself.

One avenue to explore is to rewrite the code to remove the EXISTS()
function. Since it looks like [Activity - Start Date] and [Activity - End
Date] are separate dimensions and not attributes of the same dimension, I
would guess what's happening is that you're using EXISTS to return the
combinations that occur in your fact table (despite the fact that you've not
included the measure group name as the third parameter - see
http://www.sqljunkies.com/WebLog/mos..._as2005.aspx);
and since the only difference between doing this and using NONEMPTY() and
CROSSJOIN() is that EXISTS returns combinations where there are null values
in the fact table, which are irrelevant because you're then aggregating the
resulting set, it should be possible to rewrite your code to use a
combination of NONEMPTY(), CROSSJOIN() and EXTRACT():

aggregate(
extract(
nonempty(
crossjoin(
LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - Start Date].[Calendar by Fiscal Year]).level.item(0)
:LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - Start Date].[Calendar by Fiscal Year])
,
LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - End Date].[Calendar by Fiscal Year])
: LINKMEMBER( [Calendar].[Calendar by Fiscal Year].currentmember,
[Activity - End Date].[Calendar by Fiscal Year]).parent.lastsibling.lastchild)
)
, [Activity - Start Date])
,[Measures].[No of Activities])

does this return the same results as your original calculation? The EXTRACT
is the difference between this calculation and the one in my previous post
and is what makes it logically the same as your calculation, and it would be
very interesting to see whether both of my calculations return the same
results in all circumstances. Does it also suffer from the same problem?

Regards,

Chris

--
Blog at:
http://spaces.msn.com/members/cwebbbi/


"Jéjé" wrote:

thanks.

I'll try it.

My second measure is the same but instead-of "No of activities" I'll use the
"No of employees with activities" a dcount aggregated measure.
Thanks to AS2005 to support the aggregate function with a DCount measure!!!
:-)

"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote