""
,

Export to Text automation in Web Intelligence

,

Users typically need their Web Intelligence (WebI) data tables exported automatically into text files in order to use them across other SAP BusinessObjects BI modules. Unfortunately SAP BusinessObjects, including the newest SAP BI 4 release, does not include a direct option to automate the export of content of a WebI document tab to text format. In order to cover this gap and achieve the Export to Text feature for WebI we designed a fully automated process which is shown in this article.

The problem

Users want to automatically export raw data tables from WebI to TXT file, but none of the existing scheduling format options – PDF, XLS, CSV – are satisfying, because:

  • A PDF brings an static document that cannot be re-used directly
  • An XLS or XLSX has the limitation of 65535 or 1 million rows respectively
  • CSV does not export tables, it just exports the Query content

Users of old releases could use the old Desktop Intelligence (DeskI) module as an alternative, but unfortunately it has been discontinued in the new SAP BusinessObjects BI4 release.

The consequences

Users see WebI as an “limited” module in terms of sharing options and export size. Moreover, customers will not migrate to the new SAP BI4, especially those who heavily do Query & Analysis an export the result table to txt using DeskI. The future does not look very promising because:

  • Even if a manual Export to TXT is available since SAP BI4 FP3, automation for it is not currently available and SAP does not have a release date for this feature
  • DeskI alternative is not possible in SAP BI4. Even if a DeskI add-on is planned for coming versions, the future of its scheduling function is uncertain and corporations should not allow DeskI to be part of their BI Roadmap.

The solution

The following method describes a way to schedule a WebI report with Export to text functionality and it involves the use of the following items:

  1. A 1st WebI document with the table to be exported
  2. A Web Service that is pointing to that document table as a source
  3. A 2nd WebI document with just one query that sits on the Web Service created. No tables nor charts are needed here
  4. A vbs script that adapts the output from this 2nd WebI document

Detailed steps to follow for every item are:

  1. The 1st webI document contains all the development needed (Queries, objects, variables, filters) and a table with the final data you would like to export
  2. This 1st WebI document must be edited with WebI Rich Client. Select the table you want to export -> Right Click -> Publish Block -> Create Web Service
  3. The 2nd WebI document which contains the Web Service based query can be scheduled to run with the following options:
    • CSV type
    • Double quote text qualifier, tab column delimiter
    • Export to a server folder (e.g. D:)
    • Name it with txt extension (e.g. Results1.txt)

See below a snapshot with the schedule configuration detail:

Configuration of the schedule in WebI for a txt export
Configuration of the schedule in WebI for a txt export

This example is applied to only 1 table to export, but multiple tables per document could be exported by ticking the “Generate separate CSV per Data Provider” option.

Once run with Success, the result of this schedule will be a text file (Results1.txt) with the content delimited by tabs but with a small defect: the so-called text qualifier (double quotes) appears everywhere.

In order to remove this annoying text qualifier (double quotes) a program can be scheduled. You can use your free style but if you copy and paste the following txt into a file called “QuoteRemoval.vbs” it will do the job:

set objRe = new RegExp

objRE.Pattern = """"

objRE.Global  = True

strFileName = "D:Results1.txt"

set objFS = CreateObject("Scripting.FileSystemObject")

set objTS = objFS.OpenTextFile(strFileName)

strFileContents = objTS.ReadAll

objTS.Close

strNewContents = objRE.replace(strFileContents,"")

set objWS = objFS.CreateTextFile("D:Results2.txt")

objWS.Write StrNewContents

objWS.close

The result of this executed script will be a perfectly formatted Results2.txt file

Last but not the least, you can build a system of events that triggers the different items sequentially, or embed these items in an object package that can be scheduled as a whole.

Applicability & Benefits

This method enhances the sharing options for the SAP BusinessObjects platform, allowing an unlimited amount of raw data to exit the platform through WebI automatically, and be re-used in Big Data modules like HANA, Visual Intelligence, Explorer or simply for individual consumption.

Seeing even further, this turns WebI into a real ETL (Extraction, Transformation and Load) tool providing integration capabilities to the end users.

Summarizing, this method:

  • Allows a better integration of SAP BusinessObjects with the corporate BI processes improving efficiency and effectiveness
  • Facilitates companies to opt for a migration to SAP BI4 release, with all the benefits that the newest platform brings

If you have questions about this method, or if you want to share your experience or tips, please feel free to leave a comment.