Experimentation Produces Monarch Success

In the last post we talked about the process of learning the fundamentals of Monarch, and this leads nicely into a discussion of Monarch’s unwritten (?) cardinal rule and how we can take advantage of it.

What’s the rule? Simply put, “Don’t allow the user to alter the original data in any way with Monarch”.

What’s the effect of the rule? No risk.

There’s no way that you can corrupt your report file with Monarch when learning how to model reports. That’s entirely unlike the messing around that I did with early spreadsheets and databases. You’ll never lose data or edit the original values permanently. Modeling is totally safe.

Just be carefully when exporting.

And the knowledge that you can do no wrong affords you great comfort.

You can try new things. You can experiment. You can fail. And if you do, all is not lost, save for perhaps a few minutes of your time.

But you may very well succeed, and when you do it’ll make all of your efforts worthwhile.

The Big Win

Such was the case for Grant Perkins, the UK-based consultant who pioneered an exceedingly useful little trick that’s now referred to as the “guru trap”. It’s a technique that permits a model to use append templates to add data to a record when the appended data physically appears subsequent to – not prior to, as is normally the case with append templates – the detail record displayed in the report.

Grant has contributed to ExcelWithMonarch.com in the past, and I’m pleased to have his input for you again today. Take it away, Grant…

I’d heard about Monarch and picked up a copy and spent a year or so using it on and off on a few projects. When a new reporting challenge was presented I looked at Monarch first to see if I could get the data from standard reports (lots of complex time calculations already done there) rather than reinvent the wheel. Often I could, sometimes I couldn’t.

A couple of years on, I extended the use somewhat when we needed to retrieve data, including a lot of long form text, from an external supplier’s system. The only method available to recover the information was to screen scrape using a screen emulator on the dumb terminal/dial up connection they had provided. A colleague who had authorized access to the supplier system wrote the screen scraping script and we ended up with a large file of screen dumps, mostly with data consistently positioned but there were a few exceptions that required conditional field calculations to extract and format ready for upload to our database.

Hmm. Monarch could be deployed much more widely than I had imagined.

Soon after that I discovered the Monarch forum and shortly after that was pleased to be provided with a copy of the CD that was made available to attendees of the 2003 Datawatch User Conference. On that CD were some presentations called ‘Voodoo Monarch’ that looked rather interesting …

Suddenly it was as if a door had opened and the lights came up bright. There was SO MUCH more one could do with the features and functions in Monarch than I had previously identified.

So I started to experiment hoping for success but knowing that failure would be a valuable learning experience anyway and cost little time.

The approach I adopted was to look at a problem report situation and come up with a few ideas that might or might not work. Off-the-wall was good for seeking out these ‘Voodoo’ approaches. Sometimes I would find something that worked without being sure why it worked.

As the ideas are applied to more and more examples of problems (forums are a great source of learning material) one can start to work out what is going on and perhaps even why!

The Guru trap, as it became known, had its origins in a rather nasty little data dump file (rather than a report whose appearance, nasty or otherwise, had been “designed in”) in which the master detail record was, at a minimum, about 4 lines long but some records could be several lines longer. Add to that that fields were not always present in all records or, apparently, in the same order and it looked like a completely unintelligible mess.

But two Monarch features came together to make the extraction viable in a single pass. A single pass through the report is always a good objective though there is no reason at all to stick inflexibly to that ideal if multiple passes through the report simplify the model development task and produce a quicker and/or more flexible result.

Firstly the undocumented one:

There is nothing in the processing of a report to prevent two or more templates sharing the same trap or indeed extracting the same ‘data’.

By sharing the same trap as a Detail template an Append template is, effectively, forced to reset every time Monarch finds a new detail record. In effect, this allows detail records to be extended beyond the notional limit of the number of lines that can be included in the trap sample. (This is counter intuitive to the ‘rule’ that Append data must always appear BEFORE detail data, although in fact rules in the design philosophy are not broken – just adapted slightly to suit the purpose. Well, after all, what are rules for?)

That means that if your shortest record has 3 data lines and a blank line before the next record, the sample ‘mask’ cannot be longer than 4 lines. Records with 12 lines therefore become a problem. Being able to create a ‘stack’ of template masks based on the same starting point selection became the objective from an idea that appeared from nowhere one day and proved to work rather well. (It may have been accidental at the time, but who cares if something works?)

The documented feature was the use of the ‘Start Field On’ Advanced Field Property and its ‘Preceding String’ option.

This meant that so long as the content of the report had enough information to uniquely identify the field required, the data could exist anywhere between the current template trap and the next occurrence of the trap. Specifically, as used for this concept, that meant anywhere between the start of one detail record and the start of the next.

It was actually several months before I understood well enough why things worked to be able to start to explain them if anyone asked (and to be confident that they would work reliably!)

Over time a few more possibilities turned up and enhancements in later versions of Monarch added to the array of features and functions available but the primary principle of discovery remains: Try it and see what happens and what new ideas the attempt presents you with.

Visualize the report and gain an understanding of the problem in terms of what you would need to do to get each field, or group of fields, how you need them to be. If Monarch does not immediately present an obvious solution ask yourself what the solution would be and then consider whether there are function(s) or features that might possible be helpful to move you closer to what you need.

[I find that reading the Function(s) help is very useful for keeping possibilities current in my memory as well as for prompting new ideas to try out. Often they are not practicable but sometimes they lead on to success.]

If you get to a “If I could do this ….” situation it then becomes much easier to work out how, using Monarch, you might get there. Trying it out usually costs little time and teaches a lot.

Be prepared to go back a step or two in your efforts.

Finally, be prepared to try something completely different. On any journey looking for a way around a wall is often more successful than attempting to knock it down.

Thanks Grant!

Defining a “guru trap” requires that you employ exactly the same trap characters in the append template as were used in the detail template, in exactly the same positions. This is done regardless of the representative data within the append template sample line.

Next, the field(s) to be captured is/are painted in the sample line. In the advanced options for each field, either of the “String _________ anywhere in the previous line” or the “Preceding string: _________ in current line” options is employed as is necessary, based on the report content.

There’s usually some string literal, like “NOTE:” or perhaps a / character that appears in a date in one of those lines that can be used in the advanced options of the field definition.

This technique is most often used when the data that needs to be appended appears only intermittently – perhaps as infrequently as only once – amongst an otherwise contiguous series of detail rows.

Grant’s experimentation efforts in developing this technique have enabled many reasonably straightforward (now) modeling solutions, which may well have been impossible if not exceedingly challenging without it.

Your experimentation may not be quite so radical. Maybe it will be more along the lines of trying out new functions, or combinations of functions, that you’ve not previously attempted.

Why so serious?

So go ahead and experiment. Have some fun. Risk little. And, above all, continue to find new ways in which you can excel with Monarch.