Sorry not to answer to your question but I have the same trouble way before you...
When you say that Tableau understand the hierarchy, I can't have this.
Here is my trouble http://community.tableau.com/thread/116826
Can you give me a workbooksample working ?
I have exactly the same problem as you do...
Hope that someone can help us or we'll have to change the datamodel...
I've used two database based approaches for solving this for mapping the hierarchy of our organisation. Both have limitations but overall they work. Alas neither makes use of Oracle's connect by or SQL Server's recursive queries so they're not totally dynamic.
The first forces all groups/units to have the same depth by adding "dummy" elements. This makes things neat but overall just gets messy and then you have phantom groups in there.
I've converted over to a solution where I just flatten the query results to 7 levels. That's the limitation here - it's a manual depth you can go to. In our case the organisation chart only goes to 7 levels so I know I'm good. And, in reality 7 is a lot on any viz - I usually display subsets of the hierarchy. Here's a smaller example of the query.
SELECT lvl1.OrgName AS lvl1
, lvl2.OrgName AS lvl2
, lvl3.OrgName AS lvl3
, lvl4.OrgName AS lvl4
, lvl5.OrgName AS lvl5
, COALESCE(lvl5.Sales, lvl4.Sales, lvl3.Sales, lvl2.Sales, lvl1.Sales) as Sales
FROM fact_org_hierarchy AS lvl1
JOIN organisation AS lvl2
ON lvl2.parent_id = lvl1.id
JOIN organisation AS lvl3
ON lvl3.parent_id = lvl2.id
JOIN organisation AS lvl4
ON lvl4.parent_id = lvl3.id
JOIN organisation AS lvl5
ON lvl5.parent_id = lvl4.id
WHERE lvl1.parent_id IS NULL
If you apply this to your dataset you'll end up with rows like:
Name1 Name2 Name3 Name4 Sales KLZ Group Airline Eng 50 KLZ Group Airline Com 40 KLZ Group Airline DN Travel 20 KLZ Group Airline DN Handling 30
The best way I've found of using this is to split your hierarchy away from your measure data (you're likely to have a lot of various measures).
Make the flattened hierarchy into a view and just join that to your sales information to give it a hierarchy.
A field with
COALESCE(lvl5.id, lvl4.id, lvl3.id, lvl2.id, lvl1.id) as currentItem
in the view will give you an easy key to join the sales.org_id to.
The big definiciency in all this is the lack of a truly dynamic hierarchy, but so far this is what's worked for me. I wish Tableau's display handling of NULLs in dimensions was better. Most of the time they show as NULL but I think you can alias that with a space and make them look blank. I haven't tested that fully though.
Give it a whirl and see if it does the business.