How can I visualise a "broken" hierarchical dataset?
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.
