Skip to main content
To homepage To homepage

Custom Views are similar to tables and are generated using SQL “CREATE VIEW” statements, which provide for a great deal of flexibility in terms of the joins and groupings that can be used, as well as all the other SQL functionality and syntax that are available.

Adding a Custom View

To add a custom view, follow the steps below.

  1. Expand the relevant data area, right-click Views and then click Add Custom View.
  2. In Name as in script box, type the name of the view, which must be the same as the name specified in the script, including the schema.
  3. Enter the create statement for the view using the standard SQL syntax. Drag tables, fields and variables from the list in the right-hand pane to use them as parameters. Fields entered as parameters can be mapped to the view's fields to enable documentation and data lineage - for more information see the section on Mapping Custom View Fields below.
  4. Click OK to save the view.
  5. Deploy the view
  6. Right-click the view and select Read View Fields.

     

  7. Expand the view to show the fields, and then right-click the view and select Preview View to double-check that the view is working properly.

Adding a Custom View Based on a Table

To add a custom view based on an existing table, drag the table to the Views node in a data area.

Editing a Custom View

In order to change the view:

  1. Right-click on the Custom View and select Edit Custom View
  2. Make the changes to the create statement and click OK
  3. Right-click on the Custom View and select Deploy
  4. Right-click on the Custom View and select Synchronize View Fields

Mapping Custom View Fields

Since a custom view is very flexible, the relationship between the parameterized 'input' fields and the view's fields cannot automatically be mapped. This means that documentation and visualizations, such as data lineage, cannot automatically trace a field from data source to destination if it passes through a custom view. However, documentation and data lineage through custom views can be achieved with some manual mapping.

To map the custom view's fields to the parameterized fields, follow the steps below.

  1. Make sure the fields have been read, then right-click the view and click Map Custom View Fields.

     

  2. Drag each field from the View fields list to one or more fields in the Parameter fields list to create mappings. To delete a mapping, click the mapping and then click Delete. Click Auto Map to map fields automatically based on name.
  3. Once all view fields have been mapped, click OK.

Avoid transformations in the view
There are no transformations in Christian's example, but it can be tempting to take advantage and create new columns with transformations in the view, when you're still writing code.
If you do as Christian described, you will in any case get the columns that are included in the transformation in data lineage. But if you create the new column in the table instead of in the view, you also get the transformation itself in data lineage.
A little more work, but it's worth it in the long run.

BR

Anders

​@Christian Hauggaard with the fields and tables mapped as parameter, should TimeXtender application also sense when one of the underlying tables was changed and thus advise a deployment of the view?

We have a case where the table name of one of the underlying tables was changed, but TimeXtender did not make the view ‘red’ and we ended up with errors during execution. 

Reply


Terms and ConditionsCookie settings