If you use or are familiar with dimensional applications, you've probably heard of balanced hierarchies. But what exactly does that mean? A balanced hierarchy is a data representation of parent-child relationships with the same data type at every branch level. A simple example is time, where each level of the hierarchy represents years, quarters, and months.
On the flip side, you probably have dimensions that have parent-child relationships, but the levels have inconsistent data types. Let's look at sales regions for example (pictured below). As you can see, the hierarchy is unbalanced with the lowest level of reporting data being represented in both level 3 and level 4.
Although logically, the relationships make sense to your organization, you'll run into issues when reporting on this hierarchy structure in multi-dimensional systems. Sticking with this example, if you wanted to report on Level 4 detail, you would be omitting Caribbean, Central America, North America, and South America.
A simple fix is to balance the hierarchy by adding a placeholder for Level 3 in the Americas branch.
In turn, this can be represented in a tabular format, that can be leveraged by target analytical and business intelligence applications with full dimensional parity of the source system.
If this sounds interesting, then perhaps look at OneCloud as a way to achieve this level of integration. In combination with systems that allow companies to take control of their hierarchies, such as Oracle Data Management Cloud Service (EDMCS), OneCloud transforms the hierarchies so they can be used with any subscribing system.