USER MANUALS

Creating Flatten Views

Virtual DataPort supports modeling data types with a complex structure using the types “register” and “array”. An element of the type array can be considered a sub-view and an array type always has an associated register type that acts like the schema of the sub-view it is modeling.

Sometimes you may need to “flatten” a compound field that contains an array of registers. This is particularly frequent when processing XML and Web service base views. This section describes how to do it.

Note

It is also possible to perform the inverse operation of “flatten” to create array elements from sets of tuples or register elements from several fields. This can be done by defining a GROUPBY operation using the NEST or REGISTER functions. See information about the NEST and REGISTER aggregation functions in the VQL Guide.

Note

If you need to “flatten” the fields of a register and not an array, do not create a Flatten view. Instead, create a Selection view and in the “Output” tab of the view, right-click on the register and click Project subfields of…

To follow the examples of this guide, before explaining how to create a Flatten view you have to create a Web service base view over the “sales” Web service data source (this data source was created in the section Importing SOAP Web Service Sources):

  1. Double-click on the data source “sales”, in the Server Explorer and then, click Create base view.

  2. Click Create base view beside the operation getAverageMonthlyRevenue and select Do not stream output.

  3. Rename the base view to average_revenue_array.

  4. Click Save (image3).

This view returns an array of elements. Therefore, it is very difficult to combine its data with data from other views. Thus, you will create a “flatten” view with the two attributes of the array: taxId and revenue.

Default base view created for the Web Service operation getAverageMonthlyRevenue

Default base view created for the Web Service operation getAverageMonthlyRevenue

The Flatten view dialog is very similar to the Union view dialog, except that it does not have the Associations tab. Read the section Creating Union Views to learn how the tabs of the Flatten view work.

In the “Model” tab of the view, you can add “View parameters”. The section Parameters of Derived Views explains what are view parameters and the rules you need to follow when using them.

Follow these steps to create the Flatten view of the example:

  1. Right-click on the view average_revenue_array and click Flatten on the menu New.

    You can see that the Tool has added this view to the Model tab. You can add a view from another database.

  2. In the “Model” tab, you can see a selector next to each array field. You can select as many arrays as you want to flatten. Each time you change the selected array fields, the preview is updated showing the output schema. Following with this example, select the array return. After this, the flatten preview has two attributes: taxId and revenue.

  3. In the Output tab, rename the new view to flat_revenue.

  4. Click Save (image3) to create the view.

Flattening the array of the base view average_revenue_array ("Model" tab)

Flattening the array of the base view average_revenue_array (“Model” tab)

Flattening the array of the base view average_revenue_array ("Model" tab) (2)

Flattening the array of the base view average_revenue_array (“Model” tab) (2)

Schema of the flat_revenue view

Schema of the flat_revenue view

Follow these steps to create the Flatten view of the example using the Design Studio:

  1. click on the three-dots icon next to average_revenue_array and click Flatten on the menu New.

    You can see that the Tool has added this view to the Model tab. You can add a view from another database.

  2. In the “Model” tab, click the flatten icon highlighted in this figure next to the array return. After this, the target view has two attributes: taxId and revenue.

  3. In the Output tab, rename the new view to flat_revenue.

  4. Click Save (image3) to create the view.

Flattening the array of the base view with Design Studio

Flattening the array of the base view average_revenue_array (“Model” tab) with the Design Studio

Schema of the flat_revenue view in the Design Studio

Schema of the flat_revenue view in the Design Studio

Add feedback