Informatica MDM - How to generate match report?

Once you are done with a running match job, if you would like to create a match report so that business users can review it before finalizing it then you can use the below query to generate a match report. The SQL query can be tuned to accommodate your data model changes. The result of the match report includes attributes from the Party as well as the child tables. You can adjust the result parameters as per your needs.

WITH
CTE_GOLDEN_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT ROWID_OBJECT_MATCHED AS GR_GROUP_ID, ROWID_OBJECT_MATCHED AS ROWID_OBJECT, 'FIRST_RECORD' AS RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM (  SELECT DISTINCT ROWID_OBJECT_MATCHED, ROWID_MATCH_RULE, AUTOMERGE_IND, ROW_NUMBER() OVER(PARTITION BY ROWID_MATCH_RULE ORDER BY DBMS_RANDOM.VALUE) RANDSEL
          FROM CMX_ORS.C_BO_PARTY_MTCH where
          ROWID_OBJECT_MATCHED in (select rowid_object from cmx_ors.c_bo_party
                where PARTY_ROLE_TYPE='ACCT' )
        ) GR
       
  WHERE RANDSEL <= (SELECT CEIL(30000/COUNT(RULE_NO)) AS RULE_FETCH_COUNT
                   FROM CMX_ORS.C_REPOS_MATCH_RULE
                   WHERE MATCH_FOR_ROWID_TABLE = ( SELECT ROWID_TABLE FROM CMX_ORS.C_REPOS_TABLE WHERE TABLE_NAME = 'C_BO_PARTY')
                          AND RULE_ENABLED_IND = 1)
),
CTE_SUSPECT_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT SR.ROWID_OBJECT_MATCHED, SR.ROWID_OBJECT, 'OTHER_RECORD' AS RECORD_TYPE, SR.ROWID_MATCH_RULE, SR.AUTOMERGE_IND
  FROM CMX_ORS.C_BO_PARTY_MTCH SR
  JOIN CTE_GOLDEN_RECORDS GR ON GR.ROWID_OBJECT = SR.ROWID_OBJECT_MATCHED AND GR.ROWID_MATCH_RULE = SR.ROWID_MATCH_RULE
),
CTE_TRANS_SUSPECT_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT SR.ROWID_OBJECT_MATCHED, SR.ORIG_ROWID_OBJECT_MATCHED, 'OTHER_RECORD' AS RECORD_TYPE, SR.ROWID_MATCH_RULE, SR.AUTOMERGE_IND
  FROM CMX_ORS.C_BO_PARTY_MTCH SR
  JOIN CTE_GOLDEN_RECORDS GR ON GR.ROWID_OBJECT = SR.ROWID_OBJECT_MATCHED AND GR.ROWID_MATCH_RULE = SR.ROWID_MATCH_RULE
),
CTE_MATCH_RECORDS (GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND)
AS
(
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_GOLDEN_RECORDS
 
  UNION ALL
 
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_SUSPECT_RECORDS
 
  UNION ALL
 
  SELECT GR_GROUP_ID, ROWID_OBJECT, RECORD_TYPE, ROWID_MATCH_RULE, AUTOMERGE_IND
  FROM CTE_TRANS_SUSPECT_RECORDS
),
CTE_PARTY_IDENTIFICAITON (ROWID_OBJECT, LINK_ID, TIN, EID, MBR_ID, ACCT_ID) AS
(
  SELECT ROWID_PARTY, LINK_ID_IDFCTN_NBR, TIN_IDFCTN_NBR, EID_IDFCTN_NBR, MBR_ID_IDFCTN_NBR, ACCTID_IDFCTN_NBR
  FROM (SELECT PR.ROWID_PARTY, PI.IDFCTN_TYPE_CODE, PI.IDFCTN_NBR
        FROM CMX_ORS.C_BO_PARTY_ROLE PR
        JOIN CMX_ORS.C_BO_PARTY_ROLE_IDFCTN PI ON PR.ROWID_OBJECT = PI.ROWID_PARTY_ROLE
        JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
        ) PI
  PIVOT (
          MAX(IDFCTN_NBR) AS IDFCTN_NBR FOR IDFCTN_TYPE_CODE IN ('LINK ID' AS LINK_ID, 'TIN' AS TIN, 'EID' AS EID, 'MBR_ID' AS MBR_ID, 'Account Number' AS ACCTID)
        )
),
CTE_PARTY_ADDRESS (ROWID_OBJECT, RESIDENT, CORRESP) AS
(
  SELECT ROWID_PARTY, RESIDENTIAL_ADDRESS_VALUE, CORRESPONDENCE_ADDRESS_VALUE 
  FROM (  SELECT PR.ROWID_PARTY, PRA.ADDR_TYPE_CODE, POADDR.ADDR_LINE_1, POADDR.ADDR_LINE_2 , POADDR.ADDR_LINE_3 , POADDR.CITY, POADDR.ZIP_POSTAL_CODE
  , POADDR.STATE_CODE , POADDR.CNTRY_CODE
          FROM CMX_ORS.C_BO_PARTY_ROLE PR
          JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
          JOIN CMX_ORS.C_BO_PARTY_ROLE_ADDR PRA ON PR.ROWID_OBJECT = PRA.ROWID_PARTY_ROLE         
          JOIN CMX_ORS.C_BO_POSTAL_ADDR POADDR ON PRA.ROWID_ADDR = POADDR.ROWID_OBJECT
        ) PRA
  PIVOT (
          MAX(ADDR_LINE_1 || ' ' || ADDR_LINE_2 || ' ' || ADDR_LINE_3 || ' ' || CITY || ' ' || ZIP_POSTAL_CODE || ' ' ||  STATE_CODE || ' ' || CNTRY_CODE) AS ADDRESS_VALUE
            FOR ADDR_TYPE_CODE IN ('RSDNTL' AS RESIDENTIAL, 'CRSPDC' AS CORRESPONDENCE)
        )
),
CTE_PARTY_PHONE (ROWID_OBJECT, HOME, MAIN, WORK, MOBILE) AS
(
  SELECT ROWID_PARTY, HOME_PHONE_NUM, MAIN_PHONE_NUM, WORK_PHONE_NUM, MOBILE_PHONE_NUM
  FROM (  SELECT PR.ROWID_PARTY, PP.PHONE_USG_TYPE_CODE, PP.CNTRY_CODE, PP.AREA_CODE, PP.PHONE_NBR, PP.EXTN_CODE
          FROM CMX_ORS.C_BO_PARTY_ROLE PR
          JOIN CMX_ORS.C_BO_PARTY_ROLE_PHONE PP ON PR.ROWID_OBJECT = PP.ROWID_PARTY_ROLE
          JOIN CTE_MATCH_RECORDS MR ON MR.ROWID_OBJECT = PR.ROWID_PARTY
          ) PRP
  PIVOT (
          MAX(CNTRY_CODE || ' ' || AREA_CODE || ' ' || PHONE_NBR || ' ' || EXTN_CODE ) AS PHONE_NUM
            FOR PHONE_USG_TYPE_CODE IN ('HOME_PHONE' AS HOME, 'PHONE' AS MAIN, 'WORK_PHONE' AS WORK, 'MOBILE' AS MOBILE)
        )
)
SELECT DISTINCT
  MR.GR_GROUP_ID,
  MR.ROWID_OBJECT AS MDM_PARTY_ID,
  MR.RECORD_TYPE,
  RL.RULE_NO AS RULE_DESCRIPTION,
  MR.AUTOMERGE_IND AS AUTO_MERGE,
  PARTY_XR.ROWID_SYSTEM AS SOURCE,
  PARTY_XR.PKEY_SRC_OBJECT AS SOURCE_KEY,
  PARTY.PARTY_TYPE_CODE AS PARTY_TYPE,
  PARTY.PARTY_ROLE_TYPE AS ROLE_TYPE,
  PARTY.ORG_NAME AS ORG_NAME,
  PARTY.ACCT_TYPE_CODE AS ACCT_TYPE_CODE,
  PARTY.ACCT_LVL_CODE AS ACCT_LVL_CODE, 
  PARTY.DISPLAY_NAME AS FULL_NAME,
  PARTY.FIRST_NAME ,
  PARTY.MID_NAME ,
  PARTY.LAST_NAME ,
  PARTY.DOB AS BIRTH_DATE,
  PARTY.GENDER_CODE AS GENDER,
  PARTY.INDUSTRY_CLS_CODE,
  PARTY.ACCT_NBR,
  PI.LINK_ID,
  PI.TIN TIN,
  PI.EID EID,
  PI.MBR_ID AS MEMBER_ID,
  PI.ACCT_ID AS ACCT_ID,
  PA.RESIDENT AS RESIDENTIAL_ADDR,
  PA.CORRESP AS CORRESPONDENCE_ADDR,
  PP.HOME AS HOME_PHONE,
  PP.MAIN AS MAIN_PHONE,
  PP.WORK AS WORK_PHONE,
  PP.MOBILE AS MOBILE_PHONE
 
