Using the Report window we’ve built templates and taught Monarch how to extract our data. In the Table window we’ve added new fields and new values, but even when using filtering and sorting the data, it can be difficult to see the big picture; we’re often just too close to the details.
To give us a new perspective, on day 14 of the 30 Days to Become a Better Monarch Modeler, we’ll begin to explore Monarch’s ability to summarize data using Monarch third and final viewing mechanism, the appropriately named Summary window.
For those unfamiliar with Monarch’s summaries, they’re very similar to Excel’s pivot tables, which aggregate data by grouping data either horizontally (in rows) or vertically (in columns). By this description, you’d think that they’re not much different than a regular tabular spreadsheet, but they’re fundamentally different.
Summarizing and aggregating data is a critical step in converting what often started out as a simple and unassuming report into a management tool that provides Key Performance Indicators (KPIs) and other metrics. KPIs are indispensible for evaluating and managing an endless array of topics.
How to define a new summary
The process begins by assigning the new summary a unique name, and by associating a particular filter to the summary. Only data which satisfies the filter criteria will be reflected in the summary.
It’s important to note that the default filter is literally that: “Default filter”. What it really means though is “Active filter”; the filter that is currently controlling the display of records in the Table window.
The next commonly selected item is choosing whether to include a grand total line at the bottom of the summary.
To define the content of the summary navigate to the Fields tab. When the summary is complete, it will display data beginning with the left edge and continuing to the right edge. On the left edge will be “key” fields, followed (optionally) by “item” fields, and finally “measure” fields.
The exception is that we can set a single key field to display it values across the top of the measures. This is particularly useful for calculating periodic KPIs, whether for a weekly, monthly or quarterly basis or some other timeframe.
When fields have been added to the various key, item and measures categories, double-clicking on a field name displays yet another dialog window that is used to determine the presentation of that field, and depending on the field category.
Key fields offer five tabs:
- General, which allows for customizing the name of the field (which doesn’t affect the original name of the field in the Table window), and the number of characters displayed, or width.
- Matching, which allows for controlling which values will be displayed in the summary.
- Sorting, which allows whether we want to sort the field (either ascending or descending), or to sort the key based on the values in another field.
- Display, which controls how, and what, data is presented onscreen.
- Coloring, which further customizes the display characteristics of the data.
Item fields offer the General, Sorting and Coloring tabs.
Measure fields are subtly and yet significantly different. They have the now familiar General tab, but the Calculation tab is new, and the last tab is Coloring and Limit, which permits for presenting special values of interest in different presentations than other displayed measure values has a different nature than the Coloring tabs available when fine-tuning key and item fields.
The Calculation tab allows us to define which aggregation methods we want to employ, or to define other customized formulas which enable deriving for management metrics. The thing that significantly differentiates formulas for measures from formulas used in calculated fields built in the Table window is that any fields used within summary formulas must be used within aggregating functions, such as SUM(), MAX(), and MIN(). Of course we can still use conventional mathematical operators and constant values in addition to aggregated fields.
The final tab on the summary definition is named Chart. The approach here is to select a chart type, define how you want to cluster the data points on the chart, define a title for the chart, select which of the various display options you want to employ in your chart.
While there are numerous options that produce a range of effects on your charts, for one specific reason I won’t describe them in detail here. And what’s that reason? In my opinion, you shouldn’t spend your time creating charts in Monarch.
Monarch’s charting abilities, which haven’t significantly changed in years, just don’t compare with what you can produce by yourself with Excel, especially if you use MicroCharts by Bonavista Systems. (UPDATE March 2012: BonaVista Systems has closed due to the sad and untimely passing of its owner.) The best use of your time devoted to charting would be spending it building useful visual tools in Excel.
If you don’t have much experience with Excel’s charts or want to sharpen your charting skills then I’d recommend reading Jon Peltier’s excellent blog regularly.
Even Summaries are About the Details
Monarch’s ability to create summaries that you, or your model user, can interact with without changing the structure (key, item and measure fields) that you’ve built into the summary is probably it’s biggest advantage over Excel pivot tables. Even Excel 2007 pivot tables and their inherent ability to expand and collapse fields doesn’t compare to the simple usability of Monarch’s drill up and drill down buttons.
When you define multiple key fields in a summary, Monarch automatically activates the toolbar icons to drill down and drill up, hiding or displaying lower level key fields as you go.
As much as I enjoy the work in the Table window that lets us add new fields via calculated fields and external lookups and such, it’s really when we build and, more importantly, use summaries that we can see just how far we’ve come.
You don’t agree? After you’ve added a summary or two, go back to the report window and look at that basic report that you started out with. Odds are that unless you’re working with the most basic data needs, you’ve added something of value to the data. Maybe it’s new calculations; maybe it’s connect that data to an external source and importing a few related fields. Maybe it’s just as simple as adding subtotals in the summary that weren’t included in the original report. Maybe you’ve spun things around and have created not just a new way of looking at the data, but a new way of thinking about the data.
And why stop there with only one summary? In the dialog used to manage existing summaries, you can click the Duplicate button and make some minor changes, say re-sequencing the key fields, so that you can easily switch between the two summaries for different perspectives, possibly for different audiences. Using an outstanding accounts receivable report, a collections manager wants to see customer totals, while the sales manager needs amounts invoiced by salesperson. It’s the same source report but they’re completely different business applications of the data.
Does the original printed report do that? Nope, not even close.
With such a robust and important feature, there’s much to discuss concerning summaries, but today isn’t about delving into every possibility and feature available to a summary, but rather is about getting us accustomed to building more summaries and really pushing ourselves to take advantage of what’s readily available, but often overlooked.
In Summary Then…
I’ve seen many models come my way that don’t have any summaries defined. That’s like buying great audio equipment and only ever listening to AM radio. Monarch isn’t just an extraction, transformation and loading (ETL) tool. When you combine it with your knowledge of your business and its varying information needs then it can be a fantastic analytical tool. But you have to elect to use it in that manner.
By introducing fresh thinking and new perspectives, simple summaries will demonstrate why it is that you continue to choose to excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 15 of the series, or review Part 13.
