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
- Expand the relevant database, right-click Views and then click Add View.
- In the Namefield, type a name for the view.
- In the Field type field, select Standard Table Field.
- In the Table list, select the table you want to retrieve data from.
- In the Field list, select the field you want to use in the view.
- In the Alias field, type a name for the view, and then click OK. The selected field is displayed in the View pane.
- 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.
- Expand the relevant database, right-click Views and then click Add View.
- 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.
- 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.
- Click OK. The field is displayed in the View pane of the window.
- Click New if you want to create another field.
- You have to specify a join between the view table and the lookup tables.
- Click the Lookup field list and select the field to look up.
- 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
- Click OK, and then click New if you want to specify a new join.
- You also have to define the sort order.
-
- In Lookup Table Field list, click the field you prefer.
- In the Sort Order list, select how you want the results sorted. Results can be sorted in either ascending or descending order.
- Click OK, and then click New if you want to specify a sort order for another field in the view.
- 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.
- Expand the relevant database, right-click Views and then click Add Custom View.
- In Name as in script, type the name of the view. You must use the same name in the script, including schema.
- 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.
- Click OK to save the view.
- 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.
- Make sure the fields have been read, then right click the view and click Map Custom View Fields....
- 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.
- Once all view fields have been mapped, click OK.
5 Comments