Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ozancavus
Regular Visitor

Creating hierarchies in SQL (Oracle HFM & Essbase models)

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

4 REPLIES 4
arify
Employee
Employee

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:

Untitled.png

 

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:

 

Untitled.png

 

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:

Untitled.png

(shows the employees who report to 2)

 

And, this is the Value ("Employees(ReportsTo)" column) on 3rd row (EmployeeID=3) looks like:

Untitled.png

 

Regards,

Oğuz

@arify

Dear Oğuz,

 

Many thanks for your response, when I connect to SQL DB I can see the relationship columns.

 

Example.png

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:

 

Data Model.png

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.

 

Data.png

 

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).

Untitled.png

 

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.

 

 

 

 

@arify

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors