Tree tool and Essbase sourcing

[sbjkiss]

I am trying to wrap up a use case for Alteryx based on our use of Essbase data.  My present dilemma is related to using the true Essbase Hierarchy for a multi-selection box that would become a filter in the data set.  At present I am bring the full hierarchy in.  (Ideally, I would use the tool to affect the MDX limiting the inbound data in the first place - but that 's for another time. )  

I have the Exo Insight Casabase tool pulling the hierarchy (visual: Essbase Tree) and would like that to be used as the source for the drop down.  A sample of the Essbase tree that is pulled from Exo can be seen in the Hierarchy.JPG file. 

Is there a way to link the Essbase extract to the Tree tool?  Or, would I need to drop that to a table and link the table? Or, something else?  Almost all of our reporting is based on Essbase, so being able to filter the tree with defaults already selected would be ideal.

The tree would then need to be associated to an action I assume to build the filtered tree set.

Thank you,

Sean 

[RolandSchubert]

Hi @sbjkiss ,

unfortunately, I don't know the output of the Exo Insight Casabase tool, but I think, to use the Tree tool, you have to write the structure to a file using a specific structure. Two columns are needed  - "Key" and "Description".  You can translate "Key" as "position in hierarchy" and "Description" as member name (or alias). So related to your example data, the file would look like this:

Key, Description

0, Member 0 (Gen1)

01, Member 1 (Gen2)  -> first child of member 0

011, Member 2 (Gen3) -> first child of member 1

0111, Member 3 (Gen4) -> first child of member 2

0112, Member 4 (Gen4) -> second child of member 2

0113, Member 5 (Gen4) -> third child of member 2

Depending on the maximum number of children, the number of places to build the key will vary. 

I've added a sample to illustrate file structure and Tree tool configuration, hope this is helpfull.

Best,

Roland 

[sbjkiss]

Looks like a good option.  If I treat those as text I should be able to sort properly.  That sorting should let me retain position of data as I aggregate where necessary.  I'll give it a look sometime this week.

Thank You,

[sbjkiss · Accepted solution]

I was able to get this working with a similar concept to what is above.  I pulled two extracts from Casabase.  One for the Orgs and one for the Accounts.  There are 21 levels in each.  For the orgs I concatenated the 21 levels into a column titled Value.  I used a second column from the Hierarchy which is an alias of the org name.  That field was titled "name".  The 2 column result were pushed to an xlsx file.  I pointed the Tree tool to the result xlsx file.  This gave me the data in a hierarchy that was visible in the Tree Tool.  I repeated the process for the Accounts.