Adding a Data Provider

Usage

Adds a new data provider to a Web Intelligence document.

You can add:

  • A universe (unv or unx)
  • A BEx query
  • A Microsoft Excel 2003 or Microsoft Excel 2007 file
  • A free-hand SQL script
Request

POST /documents/<documentID>/dataproviders

Request body:

<dataprovider>
    <name>
    <dataSourceId>

Where:

  • <name> is the data source name
  • <dataSourceId> is the data source identifier
Request Body for a Microsoft Excel File

In the case of a Microsoft Excel file, you may add the following <property> elements to provide supplementary details on the data source.

<properties>
    <property key="selectedSheet">
    <property key="rangeSelectionMode">
    <property key="firstRowAsObjectNames">
    <property key="selectedRange">
Table 1: Properties
Property Key Description
selectedSheet Specifies the name of the sheet to be used as data provider. The first sheet is used if the property is not set.
rangeSelectionMode Can be:
  • all, if the whole sheet is selected
  • named, if one of the named cell ranges is selected
  • freehand, if the end-user selects the cell range
firstRowAsObjectNames Boolean. Default is true. If true, the first row of the sheet contains column names.
selectedRange Do not use it if rangeSelectionMode is set to all. Specify one of the named cell ranges if any, or use the end-user input.
Note The present release does not suppot fragmented named ranges.
Request body for a Free-Hand SQL Script
In the case of a free-hand SQL script, the request body must contain a SQL statement as <property>:
<dataprovider>
    <name>
    <dataSourceId>
    <properties>
        <property key="sql">

All SQL statements conform to standards ANSI-SQL 89/92/98 are supported. For example, SET options, stored procedures, and WITH clauses are supported. The @Variable and @Prompt functions for interactive queries are also supported. A SQL script containing DDL commands such as DROP TABLE or ALTER TABLE is not executed. Multiflow SQL statements are not supported. The query will return the first result only.

You may also add the following <property> elements to provide supplementary details on the desired results:

<properties>
    <property key="maxRows">
    <property key="timeout">
Table 2: Properties
Property Key Description
maxRows Specifies the maximum rows retrieved. If not set or value is -1, the property is disabled.
timeout Specifies the maximum retrieval time (in seconds). If not set or value is -1, the property is disabled.
Response

Response type: application/xml or application/json

The response is a message stating the success or failure of the request.

Example

POST /documents/3422/dataproviders

Request body for a BEx query:

<dataprovider>
    <name>Query1</name>
    <dataSourceId>11990;Z_BOBJ;AAQUERY_SAMPLE</dataSourceId>
<dataprovider>

Request body for a Microsoft Excel file:

<dataprovider>
    <name>My Excel data provider</name>
    <dataSourceId>6641</dataSourceId>
    <properties>
        <property key="selectedSheet">mysheet</property>
        <property key="rangeSelectionMode">freehand</property>
        <property key="firstRowAsObjectNames">true</property>
        <property key="selectedRange">A2:F7</property>
    </properties>
<dataprovider>

Request body for a free-hand SQL script:

<dataprovider>
    <name>FHSQL dp on beachOraJdbc</name>
    <dataSourceId>6250</dataSourceId>
    <properties>
        <property key="sql">SELECT * from country</property>
        <property key='maxRows'>286</property>
        <property key='timeout'>857</property>
    </properties>
</dataprovider>

Response:

<success>
    <message>The resource of type "Data provider" with identifier "DP3" has been successfully created.</message>
    <id>DP3</id>
</success>