How to create a new Document with Data Provider through RESTful API

[Former Member]

Hello,

I have a simple Document created through BI Web Intelligence that looks like this:

{"document":
  "id":2128049,"cuid":"ARjk4uobpgBAjSU6RQPkx.k","name":"Test1","folderId":2128036,
  "path":"Public Folders\/WISDK","updated":"2015-11-30T10:58:59.048+02:00",
  "scheduled":false,"state":"Unused","createdBy":"Administrator",
  "lastAuthor":"Administrator","size":25886,"refreshOnOpen":false}
}

It has one simple Data Provider:

{"dataprovider":
  {"id":"DP0","name":"Query 1","dataSourceId":5085,"dataSourcePrefix":"DS0",
  "dataSourceType":"unv","updated":"2015-11-30T10:58:20.000+02:00","duration":1,
  "isPartial":false,"rowCount":13,"flowCount":1,
  "dictionary":{"expression":[
    {"@qualification":"Dimension","@dataType":"String","id":"DP0.DOa6","name":"City",
      "description":"City located.","dataSourceObjectId":"DS0.DOa6","formulaLanguageId":"[City]"},
    {"@qualification":"Dimension","@dataType":"String","id":"DP0.DOda","name":"State",
      "description":"State located.","dataSourceObjectId":"DS0.DOda","formulaLanguageId":"[State]"}
  ]},
  "query":"SELECT Outlet_Lookup.State, Outlet_Lookup.City FROM Outlet_Lookup"}
}

I am trying to recreate a very similar document through RESTful API and can't seem to figure out how to add Expressions to Data Provider.

What I am doing now (note: newlines in request JSON are added for readability, they are not present in original requests):

1) Sending logon request, getting token.

2) Creating Document.

Request:
  POST /biprws/raylight/v1/documents HTTP/1.1
  {"document":{"name":"eFashion document","folderId":2128036}}
Response:
  HTTP/1.1 200 OK
  {"success":
    {"message":"The resource of type \"Document\" with identifier \"2151274\" has been successfully created.",
    "id":2151274}
  }

3) Adding Data Provider.

Request:
  POST /biprws/raylight/v1/documents/2151274/dataproviders HTTP/1.1
  {"dataprovider":{"name":"Query 1","dataSourceId":5085}}
Response:
  HTTP/1.1 200 OK
  {"success":
    {"message":"The resource of type \"Data provider\" with identifier \"DP0\" has been successfully created.",
    "id":"DP0"}
  }

4) Interesting part, adding Expressions to Data Provider.

Request:
  PUT /biprws/raylight/v1/documents/2151274/dataproviders/DP0 HTTP/1.1
  {"dataprovider":
    {"dictionary":
      {"expression":[
        {"name":"State","dataSourceObjectId":"DS0.DOda"},
        {"name":"City","dataSourceObjectId":"DS0.DOa6"}
      ]}
    }
  }
Result:
  HTTP/1.1 200 OK
  {"success":
    {"message":"The resource of type \"Data provider\" with identifier \"DP0\" has not been modified.",
    "id":"DP0"}
  }

And no matter what I try, I can't get it to add expressions. I have tried including other properties in expressions (different combinations with id, qualification, datatype), sending dataSourceId and id with and without DS0/DP0 prefixes and even including dictionary from step 4 into request in step 3. No matter what I try, the result is the same (in case of combining steps 3 and 4 - provider is created, but without expressions).

The Document and Data Provider I get in the end look like this:

{"document":
  {"id":2151274,"cuid":"ASrC12AtuRxNpCx50WOmX7A","name":"eFashion document","folderId":2128036,
  "path":"Public Folders\/WISDK","updated":"2016-01-28T10:01:46.759+02:00",
  "scheduled":false,"state":"Modified","createdBy":"username","lastAuthor":"username",
  "size":14423,"refreshOnOpen":false}
}

{"dataprovider":
  {"id":"DP0","name":"Query 1","dataSourceId":5085,"dataSourcePrefix":"DS0",
  "dataSourceType":"unv","duration":0,"isPartial":false,"rowCount":0}
}

And, what is interesting, after re-logging and getting documents provider list, my new Data Provider disappears:

{"dataproviders":""}

What am I doing wrong and what are the steps necessary to recreate my target document?

[Former Member · Accepted solution]

Thank you for your suggestions, but in the end it turned out that I wasn't creating data provider query properly. The correct way was to edit Query Specification.

So, the way it works now, is this:

Steps 1-3 are the same.

4) Update Query Specification

Request:

  PUT /biprws/raylight/v1/documents/2168171/dataproviders/DP0/specification HTTP/1.1

  <?xml version="1.0" encoding="UTF-8" standalone="no"?>
  <queryspec:QuerySpec xmlns:queryspec="http://com.sap.sl.queryspec" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     dataProviderId="DP0">

     <queryParameters>

     <duplicatedRowsProperty activated="true" value="true"/>

     <maxRetrievalTimeInSecondsProperty value="300"/>

     <maxRowsRetrievedProperty value="90000"/>

     <removeEmptyRowsProperty activated="true" value="true"/>

     <allowOtherUserToEditQueryProperty activated="true" value="true"/>

     <resetContextOnRefreshProperty activated="true" value="true"/>

     <stripQueryProperty/>

     <useBexQueryDefaultValue activated="true" value="true"/>

     </queryParameters>

     <queriesTree xsi:type="queryspec:QueryDataNode">

     <bOQuery identifier="8ef6c40e-fa03-4b18-af56-c6596e8a4a4f" name="Query">

     <resultObjects identifier="DS0.DOda" name="State"/>

     <resultObjects identifier="DS0.DOa6" name="City"/>

     </bOQuery>

     </queriesTree>

     <propertyBag key="DUPLICATED_ROWS_UNDEFINED_VALUE" value="-1"/>

     <propertyBag key="RESET_CONTEXT_ON_REFRESH_UNDEFINED_VALUE" value="-1"/>

     <propertyBag key="ALLOW_THE_USER_TO_EDIT_QUERY_UNDEFINED_VALUE" value="-1"/>

  </queryspec:QuerySpec>

Result:

  HTTP/1.1 200 OK

  {"success":

    {"message":"The resource of type \"Data provider\" with identifier \"DP0\" has been successfully updated.",

    "id":"DP0"}

  }

5) Save document

Request:

PUT /biprws/raylight/v1/documents/2168171 HTTP/1.1

{"document": {

  "name": "eFashion document",

  "folderId": 2128036
}}

Result:

  HTTP/1.1 200 OK

  {"success":

    {"message":"The resource of type \"Document\" with identifier \"2168171\" has been successfully updated.",

    "id":2168171}

  }

Resulting document has Data Provider that looks like this:

{

  "dataprovider": {

   "id": "DP0",

   "name": "Query 1",

   "dataSourceId": 5085,

   "dataSourcePrefix": "DS0",

   "dataSourceType": "unv",

   "duration": 0,

   "isPartial": false,

   "rowCount": 0,

   "flowCount": 1,

   "dictionary": {

   "expression": [

  {

   "@qualification": "Dimension",

   "@dataType": "String",

   "id": "DP0.DOa6",

   "name": "City",

   "description": "City located.",

   "dataSourceObjectId": "DS0.DOa6",

   "formulaLanguageId": "[City]"
   },

  {

   "@qualification": "Dimension",

   "@dataType": "String",

   "id": "DP0.DOda",

   "name": "State",

   "description": "State located.",

   "dataSourceObjectId": "DS0.DOda",

   "formulaLanguageId": "[State]"
   }

  ]

  },

   "query": "SELECT Outlet_Lookup.State, Outlet_Lookup.City FROM Outlet_Lookup"
  }

}

The document itself if you export it still isn't completely identical - the visual representation of a table is missing. From what I've gathered it can be created by adding required Report / Report Elements but I haven't tried it at this point since it isn't necessary for my project.

[Former Member]

Hello,

I think you have to perform an extra step for saving the document back to enterprise with the data provider expression that you mentioned in step 4.

Kindly try the following step after performing your Step 4.

Request Body:

URL: http://<server-name>:6405/biprws/raylight/v1/documents/{Your Webi document SI_ID}

Headers: Content-Type: application/xml

               X-SAP-LogonToken: "Your X-SAP-LogonToken"

Data: <EMPTY>

Your response after sending the above request should be like below:

Response Body:

Data:

<success>

<message>The resource of type "document" with identifier "{Your Webi document SI_ID}" has not been modified.</message>

<id>{Your Webi document SI_ID} </id>

</success>

You can also refer the Section 8.1.5: Saving a Document from REST Developer Guide, which gives you more detail about the above step.

Hope this helps.

Kindly try the above steps & share your outcomes, so that it would be helpful for other forums users (if they encountered the same issue).

Thanks,

Shailendra

[former_member197386]

Hello,

I think DP dictionary is not updatable (or only some specific attributes, like "high precision" flag).

To get expressions, I think you should simply refresh the document (or dataprovider).

Regards,

Anthony