Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Power BI Community,
In most of the cases we have Oracle Hyperion Financial Management (HFM) or Oracle Hyperion Planning (HPL) as EPM and we need to integrate those with BI tools. While HFM uses SQL for storing data and meta data HPL uses Essbase. In order to divide the EPM and the BI layer we often extract data and meta data to another SQL database for HFM (we might do the same for Essbase)
Regardless of extracting meta data & data to another DB both original SQL database for HFM and the extracted database contains meta data tables and a fact table. Meta data tables such as Accounts and Entities often have the following columns for hierarchical relationships.
Edit: The above information with strikethrough is incorrect. What I have is a seperate table for each meta data table that holds the hierachical information with ChildID and ParentID columns.
In example, say that Revenue is a child of Total Revenue in the Account Dimension so the tables would be like:
[Account Table]
AccountID | Account Description
...
100 Total Revenue
101 Revenue
...
[Account Parent Table]
ChildID | ParentID
...
100 null
101 100
By looking at the Account Parent Table we can see that member 101 is a child of 100.
AccountID | Account Description | ... | Account ParentID | ...
...
100 Total Revenue 50
101 Revenue 100
...
OR
EntityID | Entity Description | ... | Entity ParentID | ...
...
5 Microsoft 1
6 Power BI Inc. 5
...
As you can see the hierarchical information is stored in the tables rather than stored as separate tables such as common examples of Category and Subcategory tables or Year and Period tables.
I am looking for ways to give this hierarchical information to the eyes and hands of the end users so that they can do drill downs not only in Power BI but also in Tableau and other similar software.
My question is in the Power BI perspective is there any way to create a hierarchical model with this kind of table structure and does anyone have any kind of experience with handling it?
I would appreciate any guidance even if it's unrelated to BI tools or specific way of doing it in SQL DB.
Best
Ozan
Hi Ozan, here's an example from SQL:
(tl;dr: Make it have a foreign key to itself (Account ParentID -> AccountID))
Employees table has a foreign key to its own primary key:
When you connect with Sql.Database (or Sql.Databases), make surethe option CreateNavigationProperties isn't set to false (default value is true, so if you don't see this being set, don't worry)
Sql.Databases("montegoref6", [CreateNavigationProperties = true])
Now, see the ReportsTo column, it represents a hierarchy:
As you can see, there are 2 magic columns created for the foreign key (first is the incoming foreign key, second one is the outgoing foreign key). "Employees(EmployeeID)" column has a table that shows which employees report to that employee (on that row). "Employees(ReportTo)" has a single value, which shows the parent (ReportsTo).
Here's how the Table ("Employees(EmployeeID)" column) in 2nd row (EmployeeID=2) looks like:
(shows the employees who report to 2)
And, this is the Value ("Employees(ReportsTo)" column) on 3rd row (EmployeeID=3) looks like:
Regards,
Oğuz
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.
Dear Oğuz,
Thank you for your reply, I was focused on a different project so I couldn't replied.
Now I tried your suggestion but I end up with something that is useless. I think the drill down functionality is not available with Power BI yet.
Many thanks for your help
Best
Ozan