Ravindra Gullapalli February 2016

Aggregation issue in MDX

I have a dimension table dim_employee with columns employee_id, employee_name, department, language

Sample Data would be like this

+-------------+---------------+-------------+----------+
| employee_id | employee_name | department  | language |
+-------------+---------------+-------------+----------+
|           1 | ABC           | PROGRAMMING | JAVA     |
|           2 | BCD           | PROGRAMMING | .NET     |
|           3 | CDE           | TESTING     | JAVA     |
|           4 | DEF           | TESTING     | .NET     |
|           5 | EFG           | DESIGNING   | PHP      |
|           6 | FGH           | PROGRAMING  | JAVA     |    
|           7 | GHI           | PROGRAMING  | JAVA     |    
+-------------+---------------+-------------+----------+

And my fact table is

+-------------+----------+----------------+
| Department  | Language | Employee Count |
+-------------+----------+----------------+
| PROGRAMMING | JAVA     |              3 |
| PROGRAMMING | .NET     |              1 |
| TESTING     | JAVA     |              1 |
| TESTING     | .NET     |              1 |
| DESIGNING   | PHP      |              1 |
+-------------+----------+----------------+

Out of this, I want to have the following statistics departmentwise language wise employee count.

So I created a cube with Hierarchy with two levels department and language. Also a measure Employee Count with count aggregate function.

Now with the following MDX

WITH
SET [~ROWS] AS
    Hierarchize({[Dim_Dept.Hier_Dept].Members})
SELECT
NON EMPTY {[Measures].[Employee Count]} ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [MyCube]

I expect the output

+-------------+----------+----------------+
| Department  | Language | Employee Count |
+-------------+----------+----------------+
| PROGRAMMING        

Answers


mxix February 2016

Crossjoin should return the expected output:

SELECT
    {
        [Measures].[Employee Count]
    } ON COLUMNS,
    NON EMPTY 
        CROSSJOIN(
            [Dim_Dept].[Department].MEMBERS,
            [Dim_Dept].[Language].ALLMEMBERS
        )
    ON ROWS
FROM [MyCube]


whytheq February 2016

It looks to me like your two levels do not have the correct attribute relationships set up.

Do both levels have respective attributed hierarchies?

Is a relationship set up between them, in a similar way to these levels?

enter image description here

Ref: http://sqlmag.com/sql-server-analysis-services/avoid-common-sql-server-analysis-services-problems

Post Status

Asked in February 2016
Viewed 1,898 times
Voted 11
Answered 2 times

Search




Leave an answer