Updating a Data Provider

Usage

Purges or updates a data provider.

The following table shows what you can update in the particular cases of a data provider based on a Microsoft Excel spreadsheet or a free-hand SQL script.

Data Provider Possible Updates
Microsoft Excel spreadsheet
  • Properties
  • Dictionary expression details:
    • Data types
    • Qualification
    • Name
    • Aggregation functions for measures
    • Associated dimensions for attributes
Free-hand SQL script
  • Properties
  • Connections
  • SQL script
  • Dictionary expression details:
    • Qualification
    • Name
    • Aggregation functions for measures
    • Associated dimensions for attributes
Note
  • The connection and SLQ script can be changed through either one request or two distinct requests.
  • Changes to <dataSourceObjectId> and <formulaLanguageId> are ignored during update.
  • The SDK handles missing result objects, duplicate expression identifier, duplicate name, unknown expression identifier, and modified object order.
Request

PUT /documents/<documentID>/dataproviders/<dataProviderID>?purge=<purge>&purgeOptions=<purgeOptions>

Where:

  • <purge> (optional) indicates whether to purge the data provider or not. Default is false.
  • <purgeOptions> (optional) is prompts to control the purge operation. If not set or empty, only the data provider will be purged.
Note An error occurs when purge=false and purgeOptions are both specified in the call.

The request body is optional.

<dataprovider>
    <property key="selectedSheet">
    <property key="rangeSelectionMode">
    <property key="firstRowAsObjectNames">
    <property key="selectedRange">

You can also add the isRefreshable property to the request body to make the data provider refreshable:

<dataprovider>
    <property key="isRefreshable">true|false</property>

The isRefreshable property value has an impact on the purge operation. If this property is false on the Web Intelligence server, the purge is not performed even if <purge> is set to true.

<dataprovider>
    <property key="maxRows">
    <property key="timeout">
<dataprovider>
    <dataSourceId>
    <properties>
        <property key="sql">

<dataSourceId> indicates the connection ID.

Connection and SQL script can be updated in separate requests.

Errors are thrown if:

  • Connection and SQL script are not compatible (HTTP error code 400, WSR 00102).
  • The end-user is not allowed to edit the SQL script (HTTP error code 401, WSR 00402).
Response

Response type: application/xml or application/json

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

Example

To Purge a Data Provider, but not Prompts

PUT /documents/7738/dataproviders/DP0?purge=true

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

Example

To Purge a Data Provider and Prompts

PUT /documents/7738/dataproviders/DP0?purge=true&purgeOptions=prompts

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

Example

To Rename a Data Provider

PUT /documents/7738/dataproviders/DP0

Request body in an XML file:

<dataprovider>
    <name>My Renamed Data Provider</name>
</dataprovider>

Response:

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

Example

To Modify the Free-Hand SQL Data Provider Properties

PUT /documents/8229/dataproviders/DP0

Request body in an XML file:

<dataprovider>
    <property key="maxRows">928</property>
    <property key="timeout">101</property>
</dataprovider>

Response:

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

Example

To Change the Connection and SLQ Script of a Free-Hand SQL Data Provider

PUT /documents/8229/dataproviders/DP0

Request body:

<dataprovider>
    <dataSourceId>6340</dataSourceId>
    <properties>
        <property key="sql">SELECT DISTINCT SALES.INVOICE_DATE FROM SALES</property>
    </properties>
</dataprovider>

Response:

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

Example

To Update the Object Dictionary of a Data Provider Based on a Microsoft Excel Spreadsheet

A first call allows you to get the data provider details:

GET /documents/11837/dataproviders/DP0

Response:

<dataprovider> 
    <id>DP0</id>  
    <name>named cell</name>  
    <dataSourceId>11835</dataSourceId>  
    <dataSourceType>excel</dataSourceType>  
    <duration>0</duration>  
    <isPartial>false</isPartial>  
    <rowCount>0</rowCount>  
    <flowCount>1</flowCount>  
    <dictionary> 
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO0</id>  
            <name>OrderDate</name>  
            <dataSourceObjectId>DS0.DO0</dataSourceObjectId>  
            <formulaLanguageId>[OrderDate]</formulaLanguageId> 
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO1</id>  
            <name>Region</name>  
            <dataSourceObjectId>DS0.DO1</dataSourceObjectId>  
            <formulaLanguageId>[Region]</formulaLanguageId> 
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO2</id>  
            <name>Rep</name>  
            <dataSourceObjectId>DS0.DO2</dataSourceObjectId>  
            <formulaLanguageId>[Rep]</formulaLanguageId> 
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO3</id>  
            <name>Item</name>  
            <dataSourceObjectId>DS0.DO3</dataSourceObjectId>  
            <formulaLanguageId>[Item]</formulaLanguageId> 
        </expression>  
        <expression dataType="Numeric" qualification="Measure"> 
            <id>DP0.DO4</id>  
            <name>Units</name>  
            <dataSourceObjectId>DS0.DO4</dataSourceObjectId>  
            <formulaLanguageId>[Units]</formulaLanguageId>  
            <aggregationFunction>Sum</aggregationFunction> 
        </expression>  
        <expression dataType="Numeric" qualification="Measure"> 
            <id>DP0.DO5</id>  
            <name>Cost</name>  
            <dataSourceObjectId>DS0.DO5</dataSourceObjectId>  
            <formulaLanguageId>[Cost]</formulaLanguageId>  
            <aggregationFunction>Sum</aggregationFunction> 
        </expression> 
    </dictionary>  
    <properties> 
        <property key="selectedSheet">Sheet1</property>  
        <property key="rangeSelectionMode">freehand</property>  
        <property key="selectedRange">A1:F9</property>  
        <property key="firstRowAsObjectNames">true</property>  
        <property key="isRefreshable">true</property> 
    </properties> 
</dataprovider>

A second call allows you to change:

  • The names of DP0.DO0, DP0.DO1, DP0.DO2, DP0.DO3, DP0.DO4, and DP0.DO5.
  • The qualification of DP0.DO2 and DP0.DO5
  • The associated dimension of DP0.DO2
  • The aggregation function of DP0.DO4

PUT /documents/11837/dataproviders/DP0

Request body:

<dataprovider> 
    <dictionary> 
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO0</id>  
            <name>OrderDate1</name>  
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO1</id>  
            <name>Region1</name>  
        </expression>  
        <expression dataType="String" qualification="Attribute"> 
            <id>DP0.DO2</id>  
            <name>Rep1</name>  
            <associatedDimensionId>DP0.DO1</associatedDimensionId> 
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO3</id>  
            <name>Item1</name>  
        </expression>  
        <expression dataType="Numeric" qualification="Measure"> 
            <id>DP0.DO4</id>  
            <name>Average</name>  
            <aggregationFunction>Average</aggregationFunction> 
        </expression>  
        <expression dataType="Numeric" qualification="Dimension"> 
            <id>DP0.DO5</id>  
            <name>Cost1</name>  
        </expression> 
    </dictionary>  
</dataprovider>

Response:

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

In a third call, you check the dictionary is updated. <formulaLanguageId> have changed automatically depending on the <name> change.

GET /documents/11837/dataproviders/DP0

Response:

<dataprovider> 
    <id>DP0</id>  
    <name>named cell</name>  
    <dataSourceId>11835</dataSourceId>  
    <dataSourceType>excel</dataSourceType>  
    <duration>0</duration>  
    <isPartial>false</isPartial>  
    <rowCount>0</rowCount>  
    <flowCount>1</flowCount>  
    <dictionary> 
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO0</id>  
            <name>OrderDate1</name>  
            <dataSourceObjectId>DS0.DO0</dataSourceObjectId>  
            <formulaLanguageId>[OrderDate1]</formulaLanguageId> 
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO1</id>  
            <name>Region1</name>  
            <dataSourceObjectId>DS0.DO1</dataSourceObjectId>  
            <formulaLanguageId>[Region1]</formulaLanguageId> 
        </expression>  
        <expression dataType="String" qualification="Attribute"> 
            <id>DP0.DO2</id>  
            <name>Rep1</name>  
            <dataSourceObjectId>DS0.DO2</dataSourceObjectId>  
            <formulaLanguageId>[Rep1]</formulaLanguageId>  
            <associatedDimensionId>DP0.DO1</associatedDimensionId> 
        </expression>  
        <expression dataType="String" qualification="Dimension"> 
            <id>DP0.DO3</id>  
            <name>Item1</name>  
            <dataSourceObjectId>DS0.DO3</dataSourceObjectId>  
            <formulaLanguageId>[Item1]</formulaLanguageId> 
        </expression>  
        <expression dataType="Numeric" qualification="Measure"> 
            <id>DP0.DO4</id>  
            <name>Average1</name>  
            <dataSourceObjectId>DS0.DO4</dataSourceObjectId>  
            <formulaLanguageId>[Average1]</formulaLanguageId>  
            <aggregationFunction>Average</aggregationFunction> 
        </expression>  
        <expression dataType="Numeric" qualification="Dimension"> 
            <id>DP0.DO5</id>  
            <name>Cost1</name>  
            <dataSourceObjectId>DS0.DO5</dataSourceObjectId>  
            <formulaLanguageId>[Cost1]</formulaLanguageId> 
        </expression> 
    </dictionary>  
    <properties> 
        <property key="selectedSheet">Sheet1</property>  
        <property key="rangeSelectionMode">freehand</property>  
        <property key="selectedRange">A1:F9</property>  
        <property key="firstRowAsObjectNames">true</property>  
        <property key="isRefreshable">true</property> 
    </properties> 
</dataprovider>