Is this a good method to implement Hierarchy

[Anonymous]

Summary:

I have 2 qvws named first and second. First is where I have my fact table where I also do the aggregation. Then I am doing a binary load onto second. On the second, I have 3 tabs with 2 objects each. The 2 objects per page are supposed to be “filterable” by Dir, Mgr & Staff respectively. I have 2 data sets defined by Date 1/31/2016 & 2/28/2016. Let us remain with 1/31/2016 in this sample. When you select 1/31/2016 and then ‘Tom’ under Dir, Label 1 and Label 2 (the 2 objects) are supposed to filter the metrics/data associated with Tom. It also should associate corresponding Mgrs in the Mgr tab:- John & Chris. If you select John, Label 1 object shows the metrics and Label 2 has no data to display. Meanwhile on Staff tab, Personnel 2 is associated as shown by Staff List Box. So it seems to be doing what I need it to but I thought a better way would be to use Hierarchy function.

Here is my main Question: I am using combination key ‘’ Group”- “Prime Key” in each level/hierarchical data:-----Dir, Mgr, Staff to link with fact table which is loaded on the qvw which I binary load on the second qvw (the binary load is a requirement and can not be removed as part of representing the actual application). Is this a proper way?

Many Thanks,

p.s. attached QVWs and Excel Files.

[Anonymous]

Here is improved description to the question I asked. Pls see the attached documents.

Instead of using a link table, I have used Hierarchy function. I need the following: when I select Tom under Dir Name, it should be associated with John and Chris under Mgr Name and when John is selected, it should be associated with Personnel 1 under Staff Name. Below is the code I have but it is not working. Help would be really appreciated.

Table1:

LOAD [Exec Type],
if( [Exec Type] = 'Dir', [Exec Name]) as [Dir Name],
if( [Exec Type] = 'Mgr', [Exec Name]) as [Mgr Name],
if( [Exec Type] = 'Staff', [Exec Name]) as [Staff Name],
Group,
[Exec Name],
Chart1,
[Prime Key]
FROM
[sample excel files\hierarchy.xlsx]
(ooxml, embedded labels, table is Sheet1);

Hierarchy_table:
Hierarchy(NodeID, ParentID, 'Exec Type', 'Parent Location', 'Location',
'PathName', '~', 'Depth')


LOAD * INLINE [
NodeID, Exec Type, ParentID
1, Dir,
2, Mgr, 1
3, Staff, 2
];

[Anonymous]

stalwar1‌ ?

[sunny_talwar]

Not so good with Hierarchy load

[Anonymous]

ok no worries. swuehl‌ ..? any pointers

[Anonymous]

digvijay‌ ..any guidance?

[Digvijay_Singh]

I think Hierarchy will only work if you have one row having node and parent node linked like NodeID, ParentID, Nodename. I don't see in your data where director and manger linking is defined. Is it possible to link them in same record in some way?

[Anonymous]

Digvijay, Thanks. I will update my data source and get back.