Is there a way of including a Distinct Count on Dimension Table in the
measures?

e.g.
Fact Table
Employee
PayrunId
HoursWorked
StandardHours

Dimension Table
PayrunId
PayrunDate
Month
Year

The Dimension table can be mde into a hierachy
Year=>Month=>PayrunDate

This can be used for adding hours over months or years.
What I'm trying to do is get the average over time.

e.g. SUM(HoursWorked/StandardHours)/CountDistinct(Dimension Table.PayrunId)
Where CountDistinct(Dimension Table.PayrunId) is the number of pays in the
year or month (as opposed to the number in the fact table)
e.g. If an employee has a record for 2 out of 3 pays in a month they would
be 0.66

Thanks

Hi,

There seems a design issue on this cube. I think it shall be:

Fact Table

Month
Year
PayrunDate
PayrunId
Employee
HoursWorked
StandardHours

Dimension Table1
PayrunID
PayrunDate
Month
Year

You may want to add a additional column with the same value as PayrunId,
use this PayrunID as a measure, and select Aggregate function to "distinct
count'.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Hi Peter,

The dimension table has details of all pay runs. (Like a time dimension has
details of all time periods)
In the fact table the employee may have only been paid for 2 of the 3 pays
in a month so should only register 2/3. i.e. I need to do a distinct count on
something not in the fact table

Cheers

"Peter Yang [MSFT]" wrote:

Hello,

To the best of my knowledge, there is such feature built in AS could do
this job. You may want to consider develop your own user define function to
do this job. Please refer to "
User-Defined Functions with MDX Syntax" topic in BOL for details. You may
need to count this via SQL command directly from dimension table in UDF
implementation.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

================================================== ===


--------------------

You could create a calculated measure that returns the number of pay runs
within a particular time period.

Let's assume your pay run dimension is called [pay run] and it has a leaf
level called [pay run id]. Then the following MDX should return the count
that you want: -

COUNT(DESCENDANTS([pay run].CURRENTMEMBER,[pay run].[pay run id]))

This calculation references the dimension only, not the facts, so the count
will include all pay runs that exist in the dimension. You could then use
this MDX, or a calculated measure based on it, as the denominator in your
other calculations.

--
Dean Adam
Magenic Technologies


"Acius" wrote:

have you try to use 2 cubes + 1 virtual?

from what I understand, you want to compare real values with planned or
possible values. (real payrun by employee versus planned payrun by employee)

1 with the real number of payrun by employee (simple sum or count measure)
the second is the number of payrun planned by month, and the number of
payrun * nb of employees (which provide the total number of payrun-employee
for the month) (this cube doesn't include the employee dimension, but you
could include it)

create a virtual cube based on these 2 cubes.

In your sample, the cube 1 provide 2 payrun for the employee, the second
cube display 3 planned payruns, result = 2/3


"Acius" <acius (AT) nospam (DOT) nospam> wrote

Thanks .. all a lot simpler than I thought.
Translating what you said to AS2005.
Create a second measure group with a distinct count on payruns based on the
pay run table as a fact table.
This operates independantly of the fact table so you can have YTD pay runs,
pay runs in the month, etc.
Just divide the summed value in the fact measure group by the value in the
count distinct measure group.

Cheers


"Jéjé" wrote: