It’s day 16 of the 30 Days to Become a Better Monarch Modeler series and for today I had scheduled a discussion about combining data sources with Monarch.
But reviewing some of the existing content on the topic already available here on ExcelWithMonarch.com, most notably a somewhat lengthy comparison of the available internal and external lookup methods, leads me to think that I’ve already described a good portion of what you need to know about the technicalities.
So I thought it best, rather than somehow restating and repackaging it, to instead focus exclusively on how combining data sources can help you become a better Monarch modeler.
What’s The Purpose?
The main idea reason to combine data sources is that we need to add new data to the original data and probably use that as a basis for further analysis, probably employing this new data as a key field in a summary.
Most information systems offer some kind of sales report, but I haven’t seen many that have included different types of geographic data in that report. Perhaps you need to analyze the basic results by breaking down the basic country-wide east, west, north and south regional sales. Maybe it’s a lot closer to home, but your report just includes a handful of nearby city names, and you’d really like to isolate the various neighborhoods or districts within a city. In either case, you can accomplish this easily and with just a few clicks in Monarch.
The key, as it were, is to isolate either a single field or a combination of two or three fields that you can use to connect the dots between your data sets. What you want to do is find a way to create unique connections. Examine the source report and determine which field you could use to create a list of unique meaningful values. Customer and vendor numbers often work well, as do part numbers. Other times you might find category names and geographic names will do just fine.
Let’s stick to the geography concept. Suppose that using our customer master list that displays address data, we derive a list of unique US state names in which our customers operate. We develop a model for this report and export the customer numbers, customer names and state names to an Excel 2007 file. This is great format to use for lookups because Monarch reads them very quickly and you can store over a million records in your lookup table. That’s not a large table if it’s storing transactional data, but it is a lot of records for a simple lookup table of unique values.
Let’s say that we know from our past experience with the reports available to us, sometimes a particular report will display the customer number, sometimes the name, and sometimes both fields. Now with our exported list, we’re ready for any report than contains customer information.
Before we can begin using our exported file as an input source, we need to add some other useful data to it. We would open the file and add columns for Region and it values (North, East, South, West, Northeast, Northwest, Southeast and Southwest), and another field for the customer’s main business type or market segment (Retail, Wholesale, Manufacturing, Restaurant, Consumer Service, what have you) and maybe even a sub-type (Electronics, Food & Beverage, Automotive, etc.).
Now you have a list that can provide different insights than could gain prior to your developing this list. You can report your sales by Business Type and Sub-Type. But you can just as easily now incorporate this information into an Accounts Receivable report (which business types are having trouble paying your bills?) or you might convert your simple sales levels report into a profitability report by Business Type and Sub-Type (which industries are your most and least profitable?).
These are pretty simple little examples, but it’s the possibilities that are important. I’ve never seen a single system that delivers, as a canned or standard report, everything that management would like to have or would otherwise be a beneficial analytical tool.
Sometimes you might be lucky and you‘ll be supplied with files that you can immediately use as lookup sources. Other times you’ll need to develop them yourself.
In either case, the only thing holding you back as a Monarch modeler contributing to your organization is your imagination and perhaps your overall knowledge of your organization.
If you can quantify or otherwise describe something in a new way that doesn’t already exist in your system, then your Monarch skills will be of great value. If you can build useful tools – reports and other analyses – by employing lookups in your models, that haven’t even been envisioned by anyone else in your group, then you’ll be ahead of the curve and in all likelihood will be a real asset to the group.
Some Technicalities
Many external lookups can be created using a single common key field, but don’t forget that you may need to use multiple key fields to tighten up the connections a bit from time to time, as needed.
One of the common challenges encountered is the message indicating that “The Source and Table window columns must be of the same type. This is telling you that Monarch has detected the data type of the fields that you’ve listed as linking columns, and that they’re not the same type of field.
This is frequently encountered when a field that you believe contains only numeric values actually contains, somewhere in the list, an alpha character or a devilish space. That cell that you thought was empty might not be; it might contain one or more space characters.
Another common problem is encountered because of the way in which Monarch determines the field types in the remote Excel file. Monarch examines the first 250 records, and if they’re all, say, numeric, then it believes the entire column to be numeric. But if you have a product number of 1000A in there somewhere after record number 250 you’re about to run into trouble.
The easiest solution is to pre-sort your lookup file before attempting to connect to it with the Monarch model. Sort the Excel table so that any text values appear at the top of the list, and you’ll avoid many problems.
Unfortunately there’s not a easy solution if you’re linking to a text file, like csv. Using Access databases as a source should experience this problem as Access can better control the field content. Without a pre-programmed macro running behind the scenes, Excel doesn’t care what you put into a cell; anything will be accepted.
Your Task for Today
Today’s content has largely been conceptual in nature. If you haven’t already read the existing content on ExcelWithMonarch.com concerning lookups, please do so today; especially if you’re new to Monarch and haven’t used lookups at all in your modeling efforts to date.
In particular, I’d once again direct you to the comparison of internal and external lookups as there continue to be times when one approach is warranted over the other and being aware of the differences may affect how you choose to build your models.
Speaking of building models, some work to do in addition to the reading: today you should build both internal and external lookups into one of your models. Experiment with using different file types as inputs for your external lookups.
Did you realize that you can create multiple external lookup definitions? You can even use the retrieved values generated by one external lookup as the key field of another external lookup! Using lookups, you can tie a lot of information together and derive final data sets that you may have thought was nearly impossible to generate.
Come Together
The critical piece to using lookup fields in your models is dreaming up what you want the end result to include. What will the structure of your summary be? Which fields are readily available in your report, and which will you need to gather from elsewhere, and how will you connect the dots? What does the business need? What already exists in one manner or another? What can you contribute?
The Monarch work is easy, now that you know how. The hard part is up to you. But when you combine them it will be clear that you excel with Monarch.
—
Continue your commitment to Become a Better Monarch Modeler with Part 17 of the series, or review Part 15.