FROM CTE_MATCH_RECORDS MR
JOIN CMX_ORS.C_REPOS_MATCH_RULE RL ON RL.ROWID_MATCH_RULE = MR.ROWID_MATCH_RULE
JOIN CMX_ORS.C_BO_PARTY PARTY ON PARTY.ROWID_OBJECT = MR.ROWID_OBJECT
JOIN CMX_ORS.C_BO_PARTY_XREF PARTY_XR ON PARTY_XR.ROWID_OBJECT = PARTY.ROWID_OBJECT
LEFT JOIN CTE_PARTY_IDENTIFICAITON PI ON PI.ROWID_OBJECT = MR.ROWID_OBJECT
WHERE PARTY_XR.ROWID_OBJECT = PARTY_XR.ORIG_ROWID_OBJECT
LEFT JOIN CTE_PARTY_ADDRESS PA ON PA.ROWID_OBJECT = MR.ROWID_OBJECT
LEFT JOIN CTE_PARTY_PHONE PP ON PP.ROWID_OBJECT = MR.ROWID_OBJECT
ORDER BY GR_GROUP_ID, RULE_NO, RECORD_TYPE;

Informatica MDM - Helpful SQL Queries

This article provides the SQL queries which will be helpful for daily usage for monitoring jobs, analyzing data as well as developing custom components.

1. Query to get Landing and Base Object tables

SELECT table_name

  FROM cmx_ors.C_repos_table

  where table_name not like '%_CTL' AND table_name not like '%_DRTY' AND table_name not like '%_EMI'

  AND table_name not like '%_EMO' AND table_name not like '%_HIST' AND table_name not like '%_HMRG'

  AND table_name not like '%_HPCT' AND table_name not like '%_HVXR' AND table_name not like '%_HXRF'

  AND table_name not like '%_MTCH' AND table_name not like '%_HTIP' AND table_name not like '%_STRP'

  AND table_name not like '%_MTIP' AND table_name not like '%_VCT'  AND table_name not like '%_VXR'

  AND table_name not like '%_XREF' AND table_name not like 'C_MT%' AND table_name not like 'C_RBO%'

  AND table_name not like 'C_REPOS%' AND table_name not like 'C_REPAR%' AND table_name not like 'C_S%' 

  AND table_name not like 'PKG%'

2. Query to get Column names

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,

NULLABLE, DATA_DEFAULT, SYS_IND, PKEY_IND

FROM CMX_ORS.DBO.C_REPOS_COLUMN

WHERE TABLE_NAME IN (

SELECT table_name

  FROM cmx_ors.C_repos_table

  where table_name not like '%_CTL' AND table_name not like '%_DRTY' AND table_name not like '%_EMI'

  AND table_name not like '%_EMO' AND table_name not like '%_HIST' AND table_name not like '%_HMRG'

  AND table_name not like '%_HPCT' AND table_name not like '%_HVXR' AND table_name not like '%_HXRF'

  AND table_name not like '%_MTCH' AND table_name not like '%_HTIP' AND table_name not like '%_STRP'

  AND table_name not like '%_MTIP' AND table_name not like '%_VCT'  AND table_name not like '%_VXR'

  AND table_name not like '%_XREF' AND table_name not like 'C_MT%' AND table_name not like 'C_RBO%'

  AND table_name not like 'C_REPOS%' AND table_name not like 'C_REPAR%' AND table_name not like 'C_S%' 

  AND table_name not like 'PKG%')

