|
Home > Archive > MS SQL Server OLAP > December 2005 > "between" calculated measure issue in AS2005...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
"between" calculated measure issue in AS2005...
|
|
|
| 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.
| |
| Chris Webb 2005-12-20, 11:24 am |
| 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:
> 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.
>
>
>
| |
|
| 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" < OnlyForPostingToNews
groups@hotmail.com> wrote in message
news:B5F5878A-2D6D-4268-86DA- E7897B0BDDD5@microso
ft.com...[color=darkred]
> 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:
>
| |
| Chris Webb 2005-12-21, 7:23 am |
| 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/mo...005
.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:
> 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" < OnlyForPostingToNews
groups@hotmail.com> wrote in message
> news:B5F5878A-2D6D-4268-86DA- E7897B0BDDD5@microso
ft.com...
>
>
>
| |
|
| 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" < OnlyForPostingToNews
groups@hotmail.com> wrote in message
news:26F0263A-56D4-49A5-8064- 8FDE31E61D04@microso
ft.com...[color=darkred]
> 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/mo...005
.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:
>
|
|
|
|
|