SpagoBI DataSet REST Webservice via Groovy

SpagoBI DataSet REST Webservice via Groovy

Groovy Data Set to make RESTful web service call and return IP Address related information in SpagoBI’s XML format

Prerequisites :

  1. Upgrade Groovy in SpagoBI and Drop in a copy of the WSLite library
    1. See Upgrading Groovy in SpagoBI
    2. See SpagoBI DataSet SOAP Webservice via Groovy
  2. Restart SpagoBI

Create a new SpagoBI Data Set

DS-tab1

For the Type, Specify Script
For the Script Language, Specify Groovy
Create a parameter : param_ip,  of the type Generic
View the code section below and copy/paste that into your Script section.

DS-tab2-rev2

Groovy code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import wslite.rest.*
 
def param_ip = parameters['param_ip'] ?: ''
def client = new RESTClient("http://ip-api.com/json/${param_ip.toString()}")
def response = client.get()
 
assert response.statusCode == 200
def str = response.json
 
"""
<ROWS>
<ROW
 params="${parameters['param_ip'].toString()}" 
 status="${str.status.toString()}"
 country="${str.country.toString()}"
 countryCode="${str.countryCode.toString()}"
 region="${str.region.toString()}"
 regionName="${str.regionName.toString()}"
 city="${str.city.toString()}"
 zip="${str.zip.toString()}"
 lat="${str.lat.toString()}"
 lon="${str.lon.toString()}"
 timezone="${str.timezone.toString()}"
 isp="${str.isp.toString()}"
 org="${str.org.toString()}"
 as="${str.as.toString()}"
/>
</ROWS>
""".toString()

Press the Save Button

Next…

Now,  Switch to the Preview tab
Enter a Domain or IP address and press the Submit/OK button

ds-tab5a ds-tab5b

SpagoBI DataSet Webservice via Groovy

SpagoBI DataSet Webservice via Groovy

Note: I have not yet figured out how to get a SpagoBI Data Set (of the type Web Service) to work. However… I’ve figured out a way to make it work by creating a SpagoBI Data Set( type Script) and then using Groovy to make a web service call via Groovy WSLite library.

As part of the POC, I used the publicly available Holiday Web Service to look up Mother’s day for any given year. (Groovy WS-Lite uses it as the simple example).

SO… Round-Trip goes something like this

  1. Report is Requested from SpagoBI , given a parameter of “year”.
  2. SpagoBI executes the Document, delegating to the BIRT report engine.
  3. BIRT report engine executes the Report, passing in the parameter of year
  4. The Report has a SpagoBI Data set reference, which passes the year to the SpagoBI Data Set (back in SpagoBI Core)
  5. SpagoBI Data Set
    • Makes a SOAP call via Groovy and Groovy WS Lite library
    • Parses the response
    • Rewrites the response in the XML format standard for SpagoBI Data Sets, and includes the “requested year” and the response (date for Mother’s day)
    • The Report retrieves the data from the SpagoBI Data Set
    • Report renders showing the requested year and the date on which Mother’s Day occurs for that year

Dependencies:

Groovy WSLite 2.0
Groovy-based Web-Service library which supports SOAP and RESTful calls
Versions

  • 2.0 Beta . Requires Groovy 2.3 or later. Worked fine for SOAP. Failed for RESTful call
  • 1.0 / 0.7.1 : Requires Groovy 1.7.6 or later.  Worked fine for SOAP and REST

Groovy Language
We’re using Groovy WS Lite, required a newer version of Groovy than that which shipped with SpagoBI 5.0 (Groovy 1.5.6)
So, it was necessary to replace the Groovy-all library jar with a later version and add the Groovy WS Lite Jar
Download : groovy-all-2.4.3.jar

Steps

  • See instructions for Upgrading Groovy and Installing Groovy WS Lite
  • Log into Spagobi
  • Create a new data set
  • Create a new data set
  • Set the Data Set label
  • Set the Data Set Type (on the Type tab) to Script
  • Set the Language for the Script to Groovy
  • Setup a parameter named “param_year”, with the type of “Generic”
  • Add the script code (below)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import wslite.soap.*
 
 
def param_year = parameters['param_year']
 
// When SpagoBI Studio inquires with SpagoBI Server for the definition of the data set, 
// it will not work because it doesn't pass a default parameter
// Get around this by specifying a parameter value to use when none is provided.
 
if (param_year ==null) {
   param_year  = "2015"
}
 
def client = new wslite.soap.SOAPClient('http://www.holidaywebservice.com/Holidays/US/Dates/USHolidayDates.asmx')
def response = client.send(SOAPAction:'http://www.27seconds.com/Holidays/US/Dates/GetMothersDay') {
    body {
        GetMothersDay('xmlns':'http://www.27seconds.com/Holidays/US/Dates/') {
            year("${param_year.toInteger()}")
        }
    }
}
//assert "2011-05-08T00:00:00" == response.GetMothersDayResponse.GetMothersDayResult.text()
assert 200 == response.httpResponse.statusCode
assert "ASP.NET" == response.httpResponse.headers['X-Powered-By']
 
