Rollups and Rolldowns: The Importance of a Planning Data Mart

By Ric Kosiba, Interactive Intelligence

Many years ago, I was visiting a customer’s contact center to discuss how they were managing their capacity planning process. They had implemented our system several years before, and were outstanding contact center planners

They mentioned that they had regular monthly decision making meetings, where variance to plan was discussed and decisions were made as to what to do about any trends or changes they identified. They mentioned that the decision making team would always refer to “the book,” a set of reports that described the state of the contact center. Of course, I was excited to see this book of reports.

I was blown away. These reports took planning data,
trends, comparisons, and scenarios and turned them into analytic pieces of art. These reports outlined the business risks and the costs associated with every what-if scenario being investigated, and spoon fed executives on the correct set of resource decisions they should make (and the costs of not making those decisions). There were trend charts, pie charts, comparison charts, bar graphs, and, most importantly, sensitivity analysis charts.

Then, looking closer, I noticed that most of the data actually came from our system, albeit made beautiful. My question to them was, “how did you do this?” Their answer was that they had built a data mart and used a business intelligence system to develop their reports. Oh. And that they did a lot of design work.

Automating Reports

Maybe the most impressive part of this decision book was that most of it was automated. Data was dumped automatically from our planning system, their workforce management system, their payroll system, and their HR system. Production of this report was by and large effortless.

When they were developing a planning scenario or a new forecast, they would take the information from the scenario, along with the call center history, and simply drop it into the data mart. Because the reports were previously developed, putting together the master decision-making report was also automatic. And these reports could be rolled up, rolled down, combined, or split up, in numerous dimensions.

Presentation is an Art

Anybody who has read this column before knows that I am big on contact center planning and decision-making. We’ve often spoken in this column about using mathematical models to analyze decisions, but we’ve never discussed how to present these analyses to executives to make clear the business alternatives, and their costs, service, revenues, and customer experience trade-offs.

We’ve never really chatted about planning reports and how to create them. The reason we’ve not discussed this here may be because I am simply terrible at it. And I believe that many, if not most, analytic folks are also not great at it either; in many ways, this other aspect to our left-brained job requires a right-brained skillset. Therefore, it is one aspect of the planning job that is often overlooked — reporting and presenting information in ways that are most clear.

Our customer had access to resources that many of us don’t: they have a centralized reporting team that can be farmed out to different business units to create reporting templates. This team listens to the types of questions that reports were to answer, and suggest better ways to visualize the data in order simplify the decision making. They then set up the report templates for the operating group. They did a great job for this team, their work product was/is beautiful.

Step 1: Build a Data Mart

The first step to this whole process is to make the information needed for the reports current, accessible, and easy to access. For this we lean on the old database. For big picture, executive level decision-making, the information required is a six dimensional view into weekly planning data: historical (what happened), historical plans (what we expected to happen), plans (what we expect to happen — or a scenario we are evaluating), by center, staff group, and contact type.

Data to include are all shrinkage categories, all hiring plans, overtime forecasts, undertime plans, agent attrition, to and from transfers, contact volumes, handle times, service levels, abandons, costs, revenues, customer experience scores, etc… In our system, there are 900 line items, and I expect all of you have similar numbers of items in your capacity plans. If we have all of these metrics in the data mart in all six dimensions, we will be able to create some pretty cool reports!

Step 2: Build This Across Your Contact Center Enterprise

Different organizations in the contact center operation, or even in the larger business, likely provide their own capacity plans. However, if these organizations feed the same information to their data mart, it enhances the utility of contact center reporting. These separate reports can be combined and rolled up, in order to provide an enterprise view of the operation—executives love this.

There is often a temptation to create larger “enterprise models” of the contact center operation in order to answer questions like: “what would happen to the whole business if handle times were to increase by 5%?” However, these larger models are rarely accurate, the economies of scale of one rolled up mega-center is very different from disparate calling groups rolled up. It is more realistic to create accurate models of smaller contact center groups, and roll up the results of any analysis in order to get a top line view than to start with a large enterprise model. So the power of rollups in an enterprise planning data mart is really straightforward: it lends accuracy to the big picture business questions.

Another benefit of an accurate enterprise view of the center network performance is that variance analyses against historical plans are also accurate and meaningful. It is easy to defend and explain variance if the plan remains consistent and intuitive from the top line enterprise view to the bottom line staff group or call type level.

Further, an enterprise view of the contact center operation allows comparisons amongst those different contact center groups, especially if the different groups use the same planning tool and metric assumptions to develop their plans. Comparisons across similar operations help with best practice analyses.

Step 3: Build and Automate Reports

This is the step I’m going to skip, because it is so outside of my skillset. The only thing I will tell you all is that it is very valuable to find a good reporting analyst or data visualization consultant to help you make your business intelligence system the analytic weapon it can be.

Final Considerations

First, contact center planning may involve some touchy and sensitive scenarios. Executives often dabble with scenarios such as closing centers, outsourcing more or less, or growing or shrinking the business. None of these scenarios may ever come to pass, but may be part of a larger imagination of the operation. These scenarios must be held securely between the analyst/ planner who is evaluating the scenario and the contact center executive who is asking the questions. It is very important that any data mart have the ability to lock certain scenarios from view.

Second, scenario management in general — even those that are not terribly touchy — require management discipline. There are usually a lot of scenarios, some one-offs and some official plans. They need to be able to be catalogued, deleted, kept private, and via the name of the scenario within the data mart, understood at a glance for what it is. Official budget plans need to be called something official sounding, while scenarios need to
be called something else, or stored in a place understood to be aresting place for analytics.

But, a planning data mart enables clear and beautiful sets of plans, repeatable and consistent, for all scenarios, all budgets, and all interim plans. You don’t have to wield a paint brush to appreciate a beautiful piece of analytic art.

Ric Kosiba, Ph.D., is a charter member of SWPP and vice president of Interactive Intelligence’s Decisions Group. He can be reached at or (410) 224-9883.