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

Using Groovy to Deploy Federated (or Linked Server) views

I have several federated server / linked views in SQL Server 2000, which allows querying across multiple servers and databases simultaneously.

While very powerful, there are some limitations.

The primary database server has some linked servers defined. The primary database has a view defined in it which points to a specific linked server and linked database. Unfortunately, Linked Server aliases are not supported and the linked database name varies between environments.

Because the View has the Linked Server and Database Name hard coded into the view and they vary per environment, it breaks the federated view in the target database when a non-production server is refreshed from production.

Enter some Groovy, JDBC, and GStringTemplateEngine to solve the problem.

I use a map of database settings per environment. Then, the view can be recreated in a matter of seconds with the correct settings in each environment.
Below is an example of using Groovy, JDBC (JTDS), and Groovy GString Template Engine to re-created Federated / Linked Server views in Microsoft SQL Server.

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
@Grapes(
    @Grab(group='net.sourceforge.jtds', module='jtds', version='1.3.1')
)
@GrabConfig(systemClassLoader=true)
 
import groovy.sql.Sql
import static java.sql.ResultSet.*
import groovy.text.GStringTemplateEngine as Engine
 
def dropView(name) {
    println "dropView ${name}"
    try {
          res = sql.execute("DROP VIEW [dbo].["+name.toString()+"]")
          println "Drop result : ${res}"
    }
    catch (Exception e) {
          println "Exception on dropping view : ${e}"
    }
}
 
def getViewFromTemplate(viewName) {
    println "getViewFromTemplate ${viewName}"
    viewText = new File("./${viewName}.sql").text
    def e = new Engine()
    curTemplate = e.createTemplate(viewText).make(dbMap[env])
    return curTemplate.toString()
}
 
def createViewFromTemplate(viewName) {
    println "createViewFromTemplate ${viewName}"
    viewText = getViewFromTemplate(viewName)
    dropView(viewName)
    //println viewText
    println "createViewFromTemplate creating view ${viewName}"
    res = sql.execute(viewText)
    println "create stored proc : ${res}\n"
}
 
env = 'prd1' 
 
dbMap = [:]
dbMap['prod'] = [ db1_server:"proddb.internal.myOrg", db1_db:"appdb",     linked_db:"app2db_reporting_SNAP", linked_server:"app2ReportingDB.internal.myOrg" ]
dbMap['qa1']  = [ db1_server:"qa1db.internal.myOrg",  db1_db:"appdb_qa1", linked_db:"app2db_qa1",            linked_server:"app2NonProd.internal.myOrg" ]
dbMap['dev'] =  [ db1_server:"devdb.internal.myOrg",  db1_db:"appdb_dev", linked_db:"app2db_dev",            linked_server:"app2NonProd.internal.myOrg" ]
 
 
['qa1','dev'].each() { env -> 
    println "---------------------"
    println "Processing for ${env}"
    println "---------------------"   
    db1_server = dbMap[env]['db1_server']
    db1_db = dbMap[env]['db1_db']
    linked_db = dbMap[env]['linked_db']
    linked_server = dbMap[env]['linked_server']
 
    def jdbcURL = "jdbc:jtds:sqlserver://${db1_server}/${db1_db}"
    def user = 'YOUR_ADMIN_USER'
    def pw = 'YOUR_ADMIN_PASSWORD'
    sql = Sql.newInstance( jdbcURL, user, pw, 'net.sourceforge.jtds.jdbc.Driver' )
    sql.execute("SET ANSI_NULLS ON")
    sql.execute("SET QUOTED_IDENTIFIER ON")
 
    createViewFromTemplate('FederatedView1', dbMap[env])
    createViewFromTemplate('FederatedView2', dbMap[env])
}

SpagoBI Groovy LOV for Last Day of Last Month

SpagoBI Lookup Value (LOV) in Groovy to get the last day of last month

1
2
3
4
5
6
7
8
9
10
11
12
Calendar cal = Calendar.getInstance()
cal.add(Calendar.MONTH, -1)
cal.set(Calendar.DATE, 1)
Date firstDateOfPreviousMonth = cal.getTime()
cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE))
Date prevDate = cal.getTime()
 
//You pick whether you like sprintf or GString better
//String returnStr = "${(cal.get(Calendar.MONTH)+1).toString().padLeft(2,'0')}/${(cal.get(Calendar.DATE)).toString().padLeft(2,'0')}/${cal.get(Calendar.YEAR)}"
//return returnStr.toString()
String returnStr = sprintf("%02d/%02d/%4d", cal.get(Calendar.MONTH)+1, cal.get(Calendar.DATE), cal.get(Calendar.YEAR))
return returnStr

Getting the first day of the last month for a SpagoBI LOV default date value is slightly easier

1
2
3
4
5
6
7
8
9
10
Calendar cal = Calendar.getInstance()
cal.add(Calendar.MONTH, -1)
cal.set(Calendar.DATE, 1)
Date firstDateOfPreviousMonth = cal.getTime()
cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE))
Date prevDate = cal.getTime()
 
 
String returnStr = sprintf("%02d/01/%4d", cal.get(Calendar.MONTH)+1, cal.get(Calendar.YEAR))
return returnStr

1-lov

2-lov-detail

3-ad-detail

4-report-param

5-report-param-prompt