Export to Data Mapping (SQL like) Scripts
Any data mapping with replication mappings and/or query mappings can be exported (without loss) to the Data Mapping Scripts format. This format is based on the standard database SQL Data Manipulation Language (SQL DML) syntax and includes both:
- The data connection data models (e.g. database schema, tables, columns) of their source and target data stores faithfully representing any supported technology (RDBMS, NoSQL, File Systems)
- The data integration (DI/ETL/ELT/CDC) for the data flow lineage between these data stores.
The specifics of the syntax for the data mapping script format are explained in a sample file at:
$MM_HOME/conf/MIRModelBridgeTemplate/DataMappingScript/DataMappingScriptTutorial.sql
The data mapping scripts can be edited /modified or may be generated entirely from scratch to model (simulate) a DI/ETL/ELT/CDC tool which may not be a part of the supported tools for a native model import.
Steps
- Sign in as a user with at least the Metadata Viewing capability object role assignment on the data mapping you are going to export from.
- Navigate to the object page for the data mapping model. Create a new empty model if necessary.
- Go to More Actions > Export Data Mapping Script.
- Go to the Operations widget in the banner and select the Export data mapping… to Show log.
- Click Download Operation Files.
Example
Sign in as Administrator and open the Adjustments to Staging data mapping.
Go to More Actions > Export Data Mapping Script.
Go to the Operations widget in the banner and select the Export data mapping… to Show log.
Click Download Operation Files.
The result contains three files, one for each query and/or replication mapping in the data mapping.
And the script contains the SQL like text:
CREATE MAPPING CustomerPayment;
CREATE CONNECTION Adjustments TYPE "RELATIONAL DATABASE";
CREATE CONNECTION "Staging DW" TYPE "RELATIONAL DATABASE";
SELECT
CONCAT(Adj.TransSetNm, CONCAT(Adj.AdjNum, Adj.TransDT)) AS PaymentID COMMENT 'Based upon Transaction Set Name, Adjustment Number and DateTime'
, Adj.TransDT AS PaymentDate COMMENT 'Transformation of Transaction DateTime'
, CONCAT(Adj.TransAmt, CONCAT(Adj.AdjNum, CONCAT(Adj.AdjTyp, CONCAT(AdjType.AdjTypNm, AdjType.AdjTypTxt)))) AS PaymentDescription COMMENT 'Text constructed from Adjustment Reason and the decoded value of Adjustment Type Code depending upon the Transaction Set Name:
- if Transaction Set Name is “Other” then it is a simple action with little information about type and so the type should not be included here
Otherwise, the decoded value of Adjustment Type Code should be included in the description text.
'
, Adj.TransAmt AS PaymentAmount COMMENT 'Customer pay amount is derived from all adj amount (Transaction Amount)'
, 'Adjustments' AS CheckNumber COMMENT 'Fixed “Adjustments" Check Number'
, 'Adjustments' AS InvoiceNumber COMMENT 'Fixed “Adjustments" Invoice Number'
, 'Paid in Full' AS PaymentStatus COMMENT 'Set to "Paid in Full"'
, 'Adjustments' AS PaymentType COMMENT 'Fixed “Adjustments" PaymentType'
INTO dbo.CustomerPayment@"Staging DW"
FROM dbo.Adj@Adjustments AS Adj
INNER JOIN dbo.AdjType@Adjustments AS AdjType
ON Adj.AdjTyp = AdjType.AdjTyp;
Again, unlike the Metadata Excel Format, where it is NOT a good practice to then re-import or update and re-import the spreadsheet into a data mapping, with the data mapping script format you may perform as many round-trips as you wish.
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!