Informatica MDM

Informatica MDM

1. Hub Store

   • Operational Reference Store: CMX_ORS
     • Database that contains the master data, content metadata, the rules for processing the master data, the rules for managing the set of master data objects, along with the processing rules and auxiliary logic used by the Informatica MDM Hub in defining the best version of the truth (BVT). An Informatica MDM Hub configuration can have one or more ORS databases. The default name of an Operational Reference Store is CMX_ORS.
     • Enable and Disable Production Mode
     • Register ORS
     • Un-register ORS
     • Create database tables
     • Data Source
       • Schema: is a data model
         • Requirements for Defining Schema
           • Make Schema Changes Only in the Hub Console
           • Think Before You Change the Schema
           • You Must Have a Write Lock to Change the Schema
     • Type of Tables
       • Configurable Tables
         • Landing table
           • Used to receive batch loads from a source system. You need to create and configure the landing tables.
         • Staging table
           • Used to load data into a base object. You define mappings between landing tables and staging tables to specify how data must be cleansed and standardized when it is moved from a landing table to a staging table. You need to create and configure the staging tables.
       • Infrastructure Tables
         • Cross-reference table
           • Used to track the origin of each record in the base object. Cross-reference tables are named according to the following pattern: C_<base object name>_XREF where <base object name> is the root name of the base object. For example, if the base object name is PARTY, the cross-reference table name is C_PARTY_XREF. When you create a base object, the MDM Hub creates a cross-reference table to store information about data that comes from source systems.
         • History table
           • Used if history is enabled for a base object. History tables are named according to the following pattern:- C_<base object name>_HIST. Base object history table.- C_<base object name>_HXRF. Cross-reference history table. where <base object name> is the root name of the base object. For example, a base object named PARTY has the C_PARTY_HIST and the C_PARTY_HXRF history tables. The MDM Hub creates and maintains different types of history tables. The history tables provide detailed change tracking options, including merge and unmerge history, history of the pre-cleansed data, history of base objects, and the cross-reference history.
         • Match key table
           • Contains the match keys that the MDM Hub generates for all base object records. The match key tables are named according to the following pattern: C_<base object name>_STRP where <base object name> is the root name of the base object. For example, if the base object name is PARTY, the match key table name is C_PARTY_STRP.
         • Match table
           • Contains the pairs of matched records in the base object resulting from the execution of the match process on the base object. The match tables are named according to the following pattern: C_<base object name>_MTCH where <base object name> is the root name of the base object. For example, if the base object name is PARTY, the match table name is C_PARTY_MTCH.
         • External match table
           • Contains the data for external match jobs. You can use the following types of external match tables:- EMI table. External match input table. Contains records to match against the records in the base object.- EMO table. External match output table. Contains the output data for External Match jobs. Each row in the EMO table represents a pair of matched records. One of the pairs of records is from the EMI table and one is from the base object. The external match tables are named according to the following pattern:- C_<base object name>_EMI- C_<base object name>_EMO where <base object name> is the root name of the base object. For example, if the base object name is PARTY, the match table names are C_PARTY_EMI and C_PARTY_EMO.
         • Temporary tables
           • The MDM Hub creates temporary tables that it needs to process data during batch jobs. After the MDM Hub completes processing data, the batch processes remove the temporary tables because they are no longer needed. If the batch process, application server, or the database server fails, temporary tables may not be deleted. Temporary tables have the prefix T$_. After the batch process completes, if the repository contains temporary tables that the batch process created, you can manually remove them. Some temporary tables do not have the prefix T$_ prefix. For example, the cleanse temporary table has the suffix _CL instead, and the delta temporary table has the _DLT suffix instead.
   • Master Database: CMX_SYSTEM; One or more
     • Contains the Informatica MDM Hub environment configuration settings—user accounts, security configuration, Operational Reference Store registry, message queue settings, and so on. A given Informatica MDM Hub environment can have only one Master Database. The default name of the Master Database is CMX_SYSTEM. In the Hub Console, the tools in the Configuration workbench (Databases, Users, Security Providers, Tool Access, and Message Queues) manage configuration settings in the Master Database.
     • separate Operational Reference Stores for development and production

