MDX to count the number of members in a dimension based on another dimension

_{Saturday, November 24, 2012 8:21 AM}

i have the following :

- employees dimension [D_EMP], with columns emp_id, name, .... etc

• projects dimension [D_PROJ], with columns proj_id, title, .... etc
• date dimension [D_DATE], with columns date_id ....
• fact table contains the number of hours an employee worked on a project on a certain date, with columns: emp_id, proj_id, date_id, total_hr ... etc

+ i want MDX query to find the number of employees worked on each project

_{Saturday, November 24, 2012 7:35 PM ✅Answered}

There's two ways you can accomplish and which one is the best depends on other complexity. You can use the Count function to determine the number of Employees that have a non-null value of [Project Hours] like below.

WITH MEMBER [Measures].[Employee Count] AS
    COUNT( NonEmpty( { [Employee].[Employee].[Employee].Members }, ( [Measures].[Project Hours] ) ) )
SELECT
    {
        [Measures].[Employee Count]
    } ON COLUMNS,
    NonEmpty(
        { [Project].[Project].[Project].Members },
        ( [Measures].[Project Hours] )
    ) ON ROWS
FROM
    [Project Cube]

Another approach which sometimes yields better performance is to use an IIf in a calculated measure and sum that measure over the Employee set to determine the number of employees associated with each project, but this approach is much better only with COUNT(Filter .... ) statements. http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx

WITH MEMBER [Measures].[Has Hours] AS 
    IIf( 
        [Measures].[Project Hours] <> 0,
        1,
        NULL
    )
MEMBER [Measures].[Employee Count] AS
    SUM(
        { [Employee].[Employee].[Employee].Members },
        ( [Measures].[Has Hours] )
    )
SELECT
    {
        [Measures].[Employee Count] 
    } ON COLUMNS,
    NonEmpty (
        { [Project].[Project].[Project].Members },
        ( [Measures].[Project Hours] )
    ) ON ROWS
FROM
    [Project Cube]
    

HTH, Martin

http://martinmason.wordpress.com