Topic
  • 4 replies
  • Latest Post - ‏2012-10-15T22:34:36Z by andy__clifton
andy__clifton
andy__clifton
9 Posts

Pinned topic ISO2 Country Code lookup

‏2012-10-12T00:58:48Z |
Hi All,
Im pretty new to this stuff so be kind...
I am setting up some data quality jobs in information analyzer 8.7 and have implemented some rulesets using Harald Smiths predefined ruleset at http://www.ibm.com/developerworks/data/library/techarticle/dm-1112isanalyzerrules/index.html, very useful stuff.

One thing which is confusing me and Id LOVE to understand is how and where the ISO2CountryCd lookup rule pulls its reference data from?
The rule is as follows:

<DataRuleDefinition name="IsoCountryCode2DigitAlphaIsValid" folder="General Rules / Country">
<description>ISO Country Code 2 digit alpha is valid; 2 digit value and ISO Country code lookup; COUNTRY Standardization rule set</description>
<expression>len(trim(CountryCd)) = 2 AND ucase(CountryCd) IN_REFERENCE_COLUMN isoCountryCd2</expression>
</DataRuleDefinition>

The expression "IN_REFERENCE_COLUMN isoCountryCd2" is the part that is losing me. What is isoCountryCd2? Where is the "file" or "table" and where can I get a list of avaialable predefined other reference columns like this and how can i create my own!

Really appreciate your help on this!
Updated on 2012-10-15T22:34:36Z at 2012-10-15T22:34:36Z by andy__clifton
  • smithha
    smithha
    162 Posts

    Re: ISO2 Country Code lookup

    ‏2012-10-12T12:32:20Z  
    Hi Andy,

    The forums are here for people to raise questions, so please feel free to post them. :-)

    I'm glad you are finding the predefined rules of use. In regards to your specific question on reference data, the answer is an "it depends" type of response.

    The rule definitions simply include field or variable names, such as "ISO2CountryCd", which allows them to be connected to any relevant data source.

    When you generate a data rule from the rule definition, you will be asked to bind the variable (such as ISO2CountryCd) to an actual column/field in a data source (such data sources must have had metadata imported into Information Server, and the data sources must be added into your Information Analyzer project, in order to be used).

    So, in this case, you need to have a database table or a data file that contains the actual ISO Country Code reference data IBM does not provide this as most reference data is proprietary. You then need to do the following:
    • create an ODBC DSN on your server pointing to the source
    • add a Data Source/Data Connection in Information Analyzer (or via other Information Server products/UI's)
    • import the metadata for your reference table in Information Analyzer (or via other Information Server products/UI's)
    • add the Data Source to your IA project
    • generate your data rule from the rule definition
    - in this process, bind the ISO2CountryCd to the column in the database table
    • run your data rule

    In creating your own rule definitions for other reference data sources, simply include a variable name. E.g. to check against a defined set of Address Types, you could create a rule definition such as: addressType InReferenceColumn MasterAddressType -- when generating your data rule you would bind addressType to the field to evaluate and would bind MasterAddressType to your target reference data source, wherever that is.

    Hope that helps!

    Harald
  • andy__clifton
    andy__clifton
    9 Posts

    Re: ISO2 Country Code lookup

    ‏2012-10-14T23:06:11Z  
    • smithha
    • ‏2012-10-12T12:32:20Z
    Hi Andy,

    The forums are here for people to raise questions, so please feel free to post them. :-)

    I'm glad you are finding the predefined rules of use. In regards to your specific question on reference data, the answer is an "it depends" type of response.

    The rule definitions simply include field or variable names, such as "ISO2CountryCd", which allows them to be connected to any relevant data source.

    When you generate a data rule from the rule definition, you will be asked to bind the variable (such as ISO2CountryCd) to an actual column/field in a data source (such data sources must have had metadata imported into Information Server, and the data sources must be added into your Information Analyzer project, in order to be used).

    So, in this case, you need to have a database table or a data file that contains the actual ISO Country Code reference data IBM does not provide this as most reference data is proprietary. You then need to do the following:
    • create an ODBC DSN on your server pointing to the source
    • add a Data Source/Data Connection in Information Analyzer (or via other Information Server products/UI's)
    • import the metadata for your reference table in Information Analyzer (or via other Information Server products/UI's)
    • add the Data Source to your IA project
    • generate your data rule from the rule definition
    - in this process, bind the ISO2CountryCd to the column in the database table
    • run your data rule

    In creating your own rule definitions for other reference data sources, simply include a variable name. E.g. to check against a defined set of Address Types, you could create a rule definition such as: addressType InReferenceColumn MasterAddressType -- when generating your data rule you would bind addressType to the field to evaluate and would bind MasterAddressType to your target reference data source, wherever that is.

    Hope that helps!

    Harald
    It does, yes, Phew!
    Thanks for taking the time to reply Harald, i thought I was going to go insane trying to figure out what was going on there as it looked like it was working ok without binding an actual data source to the rule. I must have been seeing a bunch of false positive or something and therefor saw this as some kind of groovy back box process referencing some predefined list somewhere! My curiosity got the better of me :)
    We have our own master list of various ISO Codes in our DWH reference schema. I have referenced those through a virtual table and it works swimmingly now.
    Its actually remarkably simple once you get you head around it!

    Thanks again...
  • smithha
    smithha
    162 Posts

    Re: ISO2 Country Code lookup

    ‏2012-10-15T16:56:21Z  
    It does, yes, Phew!
    Thanks for taking the time to reply Harald, i thought I was going to go insane trying to figure out what was going on there as it looked like it was working ok without binding an actual data source to the rule. I must have been seeing a bunch of false positive or something and therefor saw this as some kind of groovy back box process referencing some predefined list somewhere! My curiosity got the better of me :)
    We have our own master list of various ISO Codes in our DWH reference schema. I have referenced those through a virtual table and it works swimmingly now.
    Its actually remarkably simple once you get you head around it!

    Thanks again...
    Hi Andy,

    Glad it's making sense and working for you now.
    While I would love to package the actual reference data for items such as this, most sources of this type of information, including standards organizations, usually have restrictive licensing/costs that make that approach prohibitive.

    Harald
  • andy__clifton
    andy__clifton
    9 Posts

    Re: ISO2 Country Code lookup

    ‏2012-10-15T22:34:36Z  
    • smithha
    • ‏2012-10-15T16:56:21Z
    Hi Andy,

    Glad it's making sense and working for you now.
    While I would love to package the actual reference data for items such as this, most sources of this type of information, including standards organizations, usually have restrictive licensing/costs that make that approach prohibitive.

    Harald
    Tell me about it! Most of what Ive done so far is cobbled together due to unwillingness to spend money on such trivial stuff as data quality! They then cry when their data doesn't look right etc etc.