// Remember Java doesn't know how to interpret a Groovy GString. The resulting output must be cast to a Java String
// If you leave it as a GString preview in SpagoBI Server will work  but Studio will be unable to attach to the data set.
 
"""
 
 
 
""".toString()
  • Preview in SpagoBI (Server) providing a year. Press the preview button and you should get the response back from the Holiday Web Service
    Preview Groovy-WS Data Set Prompt

    Preview Groovy-WS Data Set Prompt

    SpagoBI Groovy-WS Preview Result

    SpagoBI Groovy-WS Preview Result

  • The next post will cover consuming that SpagoBI Data Set in a BIRT report

Upgrading Groovy in SpagoBI

Groovy Language

It is possible to create a SpagoBI Script (Groovy-based) Data Set which calls a web service and returns data in the standard XML format needed by consumers of SpagoBI Data Sets. As a proof of concept, I used Groovy-WS Lite (a Groovy SOAP/RESTful client library, which required a newer version of Groovy than that which shipped with SpagoBI 5.0 (Groovy 1.5.x).

So, it was necessary to replace the Groovy-all library jar with a later version and add the Groovy WS Lite Jar (version 2.0 beta was chosen)
Here is how I updated Groovy in SpagoBI core.

Download : groovy-binary-2.4.3.zip and extract the contents to get the file groovy-all-2.4.3.jar

Steps for updating Groovy version in SpagoBI

  1. In SpagoBI/WEB-INF\lib, replace the groovy-all-1.5.x.jar with groovy-all-2.4.3.jar
  2. Add the jar for Groovy-wslite, by dropping in a groovy-wslite jar ( groovy-wslite-2.0.0-20150603.161122-8.jar )
  3. Restart SpagoBI (ex: service tomcat7 restart)

So far, all normal SpagoBI functionality seems to work with the newer version of Groovy

SpagoBI report deployment via SDK

Below are my thoughts on approach, capabilities, and limitations on doing automated deployments of SpagoBI reports through the SDK. I welcome your comments and suggestions. If you’re doing something differently, please share.

As I now have 12 SpagoBI instances, keeping the reports in Sync on all environments has become a challenge. As a proof of concept, I’ve created a groovy script that can import an Exported-SpagoBI document to a list of SpagoBI servers. (Happy to share that if anyone is interested).

    Question to the world:

  • Is there an SDK call to “exportDocuments”, similar to the “importDocuments” method?
  • What does Engineering Group recommend as a best practice for 1) report revision control and 2) automatic deployment of reports to multiple SpagoBI servers?

SpagoBI report deployment via SDK
Approach

  1. Check out the report template from revision control into your SpagoBI project (in SpagoBI Studio)
  2. Make the necessary revisions and test them locally
  3. Deploy those revisions (via SpagoBI Studio) into Dev3
  4. Test the report revisions in SpagoBI
  5. Via SpagoBI Server web UI
  6. Via Web-site portals (if appropriate)
  7. In SpagoBI Server web UI
  8. Export screen
  9. Select the report to export
  10. Name the file for the export
  11. TODO: File Naming convention
  12. Export the report
  13. download the resulting zip file
  14. TODO: Add details – Capture the ZIP file and put it in Artifactory as a deliverable

When ready to deploy to the next SpagoBI Server

  1. TODO: Add details – Run the automatic deployment program, (via pipeline or manual run)
  2. Specify SpagoBI Server
  3. Specify the Exported zip file and location (from Artifactory)
  4. Specify the Associations map
  5. TODO: Add detail on Associations map and editing / creating
  6. TODO: Add detail – Capture the log
  7. Test the report revisions in SpagoBI instance
  8. Via SpagoBI Server web UI
  9. Via Web-site portals (if appropriate)
    SpagoBI API call

  • importDocuments : http://wiki.spagobi.org/xwiki/bin/view/spagobi_sdk/SDK_3_5_1_User_guide

Observations:

  1. Associations file can be used to provide exact mapping between Source and Target system. This takes care of all but one prompted value
  2. “Overwrite” can be specified on the API call. This should be true in the cases I can envision..

Limitations

  1. LOVs used by the report will be updated in the Target SpagoBI Instance.
  2. Be sure that the LOVs, as they are in the source tenant, are what you want to move forward.
  3. The Deployment zip does not contain a mapping of secondary Functional Hierarchy folders where the report should be accessible.
  4. Consequences:
  5. If you delete the report in the target system, before import, you’ll need to manually add the hierarchy folder permissions back in the Target SpagoBI web UI
  6. If you deploy report for the first time in the target system, you’ll need to to manually add the hierarchy folder permissions back in the Target SpagoBI web UI