TM1Connect allows you to flatten both level-based (uniform) hierarchies as well as ragged (non-uniform) hierarchies. A level based hierarchy is the most straight forward type of dimension to flatten. As a general rule, when flattening a level based hierarchy, you should select only the members of one specific level in your member selection list (for example months) and then let TM1Connect return the flattened hierarchy from these members.

In most applications, the leaf level members (TM1 Level 0) are usually desired as the members of the dimension to be returned. When level 0 is used, options such as Fill Gaps, and Include Member in Hierarchy are not applicable and can be ignored. However, if elements other than level 0 are chosen in a level based hierarchy, the number of columns added will still be the same as when level 0 was chosen, and gaps will appear between levels. These gaps can be managed using the Fill Gaps in Hierarchy options.

Ragged hierarchies are non-uniform and oftentimes require more consideration when determining which members to return with the subset and which options should be set when flattening the hierarchy. Consider, for example, an employee org chart:

In this example, a manager is defined as someone who has one or more employees working for them, but an employee can report to someone at any level within the organization.

How you flatten this dimension will now depend on what information you need to obtain from TM1 and how it is to be used in your applications. If the goal is to obtain a complete organization chart, simply select all leaf level employees as the member selection, and then check the Include Hierarchy option. The dimension will be flattened in a fashion similar to the table below.

If, however, only managers and above are needed, the member selection becomes more difficult. In this example, selecting level 1 would return Managers well as the CEO and CFO because they have employees that report directly to them, so level based selection doesn't work. In ragged hierarchies, oftentimes attributes and the use of them in MDX statements can assist in selecting the right set of members for structuring the results. For this example, one possible MDX statement may be:

Filter( TM1SubsetAll( [OrgChart] ), [OrgChart].[Position] = "Manager") 

The corresponding display in TM1 and TM1Connect using this MDX would be:

The Fill Gaps in Hierarchy option will ensure that a value exists in all levels of the dimension as it is flattened into columns. How you fill in the gaps will also sometimes depend on the needs of the application. The most common approach is to fill from Left to Right. With the OrgChart dimension from above filled in with the Left to Right option set, the output becomes:

In this example, using the Left to Right option, Mary fills any gaps that exist between her and Bill. It is important to understand that how you use this information will determine if this is the right treatment of filling the gaps. In this example, the column OrgChart.L4 doesn't necessarily equate to a person holding a manager position, but it does indicate who is the direct reporting manager of the person in the OrgChart column.

Using the Right to Left option reverses the order such that rather than propagating Mary fills any gaps between Mary and Bill, Bill is propagated to all empty columns between Mary and Bill. Again, use of this information in your applications will dictate which method is to be used.

Setting this option incorporates the member into the hierarchy as it is flattened and includes an additional column in the output for this member. The most common use of this option is occurs when a dimension member is to be used as a key to locate the record which contains the fully flattened hierarchy. This option is also normally used when gaps in the hierarchy are not filled in. The image below illustrates the results of checking this option along with leaving gaps in the hierarchy:

Notice that the column OrgChart.L5 has been added and that the member in the OrgChart column (defined by the subset member selection) is repeated at the last applicable position within the hierarchy. Also notice that the subset is not restricted to people at an employee position, but all positions. With this method, Mary can be queried by using the OrgChart column to lookup (or display) the organization chart for her position. Similarly, Bill appears in L3, Sam appears in L5 and Martha appears in L4, but they also appear in OrgChart, so they too, can be queried.

Normally when you select attributes to be included in a subset, only the attributes for the members from the Member Selection Method are returned (i.e. OrgChart column in this example). In certain situations, attributes for even the flattened levels are needed, such as a Description or Name attribute. With this option enabled, the column name will be appended with the name of the attribute, as shown below:

In addition to the attribute being added to the OrgChart column (named OrgChart.Position), the attribute has also been added to the levels (OrgChart.L2.Position, OrgChart.L3.Position, OrgChart.L4.Position etc.).

If the dimension that is being flattened has multiple hierarchies, by default, TM1Connect will select the first parent element for each dimension member returned from the member selection. Oftentimes the first hierarchy defined in the dimension is the one TM1 recognizes as the first parent, however, in some situations, this may not be the case. The Start Hierarchy from Member option allows you to choose which root element you want to confine the hierarchy flattening to.