I recently did a proof of concept experiment to publish reports directly from the BI Platform to my Google drive.  The idea is to write a small publication extension with the BI Platform Java SDK and Google Data API (GData).  I thought it might be useful for other people who are seeking for similar solutions, I am sharing my experience and code here.  The blog first has a demo walkthrough with a lot of screenshots, followed by code snippets explaining each step, and then some ideas and references.

This code requires SAP BusinessObjects BI 4, Edge Edition or SAP BusinessObjects Enterprise 4.  Any patch level should work, although I tested this with 4.1.

Quick Jump

Demo Walkthrough

Note: This demo requires a BI 4.x system, a Google account, and Internet connection from the BI system to Google (e.g. proxy settings). It has been successfully tested against BI 4.1 and gdata-src.java-1.47.1.


Step 1 – Deploy demo publication extension
  • Grab ppexampl.jar and Publish2Google.properties from here and drop to i.e. <BOE Installdir>/SAP BusinessObjects Enterprise XI 4.0/java/lib/publishingPlugins.
  • Create a lib sub folder under the publishingPlugins folder. Grab GData jars from here and drop gdata/java/lib/* and gdata/java/deps/* to the lib sub folder created.

>set publishingPluginsHome=C:\boe\SAP BusinessObjects Enterprise XI 4.0\java\lib\publishingPlugins

>copy C:\workspace\ppexampl.jar “%publishingPluginsHome%”

>copy C:\workspace\Publish2Google.properties “%publishingPluginsHome%”

>mkdir “%publishingPluginsHome%\lib”

>copy C:\workspace\gdata-src.java-1.47.1\gdata\java\lib\* “%publishingPluginsHome%\lib”

>copy C:\workspace\gdata-src.java-1.47.1\gdata\java\deps\* “%publishingPluginsHome%\lib”

Step 2 – Adjust settings in the .properties file
  • The Publish2Google.properties file contains logging and proxy configurations. Make sure the logdir exists and proxy settings are valid for this demo to work.

# Logging Settings  

logdir=C:/   

# Proxy Settings

# To access Google  

proxySet=true  

proxyHost=proxy  

proxyPort=8080


Step 3 – Restart SIA server
  • πŸ˜‰ Some other blogs mentioned restarting the Adaptive Processing Server should be fine, but I found it is not enough for me.  Instead, restart the SIA server to ensure it takes the demo publication extension.


Step 4 – Create publications
  • This could be done in either BI LaunchPad or CMC, except that only CMC allows to assign a publication extension.

/wp-content/uploads/2013/09/pe01_278171.png

  • Enter a title for the publication

/wp-content/uploads/2013/09/pe02_278172.png

  • Add Source Documents
    • Add some WebI and Word/Excel/PPT/Pdf reports.
    • πŸ˜‰ The publication doesn’t allow you to mix Webi and CR reports.  We will have to create another publication to test CR reports.

/wp-content/uploads/2013/09/pe03_278173.png

  • Add Enterprise Receipient
    • πŸ˜‰ At least one recipient is required for publication extension to work.

/wp-content/uploads/2013/09/pe04_278177.png

  • Add Destinations
    • πŸ˜‰ At least one of BI Inbox, Email, FTP Server, File System is required for publication extension to work. Default Enterprise Location alone doesn’t trigger publication extension.
    • πŸ˜‰ Default Enterprise Location refers to the history page of the publication from where one can click to view the instance.

/wp-content/uploads/2013/09/pe05_278178.png

  • Choose Formats
    • Supported output format for Webi by this demo:  Web Intelligence (convert to CSV), Excel, PDF
    • Supported output format for CR by this demo: Excel, PDF, RTF, TXT, CSV
    • When specifying Web Intelligence as the output format, this demo will convert it to CSV file
    • For future: XML, mHTML, CR as output format

/wp-content/uploads/2013/09/pe06_278179.png

  • (Only available in CMC) Assign a Publication Extension
    • The Publication Extension Name could be anything you like.
    • The Class Name is com.businessobjects.publishing.processing.plugin.example.Publish2Google for this demo
    • You must provide Parameter with a valid Google account user name and password (with no spaces and comma) separated by a comma.

/wp-content/uploads/2013/09/pe08_278184.png

  • Click the Add button above Before Publication Delivery
  • Save & Close the publication
  • Now you can follow the same to create another publication to test CR reports. Below illustrates the Formats selection for CR reports
    • πŸ˜‰ When converting CR to CSV, to avoid duplication of report/page/group sections on each row, consider to choose Do not export or Isolate report/page sections or Isolate group sections.

/wp-content/uploads/2013/09/pe07_278183.png

Step 5 – Run the publications
  • Right click each publication created, choose Run Now or Schedule (Now)


Step 6 – Check the history page and the log files
  • Be patient to wait for a while (e.g. a minute depends on the machine speed).
  • To check the history page, right click the publication, choose History.
  • To view details, you can check the log files (e.g. Publication Post Processing 1378929374504.log) in the folder specified in the properties file.
  • Each run will generate two log files – one for getTargetDestinations() and one for handle().


Step 7 – OK, now it is show time. 
  • Check out the published reports in Google drive either from a desktop or a mobile app. To save words, let me put some screenshots here:

/wp-content/uploads/2013/09/pe12_278192.png/wp-content/uploads/2013/09/pe14_278195.png/wp-content/uploads/2013/09/pe15_278196.png

/wp-content/uploads/2013/09/pe16_278197.png/wp-content/uploads/2013/09/pe09_278198.png/wp-content/uploads/2013/09/pe10_278199.png/wp-content/uploads/2013/09/pe11_278200.png


Code Explained

The full source code can be found here.

There are two java files for this demo program, Publish2Google.java and Publish2GoogleImpl.java.

Publish2Google is like a shell program, implementing the IPublicationPostProcessingPlugin interface.  It reads .properties files, gets the option string from the publication extension setup UI, generates BI logonToken, iterates each publication instance by launching Publish2GoogleImpl in a new JVM with proxy information, classpath, BI logonToken, and other parameters.

Publish2GoogleImpl is where the real publishing work gets done.  It either converts or copies the instance to the desired format based on the kind, then uploads to Google drive.

πŸ˜‰ There are several good reasons to have this two tier architecture – i.e. to launch a separate JVM to do the real work:

  • Class path consideration – because the publication extension will run in the same JVM as the host service (Adaptive Processing Server), the dependent jar files put together with the publication extension sometimes conflicts with the ones used by the host service.  Sometime it is quite hard to workaround this, and sometimes this even disable the host service from running.
  • It is safe to put dependent jar files into a sub lib folder which is not visible to the host service. Otherwise, the host service even couldn’t be started if you put a conflicting depend jar file.
  • It is more efficient for the development.  I can test the Publish2GoogleImpl part without restarting the SIA (which is painful).

Publish2Google.java


getTargetDestinations
public Collection getTargetDestinations() throws Exception {
  configProperties();

  PluginTargetDestination inboxDestination =
     new PluginTargetDestination(CeProgID.MANAGED_DEST,
     IDestinationPluginArtifactFormat.CeDistributionMode.FILTER_EXCLUDE_SOURCE_DOCUMENTS);

  PluginTargetDestination smtpDestination =
     new PluginTargetDestination(CeProgID.SMTP,
     IDestinationPluginArtifactFormat.CeDistributionMode.FILTER_EXCLUDE_SOURCE_DOCUMENTS);

  PluginTargetDestination diskDestination =
     new PluginTargetDestination(CeProgID.DISKUNMANAGED,
     IDestinationPluginArtifactFormat.CeDistributionMode.FILTER_EXCLUDE_SOURCE_DOCUMENTS);

  PluginTargetDestination ftpDestination =
     new PluginTargetDestination(CeProgID.FTP,
     IDestinationPluginArtifactFormat.CeDistributionMode.FILTER_EXCLUDE_SOURCE_DOCUMENTS);

  PluginTargetDestination[] destinations = {
    inboxDestination, smtpDestination, diskDestination, ftpDestination };

  return Arrays.asList(destinations);
}
The return of this function is to declare which target destinations the following handle() function will be triggered.  Please note Default Enterprise Location is not an option.  In this demo, any of the inbox, smtp, disk, ftp will trigger below handle() function.
handle

public IInfoObjects handle(IPublicationPostProcessingContext context)

     throws Exception {      configProperties();           IInfoStore m_infoStore = context.getInfoStore();      IInfoObjects objs = m_infoStore.newInfoObjectCollection();           // get Google account settings from context option string entered in CMC      //   format:  username,password  (assuming no ',' is allowed

     //   in username and password)      String username = "username";      String password = "password";      String optionstr = context.getOptions() == null ? "" :

         context.getOptions().toString();      if (optionstr.contains(",")) {           String[] options = optionstr.split(",");           username = options[0];           password = options[1];      }      IEnterpriseSession session = context.getEnterpriseSession();      String defaultToken = "";      try {           ILogonTokenMgr logonTokenMgr = session.getLogonTokenMgr();           defaultToken = logonTokenMgr.getDefaultToken();      } catch (SDKException e1) {           e1.printStackTrace();      }      ArrayList docs = context.getDocuments();      Iterator docIt = docs.iterator();      while (docIt.hasNext()) {                   IInfoObject obj = (IInfoObject)docIt.next();           String objTitle = obj.getTitle();           int objId = obj.getID();           objTitle = objTitle.replace(":", "-");           Process processRun = null;           try {                String proxypath = proxySet.toLowerCase().equals("true") ?                     "-DproxySet=true -DproxyHost="+proxyHost+

                   " -DproxyPort="+proxyPort : "";                String classpath = getSDKLibPath()+"*;"+getSDKLibPath()+                     "publishingPlugins/*;"+getSDKLibPath()+"publishingPlugins/lib/*";                String cmd = "java "+ proxypath + " -classpath \""+classpath+"\"" +

" com.businessobjects.publishing.processing.plugin.example.Publish2GoogleImpl \"";                String cmdlog = cmd+"******"+"\" \""+"******"+"\" \""+objTitle                     +"\" "+"**********"+" "+objId;                cmd           = cmd+username+"\" \""+password+"\" \""+objTitle                     +"\" "+defaultToken+" "+objId;                processRun = Runtime.getRuntime().exec(cmd);           } catch (IOException e) {                e.printStackTrace();           }           try {                printLines(" stdout:", processRun.getInputStream());                printLines(" stderr:", processRun.getErrorStream());           } catch (Exception ex) {           }           objs.add(obj);      }   return objs; }

Function handle() is the central hub of the publication extension. It reads configurations from .properties files, gets the option string from the publication extension setup UI, generates BI logonToken, iterates each publication instance by launching Publish2GoogleImpl in a new JVM with proxy information, classpath, BI logonToken, and other parameters.

Publish2GoogleImpl.java

main

public static void main(String [] args) {

     ...

     try {           log("logging into enterprise session...");           session = CrystalEnterprise.getSessionMgr().

              logonWithToken(logonToken);           log("logged in enterprise session");                     IInfoStore infoStore = (IInfoStore) session.getService("InfoStore");           IInfoObjects objs=infoStore.query(

               "SELECT * FROM CI_INFOOBJECTS WHERE SI_ID="+objID);           IInfoObject obj = (IInfoObject)objs.get(0);           String kind = obj.getKind();           log("kind="+kind);                     if (kind.equals("Webi")) {                exportWebiReport2CsvFile(session, objID, tmpdir, title);                uploadFile2Google(username, password, title, tmpdir+title+".csv");           } else if (kind.equals("CrystalReport")) {                log("no action");           } else if (kind.equals("Pdf")) {                String ofile = tmpdir+title+".pdf";                exportPdfFile(logonToken, obj, ofile);                uploadFile2GoogleResumable(username, password, title, ofile);           } else if (kind.equals("Powerpoint")) {                String ofile = tmpdir+title+".ppt";                exportPptFile(logonToken, obj, ofile);                uploadFile2GoogleResumable(username, password, title, ofile);           } else if (kind.equals("Word")) {                String ofile = tmpdir+title+".doc";                exportWordFile(logonToken, obj, ofile);                uploadFile2GoogleResumable(username, password, title, ofile);           } else if (kind.equals("Excel")) {                String ofile = tmpdir+title+".xls";                exportExcelFile(logonToken, obj, ofile);                uploadFile2GoogleResumable(username, password, title, ofile);           } else if (kind.equals("Rtf")) {                String ofile = tmpdir+title+".rtf";                exportRtfFile(logonToken, obj, ofile);                uploadFile2GoogleResumable(username, password, title, ofile);           } else if (kind.equals("Txt")) {                IProperties fileProps = obj.properties().getProperties("SI_FILES");                String SI_FILE1 = fileProps.getString("SI_FILE1");                String ofile = tmpdir+title+

                      (SI_FILE1.endsWith(".csv") ? ".csv" : ".txt" );                exportTxtFile(logonToken, obj, ofile);                uploadFile2GoogleResumable(username, password, title, ofile);           } else if (kind.equals("Agnostic")) {                // TODO: add support for multipart/related MIM type

               // (e.g. .mHTML, .xml)                log("no action");           } else {                log("no action");           }

    ...

}

Syntax to run Publish2GoogleImpl:

java Publish2GoogleImpl <GoogleUser> <GooglePass> <ReportTitle> <LogonToken> <ObjectId>

Where:

  <GoogleUser> The user name of the Google account

  <GooglePass> The password of the Google account

  <ReportTitle>  The title to name the report once uploaded

  <LogonToken> The logon token to BI Platform

  <ObjectId> The report object ID in BI Platform

The function checks the object ID and dispatches to the export functions according to the kind.

exportPdfFile

exportPptFile

exportWordFile

exportExcelFile

exportRtfFile

exportTxtFile

exportRptFile

public static void exportPdfFile(String token, IInfoObject obj, String ofile) 

    throws IOException, SDKException {           IPDF tObj = (IPDF) obj;           log (tObj.getMimeType());           writeBytes(tObj.getContent(), ofile);           log("retrieved "+ofile); } ...

A bunch of export functions to export reports to file system.
exportWebiReport2CsvFile

public static void exportWebiReport2CsvFile(IEnterpriseSession session,

     int objID, String outputFileFolder, String title) {

         ...

         reportEngines = (ReportEngines) session.getService("ReportEngines");          reportEngine = (ReportEngine) session.getService("WebiReportEngine");                         doc = reportEngine.openDocument(objID);          CSVView csvView = (CSVView)doc.getDataProviders()

             .getView(OutputFormatType.CSV);          csvView.setColumnSeparator(",");          writeBytes(csvView.getContent().getBytes(), outputFileFolder +

             "/" + title + ".csv");          ... }

Function to convert and export WebI report to CSV.
uploadFile2Google

public static void uploadFile2Google(String username, String password,

    String title, String srcfile)

    throws AuthenticationException, MalformedURLException,

    IOException, ServiceException {      DocsService service = new DocsService("MyDocumentsListIntegration-v1");      service.setUserCredentials(username, password);      java.io.File  file = new java.io.File (srcfile);      URL url = new URL("https://docs.google.com/feeds/default/private/full/");      String mimeType = DocumentListEntry.MediaType

         .fromFileName(file.getName()).getMimeType();      DocumentEntry newDocument = new DocumentEntry();      newDocument.setTitle(new PlainTextConstruct(title));      newDocument.setFile(file, mimeType);      newDocument = service.insert(url, newDocument); }

Function to upload a file to Google without Resumable upload.
uploadFile2GoogleResumable

public static void uploadFile2GoogleResumable(String username, String password,

    String title, String srcfile)

    throws AuthenticationException, MalformedURLException, IOException,

    ServiceException, InterruptedException {

  DocsService service = new DocsService("MyDocumentsListIntegration-v1");

  service.setUserCredentials(username, password);

  ...

  ResumableGDataFileUploader uploader =

          new ResumableGDataFileUploader.Builder(

                    service, url, new MediaFileSource(file, mimeType), null

          )

          .title(title)

          .chunkSize(DEFAULT_CHUNK_SIZE).executor(executor)

          .trackProgress(listener, PROGRESS_UPDATE_INTERVAL)

          .build();

  uploader.start();

  ...

}

Function to upload a file to Google with Resumable upload.
Ideas

  • Google Accounts Authentication and Authorization

The demo code simply uses clientlogin for Google account authentication and authorization.  This is good enough for a demo purpose.  It relies on the security protection offered by the BI Platform to store the Google account user and password entered in the publication extension parameter field of CMC. However, this UI could be designed better to allow entering sensitive information.

On the other hand, Google promotes OAuth quite hard over clienglogin.  There might come with better security designs when taking OAuth into the picture.  More information about Google OAuth can be found here.


  • Push vs Pull

Scheduling and publishing reports to Google drive is more like a push action.  It will be interesting to see how one can pull a report (e.g. refresh data) inside Google.


  • CR and Webi preview (say with saved data) is not available on Google.  It will be nice to see the integration.


  • It is good to see Google keep adding mobile experience to the Google Drive content, e.g. edit spreadsheet from mobile phone.


  • What about to view BI reports stored in Google drive with a native viewer (i.e. SAP BusinessObjects Mobile app)?


  • Future possible improvement of the demo code:
    • Add more safety checks
    • Support more BI contents (e.g. Agnostic)
References
To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Nikki Zuko

    Hi Xing, really good stuff!! I was trying to modify your code and noticed that the Publish2GoogleImpl.class is not the same as the one in the src file. Looks like there is in inner class missing. Would you be able to provide me with the one you used to compile your jar? Can’t seem to get the plug-in instantiated on BOBJ otherwise.

    Thanks !!

    Nikki

    (0) 
    1. Xing Jin Post author

      Hi Nikki,

      Which inner class?  If it is Publish2GoogleImpl$FileUploadProgressListener.class, it is inside the src.  Please search for “private static class FileUploadProgressListener” in Publish2GoogleImpl.java.

      Is Google site the destination you want to publish?  I haven’t tried that yet but it seems reasonable just by looking at Google Site Java API.

      Let me know and thanks,

      ~xing

      (0) 
  2. Sangdon Hur

    Hi Xing,

    I am trying to download Β ppexampl.jar and Publish2Google.propertiesΒ but site cannot be found. Is there any way I can get the files?

    Thanks

    (0) 

Leave a Reply