SpagoBI 5.2 REST DataSets with JSON Data

Introduction
SpagoBI 5.2 introduces a new dataset type : RESTful web service datasets . This allows SpagoBI to report on data from sources other than databases with JDBC drivers.
What follows is an end-to-end demonstration of creating and using a REST (JSON) Dataset in SpagoBI 5.2.
External documentation
Description | URL |
---|---|
SpagoBI 5.2 NGSI usage of REST Data Sets | https://spagobi.readthedocs.io/en/latest/user/NGSI/README/index.html |
SpagoBI 5.2 Documentation | https://spagobi.readthedocs.io/en/latest/index.html |
SpagoBI 5.2 Release Notes | https://wiki.spagobi.org/xwiki/bin/view/spagobi_server/release_notes_5_2 |
JSON Path | https://github.com/jayway/JsonPath |
Limitations in REST Data Sets
- The response of the REST service must be a JSON object.
- If the outermost JSON element is an array, it will not work.
- There is an assertion that checks that the RESTful call returns a JSON Object.
- According to SpagoBI support this is a security feature, not a bug.
- However, that means that not all RESTful web services can be used by the SpagoBI REST Dataset. If you’re designing the RESTful web service, that may be fine for you. If you’re trying to consume a pre-existing RESTful web service, this could be a problem.
- “The JSON response must return a flat data structure: SpagoBI cannot handle nested structures”, per SpagoBI Support. (Update 2016-06-22)
Still TBD – DataSet record number limitations
- How large or complex of a JSON response can SpagoBI handle?
- Per SpagoBI Support, they say they’ve successfully experimented REST Data Sets containing 40,000 records.
- Pending experimentation on my part.
- I previously attempted record sets with 100 (4MB data), 500 (22 MB data), 1000 (44 MB data), and 5000 records. The JSON was complex and most certainly not flat.
- I was able to retrieve the outermost, flat values correctly for the recordsets with 100 and 500 entries.
- My experimental recordsets failed processing in setting up the REST Data Set with 1000 or more records.
Example Implementation
Setting up the REST Data Set
Understand the structure of the response for the REST web service you want to use. Make sure it matches with the limitations above.
Example JSON response
{ "data" : [ {"id" : "1", "title" : "this is just a test"}, {"id" : "2", "title" : "second record"} ] } |
In SpagoBI 5.2 (or later)
- Go to Data Sets
- Define a new Data Set
- Set as follows
Click Preview. If everything worked right, you should get back the expected columns and rows.
Using the SpagoBI REST Data Set in a Report Document
Now, it is time to use the SpagoBI REST Data set in a Report Document. For the sake of ease, let’s create a BIRT report in SpagoBI Studio.
In report design view
- Go to the outline palette
- Create a new Data Source reference
- Data Source Type: select “SpagoBI Server Data Source”
- Provide the following details
- Server URL (Use the dns name or IP of your SpagoBI 5.2 (or later) server
- user name and password
- Create a Data Set
- Data Set Type: “SpagoBI Server Data Set”
- Data Set Name: example: “DS_Z_Rest_04 Data Set”
<a href="https://www.davidbharrison find more.com/wp-content/uploads/2016/06/image2016-6-16-11-42-37.png”> - Next
- Now, you’re free to use the data set in the report. In the example below, I’ve defined a 2 column table, associated with the data set. Each column is used.
- Let’s preview the report
- Deploy it to SpagoBI Server and run it
- Other tutorials I’ve provided go into detail on how to deploy and configure reports
- Run it in SpagoBI Server