(SQL Server Analysis Services) MDX query to get immediate parent?

Question

Desired result

When a cube is deployed and browsed, I wish to see only an immediate manager of an employee.

The cube result shows all managers of a paticular employee, like below:

Abbas, Sanchez, 825
Abbas, Welcker, 825
Abbas, Abbas, 825`

I want to see only the immediate manager of a paticular employee, meaning this:

Abbas, Welcker, 825

How to replicate the scenario:

1. Using SQL Server Data Tools
2. Create a Analysis Services Multidimensional Data Mining Project
3. Data Source is AdventureWorksDW12 database, the sample database from Microsoft.
4. Data Source View have two tables: FactResellerSales and DimEmployee
5. Create a cube. FactResellerSales = measures. DimEmployee = dimensions.

6. DimEmployee dimention has the following attributes: EmployeeKey, LastName, ParentEmployeeKey.

   • EmployeeKey attribute is modified to take Name Column: DimEmployee.LastName
   • ParentEmployeeKey attribute is in parent-child relationship. The attribute has created a hierarchy of multi-level.SCREENSHOT 1

7. Deploy cube and browse. Drag the following attributes: LastName, ParentEmployeeKey, Order Quantity. SCREENSHOT 2

Answer 1

Maybe you could try either of these mdx functions:

.Parent ... https://msdn.microsoft.com/en-us/library/ms145513.aspx

ancestor ... https://msdn.microsoft.com/en-us/library/ms145616.aspx

The context of the query in which you are trying to get the parent is important. What I mean is depending on what else you require on rows and columns or in the slicer will impact the way you use mdx to find the parent.

Something like the following may work:

WITH MEMBER Measures.ParentKey AS 
   [Dim Employee].[Parent Employee Key].CurrentMember.Parent.member_caption
SELECT 
  {[Measures].ParentKey } ON 0,
  [Dim Employee].[Parent Employee Key].[(All)].members ON 1
FROM [YourCube]