Flattening hierarchies involves including the hierarchy members along with the members that are selected in the dimension. The options for flattening hierarchies can be found in the Advanced Properties tab of the Subset Properties dialog and is enabled by checking the Include Hierarchy option. These options are shown below:

 

Flattening Options

 

Enabling Hierarchy Flattening

Selecting Include Hierarchy option will enable hierarchy flattening and TM1Connect will insert a column into the returned result for each level that is contained in the dimension. The columns will be named using the dimension name suffixed by .L# (such as Period.L1, Period.L2). You can also return attributes with the hierarchy members (see Setting Flattening Options below).

TM1 Dimension

Flattened Dimension

Level Based Hierarchy

Flattened Hierarchy

hmtoggle_arrow1Flattening Level-Based Hierarchies

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.

hmtoggle_arrow1Flattening Ragged Hierarchies

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:

OrgChart

Flattened 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:

Manager Hierarchy - TM1

Manager Hierarchy

 

Setting Flattening Options

Once you have determined how to properly restrict the members that are to be used when flattening the dimension, the options for dealing with the members can now be set.

hmtoggle_arrow1        Fill Gaps in Hierarchy        

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:

Flattened Org Chart - Left to Right

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.

hmtoggle_arrow1        Include Member in Hierarchy        

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:

Include Member in 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.

hmtoggle_arrow1        Include Attributes in Hierarchy        

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:

Include Attributes in Hierarchy

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.).

Caution_200

CAUTION: When you enable this option, any attributes which you have selected for the member are also returned for each and every level. Care must be taken when using this option; as the number of columns can explode.

 

hmtoggle_arrow1        Start Hierarchy from Member        

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.