3. Query to get jobs status, run, failure

SELECT TABLE_DISPLAY_NAME, (END_RUN_DATE - START_RUN_DATE) AS TOTAL_TIME

, START_RUN_DATE, END_RUN_DATE, STATUS_MESSAGE

, ST.JOB_STATUS_DESC,FT.OBJECT_FUNCTION_TYPE_DESC

FROM CMX_ORS.C_REPOS_JOB_CONTROL JC,

     CMX_ORS.C_REPOS_JOB_STATUS_TYPE ST,

     CMX_ORS.C_REPOS_OBJ_FUNCTION_TYPE FT

WHERE JC.RUN_STATUS = ST.JOB_STATUS_CODE

     AND JC.OBJECT_FUNCTION_TYPE_CODE = FT.OBJECT_FUNCTION_TYPE_CODE;

4. Query to get match rule set using Match table

select * from cmx_ors.c_repos_match_set where rowid_match_set in (
select rowid_match_Set from cmx_ors.c_repos_match_rule where ROWID_MATCH_RULE in
(select distinct ROWID_MATCH_RULE from cmx_ors.c_bo_party_mtch)
)

5. Query to get match rule set using ROWID_MATCH_RULE

SELECT * FROM CMX_ORS.C_REPOS_MATCH_SET WHERE ROWID_MATCH_SET IN (

SELECT ROWID_MATCH_SET FROM CMX_ORS.C_REPOS_MATCH_RULE WHERE ROWID_MATCH_RULE='SVR1.1V4T6'  

)

6. Query to get ROWID_MATCH_RULE using Match Set and Rule Number

SELECT * FROM CMX_ORS.C_REPOS_MATCH_RULE WHERE ROWID_MATCH_SET IN (  

SELECT ROWID_MATCH_SET FROM CMX_ORS.C_REPOS_MATCH_SET WHERE MATCH_SET_NAME='IDL'

) AND RULE_NO=1

7. Query to get Match column names using ROWID_MATCH_RULE

select MS.MATCH_SET_NAME, MR.RULE_NO, MC.MATCH_COLUMN_NAME from CMX_ORS.C_REPOS_MATCH_RULE_COMPONENT c,

 CMX_ORS.C_REPOS_MATCH_RULE MR,

 CMX_ORS.C_REPOS_MATCH_COLUMN MC,

 CMX_ORS.C_REPOS_MATCH_SET MS

where c.ROWID_MATCH_RULE = MR.ROWID_MATCH_RULE

and c.ROWID_MATCH_COLUMN = MC.ROWID_MATCH_COLUMN

and MR.ROWID_MATCH_SET = MS.ROWID_MATCH_SET

and MR.ROWID_MATCH_RULE like  '%SVR1.ABC%'

8. Query to get a count of records processed for each job

SELECT JM.CREATE_DATE, FT.OBJECT_FUNCTION_TYPE_DESC, JC.TABLE_DISPLAY_NAME, MT.METRIC_TYPE_DESC, JM.METRIC_VALUE AS COUNT

FROM CMX_ORS.C_REPOS_JOB_METRIC JM,

