Hi All

Here's my problem. We are building a Data Mart for an HR Recruitment and
placement company. After much pain and assistance, i discovered what needed
to be done however i am not sure how to do it with MDX.

Problem Statement: Want to be able to select skills e.g. Biology, Java,
Maths, etc and get a potential candidate. Performing a cross join on the
skills is not an option since there are hundreds of skills listed in these
tables and the resulting names will be way too long for the owc component
and it will be difficult for the user to use.

Cube info:

The fact table and the skill table have a many to many relationship i.e. one
emp can have many skills and one skill belongs to many emps. Therefore I
normalized it (whether or not this is good design appears to be unanswered)
and have a mapping table in between as shown below (only showing the
relevant fields for this problem. More fields exist)

Employee Fact Table fields
Empid Count
1 1
2 1


Employee Details Dimension Table

Empid Name URL
1 Reeza www.xyz.com
2 Lester www.abc.com
3 Christopher www.google.com


Skills Dimension

Skid Name
1 Chemistry
2 Biology
3 C++
4 Java
5 Maths


Emp_Skill Mapping Table
EmpID SKID
1 2
1 4
1 5
2 1
3 4
3 1


I did not make the mapping table a dimension but included it in the cube
editor because the fact table couldn't link to the skills table without
it....

So my question is when i was to retrieve Someone with Biology, maths and
java, what should the mdx look like???

Any help would be greatly appreciated.

Thanks and Kind Regards,
Reeza

NonEmptyCrossJoin( Employee.MEMBERS, {Biology, Math, Java}, 1)

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
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.
==================================================

Hi Mosha

i tried what you suggested and it didn't work. Is this because the mapping
table is not a dimension?

Thank you for your help

Reeza


Thanks for the info. I tried setting it through code for the owc component
however it did not work. The component told me
"Mosha Pasumansky [MS]" <moshap (AT) microsoft (DOT) com> wrote

You had posted a more generic version of this HR question about 3 weeks
ago, and I had suggested a solution (in terms of FoodMart), which still
looks applicable to me:

http://groups.google.com/groups?hl=e...8&selm=OxWTSjI
kDHA.3312%40tk2msftngp13.phx.gbl


Here is a version of that MDX query for your specifics - assuming
[Employees] and [Skills] dimensions for HRCube:

'[Employees].[EmpID].Members'
Set SelectedSkills as
'{Biology, Math, Java}'

Select SelectedSkills on Columns,
Except(EmployeeSet,Generate(SelectedSkills,
Except(EmployeeSet,NonEmptyCrossJoin(EmployeeSet,
{Skills.CurrentMember},1)))) on Rows
From HRCube
Where ([Measures].[Count])

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!