count measure question

Hi,

I have table with column which is containing two values ("Y" and "N") and I want to create measure which will count Y values and second measure which will count N value. How can I create it?

Thank you

Pavel

Is the Y/N column part of a fact or dimension table?

Probably the easiest solution would be to add two INT fields to your fact table named IsYes and IsNo. Set the values to 0 or 1 appropriately and use them as measures with a SUM aggregation function. It's simple but easy.

B.

count measure not working as expected

Hi

I have diamension Calender in my cube. I have a table called incident which is working as both measure as well as diamension. There is no relation between the calender and incident tables in the database. When i selected the incident table to be a measure using the cube creating wizard it added a measure attribute called incident count inside the incident measure. I have heirarchy defined in the calender diamension as year-month-date. What i did was dropped the incident count measure in the data area while browsing the cube. And i added the

year-month-date heirarchy to 'drop row field here' area.

Then i went into the diamension usage and defined a relation between Calender diamension and incident measure using the incidentDate field in incident table and Date field in the Calender table. Then i deployed the cube and browsed. What i get is count the total number of incidents in the incident table in front of all the years and months.

Can anybody suggest what i am doing wrong. I want a count of incidents to be displayed for every month or every year.

Thank you.

-padu.

Sounds like you have not defined attribute relationships in your Calendar dimension. With the attributes you mentioned in your previous post Date should have a relationship to month and month should have a relationship with year. Assuming that date is the key attribute you would also need to ensure that the year is not related to the date as this redundant relationship could also cause issues.sql

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!

Count & LastNonEmpty

Is there any way to make Count work properly on a measure group where aggregation for every single measure is set to LastNonEmpty?

Basically it's a fact table that contains periodic snapshots of data & so count only makes sense within the slice based on insertion time.

Thanks!

any help please?|||What kind of count do you want in this snapshot scenario - is it a count of all objects for which there is data - could you illustrate with some examples? And are the snapshots synchronized, or could they be created at different times?|||

Here's an example:

Product Amount ImportDate

a 1 Jan 2007

b 10 Jan 2007

c 100 Jan 2007

a 2 Jan 2007

b 11 Feb 2007

b 12 Feb 2007

so in this case for Jan 2007 there's 2 products 'a' for a total amount of 3, so count =2.

for Feb 2007 there's 2 products 'b' for a total amount of 23

So basically the aggregation on the amount column is set to lastnonempty using 'import date' to determine the last child

so for the entire year 2007 for product B the amount would be equal to the amount from Feb 2007 - count =2, amount = 23.

But the count always runs across all dates & so for the set above & entire year 2007 it would return 3 for product b - not 2.

all the snapshots are done for the same set of products - they're common across all dates & it's product measures that are being aggregated.

|||Add a Named Calculation to the fact table like [LastCount] = 1, then create a corresponding measure: [LastCount], with LastNonEmpty aggregation.|||

what a great idea - and how didn't I think of that?!

Thanks!