One of Monarch’s greatest strengths is its ability to add even more related data to the new data that has just been extracted.
This gives all kinds of flexibility and analytical capabilities to your main information system, be it a small accounting package or a large ERP system, that it doesn’t currently provide. Imagine being able to very quickly get the results that having your database modified and populated - complete with new reports - would provide. Now imagine achieving those results yourself. That’s the power that Monarch provides.
The two versions of Monarch, Standard and Pro, each offer an internal ‘lookup’ style calculated field, while the Pro version adds an external lookup so that you can import values from sources outside of your model automatically.
There are compelling reasons to use, and avoid, both types of lookup field. Let’s take a moment to examine them both, shall we?
|
Internal Lookups |
|
|
Advantages |
Disadvantages |
| Makes for a very portable model, as the output values (based on the input values) become part of the model, and do not rely on any external data source. | Must be updated manually by a Monarch user. |
| As this is available in both versions of Monarch, all Monarch users can make good use of the feature and add value to their extracted data. | Manual updating can be time consuming. (Although good source information and a little planning could make this quite slick.) |
| The Input Values are not restricted to values found in the current data set. You can add new input and their corresponding output values immediately should you be aware of values that will be needed for future data sets. | Data entry errors can be introduced easily. (Although good source information and a little planning could make this consistent and reliable.) |
| Creation and maintenance of the tables can, where suitable, be performed using copy and paste. This may reduce the maintenance effort and improve accuracy as well as encouraging timely updates if required. | You can only define one set of output values for the input field per calculated field, so if you need to duplicate the work if you need more than one lookup field, even if it’s based on the same input field.This problem is lessened somewhat with v9’s ability to duplicate existing calculated fields. |
| Especially useful for ‘translation’ tables - days of the week, codes to names, month by name, etc. - where the information tends to be both static (or at least stable) and can be re-used quickly in multiple models if required (see below). | There’s no easy way to validate the new values that have been entered as Output Values. This is more of a potentially significant issue with larger data sets. |
| As new input values can be introduced over time, the model needs much more attention to ensure that these values have been assigned matching output values. | |
| Lookups are not usually a feature used by Monarch beginners, so there may be a learning curve issue. | |
|
External Lookups |
|
|
Advantages |
Disadvantages |
| Models that employ external lookups do not typically require the manual maintenance that those using internal lookups do. | Not possible in the Standard version. |
| Data is brought into the model extremely quickly via Monarch’s external lookups, as compared to say, using one of Excel’s lookup functions to combine related data in different worksheets or workbooks. | Monarch sometimes struggles with the field definition as it pre-screens the linked table to attempt to determine the best setup for the field. An example would be a large Excel file with significantly longer character fields at the bottom of the data than that found in the first records. |
| Many different data sources can be used to import related data into the model, including local and networked files, and ODBC and OLEDB connections. | Requires that related files be available in consistently available, defined locations. If the external data source is moved, or isn’t available, the model is essentially broken. |
| You are able to filter records in the external table before defining which fields you want to import. This can often significantly improve the performance of lookups performed on large and/or remote tables. | |
| External lookups allow you to import more than one field into your data set from the external table. | |
| The field that you import using an external lookup can be used as a linking field in a second external lookup, thus allowing you to combine multiple data sources easily when there doesn’t appear to be a direct connection in your initial data. | |
While we’re not really comparing the Pro and Standard editions of Monarch (I’ll save both you and me another extended post here: just get the Pro version and thank me later 
), an advantage of Version 9 Pro versus the Standard edition is its ability to import objects (like lookup fields) from existing models.
How does this relate to comparing internal and external lookups?
With internal and external lookups previously defined in existing models, you can make quick work of implementing lookups in a new model by simply importing the previously defined lookup into your new model. This is especially beneficial when the original is an internal lookup with a long list of output values. Build it once and reuse it as and when you need it. You can even define the import to be linked to the original lookup so that when the original is manually updated those edits automatically carry over to your other models.
New and Improved Analytical Abilities for Your Organization!
I can’t overemphasize the impact that using this single Monarch feature can have on the value of your work. It’s the first step towards developing new analytics and new insight for your organization.
By constantly looking for meaningful ways to add value to your data with lookups you’ll definitely excel with Monarch.
3 users commented in " Comparing Monarch’s Internal and External Lookups "
Follow-up comment rss or Leave a TrackbackI cannot get external lookup to work correctly. Some items are looked-up correctly, others return nothing for not apparent reason. Internal lookup with all its limitations seems to me to work well. I am quite disappointed with external lookup.
Bob,
The external lookups should work just fine BUT, as with any interactions between systems - even Access and Excel in days gone by - data storage formats may not be the same underneath what you see on screen. This is most often (and frequently) true for numeric values.
So the first step to getting a lookup to work may be that you need to normalize the data. Monarch allows you define the link via a calculated field so as long as you know (or can guess) what the underlying data structure is in your external lookup source it should be possible to have Monarch bring in the linked data and re-format it in advance of the compare.
Typical problems often involve field lengths/number of decimal places on numeric links where the field definitions simply don’t match. I character fields things like leading/trailing spaces and even sometimes double and triple spaces where you are not expecting them (spaces are difficult to count) in a string can be a problem.
Remember that a lookup link is just something that is a code which, often, needs to be relatively unique. The key to success (apart from knowing you have reasonable data on both sides of the lookup!) is reducing everything to be as consistent as possible and so eliminate potential errors or mismatches that are built into the data. It you are using records from different systems running on different platforms there will be many situations where they will not have an exact technical match. Monarch makes it pretty easy to work around those limitations once you know what it can do.
Grant
Bob,
Grant has already supplied some good advice in my absence (sorry for the delay, but I was without access to the site for a while).
I can’t recall a situation wherein we couldn’t get an external lookup working. Sometimes, as Grant noted, it requires a conversion field, constructed with a calculated field, to help out.
Leave A Reply