David B. Harrison

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

Limitations in REST Data Sets

1. The response of the REST service must be a JSON object.
2. If the outermost JSON element is an array, it will not work.
   1. There is an assertion that checks that the RESTful call returns a JSON Object.
   2. According to SpagoBI support this is a security feature, not a bug.
   3. 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.
3. “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

1. How large or complex of a JSON response can SpagoBI handle?
   1. Per SpagoBI Support, they say they’ve successfully experimented REST Data Sets containing 40,000 records.
   2. Pending experimentation on my part.
      1. 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.
      2. I was able to retrieve the outermost, flat values correctly for the recordsets with 100 and 500 entries.
      3. 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
  • DataSet Type:
    • REST
  • Request Body:
    • if required
  • HTTP Method:
    • Post, Get, Put, or Delete.
    • In this example, use Get
  • JSON Path:
    • Must be a valid JSON Path expression
    • In this example,  we want all JSON objects in the data array
    • Use :  $.data[*]
  • JSON Path Attributes
    • See JSON Path reference
    • See screenshot below
      

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
        • Enter the Dataset Label (from SpagoBI).  It must be an exact match with the Data Set label we defined earlier, in SpagoBI server.
          • ex:  DS_Z_Rest_04
            
        • Now, the output columns should show the columns defined in the data set
          
• 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
  • Default view in SpagoBI execute document
    
  • Generate a PDF