Troubleshooting Guide for MM
Erwin Resources
Overview
ERwin is a data modeling tool that you can use to create and maintain databases or resource models. You can use ERwin to create Logical, Physical, or Logical/Physical models. You can export these models to an XML file. You can use these XML files as metadata file sources to load ERwin metadata into Metadata Manager.
Pre-requisites for Creating an ERwin Resource
To load an ERwin resource you would need to have the following setup:
Install Metadata Manager Agent on a Windows box where the ERwin metadata source files could be accessed. Metadata Manager Agent Service has to be in “Started” state.
If you are planning to use the ERwin file formats like ‘.erwin’ or ‘.er1’, Metadata Manager Agent has to be installed on machine where ERwin is installed.
If you are accessing the ERwin metadata through model manager, make sure that Metadata Manager Agent can access the ERwin Data Modeler’s database. This requires database client drivers. Incase of MS SQL server or Sybase, the system DSN pointing to ERwin data modeler database has to be created.
Supported ERwin Metadata Source Formats in MM 8.6.1
In Metadata Manager 8.6.1 ERwin Metadata can be sourced from
ERwin Model Manager
XML export of ERwin models
‘.erwin’ or ‘.er1’ or ‘.erx’ ERWIN file formats
The following table specifies the required file type formats and Model Manager for each supported version of ERwin:
File Type / Application / Version.erx / ERwin 3.0 to 3.5.2
.er1 / ERwin 4.0 SP1 to 4.1
.erwin / ERwin 7.x
.xml / ERwin 4.0 SP1 to 4.1, 7.x
ERwin Model Manager / ERwin 7.x
Notes:
1. Metadata Manager supports extracting metadata from .erwin and .er1 ERWIN file formats. This requires ERWIN installation and the Metadata Manager Agent has to be installed on the machine where ERWIN is installed.
2. XML files do not require ERwin installation where MM Agent is installed. However, while exporting ERwin 7 models in to XML format, you must use the All Fusion Repository format to correctly export a model from ERwin 7.x to an XML file. Metadata Manager requires the All Fusion Repository format to extract the entire model, including all physical names and datatypes for columns in a user-defined domain.
To export a model in the All Fusion Repository Format, open the ERWIN file in ERwin, save the file as an XML file and select the All Fusion Repository Format. ERwin requires the CA All Fusion ERwin Data Modeler COM/OLE API to export an XML file in the All Fusion Repository format.
3. When you choose to extract metadata from ERwin 7.x Model Manager, you do not need to export metadata to a file. Configure the database connection parameters to connect to ERwin’s repository while creating the resource. This approach is not yet certified as of MM 8.6.1 HF3.
Troubleshooting ERwin Metadata load failures
Incase if an ERwin resource load fails in MM 8.6.1,
* Make sure the Metadata Manager Agent is running and the Agent URL entered is correct. You can verify this by entering the following URL in your browser:
* Check the Load Monitor log for the ERwin resource. The load could have failed either in MITI bridge extraction (done by MM Agent) or PowerCenter Workflows run by MM Service. If the Load has failed during the MITI extraction, identify the error in the Load Monitor “Log” tab (Error would be highlighted in RED). Search Knowledge Base for known error messages.
* If you observe any MITI validation errors, set off the MITI validation by following the below steps:
- Open the file <PC_Home>/server/tomcat/shared/classes/IMM.properties
- Search for the parameter names “MITI_VALIDATION_LEVEL” and set its value to “NONE”.
- Save the file and restart the MM service.
* If the load has failed while running PowerCenter workflows, check for the session error in the “Session Statistics” tab of the Load Monitor logs.
* In any of the above cases, save the logs (PDF format) and send this to Informatica Support for further assistance. The log files (mm.log and mm_agent.log) can also be found the following location:
<PC_Home>/server/tomcat/temp/<MM_ServiceName>/logs/
(This is the default location but it can be overridden by editing the log4j.xml)
Required details to repro
In case of MITI errors, we may need to get the debug logs from the MITI. Enable debug logging as below:
On MM Agent installation, open <MM_Agent_Dir>/conf /mirsetup.xml
Search for “LogLevel” element and set it to 6 for DEBUG log. Eg: <LogLevel>6</LogLevel>
MITI Agent log will be available in the location: <MMAgent_Install_Folder>\log\logService\
If the Erwin XConnect load fails, please contact Informatica Technical Support and provide the ERwin model export file (format specified in the above table) to replicate the issue.
Known Limitations
ERwin XConnect in Metadata Manager 8.5.1 loads only Logical objects and not physical objects
Erwin Data Modeller with DB connections is not certified in MM861 HF3, but the Erwin Model option is available in MM861 Erwin resource.
Working with Erwin
There are three model types in Erwin. They are:
- Logical
- Physical
- Physical and logical
1. Logical Model:
Create a table TEST_LG with the following attributes:
First NameVARCHAR (15)LOGICAL ONLY (Unknown domain)
Last NameVARCHAR (15)(Unknown domain)
City NameVARCHAR (20)LOGICAL ONLY (String domain)
StateVARCHAR (3)(String domain)
We can observe that for unknown domain, default data type is CHAR (18) and for string VARCHAR (20).
Save the model as TEST_LG.xml. In XML file attribute ModelType=”1” indicates it’s a logical Model. Also for First Name and City Name attributes Logical_Onlytrue</Logical_Onlyis present in XML file.
2. Physical Model:
Create a table TEST_PHY with following attributes:
First NameVARCHAR (15)PHYSICAL ONLY (default domain)
Last NameVARCHAR (15)(default domain)
City NameVARCHAR2 (20)PHYSICAL ONLY (string domain)
StateVARCHAR2 (3)(string domain)
Note: Spaces in the attribute name replaced with _ (underscore).
We can observe that for default domain, default data type is CHAR (18) and for string VARCHAR2 (20).
Save the model as TEST_PHY.xml. In XML file attribute ModelType=”2” indicates it’s a physical Model. We can create this table in database by using Tools->Forward Engg/Schema generation.
3. Logical/Physical Model:
In logical Model create a table TEST_LG_PHY with following attributes:
First NameVARCHAR (15)LOGICAL ONLY (Unknown domain)
Last NameVARCHAR (15)(Unknown domain)
City NameVARCHAR (20)LOGICAL ONLY (String domain)
StateVARCHAR2 (3)(String domain)
In physical model, add the following attributes to above table:
Address 1CHAR (10)PHYSICAL ONLY (default domain)
Address 2CHAR (15)(default domain)
Address 3VARCHAR2 (10)PHYSICAL ONLY (string domain)
Address 4VARCHAR2 (25)(string domain)
Note: Space will be replaced by _ (underscore).
Save the model as TEST_LG_PHY.xml. In XML file attribute ModelType=”3” indicates it’s a physical Model. We can create this table in database by using Tools->Forward Engg/Schema generation.
How to Load ERwin Resources in MMwith Example
Creating Erwin Model:
1. Create an Erwin Logical/Physical model with the following details:
Logical Model:
EMP DETAILS
(EMP NO STRING,
EMP NAME STRING)
Physical Model:
EMP_DETAILS
(EMP_NO VARCHAR(20),
EMP NAME VARCHAR(20))
2. Once we create Logical model, Physical model will be created automatically, space will be replaced with underscore. (_).
3. Save the model as xml file with All Fusion Repository Format check box checked.
Creating tables for the Erwin Model in Database:
Create an Oracle (Database) table EMP_DETAILS as mentioned above in an oracle schemaand create the oracle resource with the above mentioned oracle schema and load the oracle Resource.
Creating and Loading Erwin Resource
1. Go to create, load and configure resources and click on New Resource.
2. Select the Erwin under Data Modeling options and click Next >.
3. Give a name for the resource like EMP_DETAILS. Also, we can provide description for the resource. Press Next >.
4. Provide the details that are needed to add the resource like agent URL, File name etc and press Next >.
5. If you want to schedule it, provide the details else just press Finish.
6. The added resource will be in Not Loaded status.
7. Provide the connection Assignment Details and save.
8. Click Start LoadingOperation will start the loading operation.
Note:
- Logical Tables created in Logical Model will be present under “Entities” and Physical Table will be present under “Table”.
- If the model is Physical and Logical, then only the tables that are marked as “Logical only” will be present under “Entities”. Other tables (Physical) are present only under “Tables”.
- Even though Physical Tables are associated with Logical tables in Erwin, that details will not be imported to MM.
Example: If Test is a physical table in Erwin, after loading it into MM, it can be seen only under “Tables” and not under “Entities”.
- Logical tables i.e. “entities”, will contain attributes where as physical tables i.e. “Tables” contain Columns.
- MM will not show data lineage between Logical and Physical objects.
Splitting the Model
1. We can divide the Physical/Logical Model into Logical model and Physical model.
2. This can be achieved by: Tools > Split L/P Model. . .
3. Provide the filenames for both Physical and Logical Model. These files can be used as a resource in MM.
4. Save the logical model first and the physical model will be saved next.There is an option of saving the logical and physical model in XML format.
Loading the Logical Model to MM
1. Create a Logical model in Erwin.
Example:
2. Save the model as TEST_LOGICAL.xml.
3. Check “AllFusion Repository Format” and press Ok.
4. Go to MM & Business Glossary URL. Click on Create, Load and Schedule Resources and then on New Resource.
5. Select the Erwin under Data Modeling as Resource Type andClick Next.
6. Provide Name for the Resource. Example: TEST_LOGICAL
7. Provide the Configuration Details.
8. Click on Next and Click on Finish, which will add the resource to the list.
9. Load the resource by clicking on Start Load Operation. This should show the status as “Load Successful; indexing Successful”.
10. Go to Browse and Search Metadata Catalog and Refresh the Catalog.
11. You can see the resource “TEST_LOGICAL” in the catalog. Expand it to see its contents.
12. We can see the structure in above snapshot. It consists of “TEST LOGICAL” under “Entities”. It consists of “Attributes” namely DOB, ID and NAME.
Loading the Physical Model to MM
1. Create a physical Model using Erwin 7.0
Example:
2. Save the model as TEST_PHYSICAL.xml.
3. Check “AllFusion Repository Format” and press Ok.
4. Go to MM & Business Glossary URL. Click on Create, Load and Schedule Resources and then on New Resource.
5. Select the Erwin under Data Modeling as Resource Type and Click Next.
6. Provide Name for the Resource and click on Next >. Example: TEST_PHYSICAL
7. Provide the Configuration Details.
8. Click on Next and Click on Finish, which will add the resource to the list.
9. Load the resource by clicking on Start Load Operation. This should show the status as “Load Successful; indexing Successful”.
10. Go to Browse and Search Metadata Catalog and Refresh the Catalog.
11. You can see the resource “TEST_PHYSICAL” in the catalog. Expand it to see its contents.
12. We can see the structure in below snapshot. It consists of “TEST_PHYSICAL” under “Tables”. It consists of “Columns” namely DOB, ID and NAME.
Loading the Logical / Physical Model to MM
1. Create a L/P model in Erwin 7.0
Example: Model consists of two tables: TEST LOGICAL and TEST_PHYSICAL
In Logical Model, create TEST LOGICAL:
Double click on it and make it as Logical only and click on OK.
Also, Create TEST_PHYSICAL:
2. Save the model as TEST_LOG_PHY.xml
3. Check “AllFusion Repository Format” and press Ok.
4. Go to MM & Business Glossary URL. Click on Create, Load and Schedule Resources and then on New Resource.
5. Select the Erwin under Data Modeling as Resource Type and Click Next.
6. Provide Name for the Resource and click on Next >. Example: TEST_LOG_PHY
7. Provide the Configuration Details.
8. Click on Next and Click on Finish, which will add the resource to the list.
9. Load the resource by clicking on Start Load Operation. This should show the status as “Load Successful; indexing Successful”.
10. Go to Browse and Search Metadata Catalog and Refresh the Catalog.
11. You can see the resource “TEST_LOG_PHY” in the catalog. Expand it to see its contents.
12. From the below snapshot:
TEST LOGICAL is present under “Entities”, which contains Columns.
TEST_PHYSICAL is present under “Tables”, which contains Columns.
Note: When the model is Logical/Physical, entities will contain columns not attributes. If model is Logical, then entities will contain attributes.
1