MDM Data Types

A traditional SQL DBMS has a standard set of relatively simple data types (such as text, integer, and real) that allow you store a single element of unstructured data in each field. Beyond knowing how to accept input of and properly store each type of data, SQL has no real understanding of the internal structure of each data element.

By contrast, an MDM repository supports a variety of compound and structured data types that, like the set of MDM table types, are

specifically suited for managing information in a master data repository.

The standard SQL data types for fields are shown in Table 15 and the extended MDM data types for fields are shown in Table 16. The data types for attributes, along with their corresponding field data types, are shown in Table 17.

NOTE ►► In the tables below, a bullet (•) in the column labeled “MV”

means that the data type can be defined as multi-valued, so that a single field or attribute can be used to store multiple values.

DATA INTEGRITY ►► Multi-valued fields and attributes make the structure of an MDM repository dramatically simpler, more compact, and more searchable, by allowing you to store all the values corresponding to a particular data element in the same place. The alternative requires creating multiple fields or attributes, in some cases up to a maximum of one field or attribute for each possible value.

Table 15. Field Data Types (Standard SQL)

Data Type MV SQL Server Oracle DB2 MaxDB Description Text Nvarchar Nvarchar2 Varchar Varchar Text field (<= 333 chars).

Text Large Text CLOB CLOB Long Unicode Text field (> 333 chars).

Integer Int Number Int Fixed (10) 4-byte integer field.

Real Real Number Float Float (16) 4-byte real field.

Real8 Real Number Float Float (38) 8-byte real field.

TimeStamp DateTime Date Timestamp Timestamp DateTime field.

Boolean Bit Number Smallint Fixed (1) Two-valued field.

Table 16. Field Data Types (MDM Extended)

Field Data Type MV Description

Text Normalized Text field with “special” (non-alphanumeric) characters removed for searching/sorting (always displays original).

Name Text field with internal structure for storing parts of a name (prefix, first, middle, last, suffix).

Log Text Large field with internal structure for managing multiple timestamped blocks of text within a single field.

AutoID Integer field that MDM automatically increments.

Currency Real8 Real4 field displayed with a currency symbol.

GM Time TimeStamp field that is adjusted to a particular time zone.

Measurement ● Real field with an associated unit of measure.

Literal Date TimeStamp field that ignores the time part.

Literal Time TimeStamp field that ignores the date part.

Create Stamp TimeStamp field that MDM automatically sets with the date/time of record creation.

Time Stamp TimeStamp field that MDM automatically updates with the date/time of modification when any of the fields being tracked are updated.

User Stamp Text field that MDM automatically updates with name of user who makes the change when any of the fields being tracked are updated.

Mask ● Virtual field that stores an enumeration of main table records. It is never displayed but is used for searching.

Lookup [Flat] ● Field whose value(s) are a lookup into a flat table.

Lookup [Hierarchy] ● Field whose value(s) are a lookup into a hierarchy table.

Lookup [Taxonomy] Field whose single value is a lookup into a taxonomy table.

Lookup [Qualified] ● Field whose values are a lookup into a qualified table.

Lookup [Image] ● Field whose value(s) lookup into the Images table.

Lookup [Text Block] ● Field whose value(s) lookup into the Text Blocks table.

Lookup [Copy Block] ● Field whose value(s) lookup into the Copy Blocks table.

Lookup [Text HTML] ● Field whose value(s) lookup into the Text HTMLs table.

Lookup [PDF] ● Field whose value(s) lookup into the PDFs table.

Lookup [Sound] ● Field whose value(s) lookup into the Sounds table.

Lookup [Video] ● Field whose value(s) lookup into the Videos table.

Lookup [Binary Object] ● Field whose value(s) lookup into the Binary Objects table.

Table 17. Attribute Data Types

Attribute Data Type MV Corresponding MDM Field Type

Text ● Lookup [Flat]

Numeric ● Measurement

Coupled Numeric ● n/a

NOTE ►► In the tables above, a bullet (•) in the column labeled “MV”

means that the data type can be defined as multi-valued, so that a single field or attribute can be used to store multiple values.

TIP ►► A regular Text field is faster than a Text Large field; only use a Text Large field if you are certain that some values will require over 333 characters.

WARNING ►► Real8 fields are not currently supported by MDM but were erroneously allowed in a previous version. If an existing repository contains a Real8 field, the field will be truncated to a Real4 type upon upgrading the repository to SP6.

NOTE ►► Previous versions of MDM allowed a maximum Text field width of 4000. This has been reduced to 333 due to database storage and Unicode-encoding issues, as described in the following section.

