| Magnus 2005-12-21, 8:25 pm |
| I've built a AS2000 cube for employment statistics, and having
trouble calculating average number of employees. One person can have
multiple employments (at different departments and different job
titles), and I'm only interested in counting individual persons. The
grain of my fact table is one record per employment and day.
What I'm trying to do is to calculate number of distinct employees
per day, and then sum up the measure to the selected time period and
dividing by number of days in the period.
I'm not using the distinct count function because of the trouble when
selecting multiple members. Instead I've built one cube with a single
count measure, and a virtual cube with a calculated measure to count
the distinct number of employees like this:
Count(NonEmptyCrossJ
oin([Employee].[Employee].Members,{[Measures].[Count]}))
The Employee dimension contains one member per individual employee.
I'm genereting mdx queries for creating reports. I think I'm
getting correct results, but performance is bad, even with a small
number of employees (~150, and have to handle about 5000 when in
production). An simplified MDX-example: average number of employees by
department during Jan-Jun 2005:
//Selected time period
WITH SET TimeSet AS '[Time].&[2005].&[1].&[1]:[Time].&[2005].&[2].&[6]'
MEMBER [Time].[Total] AS 'Aggregate([TimeSet])'
//Calculate number of employees per day and sum up the specified time
period
MEMBER [Measures].[Unique Employment Days] AS
'Sum(
CrossJoin(
{[Measures].[Distinct Employee Count]},
Generate([TimeSet], [Time].CurrentMember.Children
)
)'
//Count number of selected days
MEMBER [Measures].[Selected Days Count] AS 'Count(Generate([TimeSet],
[Time].CurrentMember.Children))'
//Calculate average
MEMBER [Measures].[Average Number Of Employees] AS '[Measures].[Unique
Employment Days]/[Measures].[Selected Days Count]', FORMAT='#,0.0'
SELECT { [Measures].[Average Number Of Employees]} ON COLUMNS,
NON EMPTY {[Department].[Members]} ON ROWS
FROM [Employments] WHERE ([Time].[Total])
Any suggestions on how to improve performance? Performance is worst
when grouping by department, job title etc. Not so bad when grouping by
month. Selecting a single month is also ok, but the response time
increases drastically when selecting longer time periods.
Thanks,
Magnus
|