CMX_ORS.C_REPOS_JOB_METRIC_TYPE MT,

CMX_ORS.C_REPOS_JOB_CONTROL JC,

CMX_ORS.C_REPOS_OBJ_FUNCTION_TYPE FT

WHERE JM.METRIC_TYPE_CODE = MT.METRIC_TYPE_CODE

AND JM.ROWID_JOB = JC.ROWID_JOB

AND JC.OBJECT_FUNCTION_TYPE_CODE = FT.OBJECT_FUNCTION_TYPE_CODE;

9. How to check Timeline Granularity

SELECT TIMELINE_GRANULARITY FROM C_REPOS_DB_RELEASE; 

Value	Type of Granularity
0	Seconds
1	Minutes
2	Hours
3	Days
4	Months
5	Years

Informatica Business Entity Services Overview

Are you looking for an article about then Informatica business entity services? Are you interested in knowing what is the business entity services? How to identify a route record in the business entity services and how to use the security and data filters? If so, then you have reached the correct place. In this article, we are going to learn business entity services and the operations involved and more details about this business entity services.

What is the business entity service? 

A business entity service is a set of operation that runs in the Informatica MDM hub in order to create , update , delete and search the records from the base object which act as a business entity. We can develop a custom user interface which can run Java code or javascript to make the business entity service call.

What are the operations involved in the business entity service?

The operations involved in the business services are - read operation, write operation and search operation.

• By using read operation we can read a record from the business entity. 
• By using the write operation we can write a record in the database for a business entity. 
• One other hand by using search functionality we can use the searchable field to perform the search operation.

What are the business entity service Endpoints?

There are 4 types of business entity service endpoints.

1. Enterprise JavaBeans Endpoint
2. REST Endpoint 
3. REST and EJB Endpoint
4. SOAP Endpoint

How to identify a route record in the business entity service?

We can identify a route record in the business entity by 3 ways

1. By using the Rowid. It is the Rowid object column value for the Base Object record
2. By using system name and Source Key. here the system name is the name of the system from which record is coming and Source Key is PKEY_SRC_OBJECT column value
3. By using Global Business Identifier aka GBID. If GBID is compound value then we need to pass compound column values.

How to apply security and Data filters to Business Entity Service?

The user role privileges for base object and resources are get inherited in the business entity. It also inherits any data filters that you set on business entity fields.

What are the differences between IDD Data View and IDD Business Entity applications

Would you like to know what are differences between Legacy IDD and Entity 360 or   Entity application? Are you also interested in knowing what are the open issues with Legacy IDD and Entity 360 application? If so, then you reached the right place. In this article, we will have a detailed discussion about the differences and features of both the applications. These differences are captured based on Informatica MDM 10.3 HF1.

