Parent-Child Hierarchies and Role-Based Security

Pyramid offers flexible, intuitive security for parent-child hierarchies, providing role-based control over how members are viewed and aggregated within hierarchies.

Implementing parent-child hierarchies has long been an Achilles’ heel in most BI tools from query design, processing, structure, to rendering of the hierarchy in a lucid, coherent fashion. If this wasn’t enough of a headache, applying member-level security to the hierarchy makes the challenge extremely difficult (if not impossible). Pyramid’s model tool allows for the easy definition of parent-child hierarchies (see blog), and Pyramid’s PYRANA Query Engine resolves the hierarchical structures and provides extensive, fluid, and intuitive reporting. This facilitates direct queries on supported data sources without requiring data ingestion or data source manipulation.

In this blog, we’ll focus on the setup of member-level security for parent-child hierarchical structures.

The Problem

The complex requirement to display and roll up or aggregate members within a parent-child hierarchy provides a burdensome challenge for most BI tools. Parent-child hierarchies are often used in financial accounting analytical applications or org-chart analytics. Often, these types of analytics are butchered by various hacks or clumsy workarounds.

The additional requirement to restrict users’ ability to view specific members further complicates the delivery of an elegant, tight, and intuitive solution. Depending on the business requirement, parent elements may be visible, while child items are hidden; or parent totals might need to include or exclude child members not visible to the user. Analysts are often left with no other viable option than to create multiple custom reports. In addition to the time-consuming task and the difficulty in maintaining the solution, it also becomes the ultimate antithesis of “a single version of the truth.”

The Solution

Pyramid’s parent-child hierarchy solution facilitates direct query on the existing data source, using the parent-child hierarchy to aggregate and display data as needed. Administrators can apply role-based security to include or exclude specific parents and child members within a hierarchy. When members are not selected, users will be able to view other members together with their amounts while the parent node still accurately accumulates all child nodes. By selecting a parent or child node, all ancestors up the hierarchy tree will be visible. The optional “visual totals” switch enforces totaling only for members that the user can see.

Business Case

Kate is a BI Analyst at Glennies Trading where they have a Databricks data warehouse with Pyramid for their analytics.

Kate wants to allow users from all departments to view both overall sales and sales per region, but users should only see the details for their own region. Kate creates a role for the northeastern region in the admin console. Users belonging to this role will be able to see details for all branches in the northeastern region, but they can only see totals for all other regions.

When a user with the northwestern sales region role views the sales report, Pyramid secures the member-level data from the parent-child hierarchy and only displays the secured data.

A user from the southwest region will only be able to view their branch data, together with other regions’ overall data.

Even though total company sales can be viewed by all regions (as it adds to positive competitiveness), when it comes to Standard Cost of Sales, users from one region must not be able to view other region’s expenses as that will give them insight into the company’s overall profitability (which is restricted information). Kate clicks on the visual totals option to ensure total expenses will only reflect expenses accessible to the user, so nobody can calculate total expenses and by extension, the company’s profitability.

Now when she views the report, she only sees the totals of the items that she can view. Importantly, note how the parent total consists only of members for which it has access.

Summary

Pyramid’s flexible, intuitive security for parent-child hierarchies provides control over how members are viewed, queried, and aggregated within hierarchies. By allowing administrators to include or exclude specific parent and child members within a hierarchy, relevant applications can be optimized to provide world-class reporting and analytics.