We have a new community! Please visit support.timextender.com
Follow

Views

Views

A view is a virtual table in your data warehouse or in your staging database where you can group together information from two or more tables in your data source. Views can, for example, be used to provide a user with a simplified view of a table and to limit access to sensitive data.

In TimeXtender , you can add 'regular' views with code generated by the software or custom views where you write the SQL that creates the view.

Views

In TimeXtender, a regular view can be based on a group of regular fields from different tables or lookup fields.

Adding a View Based on Regular Fields

  1. Expand the relevant database, right-click Views and then click Add View.
  2. In the Namefield, type a name for the view.
  3. In the Field type field, select Standard Table Field.
  4. In the Table list, select the table you want to retrieve data from.
  5. In the Field list, select the field you want to use in the view.
  6. In the Alias field, type a name for the view, and then click OK. The selected field is displayed in the View pane.
  7. To add more fields from the same table or a field from another table, click New, and then repeat steps 3-6. Do this for all the tables you want in the view.

Note: If you want to add fields from more than one table, the tables must be related.

Adding a View Based on a Lookup Fields

Creating a view based on a lookup field consists of the following steps.

  1. Expand the relevant database, right-click Views and then click Add View.
  2. In the Name field, type a name for the view. You can also click Schema to select a schema and Outer Join to use outer join in the view.
  3. In the Field Type list, select Lookup Field. The dialog changes so that you can create and specify the properties of the lookup field:
    • Click the Table list and select the table that holds the lookup field.
    • Click the Field/Function list, select the field or function you want to use and then specify which values to return. You have the following options:
      • TOP returns the value of the first record in the column.
      • SUM returns the sum of all field values in the column.
      • COUNT returns the number of records.
      • MAX returns the maximum value of the records in the column.
      • MIN returns the minimum value of the records in the column.
    • In the Alias field, type a name for the lookup field if you want the name to be different from the source field name.
  4. Click OK. The field is displayed in the View pane of the window.
  5. Click New if you want to create another field.
  6. You have to specify a join between the view table and the lookup tables.
  7. Click the Lookup field list and select the field to look up.
  8. In the Operator field, select the operator that determines how you want the columns compared.
    Operator Description
    Equal Returns values that are equal
    Greater Returns values that are greater than the value of the lookup field or the specified fixed value

    Greater or Equal

    Returns values that are greater than or equal to the value of the lookup field or the specified fixed value

    Less or Equal

    Returns values that are less than or equal to the value of the lookup field or the specified fixed value

    Less Than

    Returns values that are less than the value of the lookup field or the specified fixed value

    Not Equal

    Returns values that are different from that of the lookup field or the specified fixed value

    Note: A default inner join is created which only returns results from the rows common to the two joined tables. For the complete set of records from the joined tables, check the Outer Join box at the top of the dialog.
  9. Click OK, and then click New if you want to specify a new join.
  10. You also have to define the sort order.
    -
  11. In Lookup Table Field list, click the field you prefer.
  12. In the Sort Order list, select how you want the results sorted. Results can be sorted in either ascending or descending order.
  13. Click OK, and then click New if you want to specify a sort order for another field in the view.
  14. Once you have completed all steps, and created all the joins you need, click OK in the upper right corner of the dialog to create the view.

Custom Views

With custom views, you write the SQL that generates the view, giving you a large amount of flexibility.

Adding a Custom View

To add a custom view, follow the steps below.

  1. Expand the relevant database, right-click Views and then click Add Custom View.
  2. In Name as in script, type the name of the view. You must use the same name in the script, including schema.
  3. In the text box, enter the script that creates the view using the standard SQL syntax. Drag tables, fields and variables from the list to the right to use them as parameters. Fields used this way can be mapped to the view's fields to enable tracing - see Mapping Custom View Fields below.
  4. Click OK to save the view.
  5. Deploy the view and then right-click the view and click Read View Fields to show the view's fields in the tree.

To add a custom view based on an existing table

  • Drag the table to a Views node on a data warehouse or staging database.

Synchronizing View Fields

If you change the view to add, edit or remove fields, you need to synchronize the fields with the project.

To synchronize view fields

  • Right click the view and click Synchronize View Fields.

Mapping Custom View Fields

Since a custom view is very flexible, it is not possible to calculate the relationship between the parameterized 'input' fields and the view's fields with certainty. This means that documentation and visualizations, such as data lineage, cannot trace a field from data source to destination if it passes through a custom view. However, this is possible 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.

 

Was this article helpful?
0 out of 0 found this helpful

5 Comments

  • 0
    Avatar
    Jon Catt

    Hi Thomas - I don't see a "Synchronize View Fields" in the right-click menu.

    Is that option version dependent?  I'm using TX/ODX 20.10.21.64

  • 1
    Avatar
    Thomas Hjermitslev

    Hi Jon - synchronizing fields only apply to custom views where the application needs to read the script to see what fields are defined there.

  • 1
    Avatar
    Hongwei

    Hi Thomas,

    Everytime when I save Custom View, there are a Waring message. Do you know why?

  • 1
    Avatar
    Thomas Hjermitslev

    Hi Hongwei,

    As the message tries to explain, the custom view script needs to contain the name of the view as defined in the "Name as in script" box. Basically, TimeXtender needs to know what the view is called to be able to use it.

    If you start your custom view with the following, it should work:

    CREATE VIEW [dbo].[V_JobMileStonesDate] AS
  • 0
    Avatar
    Hongwei

    Thanks, it's worked.

Please sign in to leave a comment.