You can use templated organizations to create an organization tree that tracks the same KPIs for each organization. With rollup KPIs, you only have to update the KPIs at the bottom of the tree. The software will then automatically roll the KPI values up the tree to create totals for the KPIs in the higher organizations.
Building Rollup KPIs
KPI values can be updated in a variety of ways. They can be updated manually, they can be calculated, or they can be part of template rollups. When you select the "Template Rollup" update type, Spider Impact automatically aggregates the KPI values from children organizations that are based on the same organization template.
To set up these automated rollup KPIs, first create an organization that you want to use as your template. Any KPIs in the template that you want to be automatically calculated should have the update type of "Template Rollup." You also can change the Template Rollup Equation, which determines how the KPI values will be aggregated together as they’re rolled up the organization tree.
Once you’ve created your organization with rollup KPIs, the next step is to create templated copies of that organization in a tree structure. In this example, “Rollup Template Example” is the template organization we built, and the six organizations underneath it are templated copies.
Finally, update the KPIs in the organizations at the bottom of the organization tree. The KPIs in the organizations higher up the tree will automatically have calculated values based on the KPIs in lower organizations.
For example, when we go to the highest-level organization and hover over the “product revenue” actual value, we can see that its actual value is being automatically calculated.
We can click on this value to see more information about where the data actually comes from.
By hovering over the different parts of the equation, we can see that this highest-level KPI is the sum of the KPIs in the four organizations at the bottom of the tree. If you remember, though, the rollup tree is three levels deep. Why is the automatic template rollup equation skipping the middle level of the tree and going directly to the lowest level?
That’s on purpose. For “sum” template rollup equations, the results would be the same either way, and it makes things a little simpler to show where the data is really coming from. For “average” template rollup equations, skipping the middle levels avoids some messy math problems that you’d get by taking averages of averages.
Separate Tree and Time Aggregations
Rollup KPIs are aggregated in two different ways. The template rollup equation is used when aggregating values up the organization tree. The aggregation type is used when aggregating smaller calendar periods into larger calendar periods, like turning monthly values into a yearly value.
An example will make it easier to understand the need for separate ways to aggregate. Let’s say we run a large waste disposal company and we’d like to reduce the number of trucks that are out of service due to them being repaired. We have dozens of trucks in each city, and thousands of trucks across each region, so we realize that there will always be some that are broken. We just want to minimize that number over time.
We’ll create a KPI called “Number of trucks out of service” that will track the number of broken trucks. We’ll also make it a rollup KPI and build out a multi-level templated organization structure that matches our company.
So, we have a rollup KPI that takes the number of broken trucks and aggregates them up the org tree. What should the template rollup equation be? Sum makes the most sense, because the number of broken trucks at the regional level would be the sum of all broken trucks in that region’s cities.
Now, how do we aggregate our broken truck KPIs over time? That’s a little trickier. Let’s say each of the cities update their KPI with a new value every week. What should each city’s KPI value be when you look at it yearly? It doesn’t really make sense to add all of the weekly values together. That would mean that the more often you measure the KPI, the higher the yearly value will be.
For this KPI, an average aggregation type makes the most sense. If you measure the number of broken trucks every week, and average those numbers over a year, you can good a good feel for how many trucks are out of service most of the time.
With this KPI structure, you could go to a region in the organization tree, change the application calendar to monthly, and you could see the average number of broken trucks in that region for that month. Each city updates their broken truck KPI every week, and the software takes care of the rest.
And that’s why there are two different ways to aggregate rollup KPIs. There are situations like our example above where the KPI value should be summed up the rollup tree but averaged over time.
Rollup KPIs Without Templates
The vast majority of the time, rollup KPIs are only used in templated organizations. You may notice, however, that the "Template Rollup" update type shows up as an option for KPIs that aren't part of a template. That's because in some rare situations, you may want to use rollup KPIs with non-templated organizations.
The requirements for non-template structure are the same as templated. The organizations still need to be in a tree, and the scorecard structures that you're using for rollup KPIs need to have the same names. The only difference is that the software is matching the KPIs based on their names and their ancestor's names rather than the template they're using.
This is so uncommon, however, that we decided to call the update type "Template Rollup," even though the organizations don't technically have to share a template.