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

  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
        image2016-6-16 11-34-29

image2016-6-16 11-30-34

 

Click Preview. If everything worked right, you should get back the expected columns and rows.

image2016-6-16 11-35-45

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
        image2016-6-16 11-39-50
      • 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”>image2016-6-16 11-42-37
        • 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
              image2016-6-16 11-45-8
          • Now, the output columns should show the columns defined in the data set
            image2016-6-16 11-46-28
  • 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.
    image2016-6-16 11-47-35
  • Let’s preview the report
    image2016-6-16 11-47-57
  • 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
      image2016-6-16 11-49-26
    • Generate a PDFimage2016-6-16 11-50-12

 

 

 

Comments |0|

Legend *) Required fields are marked
**) You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
In category: Uncategorized
Tagged with: , ,