Hello!

I have a cube that contains sales of various products and sub-products by
employees and territory and by month. The fact table looks like this:

Period Emp_ID FTE Prod_Cat Sub_Product Units_Sold
200501 12345 1.0 Apps HE 20
200501 12345 1.0 Apps PC 13
200501 12345 1.0 Boards HE 12
200501 12345 1.0 Boards PC 16
200501 32545 .75 Apps HE 3
200501 32545 .75 Apps PC 2
200501 32545 .75 Boards HE 10
200501 32545 .75 Boards PC 14


There are two product categories and two sub-products in each, making it up
to four instances of an employee in a given period (month). Employee
dimension’s primary key is a combination of period and emp_id - an employee’s
attributes such as FTE (Full time equivalent) or Position might change over
time. (However, these attributes do not change with category or
sub-category, i.e. up to four instances the same FTE number would appear for
an employee in a given period).

I am stuck at calculating employee count and FTE at aggregate levels. I
cannot use Analysis Service's ‘DISTINCT COUNT’ feature as end-users would not
be able to select multiple entries on the page axis in Excel PivotTables -
known bug. I need to figure out the exact count of employees per month per
territory, as well as average number of employees YTD as of the last
reporting period or average for the year (for previous years). The problem
is that there are multiple entries per employee in the fact table, which
creates this mess. Basically I'm looking for some kind way to emulate a
distinct count without de-normalizing the fact table any more. In addition,
I need the same type of calculations for FTE, which must be summed up on
aggregate levels and averaged on annual levels.

Any suggestions?

Any kind of MDX which will emulate Distinct Count, will likely suffer from
the same problem of end users not being able to do multi-select properly.
Therefore, I recommend upgrading to Yukon, where multi-select works fine
with Distinct Count measures.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Pasha" <Pasha (AT) discussions (DOT) microsoft.com> wrote