Generational Extract Examples

This topic provides some examples of different extract options such as the node and ancestor filters, the maximum depth, and the balance fill options in order to illustrate different ways to use generational extracts.

Tip:

• A balanced hierarchy is one in which all of the bottom nodes are at the same level.

• An unbalanced hierarchy (or natural hierarchy) is one in which the bottom nodes are at different levels.

  You can use the Maximum Depth and Balance Fill Option fields to balance an unbalanced hierarchy by repeating the parent, node, or top node in order to put all of the bottom nodes at the same level. This is illustrated in Example 2: Using Balance Fill Option in an Unbalanced Hierarchy.

Example 1: Node and Ancestor Filters in a Balanced Hierarchy

In this example, we will use the node and ancestor filters to extract a report of sales representatives by region. We will use a node filter to return only bottom level nodes, and then we will use an ancestor filter to reduce the report by filtering out the states to get a report by region. Because this is a balanced hierarchy, we do not need to use the Balance Fill Option.

For this example, we will use a hierarchy that has a top node called Sales Representative by Territory (SRT) with several regional nodes under it: Midwest (MW), Northeast (NE), Southeast (SE), Southwest (SW), and West (W). Each region has a series of states under it (Iowa, Illinois, Indiana, Michigan, and Ohio under the Midwest, for example), and each state has a list of sales representatives as bottom nodes:

We will start by extracting the names of the nodes and their ancestors by adding Core.Name for the ancestors and Core.Name for the nodes to the extract (see columns in an inset, below).

Notice that several rows in the extract have information that is not useful in our report. For example, rows 2, 3, and 4 contain ancestor information but not the bottom nodes that have the sales representative information that we are looking for. We can use a node filter expression (displayed in an inset) to return only bottom level nodes.

Next, we want to reduce our extract to display the sales representatives by region, but not by state. This requires two changes to our extract. First, we need to identify the ancestor level where the state information is stored. We do that by specifying the maximum depth (see inset) so that the extract adds header information to each column. Notice that columns A, B, C, and D now all have headers that identify the levels and node name.

Now that we know that the state information is at ancestor level 3, we can add an ancestor filter to filter out level 3 and return the regional information that we are looking for. The ancestor filter is displayed in an inset. Remember that now that we are filtering out a level, we need to change our Maximum Depth to 3 instead of 4.

We finalize our report by adding some additional columns such as Employee ID, Start Date, and Job Title (see inset) and using custom headers to identify the information in the report.

Example 2: Using Balance Fill Option in an Unbalanced Hierarchy

Next, let's look at an example of using the Balance Fill Option to balance out an unbalanced hierarchy. This time, we will use an Account hierarchy. Notice that the bottom nodes 1770 Goodwill Asset and 7110 Advertising are at different levels in the hierarchy.

When we extract this hierarchy with a maximum depth of 8, the columns for levels 5, 6, and 7 for bottom node 1770 Goodwill Asset have null values, making this hierarchy unbalanced.

We select Repeat Parent in Balance Fill Option to fill in the hierarchy with the parent name and description where there are no values (red highlighted area, below). The hierarchy is now balanced.