CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)

Question

Below is my scenario and any help in this regard would be highly appreciated. I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level. But the Fact table doesnt have hierarchial relationship with itself. Needs to get the id's from key's from 2 different dimension tables to generate the list. I am not familiar with CONNECT_BY and JOINS to get list.

Fact table:

Person_ky    Mngr_ky  
100          3  
50           6  
2000         9999  
60           3  
150          4  
20           2  

In DIM Person table

Person_ky    Pers_Id  
100          x  
50           y  
2000         aaaa  
60           a   
150          m  
20      q   

In DIM Manger table

Mngr_ky    Mngr_id  
3           m  
6           a  
4           q  
2           t                                                 
999         bbb 

In the above scenario,
pers_ky 100 (Id -'x') is reporting to mngr_ky 3(Mngr_id - 'm') and person_ky 150 ('m') is reporting to mngr_ky 4 ('q') and person_ky 150 ('q') reporting to mngr_ky 2('t').. So Need to have for person x, his managers are 'm','q' and 't' in the result.

pers_id mgr_id level  
x       m        1     --> for pers_ky 100  
x       q        2  
x       t        3  
y       a        1     --> for per_ky 60  
y       m        2  
y       q        3  
y       t        4  
aaaa    bbb      1     --> for pers_ky 2000  
a       m        1     --> for pers_ky 60  
a       q        2   
.  
.  

Answer 1

This appears to be a straightforward connect by where you're getting the next person based on the prior manager. The only difference being you want to get the top-level (root) person listed for each row it applies to.

This can easily be found using the connect_by_root function (docs), which will return you the value of the column listed at the root node in each hierarchy.

This gives you a query like:

SELECT connect_by_root p.pers_id per, m.manager_id, level
FROM   fact_tab f
join   dim_per p
on     f.person_ky = p.person_ky
join   dim_mgr m
on     f.manager_ky = m.manager_ky
connect by  p.pers_id = prior m.manager_id

For a full example, see this fiddle.

Answer 2

Hmmm, well that's unlike any fact table I've ever seen or used.

This sort of complex line-by-line processing is best done during data load, and these relationships probably ought to be embedded in a single table that represents every person and their entire management hierarchy along with the management level.

So a single dimension table for persons, and an associated table for management relations to which it can be joined, ought to be a better design.