Question

In a Fabric warehouse, an analytics engineer must flatten an employee dimension that stores a self-referencing parent-child hierarchy (each row has EmployeeID and ManagerID). The requirement is to produce, for every employee, the full management chain depth and their top-level executive, using a single reusable T-SQL query object that other analysts can call. Which T-SQL approach should the engineer use?

Accepted Answer

Write a recursive common table expression (CTE) that anchors on top-level managers and iteratively joins child rows, then expose it through a view

Answer

Create a cursor inside a stored procedure that loops through each row and updates a hierarchy-level column one record at a time

Answer

Use a series of self-joins hard-coded to a fixed number of levels wrapped in a scalar function

Answer

Apply the DAX PATH function against the imported table in the semantic model layer