How can I visualise a "broken" hierarchical dataset?

Question

I have a reasonably large datatable structured something like this:

 StaffNo    Grade    Direct   Boss2    Boss3    Boss4    Boss5    Boss6
 -------    -----    -----    -----    -----    -----    -----    -----
 10001        1      10002    10002    10057    10094    10043    10099
 10002        2      10057    NULL     10057    10094    10043    10099
 10003        1      10004    10004    10057    10094    10043    10099  
 10004        2      10057    NULL     10057    10094    10043    10099  
 10057        3      10094    NULL     NULL     10094    10043    10099

etc....

i.e. a unique id , their level (grade) in the hierarchy, a record of their bosses ID and the IDs of the supervisors above. (The 2,3,4, etc refers to the boss at that particular grade).

The system relies on a strict hierarchy - if you are my boss (/parent) then your boss must be my grandparent.

Unfortunately this rule is not enforced within the data model and the data ultimately comes from other systems which don't even know about the rule, let alone observe it. So you and I may share the same boss, but our bosses boss won't be the same.

note:

• I cannot change the data model
• I cannot fix the data at source.

So (for the moment) I have to fix the data once it's in place. Once a fortnight someone will do something which breaks the model and I'll need to modify the procs slightly to resolve. Not ideal, but I'm stuck with this for the next six months.

Anyway, specific queries are easy to produce but I find it hard to keep track of the bigger picutre. The application which sits on this runs without complaint regardless but navigating around the system becoming extraordinarily confusing. So my question is:

• Can anyone recommend a tool (or technique) for generating some kind of "broken tree" diagram in this sort of circumstances?

I don't want something that will fix things for me, or attempt statistical analysis but at least something that will give a visual indication of how broken it is at any one time.

Note : At the moment this is in a SQL Server database but I'm open to ideas utilising C#, Perl or Python.

Answer 1

list all records where boss1 has more than one boss2:

select staffno, boss1
from brokentable
where boss1 in
(
 select boss1
 from brokentable
 where count(boss2) > 1
)
order by staffno

Answer 2

I'd start with a strict hierarchical build of the org chart in a tree structure using the raw data for direct reports only

- 10099
    - 10043
        - 10094
            - 10057
                - 10002
                    - 10001
                - 10004
                    - 10003

Once you have this, you can run audit routines to verify that all of the bossX data is represented correctly in this tree.

This is assuming that you trust direct reports at least enough to build a tree as a baseline, of course. If the direct reports are the most likely to be suspect, you have bigger issues.