2. Base object

   • Used to store data for a central business entity, such as customer, product, or employee, or a lookup table such as country or state. In a base object, you can consolidate data from multiple source systems and use trust settings to determine the most reliable value of each base object cell. You can define one-to-many relationships between base objects. You need to create and configure the base objects.
   • ROWID_OBJECT
     • Primary key. Informatica MDM Hub assigns a unique value when a record is inserted into the base object
   • CONSOLIDATION_IND
     • Integer value indicating the consolidation state of this record. Valid values are:- 1 = unique. The record represents the best version of the truth.- 2 = ready for consolidation.- 3 = ready for match; this record is a match candidate for the currently executing match process.- 4 = available for match. The record is new and needs to undergo the match process.- 9 = on hold. A data steward has put this record on hold.
   • LAST_ROWID_SYSTEM
   • INTERACTION_ID
     • For state-enabled base objects. INTERACTION_ID is an interaction identifier that protects a pending cross-reference record from updates that are not part of the same process as the original cross-reference record.
   • Cross Reference Table
     • Each row in the cross-reference table represents a separate record from a source system • An identifier for the source system that provided the record.• The primary key value of that record in the source system.• The most recent cell values provided by that system.• The original ROWID_OBJECT value of the record.• The original GBID value of the record, if applicable.• The start date and end date of the period of effectiveness of the record, if applicable.
     • Load Process
       • The load process populates cross-reference tables. During load inserts, new records are added to the cross-reference table. During load updates, changes are written to the affected cross-reference records.
     • Table Columns
       • ROWID_XREF
         • Primary key that uniquely identifies this record in the cross-reference table.
       • PKEY_SRC_OBJECT
         • Primary key value from the source system. You must concatenate keys that contain multiple fields or multiple columns into a single key value. Use the MDM Hub internal cleanse process or an external cleanse process such as an ETL tool or some other data loading utility to concatenate the keys. For admin system cross-reference records that are a result of an edit, the PKEY_SRC_OBJECT is SYS0:<ROWID_OBJECT>, where ROWID_OBJECT is the row ID of the edited record. If the PKEY_SRC_OBJECT is SYS0:<ROWID_OBJECT> and the PUT_UPDATE_MERGE_IND is 1, MDM Hub deletes the cross-reference record during unmerge.
       • ROWID_SYSTEM
         • Foreign key to C_REPOS_SYSTEM, which is the MDM Hub repository table that stores an MDM Hub identifier and description of each source system that can populate the ORS.
       • ROWID_OBJECT
         • Foreign key to the base object. The MDM Hub assigns ROWID_OBJECT to the associated base object record.
       • ORIG_ROWID_OBJECT
         • The original ROWID_OBJECT of the cross-reference record.
       • STG_ROWID_TABLE
       • S_Foreign_Key_Column_Name
       • HUB_STATE_IND
         • For state-enabled base objects. Integer value indicating the state of the record. Valid values are:- 0 = Pending- 1 = Active- -1 = Deleted The default value is 1.
       • PROMOTE_IND
         • For state-enabled base objects. Integer value indicating the promotion status. The promote process uses PROMOTE_IND to determine whether to promote the record to an ACTIVE state. Valid values are:- 0 = Do not promote the record.- 1 = Promote this record to ACTIVE. The MDM Hub does not change PROMOTE_IND to 0 if the promote process does not successfully promote the record.
   • History Table
     • If you enable history for a base object. Informatica MDM Hub provides detailed change-tracking options, such as merge and unmerge history, history of the pre-cleansed data, history of the base object, and the cross-reference history in history tables.
     • Base Object History Tables
     • Cross-Reference History Tables
   • Properties
     • Enable History
     • Complete Tokenize Ratio
       • When the percentage of the records that have changed is higher than the complete tokenize ratio, a complete re-tokenization is performed. If the number of records to be tokenized does not exceed this threshold, then Informatica MDM Hub deletes the records requiring re-tokenization from the match key table, calculates the tokens for those records, and then reinserts them into the match key table. The default value is 60.Note: Deleting can be a slow process. However, if your Process Server is fast and the network connection between the Process Server and the the database server is also fast, then you may test with a much lower tokenization threshold (such as 10%). This will enable you to determine whether there are any gains in performance.
     • Allow constraints to be disabled
       • During the initial load/updates—or if there is no real-time, concurrent access—you can disable the referential integrity constraints on the base object to improve performance. The default value is 1, signifying that constraints are disabled.
     • Duplicate Match Threshold
     • Generate Match Tokens on Load
     • Match Flag Audit Table
     • Enable State Management
     • Enable History of Cross-Reference Promotion
     • Lookup Indicator
   • Actions
     • Custom Indexes for Base Objects
     • Viewing the Impact Analysis of a Base Object
   • Column Properties
     • Trust
       • If enabled, Informatica MDM Hub uses the source system value with the highest trust score. If not enabled, Informatica MDM Hub uses the most recently updated value.
     • Unique
       • If enabled, Informatica MDM Hub enforces unique constraints on the column. If unique constraint is enabled, ensure that duplicate values do not exist in the unique column. Most organizations use the primary key from the source system for the lookup value. Informatica MDM Hub rejects any record with a duplicate value in the column when you enable the Unique property. You must configure a default value for the column if you enable the Unique property. If you enable the Unique property for consolidated base objects, an insert into the base object could fail because Informatica MDM Hub might load the same key from different systems. Ensure you have unique keys for this column across all source systems.
     • Validate
       • If enabled, Informatica MDM Hub applies validation rules during the load process to downgrade trust scores for cell values that are not valid. You must configure validation rules if you enable the Validate property.
     • Putable
       • If enabled for system columns, batch jobs and SIF API requests can insert or update the data in the system column. You cannot enable the Putable property for the following system columns:
     • Global Identifier (GBID) Columns
       • • Identifiers defined by applications external to the MDM Hub, such as ERP (SAP or Siebel customer numbers) or CRM systems.• Identifiers defined by external organizations, such as industry-specific codes (AMA numbers, DEA numbers. and so on), or government-issued identifiers (social security number, tax ID number, driver’s license number, and so on).
   • Configuring Foreign-Key Relationships
     • Configuring Lookups for Foreign-Key Relationships

