Forum Discussion
Creating hierarchies in SQL (Oracle HFM & Essbase models)
Dear Oğuz,
Many thanks for your response, when I connect to SQL DB I can see the relationship columns.
I am sorry to say that I noticed that I posted an incorrect table structure. I said that I have the relationship columns in the meta data tables such as:
AccountID | Account Description | Account ParentID |
But this was not true at all what I have is as follows:
So I have a seperate table to hold hierarchical information on it and the table of Account and Account_Parent are joined on ChildID which is equal to ID column in Account table.
As I said before I have an hierarchical relationship in my Account dimension as in the example:
5398
|-> 5419
What I want to achieve is to let the user drill-down on those hierarchies of Account and/or Entitiy dimensions. For example in the picture below you can see the ID from Account table, ChildID and ParentID from Account Parent table and dData from Fact table. In the red rectangle we see that member with AccountID 5419 is a children for member with AccountID 5398.
So what I am trying to achieve is to drill-down to 5419 when I click to 5398.
Is there a way to do that?
Regards
Ozan
At first (in ACCOUNT table), from those Table columns, remove the FACT, _FACT_NEW etc. columns to avoid the confusion in the future steps. (Don't remove ACCOUNT_PARENT(ID) column)
You can expand the Table column (I think in your case it'll be ACCOUNT_PARENT(ID) column).
You may need to expand the expanded column again since you have a middle table.
And then, in each row you'll be seeing a parent and one of its children. (There will probably be a lot of rows now).
Expand the inner(expanded, the one belongs to the child) Fact table, with only choosing dData column.
Now, in each row you should be seeing a parent ID, a child ID and the child's dData value (like your last screenshot)
And then, Group by the parent account ID (right click on the column name, "Group By"), choose "All Rows" operation.