Azure_Synapses_parent-child-hierarchies

Question

The need is to get the company hierarchy from employee and supervisors. Currently I'm able to do this directly in powerbi by using DAX functions:

HierarchyPath = PATH(workforce[employee_number],workforce[supervisor_number])

The result is this:

Then I segregate the levels by using the function PATHITEM.

I want to perform the same logic in azure synapse by creating a derived columns, but until the moment without success. I suspect that I could some functions like haspath or bypath?

Can someone help me?

Answer 1

Hi @Daniel Moreira ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your query, you want to build the hierarchy path for records using mapping dataflow. Please correct me if my understanding is wrong.

As of now, there is no such equivalent function in dataflow for 'path' function of powerBI.

In dataflow, byPath function doesn't work the same way . Also, looping is not possible yet in dataflow, if you want to achieve the requirement in dataflow, it would be lots of manual effort.

Closest possible output I got was using windows transformation in dataflow having expression like below:

toString(lag(supervisor_number,2)) + '|' + toString(lag(supervisor_number,1)) + '|' + toString(lag(supervisor_number,0)) + '|' + toString(lag(employee_number,0))  

which would result in something like this:

To achieve the desired result, you can go for case statements:
example: if empid= 1 , path would be : toString(lag(supervisor_number,1)) + '|' + toString(lag(supervisor_number,0)) + '|' + toString(lag(employee_number,0))
if empid= 2 , path would be : toString(lag(ReportingTo,2)) + '|' + toString(lag(ReportingTo,1)) + '|' + toString(lag(ReportingTo,0)) + '|' + toString(lag(Employee,0))
and so on.

So , you would have to write as many queries in case as the number of rows.

I would recommend to use until activity in ADF pipeline to achieve the requirement or use append variable activity in ADF pipeline or use custom codes in C# or azure function.

If you would like me to create a replica ADF pipeline for the scenario for help, kindly let me know since achieving this using dataflow seems a bit infeasible.

Hope this will help. Please let us know if any further queries.

---

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hello @AnnuKumari-MSFT ,

Thanks for the idea, but this doesn't seem to work since is considering last records.

Considering the dataset you provided the correct answer should be this:

I'm open to use activity in ADF or azure function.

Thank you for the help

Answer 3

Hi Daniel Moreira ,

Taking this as the dataset : companyhierarchy.txt

I am sharing the pipeline codes for parent pipeline: pipeline32.txt and child pipeline : pipeline33.txt to achieve the equivalent solution for path function in ADF directlty. Please try it out and let me know if it works for you. Thankyou.

---

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well

Answer 4

Hello ,

Sorry for the delay,

I'm receiving errors loading the code (I tried multiple options). Is there any article explaining how to load the JSON code?