You thought that you’d whip up a new Monarch model for that odd looking report in just a few minutes, as you’ve done countless times before, but it’s been two hours now and there’s no end in sight. It’s just not clear how to handle this one.

On day 25 of the 30 Days to Become a Better Monarch Modeler series we’ll review some of the options that are available to us when it seems like it’s impossible to develop a model for a particular report or data challenge.

Recognize That You’re Not Alone

Firstly, remember that you can get help from someone who might just need to look at the report with “fresh eyes”. There are experienced Monarch modelers who reside in many different parts of the world (somebody else in a different time zone is working at whatever time that you are), and seeking help on the Monarch Forum is always a good idea, and you’ll likely get the help you need free of charge.

Additionally, if time is of the essence, consultants can be contracted to assist.

Find the Pattern

Monarch’s primary functionality operates by detecting patterns and changes in those patterns in your report, but it’s only looking for what you’ve instructed it to find (by building templates). Sometimes the proper pattern to use just isn’t obvious, and if you’re using Monarch v10 you might get lucky by using the Auto-Define Trap feature to have Monarch try to find the patterns for you.

But even that’s not bulletproof for the most challenging of reports.

Make the Pattern

When you hear yourself thinking “If only there was an empty line there,” or “I’d be able to trap that if there were a (name, label, etc.) right there”, then there’s a good chance that your best option might be to create the pattern yourself. Your report might be candidate for the Prep or MSRP (Monarch Search and Replace Program) utilities which are available on the Datawatch downloads page.

You can also use the Monarch Utility, which is usually installed alongside Monarch, to perform search and replace operations. You might want to first use the Examine utility or other software that allows you to view the characters within the problem file by their hexadecimal or ASCII codes to see specifically what it is that you need to replace. If the report file isn’t too large, you can even use Microsoft Word to replace special characters such as paragraph characters or line breaks.

Use the Proper Approach

Sometimes the difficulty in building a model can be rather self-imposed, if only because we attempt to bring the data into Monarch using the wrong method, or attempt to use a difficult format.

For instance, a comma separated values (CSV) file can be modeled if read into Monarch as a regular report file, but it’s far more efficient to open it as a database source (using Monarch Pro).

On that note, though it’s not really part of this overall discussion, the CSV format is particularly useful as a database format as only delimited text files can be opened as database sources simultaneously. In other words, only a single Excel file or Access database can be opened by a Monarch project, but multiple text files can be used at once. Can you think of any time when you’d want to take advantage of this? What would this functionality offer your needs?

Right then, back on track.

HTML files can be modeled, but honestly they’re a bit of pain. I’ve developed a method for modeling them that I really think I ought to patent, but I’d prefer to instead change the format of the file. Open the file with your favorite web browser and then print it to a PDF file. Get Cute PDF if you don’t already have a free PDF creator. It’ll be far easier to create the model.

Now I like to work with Excel, but even I must admit that at times Monarch behaves a little more predictably with tables from Access databases. There are some things that can be done to help Monarch when it does stumble, but if you can import the Excel table into an Access table yourself, and then connect your Monarch project to the Access table.

The other database connectivity options include ODBC and OLEDB, and while they can and do provide great power and flexibility, this approach can be a bit of a double-edged sword as the databases that they allow you to connect to can be monstrously huge. You’d better know what you’re looking for, and what’s really stored in each field. In those cases, it’s probably a better idea to obtain reports generated by the host systems upon which those databases are built and then develop regular Monarch models for those reports.

Your Task for Today

You might be lucky and be in the middle of a challenging project at the moment – if you can call that lucky – and you won’t have to seek one out, but if you don’t have one handy, keep today’s post in your mind as you work through your next workday.

When you next run full-steam into a brick wall, step back and instead of looking for any little crack that you might be able to exploit, look for ways to go around it, jump over it, go under it, or - my favorite – blow that wall up and rebuilt it with nice door right in the middle of it.

Make Monarch Your Preferred Power Tool

The best tools are either so specialized that they can only do one thing, but do it exceedingly well, or are flexible and can be easily adapted to handle many tasks and can be used to accomplish each task with equal aplomb. The secret is in knowing in advance what the tool can do, and the manner in which it should be used.

Now you’re properly equipped to efficiently accomplish even the most challenging data acquisition task. You can quickly demonstrate your ability to make the impossible possible, and excel with Monarch.

—

Continue your commitment to Become a Better Monarch Modeler with Part 26 of the series, or review Part 24.