Her Majesty Hierarchy. Classification and storage methods in MS Excel

MDX), which are directly designed to work with hierarchies. But this is a rather difficult path.

How to apply it practically?

There are two modes of working with hierarchies: 1) you need to design a hierarchy in Excel 2) you were given a ready-made hierarchy and you need to work with it. In any case, you should:

  1. Recall the classification of hierarchies. Define Hierarchy Class

  2. Consider which sorting mode makes the most sense. Is it needed at all?

  3. Decide if there are any restrictions on the homogeneity of children. Usually not.

  4. It is important to decide where and how best to store such a hierarchy so that it is convenient to work with. Remember the 8 formats that we discussed above

  5. Formulate controls and checks for the integrity of the hierarchy. Try to automate them.


What is this all for? I set myself several goals:

  1. When working with complex systems, half of the problems arise due to the lack of common terminology. Having common terms is critical for solving practical problems with hierarchies

  2. Problems with hierarchies arise every day, they need to be solved somehow using Excel, sometimes in conjunction with Python. The two most common problems are determining the optimal format for storing a hierarchy in Excel and interpreting hierarchies that someone has already created. I hope my classification and list of storage formats will simplify these processes.

  3. A deep understanding of the hierarchy will allow you to control its integrity, automate checks in Excel and Python, analyze and convert formats

ps Digitalization of consulting

The systematization of approaches to working with hierarchies takes place as part of our first step towards digitalization. Its concept is as follows:

“All key data on the project must be in 1NF (flat table), or there must be an algorithm for their transfer to 1NF”

And below is a complete list of areas of digitalization in which we are moving forward:

  1. Data, flat tables, relational databases

  2. Algorithms, Python, VBA

  3. Hierarchies

  4. Counts

  5. Mathematics, systems thinking, combinatorics, set theory

  6. Neural networks

>>> here we publish a lot of interesting videos on these topics (YouTube)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *