Skip to Content

Hi All,

I woukd like to share a script in Javascript which main objective is to change a managed datasource of a WEBI document.


Version required BOXI 4.1 SP5


What is the problem to be solved ?

Here, at my company, we frequently feel the need of having a WEBI document to read a Excel file that is the scheduled result of another WEBI document.


When we were on BOXI 3.1 we could use the approach described  by Jonathan Brown in Creating a Webi doc off of an unmanaged Excel data source on a network share .


Somehow, wtih the migration to 4.1 we loose this ability.


With 4.1 came the possibility of reading an Excel file from BI Launch Pad, what is called a managed datasource.

So we can read the managed datasource and buid the document on it.

But what if this Excel file is generated by a scheduled document every day ?

As the datasource is referenced by its ID not by the name, in order to use this new managed Excel file, one has to rebuild the entire document by reading it again.


If we were doing with Universes, we could go through this post Changing Dataproviders for a Webi Report using RESTful Web Services by Ludek Uher


Unfortunately, the re-map of an Excel datasource wasn´t implemented yet.


This script has the purpose of , automatically, change the datasource.without manual effort


Initial assumptions


Your webi doc has just one dataprovider, the one from the excel file

I´m treating the errors

I´m not dealing with drill filters nor report elements filter

The Excel file is scheduled to the Inbox of the user whom did log into the platform through the API with the name of ‘Managed-DP’

Workflow to be followed


1- Log in

2 – Determine the ID of the last excel instance of ‘Managed-DP’ at the Inbox

3 – Get the dataprovider id ( idDpDe – The id of the dataprovider, nomeDPDe – The query name

4 – Insert the new excel file as a dataprovider (with the Name “Consulta 2”, idDPPara)

5 – Gets idDPDe specification

6  Gets initial idDPPara specification

7 – Changes the idDPPara specification id´s datasources from the objects

8 – Saves the new idDPPara specification

9 – Saves the document and refresh it

10 – Gets the variables list

11 – For each variable get  its specification

12 – Changes the specification

13 – Update the variable definition

14 – Gets the report list

15 – Loops through the reports list

16 – Gets the report specification

17 – Changes the report specification

18 – Update the report specification

19 – Gets the reports data filter specification

20 – Changes the data filter specification

21 – Updates the reports data filter

22 – Gets the input control (IC) nlist

23 – Loop through the IC list

24 -Get the IC specification

25 – Deletes the original IC

26 – Changes the IC specification

27 – Insert a new IC with the specification changed

28 – Deletes idDPDe

29 – Alters idDPPara name to “Consulta 1”

30 – Saves the document

31 – Log off


The steps 24 through 27 (deleting the IC and inserting a new one) are necessary accoding to  Updating Input Control Definition.


Perhaps that´s an easier way of accomplish the proposed problem.

I would love to hear from you guys and girls about it.

One side effect of this script is that it deals with the inclusion and exclusion of columns in the excel datasource dinamically.


I´m attaching the script  to this blog post

Regards,

Rogerio

To report this post you need to login first.

5 Comments

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

  1. Victor Gabriel Saiz Castillo

    Thanks for sharing Rogerio.

    For some requirements one valid workaround could be:

    -Create a webi schedule to the Destination: File System, Format: Excel or CSV.

    -A new daily instance will update the Excel or CSV file on the BO Server

    -Create a universe on this daily output

    -Create a Web Intelligence on the universe

    http://scn.sap.com/community/semantic-layer/blog/2013/09/11/food-for-thought-creating-a-unx-universe-on-top-of-a-bex-query

    Cheers

    (0) 
    1. Rogerio Plank Post author

      Hi Victor,

      thanks for your comment. The problem with this approach and the one suggested by Ted is that it depends on IT crew.

      I´m looking for solutions that doesn´t depend on it and can be implemented at client side.

      Cgheers,

      Rogerio

      (0) 
  2. Paul Touloukian

    I’ve been messing with solutions for this problem for a while now.  I’ve found that it’s cleaner to replace the Excel file directly on the Input FRS as opposed to modifying the WebI document and risking corruption.

    (0) 
      1. Paul Touloukian

        Some of the Java code:

        // get target file from Excel InfoObject

        IProperty siFiles = properties.getProperty(PropertyIDs.SI_FILES);

        String siFile = null;

        String siPath = null;               

        if (siFiles != null && siFiles.isContainer())

        {

          IProperties fileProps = (IProperties)siFiles.getValue();

          // assume there is only one file, use SI_FILE1 property

          if (!fileProps.containsKey(PropertyIDs.SI_FILE0 + 1))

          {

            throw new Exception(“SI_FILE1 property not found on Excel InfoObject.”);

          }

          if (!fileProps.containsKey(PropertyIDs.SI_PATH))

          {

            throw new Exception(“SI_PATH property not found on Excel InfoObject.”);

          }       

          siFile = (String) fileProps.getProperty(“SI_FILE1”).getValue();

          siPath = (String) fileProps.getProperty(“SI_PATH”).getValue();       

        }

        else

        {

          throw new Exception(“SI_FILES not found.”);

        }

        siPath = siPath.substring(FRS_INPUT.length());

        siPath = siPath.replace(‘/’, ‘\\’);

        String targetPath = frsRootPath + siPath + siFile;                       

        logger.debug(“Target Path:   ” + targetPath);       

                

        // ensure file exists and can be written

        File targetFile = new File(targetPath);   

        if (!targetFile.exists() || !targetFile.canWrite())

          throw new Exception(“Target file is not accessible.”);

        // replace with source file

        FileInputStream fis = new FileInputStream(sourceFile);

        FileOutputStream fos = new FileOutputStream(targetFile);

        byte[] buffer = new byte[4096];

        int bytesRead;

        while ((bytesRead = fis.read(buffer)) != -1) {

          fos.write(buffer, 0, bytesRead);

        }

        fis.close();

        fos.close();

                        

        // trigger an update to the object’s last modified time

        boInfoObject.setTitle(boInfoObject.getTitle());

        boInfoObject.save();

        boInfoStore.commit(boInfoObjects);

        (0) 

Leave a Reply