Using dataset rollup trees

Dataset rollup trees allow you to track hierarchical data. They’re great for companies and governments that have large organization structures, as well as for geographic data (Country > State/Territory > Postal Code).

In this example we have a military fitness test dataset with 333,000 records. We’ve added the Unit Identification Code (UIC) field to the Explore tab, and we can see that the W4K9AA organization has the most fitness test records in this dataset.

Militaries are very hierarchical organizations, so we’re going to edit the UIC field and turn it into a rollup tree.

We change the data type from Text to Rollup, and then choose which rollup tree we want. Rollup trees are built and managed in the Administration section, and we’ll cover how to do that next. In this example we’re choosing Chain of Command.

Now when you add the UIC field to the Explore tab, you see a tree instead of a list of UIC. That’s because Impact has matched the UICs to items in the Chain of Command rollup tree and is now able to show you aggregated totals. And, just like every other field type, you can click on an item in the tree to drill down.

At the top of the UIC panel it now shows the tree level that we’ve drilled down to, as well as a back button that will take us to where we were before. As you navigate up and down the tree, Impact automatically applies filters to restrict records to only that tree level. In this example you can see the Gender panel updating with new numbers as we go down the tree.

Here we’ve drilled down to a third tree level. Most rollup trees are fairly small, but Impact has been tested to perform well with trees as large as 50,000 items.

Managing dataset rollup trees

You can build and manage dataset rollup trees in the “Dataset Rollup Tree” page in Administration. On the left is the rollup tree that you choose in the dropdown above, and on the right, you can edit the keys for the selected tree item. In this example, any time a field’s value is “WDARFF”, it will match with the “Dept of the Army” item in the rollup tree.

It’s most common for every item in the rollup tree to have a single key. Sometimes, however, tree items will have no key at all and will instead be used only for showing aggregated data from its descendants. Other times a rollup tree item will have multiple keys.

Because rollup trees are used across multiple datasets, each dataset may have a different way to reference the tree items. To support this, Impact allows you to create multiple keysets. For example, one keyset may be “By Payroll ID” and another keyset may be “By Human Resources ID”. When a rollup tree has multiple keysets, you’ll also need to choose which keyset to use when setting up a rollup tree field in your dataset. Keysets are important because different datasets can use the same key to refer to different items.

Just like other places in Impact, you can put the rollup tree in edit mode.

This allows you to drag and drop items to reorder the tree, and the selected item has a tooltip menu on it for modifying tree items.

To change the rollup tree that you’re editing, select the tree you want in the dropdown in the upper left.

Next to that is the Manage Rollup Trees button.

This opens a dialog where you can create, rename, reorder, delete, update, and download rollup trees.

Importing rollup trees

In addition to modifying rollup trees within the software, you also have the option to upload an entire rollup tree as a spreadsheet or JSON file. This works for brand new rollup trees, and also for updating existing trees. Note that if you upload a spreadsheet, it's a good idea to keep that spreadsheet file around for later modification, because downloaded rollup trees from Spider Impact are always in JSON file format, not as spreadsheets.

Spreadsheets should be formatted like the example shown below. You can specify a tree item is a child of another tree item by putting it one column further to the right. The first cell in a row that has data in it will be treated as the label of the tree item. All subsequent cells will be treated as the keys.

You can put keys in separate columns, and you can put keys in the same column separated with commas. In this example, both "Oklahoma City Thunder" and "Golden State Warriors" have two keys.

If you're prefer to upload a JSON file, you'll need to use a format like this:

{
	"key": "Key1",
	"label": "Label 1",
	"children": [{
		"key": "Key2",
		"label": "Label 2"
	} , {
		"key": "Key3",
		"label": "Label 3"
	}]
}

Dataset rebuilding

As described in the Creating and Editing Datasets and Updating Dataset Records articles, datasets automatically rebuild whenever you edit a field or update their records. Most of this rebuilding isn't noticeable because users still see the old data while the dataset is rebuilding. There's just a small notification on the Datasets Explore tab.

This is exactly the same thing that happens when you add or delete items in a dataset rollup tree. Any dataset using that rollup tree will be transparently rebuilt behind the scenes while people continue to see the old data until the new data is ready. Similarly, renaming a rollup tree item doesn't cause anything to rebuild.

There are more invasive changes you can make to a dataset rollup tree that cause all datasets using it to not only be rebuilt, but also to become unusable until the rebuilding finishes. These changes are:

• Moving a dataset rollup tree item
• Changing a dataset rollup tree item's key(s)
• Reimporting a dataset rollup tree

Dataset rollup tree groups

The other tab on the Admin > Dataset Rollup Trees page is Rollup Tree Groups. This special kind of group can dramatically simplify the management of tree-based permissions.

In this example we've created a rollup tree group for each of our three different types of users: IT Administrators, Managers, and Standard Employees. When you click on a rollup tree item on the left, you can assign users and groups to the different rollup tree groups on the right.

The most important thing to realize here is that every item in the rollup tree has different members for these rollup tree groups. As we click through the rollup tree on the left, we see different people appearing in the rollup tree groups on the right. If someone is a member of a rollup tree group at a higher (or lower) level of the tree, they won't show up in the current level you're viewing. The rollup tree group tab is all about assigning people to specific roles at specific levels of the rollup tree.

Once that’s done, we can use these rollup tree groups to quickly assign permissions to datasets. All you need to do is turn on advanced permissions for a dataset and then add your rollup tree group like you would a user or a group.

By giving "Managers" this permission, every member of that rollup tree group can now see records where the Unit field value is in their rollup tree level or below. You can edit this permission, just like you can with users or groups.

The only difference is that a rollup tree group has a filter that can’t be removed. This permanent filter shows that SARCs can see their rollup tree item and descendants for the Victim UIC field.

You can always add additional filters, though. For example, we could add a new filter that also limits access to only fields with a “Restricted” value of “False”.

Assigning permissions to datasets isn't the only use for rollup tree groups. You can also use them to quickly assign permissions to templated organizations that were created from rollup tree fields. See the "Organization permissions for rollup tree groups" section of the Templated Organizations from Datasets article for more information.