count distinct

can you set up a measure with "countdistinct" where some column in fact tabl
e
<> 0
i want a count of members in my fact table where total_bal <> 0 by all
dimensions
does someone have an exampleYou can create a separate cube with a "Distinct Count" measure for the
member column, and set the "Source Table Filter" for the cube to
"total_bal <> 0". This cube can be combined with existing cubes in a
virtual cube.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I have cube with staff dimension which has staff_member_id as a level
Cube has metrics for example total_bal
I want to count distinct staff_member id's for each level in my time dimensi
on
daily, weekly, monthly etc where total_bal > 0
Is this possible to do within an MDX, can you send me an example of
something like this
Creating separate cubes would mean we have to create atleast 25 cubes as we
have 25 different measures we want to calculate like above
Thanks
"Deepak Puri" wrote:

> You can create a separate cube with a "Distinct Count" measure for the
> member column, and set the "Source Table Filter" for the cube to
> "total_bal <> 0". This cube can be combined with existing cubes in a
> virtual cube.
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Assuming that there is a [tot_bal] "sum" measure, based on a "tot_bal"
fact table field, and that this field is always >= 0, MDX can be used to
count [staff_member_id] members for a given node in the time hierarchy:
[vbcol=seagreen]
With Member [Measures].[StaffCount] as
'Count(Filter(NonEmptyCrossJoin(
[Staff].[staff_member_id].Members,
{[Time].CurrentMember}, 1),
[Measures].[tot_bal] > 0))'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||this is my exact MDX statement
i have result set out oof this MDX like this in columns which separate WTD,
MTD etc
MEMBERCAPTION thisdate wktd monthtd lastmonth ytd
1008 21 43 90 100 8
97
StaffCount 12 23 12 13 88
8
Can you help me write Measures.Staffcount please..
select 1 as col1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'aggregate(time.[20040430]:time.&#
91;20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[2
0040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):tim
e.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.
1;20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20
040430])'
-- Member [Measures].[StaffCount] as ''
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
select { [measures].[StaffCount] , [measures].[1008] }
on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])
")
"Deepak Puri" wrote:

> Assuming that there is a [tot_bal] "sum" measure, based on a "tot_bal"
> fact table field, and that this field is always >= 0, MDX can be used to
> count [staff_member_id] members for a given node in the time hierarchy
:
>
> With Member [Measures].[StaffCount] as
> 'Count(Filter(NonEmptyCrossJoin(
> [Staff].[staff_member_id].Members,
> {[Time].CurrentMember}, 1),
> [Measures].[tot_bal] > 0))'
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||To count staff where [Measures].[1008] > 0:
[vbcol=seagreen]
With Member [Measures].[StaffCount] as
'Count(Filter(
[Staff].[staff_member_id].Members,
[Measures].[1008] > 0))'[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||I tied this and it makes the MX extremely slow, 50 secs instead of 2 secs
which it was taking earlier
is there any way to speed this up or some other mechanism
"Deepak Puri" wrote:

> To count staff where [Measures].[1008] > 0:
>
> With Member [Measures].[StaffCount] as
> 'Count(Filter(
> [Staff].[staff_member_id].Members,
> [Measures].[1008] > 0))'
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||You could try AS 2005 (Yukon), which supports aggregation of distinct
count measures.
If the different time ranges are defined in Named Sets (like [YTDSet]),
then staff count for each set could be optimized by NonEmptyCrossJoin()
pre-filtering.
Short of these options, you could try optimizing the longer time ranges,
using the approach discussed in this thread, where Chris Webb discusses
the possibility to optimize a time series, and I posted some sample MDX
code. You can refer to Chris's BI Blog (referenced in this thread) for
more details:
http://groups-beta.google.com/_grou...serv_er.olap/m.
.
Newsgroups: microsoft.public.sqlserver.ola_p
Subject: RE: Problem with MDX query
Chris Webb Dec 31 2004, 4:01 am
- Hide quoted text -
- Show quoted text -
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/__cwebbbi/Blog/cns!1pi7ETChsJ1un___2s41jm9
I.|||Deepak
I could not go to the link and am not an expret an MDX to understand your
code so easily
can you pls re-write this according to your recomendation so it can improve
in speed
THANKS!!!!
select 1 as col1, * from openquery (ROP_OLAP, "
with
member [time].[thisdate] as 'aggregate(time.[20040430]:time.&#
91;20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[2
0040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):tim
e.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.
1;20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20
040430])'
Member [Measures].[StaffCount] as
'Count(Filter([Staff].[agent].Members,[Measures].[CAS_IC_C]
> 0))'
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[1008], [Measures].[StaffCount] }
on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])")|||You should really work this out yourself, since I don't have your cube
to test against, but something like:
[vbcol=seagreen]
select 1 as col1, * from openquery (ROP_OLAP, "
with
set [ThisDateSet] as
'{time.[20040430]:time.[20040430]}'
set [WktdSet] as
'{time.[20040426]:time.[20040430]}'
set [MonthdSet] as
'{openingperiod([day],ancestor([20040430],[month])):time.&#
91;20040430]}'
set [LastMonthSet] as
'{time.[20040301]:time.[20040331]}'
set [YTDSet] as
'{time.[20040301]:time.[20040430]}'
Member [Measures].[InMonthd] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[MonthdSet]).Count
=
0'
Member [Measures].[InLastMonth] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[LastMonthSet]).Co
unt
= 0'
Member [Measures].[InYTD] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[YTDSet]).Count =
0'
Set [MonthdOpt] as 'Filter([Time].Members, [Measures].[InMon
thd]
AND Not ( [Measures].[InMonthd], [Time].Parent))'
Set [LastMonthOpt] as 'Filter([Time].Members, [Measures].[In
LastMonth]
AND Not ( [Measures].[InLastMonth], [Time].Parent))'
Set [YTDOpt] as 'Filter([Time].Members, [Measures].[InYTD]
AND Not ( [Measures].[InYTD], [Time].Parent))'
member [time].[thisdate] as 'aggregate([WktdSet])'
member [time].[wktd] as 'aggregate([WktdSet])'
member [time].[monthtd] as 'aggregate([MonthdOpt])'
member [time].[lastmonth] as 'aggregate([LastMonthOpt])'
member [time].[ytd] as 'aggregate([YTDOpt])'
member [measures].[1008] as '[measures].[ECH_Acw_Time]'
Member [Measures].[StaffCount] as
'Count(Filter(NonEmptyCrossJoin(
[Staff].[staff_member_id].Members),
[Measures].[CAS_IC_C] > 0))'
set [timeset] as '{[time].[thisdate], [time].[wktd]
, [time].[monthtd],
[time].[lastmonth], [time].[ytd]}'
select { [measures].[1008], [Measures].[StaffCount]
} on rows,
{[timeset]} on columns
from unifinal_web
where ([Function].[All Function].[01003])")[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!