Category	IDD Data View	IDD Business Entity
Customization	Use of IDD User Exits 1.     Integral part of IDD Configuration         2.    Easy to implement customization         3.    Easy to deploy as a component of IDD         4.    No separate resource configuration required, resources allocated to IDD will be used for User Exist        5.    Error handling follows MDM standard practice, no additional handling is required          6.    No additional security required as it integral part of IDD Application	No User Exit support         1.    Need to write external services (Restful or SOAP-based Web Services)        2.     Required additional efforts to build and implement and deploy these external services         3.    For scalability, high availability of external services, additional dedicated servers are required         4.    Need to apply and maintain security  as these are external services to IDD Business entity          5.    Extra error handling is required to follow MDM standard practice       6.    Extra the configuration is required to call external services          7.    Dedicated resources need to be allocated to handle user requests
Data Import template	IDD Data View provides the feature to import data. It is a very helpful tool when business would like to import bulk data in need basic. No need to create or update requires manually	Do not support Bulk import template. Need to create or update bulk volume of data manually
Unmerge functionality	It supports both Tree unmerge and linear unmerge. Note: During Tree unmerge unmerged unmerge records get separated from the group. During linear unmerge children records of unmerged record remain associated.	Supports the only Tree unmerge
Report	Easy to integrate repots in the IDD application using Jasper Reports	Jaspersoft reports work in a Home page only if it is the only component in the Home page.
Workflow	If IDD application includes workflows, we must generate the business entity schema as a requirement for Data Director to manage the workflow tasks. However, we need to migrate to business entities	The business entity the schema will be generated as part of Business Entity application publish event using the Provisioning Tool.
Both Entity and IDD Data Views- Hybrid mode	Informatica recommends that the Hybrid mode only on a temporary basis
Manual Override of matched record	Manual override  of value in the Matches view is allowed	Manual override a value in the Matching Records view is not allowed
Hierarchy View	Hierarchy relationships can be configured to show in a section to show duplicate hierarchy records.	The Hierarchy view does NOT permit the following actions:-      ·         Finding a duplicate entity.      ·         Initiating a merge.      ·         Sharing a bookmark URL
Limitations	1.       In the task inbox on the Home page, you cannot filter tasks by the creation date.      2.       When you export search results that are based on a timeline, the export process ignores the timeline and exports all data.	1.       The Cross Reference page and the Merge Preview page have pagination issues.      2.       In the search results, some rows are empty. The rows represent records that are filtered out because the user does not have permission to view the records.      3.       When a user role does not include the create and read privileges for a business entity, users with this role can still view the tasks associated with the business entity.      4.       In the History view, the timescale labels in the Options menu do not appear correctly initially.      5.       In the Hierarchy view, business entities in the Relationships tab of the history do not open in Business Entity view.      6.       In the Timeline view, you cannot open the relationship records that appear on the Relationships tab.      7.       In the Hierarchy view, in the Entity Details dialog box, when you click More Details, the dialog box closes without opening the selected business entity.      8.       In the Matching Records view, when you merge records, the system can appear unresponsive.      9.       If you delete a record and then search for the record, the ROWID of the deleted record still displays.      10.   In the History view, when you try to view event detail, an error might occur.

Overview of Informatica Customer 360i

Would you like to know more about what is Informatica Customer 360i? Are you also interested in knowing what are capabilities of the Informatica Customer 360i application? If so, then refer this article. This article also provides highlights on the underlying architecture of Informatica Customer 360i .

What is Informatica Customer 360i 

Informatica acquired AllSight company which is Artificial Intelligence enabled customer insight company on Feb 28, 2019. AllSight Inc a startup had a product named AllSight Intelligent 360. After-acquired by Informatica it called now as Informatica Customer 360 Insight (Customer 360i). It is powered by CLAIRE engine (Cloud-scale AI-powered Real-time Engine). CLAIRE uses artificial intelligence (AI) and machine-learning techniques powered by enterprise-wide data and metadata. It helps to significantly boost the productivity of all managers and users of data across the organization.

Capabilities of Informatica Customer 360i

1. It connects data of any type
2. It has capabilities to manage billions of records across all data sources
3. The customer data linkages can be easily resolved
4. With the help of Customer 360i, we can create relationships using advanced machine learning algorithms
5. Using Natural Language Processing we can provide additional customer attributes from unstructured data.
6. The relationships, households and complex B2B hierarchies using a graph data store can be easily visualized with product
7. It has capabilities to present multiple perspectives of the customer based on unique users and use case context

The architecture of Informatica Customer 360i

1. Customer 360 Insights is built on a big data technology stack.
2. The technologies used are Spark, Apache Hadoop, In-memory data stores, Graph, Columnar.
3. Data scientists can use R and Python languages with Informatica Customer 360i for flexibility.
4. It uses the microservices architecture to achieve scalability for deployment and redeployment of functionality
5. It also uses the SaaS deployment model which helps to simplify as well as accelerates implementation.

Use of Informatica Customer 360i

1. Informatica Customer 360i can be used for customer engagement
2. It works on structured and unstructured data sources
3. It will help enterprises to create the relationship between master, transaction, interaction, and reference data
4. These relationships will help to discover rich, personalized behavioral insights.