NOTE ►► A Text Normalized field stores the actual text value, but uses the normalized value for sorting and searching. The normalized value is an upper-case version of the original with non-alphanumeric characters removed (includes a-z, A-Z, and 0-9 from original value).

Maximum Text Width

As noted in the tables above, the maximum width of a Text field is 333.

This is a limit on the maximum number of characters (what the user sees) that MDM will allow for the field. Each character may then be expanded into multiple bytes (what the system sees) when it is Unicode-encoded for storage in the DBMS.

MDM enforces the limit on Text field width as follows: (1) the MDM Console does not allow you to enter a value for the Width property greater than 333; (2) the MDM Data Manager client and other GUI clients do not accept data entry of text strings with more characters than this maximum; and (3) the MDM Server rejects API requests to enter text strings that contain more characters than this maximum.

NOTE ►► Lower ASCII characters require only one byte of storage using Unicode UTF8 encoding (two bytes using UTF16 encoding). The characters a-z, A-Z, 0-9 and most punctuation are lower ASCII.

Characters with accents, Korean characters, and symbols are examples of characters that require more than one byte of storage.

Most characters can be stored in two bytes using UTF16 encoding.

The real challenge arises because some characters may require four or more bytes to encode. Limiting the maximum number of characters to 333 prevents any character string from expanding beyond the physical storage capacity of the underlying DBMS.

Dimensions and Units

As noted in the tables above, MDM has a compound data type for storing physical measurements that combines a numeric value with a unit of measure. It allows you to associate a physical dimension with a measurement field or numeric attribute, and then to assign to every numeric value a unit of measure chosen from the list of units applicable to that dimension.

MDM currently has built-in support for over 70 different physical

dimensions and over 750 different units of measure. In addition, MDM is able to convert between different units, for proper comparison and sorting of numeric values with different units within a list, impossible with most other systems that often store numeric values and units of

measure as a single text string or in two distinct fields.

DATA INTEGRITY ►► Physical dimensions make it easy to enforce data integrity, since units of measure must be selected from a predefined list of units rather than typed in by the user as a text string.

DATA INTEGRITY ►► Measurement fields and numeric attributes are 4-byte real fields with the exception of the dimensions Time and Frequency, which require the additional precision of 8-byte real fields.

F

. A

– A C

A main table field is created in MDM Console and applies to all of the records in an MDM repository. By contrast, an attribute is created and linked in the MDM Client in Taxonomy mode and applies just to records in categories to which the attribute is linked.

Table 18 highlights the similarities and differences between fields and attributes that affect how they appear and are used within MDM.

Table 18. A Comparison of Fields and Attributes

Field Attribute General

Created in MDM Console. Created in the MDM Client in Taxonomy mode.

Represents a schema change, so the MDM

repository must be unloaded to add a field. Does not represent a schema change; the MDM repository must be loaded to link an attribute.

Category-independent; applies to all main table records.

Category-specific; applies just to main table records in categories to which the attribute is linked; cannot be linked to the taxonomy root.

Fields appear in schema order and cannot be hidden in the Record Detail tab.

Attributes appear in priority order in the Record Detail tab – and can be hidden entirely – on a per category basis based on the link priority.

Included in both the Records grid and Record Detail tab; always of interest even with large record sets.

Not included in Records grid; included in Record Detail tab only for the selected records; generally of interest only when you select a single or several main table records.

Text Values (not limited to a set of specific values) Text field; used to permit data entry of a unique

value for each record. No corresponding attribute type that permits entry of a unique value for each record.

Text Values (limited to a relatively small set of specific values enumerated in advance) Lookup field into a flat table; pick list forces user

to choose from a set of specific lookup values during data entry.

Text attribute (like a “mini” lookup table); pick list forces user to choose from a set of specific text values during data entry.

The lookup table record corresponding to each lookup value can contain any number of user-defined fields.

Each text value consists of: (1) a fixed-width Text field for the text value itself; (2) a Text Large field for the text value description; and (3) a single-valued Image field for the text value image.

Supports both drilldown search and free-form

search. Supports drilldown search; does not support free-form search.

Numeric and Measurement Values

Numeric or measurement field. Numeric attribute with or without a dimension.

Supports free-form search; does not support

drilldown search. Supports drilldown search using a pick list of unique numeric values; does not support free-form search.

Coupled Numeric Values

No field type for representing two-dimensional data.

Coupled numeric attribute; used for representing two-dimensional data.

TIP ►► From a design standpoint, sparsity of data is less important than whether the data item applies to all main table records (should be a field) or just some subset (should be an attribute). For example, you may not have a Weight value for every product, but weight certainly applies to every product and therefore should most likely be a field.