Configuring Excel Connector Sources

You can retrieve data from an Excel worksheet by using EXCEL Connector. The connector is a built-in connector that is available with Cloud Application Business Insights. This connector is based on JDBC connector. You can create manual data definitions by using SQL queries to retrieve data from an excel worksheet. A single manual data definition retrieves data from a single worksheet within a workbook. Queries to retrieve data from multiple worksheets are not supported. For each workbook, you need to create a separate connector source.

Before you begin

  • Ensure that a worksheet contains a single table. Only the first row of the first table is used as the header row. If you create multiple consecutive tables with separate headers, then all the tables are considered as a single table and data is retrieved for the entire table. If you create multiple tables that are segregated throughout the worksheet, then data is retrieved only from the table that starts at an earlier row.
  • Use only alphanumeric characters to name the workbooks, and copy the workbooks at the following location on the server where Cloud Application Business Insights is installed:

    <install_dir>/wlp/usr/servers/prdapp/excel/<file_name>

    Where, install_dir is Cloud Application Business Insights installation location. By default, it is /opt/icabi.
    Note:
    • You must save the Excel files at the mentioned location. If you change this location, then the file cannot be backed up or restored by using the default backup and restore scripts that are provided along with Cloud Application Business Insights.
    • Files that are saved on a remote server cannot be accessed.

Procedure

Complete the following steps to create an Excel connector source by using EXCEL Connector:

  1. Open Dashboard Designer.
  2. In the navigation pane, go to Connector & Sources > Connector Sources.
  3. On the All Connector Sources page, click Add Source.

    An Add Connector Source window is displayed.

  4. From the Connector Type list, select EXCEL Connector.
  5. In the Connector Source Name field, enter a name for the excel data source.

    Source name can contain alphanumeric characters only.

  6. In the Excel File Name field, enter the location where the Excel file is saved.
    For example, <install_dir>/wlp/usr/servers/prdapp/excel/<file_name>
    Where, <install_dir> is Cloud Application Business Insights installation location. By default, it is /opt/icabi.
    Note: Save the Excel file at the specified location. If you change this location, then the file cannot be backed up or restored by using the default backup and restore scripts that are provided along with Cloud Application Business Insights. See Application backup and restore.
  7. Click Next.
  8. In the Query field, enter a sample query for the Excel data source.

    For example, select * from sheet2

  9. To make sure that the connection to the data source is successful, click Test Connection.
    If the source details and the sample request are valid and complete, then a message that indicates that the connection is successful is displayed. For connection failure messages, you must fix the errors that are mentioned in the messages and test the connection again.
    Note:
    • Even if the source details are valid, a connection failure message might be displayed due to an invalid response received for the sample request.
    • If you enter a sample query that retrieves larger number of records, then even if the data source is configured correctly, Test Connection displays an error message, which indicates that the data source is not connected.

      To resolve this issue, you must enter a sample query, which returns limited number of rows and is faster in execution.

  10. Click Save.

Results

The All Connector Sources page displays the following details for each connector source:
  • Connector source name
  • Connector type
  • Date and time when the connector source was first created, and the username of the user who first created the source.
  • Date and time when the connector source was last modified, and the username of the user who last modified the connector source. Only the latest record is displayed.
The data source is added, and it can be used to create data definitions.