Skip to main content

How-to use hierarchies

This guide aims at explaining what hierarchy feeding is, and how to improve performance in different use cases.

Hierarchies and their feeding at start-up​

What is hierarchy feeding?​

Upon launching an application, many queries are generated and executed on the external database, with most of them related to feeding hierarchies.

For each hierarchy, the feeding is required to retrieve the list of members. This list of members is used notably:

  • for some Copper measures (such as Lead or Lag)
  • in the filter widget of the UI, for the user to select which members to use in the filter
  • to complete the result of a query with missing member for an MDX query without "NON EMPTY"

A feeding hierarchy query looks like:

SELECT
  T0.MY_HIERARCHY AS "MY_TABLE.MY_HIERARCHY_0",
  COUNT(*) AS "contributors.COUNT_1"
FROM
  MY_TABLE AS T0
GROUP BY
  T0.MY_HIERARCHY

Why can it be slow?​

Performance issues can arise from:

  • Such a query would scan the entire table, and can be slow if the table is large.
  • A project with hundreds or more hierarchies generates as many queries upon start-up, potentially overwhelming the external database.

Define the hierarchy as virtual​

Defining the hierarchy as virtual completely eliminates the feeding and the associated query, so is the fastest.

Check the guide on virtual hierarchies on how to define it as virtual as well as the limitations of a virtual hierarchy.

Define the hierarchy as factless​

Defining the hierarchy as factless will make the feeding query execute on a potentially much smaller table, hence greatly increasing the execution time. It will also behave as a normal hierarchy, without any limitations.

Check the guide on factless hierarchies on how to define it as factless.

Use aggregate tables​

Adding an aggregate table to the external database can be a way to make the feeding query execute much faster. The hierarchy will behave as before and be fully functional. The main limitation is that it requires to define and maintain the aggregate table in the external database, as well as adding it to the project.

Check the guide on aggregate tables on how to define and use an aggregate table.