1. Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
  2. Download Data

7 Download Data

When a business user clicks Download Data from the Oracle Visual Builder tab, Oracle Visual Builder Add-in for Excel fetches data from the REST service and displays that data in the integrated workbook's layouts. This action generally involves one or more GET requests on the business object's collection path.

How the add-in manages a download depends on the type of layout, whether there are dependent layouts, and the add-in's download settings. For example, you can configure the add-in to retrieve descendant rows in a single payload. See Configure Download to Use a Single Payload.

To gain a better understanding of how the add-in behaves during a download, refer to the appropriate section:

Note:

The Download Data icon is disabled if the Download Enabled check box is not selected in the Layout Designer. See Manage Layout Capabilities. It may also be disabled if the GET operation is not configured on the collection path for the business object. See Manage Metadata Path Information.

Table Download

When a business user invokes a download for a Table layout, Oracle Visual Builder Add-in for Excel performs a number of actions including clearing existing data from the table, sending a GET request on the business object collection path, processing the response, and writing values to the worksheet.

Here is a detailed breakdown of what happens when the business user clicks Download Data for a Table layout:

  1. If the business user is not logged in, they are prompted to log in.
  2. If polymorphic business objects are used in the layout(s), then the polymorphic metadata is refreshed.
  3. The add-in may display one or more prompts in this order. If any of the prompts are canceled, the download operation is terminated.
    1. If the table has any pending changes, a warning is displayed and the business user can cancel to avoid losing changes.
    2. If path parameter values are required, the business user is prompted for these.
    3. If a row finder is configured and the finder includes row finder variables, the business user is prompted to provide values for the variables.
    4. If a graphical search is configured, the business user is prompted to provide values for the search conditions.
  4. When the final prompt has been accepted, a progress window is displayed. The business user may cancel the download at any time.
  5. During download:
    1. If there are any worksheet filters, these are cleared.
    2. All existing data is cleared from the table.
    3. Table, including headers, is fully redrawn.

      All polymorphic field sets are expanded at this time to include the global segments, discriminator (context segment), and context-sensitive segments.

  6. List of values (LOV) columns are initialized.

    If a given LOV already has choices cached, that cache is used at this point. If a given LOV has no cache, a REST request is sent for the first 300 choices.

  7. The first page of items is requested from the REST service.

    The add-in issues a GET request on the business object collection path. This request includes various query parameters and request headers.

  8. The add-in uses a single background thread to request the second page of items while processing the response from the first page.
  9. For each row received, the add-in:
    1. Prepares the values to be written to the worksheet.
    2. Determines the appropriate cell styling.
    3. For LOVs, the identity values are exchanged for display values using the cache. Missing identity values are captured for later.
  10. If there are more rows to download after the first page is processed, the add-in prompts the business user to continue.

    If the business user clicks Stop Now, the download operation stops immediately. If they click Download All, the operation continues to retrieve additional pages until no more pages are available.

  11. For each page of rows, the add-in:
    1. Issues additional GET requests on the LOV data source for LOV cache misses, and completes the swap of identity values for display values.
    2. Writes blocks of values to the worksheet.
    3. Applies appropriate styling to blocks of cells.
  12. The add-in continues to fetch page after page until there are no more items to fetch.
  13. The columns are resized to fit the current content.
  14. The table row height is reset to the add-in's standard height.
  15. If the table is not visible, the upper-left corner is selected to make it visible.
  16. A post-download macro is invoked, if configured.
During the download operation:
  • The add-in populates a special column called the "Key" column. The content of the cells in the key column is not human-readable. This columns contains "housekeeping" information for each row. This information is essential to the proper working of the table features. You should never edit or attempt to remove the key column. If you sort the table, you must include the key column in the range of sorted cells. Otherwise, you may get data corruption.
  • The status viewer is updated at various stages.

Note:

Problems communicating with the REST service may interrupt the download process. You can use the Network Monitor to see details of each request and response. See Network Monitor.

Form-over-Table Download

When a business user invokes a download for a Form-over-Table layout, Oracle Visual Builder Add-in for Excel downloads the form data first, then downloads the child rows for the current form row.

The download process is similar to the table download with these differences:

  • For the form download:
    • The add-in sets the count/limit query parameter to "1" (one) since only one row is displayed in the form.
    • There is no pagination at the form level.
    • The add-in populates the form region with data from the first row in the response from the GET request on the collection path.
  • For the table download, the path parameters for the child collection path are managed automatically by the add-in based on the current parent row.
  • The post-download macro is invoked, if configured, when the form and table downloads have completed successfully.

Dependent Layout Download

When a business user invokes a download for a set of dependent layouts, Oracle Visual Builder Add-in for Excel starts with the primary layout and then progresses through the hierarchy.

The download process for a set of dependent layouts is similar to the other processes with these differences:

  • When a download is invoked on any layout in a set of dependent layouts, the download process applies to all the layouts in the set.
  • A download always starts with the primary layout even if a different worksheet is currently active.
  • The check for pending changes applies to all layouts in the hierarchy.
  • The entire hierarchy is cleared of current data.
  • Query prompts are only available for the primary layout.
  • Additional search parameters, if configured, are applied at each respective level. See Filter Data for a Set of Dependent Layouts.
  • The prompt to continue after the first page appears once only for the primary layout.

    If the parent layout is a Form-over-Table layout, this prompt applies to the child table. There is no prompt for descendant layouts.

  • Each layout in the hierarchy uses the specific pagination settings, such as Limit and Offset, from their respective business objects. See Configure Pagination for a Business Object.

Descendant Table Layout Download

