Hi,
This example I have got from my good fried...it will be marked by green...I don't want to take the ownership on solution that I have not discovered :
This example illustrates how to use distinct count on SqlServer 7.0 - SqlServer 2000 has the function and it works much faster (x100)
If we have a cube {VistorID, Day} we can get distinct count by using the following MDX query:
"With member
Measures.DistinctCount as
'Count(CrossJoin(
Descendants( [Visitor Id].CurrentMember, [Visitor id]),
{[Measures].[Visitor Id]}),
EXCLUDEEMPTY)'
select
Union(Day.Year.Members, Day.Month.Members) on Columns,
{[Measures].[Visitor Id], Measures.DistinctCount} on Rows
from Visitors"
till now my friends' example.
My commentary
I tryed this, it works but it is not useful for me because here you can really make the distinct count of members of dimension, so I don't want to create dimension for userid if you have 1 million of users.
But if you want to do that for such dimension that is not big and its members number is not growing all the time
try this...
Please, respond me if it was helpful for you
good luck
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.