|
Home > Archive > MS SQL Server OLAP > March 2006 > Trouble with syntax: "Generate" function
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 |
Trouble with syntax: "Generate" function
|
|
| Yannick 2006-03-30, 11:29 am |
| Hi,
I wish to optimize a custom member formula for non leaves
I've tried filter ( crossjoin ( ) ) but it seems to be slower than
sum ( sum (sum ...) as below
Sum(
Descendants ( [Individus].currentMember, ,LEAVES )
,
Sum (
Descendants ( [Unit_Affaires].currentMember, ,LEAVES )
,
Sum (
Descendants ( [Period].currentMember, ,LEAVES ) ,
[Measures].[Amount]
)
)
)
I don't understand why but I can't use nonemptycrossjoin.
That's why i would want to use the generate function but I have some
difficulties in writing the syntax
It doesn't work...
Sum (
generate (
CrossJoin ( Descendants (
[Period].currentMember, ,LEAVES ) ,Descendants (
[Individus].currentMember, ,LEAVES ) )
CrossJoin ( Descendants (
[Unit_Affaires].currentMember, ,LEAVES ), { [Measures].[Amount] } )
)
)
Generate function is said to be faster than filter ( crossjoin ( ) )
Can you help me please?
Thanks
| |
| Mosha Pasumansky [MS] 2006-03-30, 8:27 pm |
| Why not just
SUM( Descendants ( [Individus].currentMember, ,LEAVES )*Descendants (
[Unit_Affaires].currentMember, ,LEAVES )*Descendants (
[Period].currentMember, ,LEAVES ),
Measures.[Amount])
in AS2005, or
SUM( NonEmptyCrossJoin(De
scendants ( [Individus].currentMember,
LEAVES ),Descendants ( [Unit_Affaires].currentMember,
LEAVES ),Descendants ( [Period].currentMember, ,LEAVES )),
Measures.[Amount])
in AS2000.
More discussion here:
http://www.sqljunkies.com/WebLog/mo...02/13/7784.aspx
--
====================
==========_=========
===========
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL_og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
====================
==========_=========
===========
"Yannick" <yaya@toto.fr> wrote in message
news:mn.f4817d6302ba6ae4.52441@toto.fr...
> Hi,
>
> I wish to optimize a custom member formula for non leaves
>
>
> I've tried filter ( crossjoin ( ) ) but it seems to be slower than sum
> ( sum (sum ...) as below
>
>
> Sum(
> Descendants ( [Individus].currentMember, ,LEAVES )
>
> ,
> Sum (
>
> Descendants ( [Unit_Affaires].currentMember, ,LEAVES )
>
> ,
> Sum (
> Descendants ( [Period].currentMember, ,LEAVES ) ,
> [Measures].[Amount]
> )
>
> )
>
>
> )
>
>
> I don't understand why but I can't use nonemptycrossjoin.
> That's why i would want to use the generate function but I have some
> difficulties in writing the syntax
>
> It doesn't work...
>
> Sum (
> generate (
> CrossJoin ( Descendants (
> [Period].currentMember, ,LEAVES ) ,Descendants (
> [Individus].currentMember, ,LEAVES ) )
> CrossJoin ( Descendants (
> [Unit_Affaires].currentMember, ,LEAVES ), { [Measures].[Amount] } )
> )
>
> )
>
> Generate function is said to be faster than filter ( crossjoin ( ) )
> Can you help me please?
> Thanks
>
>
| |
| Yannick 2006-03-31, 7:36 am |
| Hi !
I've ever tried it but there is no aggregation on the Period and
Uni_Affaires axis...
But It works with :
sum (
filter (crossjoin( )
)
)
However it's quite slow.
Ive read in a book that GENERATE function is faster.
How can you write the syntax?
Thanks in advance
Mosha Pasumansky [MS] avait soumis l'idée :[color=darkred]
> Why not just
>
> SUM( Descendants ( [Individus].currentMember, ,LEAVES )*Descendants (
> [Unit_Affaires].currentMember, ,LEAVES )*Descendants (
> [Period].currentMember, ,LEAVES ),
> Measures.[Amount])
>
> in AS2005, or
>
> SUM( NonEmptyCrossJoin(De
scendants ( [Individus].currentMember, LEAVES
> ),Descendants ( [Unit_Affaires].currentMember, LEAVES ),Descendants (
> [Period].currentMember, ,LEAVES )),
> Measures.[Amount])
>
> in AS2000.
>
> More discussion here:
>
> http://www.sqljunkies.com/WebLog/mo...02/13/7784.aspx
>
> --
> ====================
==========_=========
===========
> Mosha Pasumansky - http://www.mosha.com/msolap
> Analysis Services blog at http://www.sqljunkies.com/WebL_og/mosha
> Development Lead in the Analysis Server team
> All you need is love (John Lennon)
> Disclaimer : This posting is provided "AS IS" with no warranties, and confers
> no rights.
> ====================
==========_=========
===========
> "Yannick" <yaya@toto.fr> wrote in message
> news:mn.f4817d6302ba6ae4.52441@toto.fr...
|
|
|
|
|