On deploy of a data model, you can use modeling transformations to create views in the target database. These views are created based on the business entities that are created in the target database on deploy. A modeling transformation contains one or several SQL statements which define the transformation actions to be done.

Usually, a transformation is created in SQL and then the file is uploaded to the Data templates and transformations. On upload, a new transformation is created or an existing transformation is overwritten. You can also manually create a transformation in DMS and create or copy the SQL statements to the Definition field.


Standard procedure

1. Go to Data modeling studio > Data modeling studio > Data templates and transformations.
2. Sub-task: Upload modeling transformation.
  2.1 Click Upload.
  2.2 On the dialog, browse for and select the desired compressed (zipped) folder or single modeling transformation file.
  Click Upload.
 

Note: If you select a compressed (zipped) folder with several data templates and transformations, all these data templates and transformations are considered for upload.

  2.3 If a data template or transformation:
- Does not exist, it is created.
- Already exists, you get a message to indicate if it must be overwritten or not.
  Click the desired action: 'Yes' or 'No'.
  2.4 On the Data templates and transformations page, in the list, find and select the uploaded transformation.
  2.5 Click Edit.
  2.6 In the Description field, type or edit the description.
  2.7 In the Category field, select 'Modeling'.
3. Sub-task: Create modeling transformation.
  3.1 On the Data templates and transformations page, click New.
  3.2 In the Template field, type a value.
  3.3 In the Description field, type a value.
  3.4 In the Category field, select 'Modeling'.
  3.5 In the Type field, select 'T-SQL'.
  3.6 Expand the Definition section.
  3.7 In the Definition field, create or copy the desired SQL statements.
4. Close the page.

Notes

In the SQL statements, you can use variables. On deploy, these variables are replaced with a value. You can use these variables:

  • $MODEL$: Is replaced with the name of the deployed data model.
  • $SCHEMA$: Is replaced with schema name as defined for the deployed data model.
  • $EXECUTIONID$: Is replaced with the execution GUID that is assigned to the deployment of the data model.

Related to Notes

Set up transformations

 

Provide feedback