Map data wizard

You can use a map data rule to manipulate data in a variety of ways, such as copying a database attribute from one place to another. Data Analytics ships with a large number of these rules by default. However, you may want to copy and then modify them to gather additional or customized data.

You'll commonly use map data rules for actions related to standardization. A good example of this is the Standardize Serial Number rule, which installs with Data Analytics. Because hardware manufacturers haven't standardized a location in the BIOS where the system serial number is stored, it could be in one of several locations in inventory. This rule searches seven different areas in inventory and then copies the serial number to a standardized location. By having one consistent location for serial numbers, you can more easily link your inventory data to other data sources as well.

You can also use map data rules to search for a value in one field to populate another field with more useful data. For example, many organizations start their device names with several characters representing the office where the device is located. It's possible to analyze the characters and then populate another field with the name of the actual office location.

Open the wizard by right-clicking Map data in the DTS tree and selecting New rule. Enter a unique name and description for this rule, then select the database where the rule will store the data it gathers. On the continuing pages, enter the following information:

Database attribute: Click the ellipsis (…) button to select the destination database attribute, which is the location in inventory where the rule will copy the new data.

Click the Add button to create a list of source attributes, which are locations in inventory that the rule will check for data. Source attributes are processed in the order listed. You can map any combination of one-to-one and one-to-many attributes.

The other columns in this list are: 

  • Validation: Shows a validation string that you add to determine if the found value is valid or should be ignored. The string is created using regular expression syntax.
  • Return: Shows the value that's returned.
  • Children: Shows "Yes" if the return value is appended or pre-pended onto the current value.

This page appears if you selected a one-to-many source attribute. With a one-to-many, you may want to specify which of the entries the rule should check, because attempting to put a one-to-many in a column set or query result can create duplicate records.

Use another attribute from the component: The source attribute may not be the one that the rule needs to check. Select a different entry from the one-to-many attribute.

Where value: You can specify either = or like. In the case of like, you can use % as a wildcard.

Get min\max value: Instead of specifying the value of another attribute, you can specify that you want to select the attribute with the highest or lowest value.

Use as key: In the case of copying a one-to-many attribute to another one-to-many, you need to specify the destination record the rule will copy to. You can use any other attribute in the current one-to-many as the destination key. The key field is used to determine uniqueness.

Modify value with VBScript: You can use VBScript to modify the value that will be used as the key.

Use attribute as destination key: Instead of using the normal key field for the destination one-to-many attribute, you can select another attribute that will then be matched to the Use as key field. This option is useful in situations where the target may have a simple value (such as a number) as the key field, but the source may not have a matching number field. One of the other fields may be unique.

When matching a source attribute, you can specify a condition it must meet. The condition helps determine if a found value is valid or should be ignored. For example, if you're trying to find a valid Lenovo serial number, you know that it needs to be a 7- or 8-digit, alpha-numeric string.

In the Validation text box, enter a validation string in one of two ways:

  • Click the Browse values button to match exact values from a list already in the database.
  • Select from a number of sample strings in the Sample validation strings scroll box. Double-click a sample string to automatically add it as properly formatted to the validation string. (The syntax for this validation uses regular expressions. For information about regular expressions, see the Microsoft help.)

If a source attribute is matched, you can specify that the rule will run a DTS rule group (optional). For example, if the match is a Dell computer, you may want also to initiate the Dell Warranty rule to pull information from the Dell website. Note that groups will only run when you run this rule manually, not when it's run during real-time scanning.

By default, a map data rule will return just the value that it finds. You can also specify a simple prefix to add to the beginning or a suffix to append to the end of the value that's returned. If you want to return a value that's completely different from what is found, clear the Value found check box and enter a value in the Prefix or Suffix field.

Manual button: Allows you to use regular expression parsing and VBScript to modify the returned value. If you just want to use VBScript, leave $0 in the regular expression box (see Microsoft regular expression and VBScript help references for help with these items). You can click the Test button to verify that your script works.

Children button: Performs a function similar to running a specific group, where additional map data rules can be run with the return value appended or pre-pended onto the current value. Children values are rarely used.

Enter a value that you want returned if no source attributes match. This value must be the same data type (string, date, int, and so on) as the destination attribute. If you leave this field blank, the rule won't return a value.

Click the Finish button. Your new rule will appear in the Map Data > All Rules folder.