5. These insights can be used across the enterprise to connect customer interactions in real time and ensure the delivery of the next best action.
6. This new solution automates and simplifies profile and relationship unification
7. It also scales AI across transactions and interactions in the business data.

Customer Intelegence evolution

Application Centric
 1. Fix data quality
 2. De-duplication in the business data

Master Data Driven
 1. Resolve duplicate records from multiple store
 2. Manages master data
 3. Fix data quality in the enterprise system data


Customer Intelligence Empowered
 1. Match customer entities
 2. Enrich data with derived intelligence
 3. Provide multiple unique customer views

Details about Informatica MDM metadata or infrastructure tables

You might have come across the term metadata tables in infrastructure tables during your Informatica MDM project implementation. What are these infrastructure tables? What is the significance of these tables? How can we access it and use it? Are you facing these questions and would like to know more about these? If so, then you reached the right place. In this article, we will explore the infrastructure tables get generated during the Base Object, Stage and Landing tables configuration. So let's start.

Introduction:

The MDM infrastructure tables are the core part of Informatica MDM. These tables are created, whenever we configure the basic tables such as Base Object (BO), Stage and Landing tables along with their properties such as Raw Retention, Delta detection on the Stage table or match and merge setting on the Base Object table.

What are the MDM infrastructure tables?

Assume that we create Landing table, Stage table, and Base Object table as C_L_PARTY, C_S_SALES_PARTY, and C_B_PARTY respectively. Also assume that we configure raw retention, delta detection, tokenization, match and merge rule as well. After doing all these configurations at table level the supporting tables are created.

1. Tables at Landing table level: There is no infrastructure table created at the landing table level
2. Tables at Staging table level: The tables created at the Staging table level are 

• C_S_SALES_PARTY_RAW
• C_S_SALES_PARTY_PRL
• C_S_SALES_PARTY_OPL
• C_S_SALES_PARTY_REJ

Each of these tables has its own importance and are used during MDM batch job execution.

           3. Tables at Base Object table level: There are 14 supporting infrastructure tables are created.

• C_B_PARTY_MTCH
• C_B_PARTY_HIST
• C_B_PARTY_XREF
• C_B_PARTY_HXRF
• C_B_PARTY_DRTY
• C_B_PARTY_CTL
• C_B_PARTY_HMRG
• C_B_PARTY_HCTL
• C_B_PARTY_EMI
• C_B_PARTY_EMO
• C_B_PARTY_VXR
• C_B_PARTY_HVXR
• C_B_PARTY_VCT
• C_B_PARTY_STRP  

What is the need of the MDM infrastructure tables?

The Informatica MDM implementation involves various process such as Stage, Load, Tokenization, Match, Merge, etc. During each process, the data is transferred from the source table to the target table. During this transfer data is manipulated with the help of supporting table. e.g. During the stage job, the data is transferred from the landing tables to Staging tables. During this transfer, the landing data is maintained in _PRL, _RAW tables. The _PRL table data is used to determine delta of the source record which is subprocess during stage job. 

Similar cases are involved during load job as well tokenization job. These infrastructure tables play a vital role in Informatica MDM implementation.

Relationship between Landing table and the Base Object table

• The load job loads data from the Stage table to a Base Object
• There is still the dependency on landing table data to handle the rejection. 
• The batch job will try to pull the source table record for inserting into the reject table.
• If the landing table is missing the corresponding records, then the reject table will have an entry to state that the source table entry not found. 
• If the landing table is huge and performance issues occur in the load job during the rejection handling, then assess the environment to add a custom index on the landing table.

Is it ok to modify the existing structure of the MDM infrastructure tables?

Informatica strongly recommends that do not modify the structure of these tables as these designed for internal processing purpose only. If you modify these tables, metadata validation may complain error.

The video below provides detailed information about the MDM infrastructure tables -