3. Queries and Packages

4. Timeline

   • To maintain the effective periods for base object records, the 143 MDM Hub uses cross-reference tables that are associated with the base objects for which you enabled the timeline.
   • Record with Past and Present Data

5. Hub Processes Data Flow

   • Consolidation Indicator
     • 1.During the load process, when a new record is loaded into a base object, Informatica MDM Hub assigns the record a consolidation indicator of 4, indicating that the record needs to be matched. 2.Near the start of the match process, when a record is selected as a match candidate, the match process changes its consolidation indicator to 3. Note: Any change to the match or merge configuration settings will trigger a reset match dialog, asking whether you want to reset the records in the base object (change the consolidation indicator to 4, ready for match).3.Before completing, the match process changes the consolidation indicator of match candidate records to 2 (ready for consolidation). If you perform a manual merge, the data steward performs a manual match, and the consolidation indicator of the merged record is 2. Note: The match process may or may not have found matches for the record. A record with a consolidation indicator of 2 is visible in Merge Manager.4.If Accept All Unmatched Rows as Unique is enabled, and a record has undergone the match process but no matches were found, then Informatica MDM Hub automatically changes its consolidation indicator to 1 (unique). 5.If Accept All Unmatched Rows as Unique is enabled, after the record has undergone the consolidate process, and once a record has no more duplicates to merge with, Informatica MDM Hub changes its consolidation indicator to 1, meaning that this record is unique in the base object, and that it represents the master record (best version of the truth) for that entity in the base object. Note: Once a record has its consolidation indicator set to 1, Informatica MDM Hub will never directly match it against any other record. New or updated records (with a consolidation indicator of 4) can be matched against consolidated records.
     • 1 - CONSOLIDATED
       • Indicates that the record is consolidated, determined to be unique, and represents the best version of the truth.
     • 2 - QUEUED_FOR_MERGE
       • Indicates that the record might have gone through the match process and is ready for a merge. Also, the record might have gone through the merge process and is ready for another merge.
     • 3 - Not MERGED or MATCHED
       • This record has gone through the match process and is ready to be consolidated. Also, if a record has gone through the manual merge process, where the matching is done by a data steward, the consolidation indicator is 2.
     • 4 - NEWLY_LOADED
       • Indicates that the record is a new insert or is unmerged and must undergo the match process.
     • 9 - ON_HOLD
       • Indicates that the data steward has put the record on hold until further notice. You can put any record on hold, regardless of its consolidation indicator value. The match and consolidate processes ignore records that are on hold.
   • Cell Data Survivorship
     • By trust score. The trust score applies only if a column is trust-enabled.
       • The data with the highest ROWID_OBJECT wins. If the trust scores are equal, or if trust is not enabled for a column, the MDM Hub proceeds to the next comparison.
     • By SRC_LUD in the cross-reference record.
       • By SRC_LUD in the cross-reference record. The data with the more recent cross-reference SRC_LUD value wins. If the SRC_LUD values are equal, the MDM Hub proceeds to the next comparison.
     • By ROWID_OBJECT in the base object.
       • By ROWID_OBJECT in the base object. ROWID_OBJECT values are evaluated in numeric descending order. If the ROWID_OBJECT values are equal, the MDM Hub proceeds to the next comparison.
     • By ROWID_XREF in the cross-reference
       • By ROWID_XREF in the cross-reference. ROWID_XREF values are evaluated in numeric descending order. The data with the highest ROWID_XREF wins.
   • ROWID_OBJECT Survivorship
     • Batch Merge Job
       • During a batch merge job, the MDM Hub considers the consolidation indicator for all records. When a new record (CONSOLIDATION_IND = 4) is matched and merged with a consolidated record (CONSOLIDATION_IND = 1), the consolidated ROWID_OBJECT wins. When two new records with (CONSOLIDATION_IND = 4) are matched, the record with the lowest ROWID_OBJECT wins.
     • Merge SIF API
       • When you use the Merge SIF API to merge records, the target ROWID_OBJECT wins. The target is the first record listed in the SIF request.
     • Informatica Data Director
       • When you merge records in real time in IDD, the target ROWID_OBJECT wins. The target is the record that displays in the Merge Preview column in the Match Merge Comparison view in IDD. When you queue records for a merge, the ROWID_OBJECT of the current record that displays in IDD wins. However, survivorship in IDD also depends on the consolidation indicator for base object records. For example, if a record (CONSOLIDATION_IND = 4) is matched and merged with a consolidated record (CONSOLIDATION_IND = 1), the consolidated ROWID_OBJECT wins.