can you set up a measure with "countdistinct" where some column in fact table
<> 0

i want a count of members in my fact table where total_bal <> 0 by all
dimensions

does someone have an example

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.developersdex.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 dimension
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:

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:

'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.developersdex.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 897
StaffCount 12 23 12 13 888


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.[20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[20040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):time.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.[20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20040430])'
-- 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:

To count staff where [Measures].[1008] > 0:

'Count(Filter(
[Staff].[staff_member_id].Members,
[Measures].[1008] > 0))'

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.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:

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/*group...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.
..
In your case, I think the approach of replacing the day members in your
day
range set with common ancestors could solve the problem.
HTH,
Chris
Deepak


Puri Jan 3, 1:07 pm show options

Hi Chris,


Here's an alternative formulation that doesn't refer to the individual
time dimension levels explicitly, so it should work with various
hierachies. An assumption here is that 'MYRANGE' is at the leaf level of
the time hierarchy:


*The initial time range expressed in months*/
SET MYRANGE AS '{[Time].[1997].[Q1].[3]
:[Time].[1998].[Q3].[7]}'
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] as
'Except(Descendants([Time].Cur**rentMember,, LEAVES),
MYRANGE).Count = 0'
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE AS
'Filter([Time].Members, [Measures].[InRange]
AND Not ( [Measures].[InRange], [Time].Parent))'
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 AS 'SETTOSTR(MYRANGE)'
MEMBER MEASURES.STEP5 AS 'SETTOSTR(MYNEWRANGE)'
/*Show working*/
SELECT {MEASURES.STEP1,
MEASURES.STEP5} ON 0
FROM SALES

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.[20040430])'
member [time].[wktd] as 'aggregate(time.[20040426]:time.[20040430])'
member [time].[monthtd] as
'aggregate(openingperiod([day],ancestor([20040430],[month])):time.[20040430])'
member [time].[lastmonth] as 'aggregate(time.[20040301]:time.[20040331])'
member [time].[ytd] as 'aggregate(time.[20040301]:time.[20040430])'
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:

with
set [ThisDateSet] as
'{time.[20040430]:time.[20040430]}'
set [WktdSet] as
'{time.[20040426]:time.[20040430]}'
set [MonthdSet] as
'{openingperiod([day],ancestor([20040430],[month])):time.[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]).Count
= 0'
Member [Measures].[InYTD] as
'Except(Descendants([Time].CurrentMember,,LEAVES),[YTDSet]).Count = 0'
Set [MonthdOpt] as 'Filter([Time].Members, [Measures].[InMonthd]
AND Not ( [Measures].[InMonthd], [Time].Parent))'
Set [LastMonthOpt] as 'Filter([Time].Members, [Measures].[InLastMonth]
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])")

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!