For descendant table layouts, the rows the add-in downloads depend on the rows in the direct parent table layout. For each row in the direct parent table layout, the add-in retrieves and processes all relevant child rows. If there are search parameters defined on the descendant table layout, the search criteria may limit the number of child rows retrieved for each parent row. See Filter Data for a Set of Dependent Layouts.

By default, the add-in sends separate GET requests to retrieve descendant rows. So to retrieve child rows, the add-in starts by making a single GET request to retrieve the first page of child rows for a given parent.

Writing the child rows to the worksheet takes place in order: all child rows for parent row 1 are written, then the rows for parent 2, and so on.

If ancestor columns, including direct parent columns, are present in the descendant table, those cells are populated with the appropriate values. These cells are read-only.

Much like the standard download, the add-in processes the retrieved rows for the page, requests the next page in the background, and writes the page's rows to the worksheet. It keeps iterating over pages until all children for the parent row are retrieved and processed.

Note:

Up to 4 GET requests may be made on background threads, in parallel.

Instead of downloading with separate requests, you can configure the add-in download descendant rows in a single payload. See Configure Download to Use a Single Payload. In this scenario:

  1. The add-in makes one or more GET requests to retrieve the business object items for the table rows.

    The number of parent rows per request is determined by the value of the Limit Parameter Value field on the Download tab of the Business Object Editor. See Configure Pagination for a Business Object.

    The GET request is configured to include all descendant rows.

  2. The service sends a response payload that includes the set of parent rows for the table as well as all descendant items for those parent rows.
  3. The add-in writes the returned parent items data into the table and captures the descendant rows.
  4. The add-in repeats these steps until all parent items have been retrieved and written into the table.
  5. The add-in writes the descendant rows for or each descendant layout in the hierarchy to the respective table without making any more REST requests.

Configure Download to Use a Single Payload

If you have a set of dependent layouts, you can configure Oracle Visual Builder Add-in for Excel to download all relevant descendant rows in a single payload. Use of a single payload can significantly improve performance.

This feature is only available for ADF REST services.

If your primary layout is a Form-over-Table layout, the add-in first makes a single GET request to retrieve field values for the form part of the layout. It then makes requests to retrieve blocks of rows for the table part of the layout as well as all relevant rows for the dependent layouts.

If the primary layout is a Table layout, the add-in makes one or more GET requests to retrieve the business object items for the table rows. All descendent items for those parent rows are also included in the response payload.

Note:

This feature will not scale to handle large, enterprise-level volumes. If you enable this feature, test the download behavior to ensure that all required use cases will succeed in all customer environments and scenarios. Review Notes and Limitations of Single Payload Downloads for more information about scalability.

To enable this feature:

  1. Select the primary layout, then click Designer from the Oracle Visual Builder tab to open the Layout Designer.
  2. Click the Advanced tab in the Layout Designer.
  3. From the Advanced tab, expand Download Options, then select Retrieve Descendant Rows in Single Payload.
Notes and Limitations of Single Payload Downloads

Single payload downloads (also referred to as "nested" downloads) are only supported for Oracle ADF REST Resource services. Review this section to see if the feature is suitable for your integrated workbook.

Scalability

Be aware that this feature will not scale to handle large, enterprise-level volumes. If you enable this feature for your workbook, you are responsible for any scalability issues that arise. Make sure to test your workbook to verify that all required use cases will succeed in all customer environments and scenarios.

Use of CPU, memory, and network bandwidth resources could exceed acceptable levels, depending on the business object hierarchy and the data volume at each level in the hierarchy. Potential issues include:

  • Complex business logic or the processing of large numbers of nested descendant items could push the request duration beyond what some load balancers and other network components, such as Akamai, support. This could result in the request failing due to a timeout.
  • Large response payload sizes will likely consume large amounts of memory and CPU. This could potentially affect other clients working against the same endpoint, virtual machine, or container. It could also affect Excel and other programs running on the business user's computer.

Limitations

  • ADF REST services only
  • This feature is not compatible with the use of the fields search parameter.
  • The add-in uses the expand query parameter on the GET request for the top-most business object. The GET request includes all the descendant business objects (child, grandchild, and so on), by name for the expand query parameter value. The REST service implementation must support this.
  • In the current implementation, there are limitations on the number of descendants retrieved for a given parent row. The limit is imposed by the ADF REST framework for a given GET on the parent collection that uses the expand query parameter to include descendant items in the response payload.

    In cases where the number of descendants for a given parent item exceeds the limit (hasMore=true), the add-in displays a message in the Status Viewer that not all rows were downloaded for the affected worksheet.

Notes on Download Behavior

Review these notes for more on Oracle Visual Builder Add-in for Excel download behavior:

  • NetSuite services do not return full row details when responding to a GET request on the collection path. As a result, the add-in must issue individual GET requests on the item path for each item returned from the collection path.
  • For Oracle ADF REST Resource and Visual Builder Business Objects services, the add-in sends additional GET requests to the LOV data source to get rows with missing identity values only. For other service types, the add-in may get all rows in the LOV data source to fix the cache miss.

    If the add-in still can't find the display value for an identity value, the identity value is written to the worksheet without the swap.

  • REST services are stateless. As a result there are several issues to consider:
    • The query is re-executed for each page request
    • REST services cannot guarantee "read consistency" across multiple requests. If the data is changed during a multi-page download, it is possible for some rows to be missed entirely or downloaded more than once.

    Note:

    If pagination is not configured for a given business object, the add-in attempts to fetch and process all available rows in a single request. For larger volumes, such requests may time out.
  • If Send Only Changed Data During Upload is enabled, the add-in caches a copy of the data during download to be used for comparisons later on during upload.

For more information how some features affect the add-in's download behavior, see: