Skip to Content
Author's profile photo Shawn Penner
Shawn Penner
March 8, 2013 10 minute read

Remapping Universes for Webi Reports

I have encountered quite a few issues related to webi reports linking to wrong universe, or even losing connection to the universe completely.  While most of these issues can be fixed by opening up the webi reports in the webi designer and remapping the universes through that – it can be difficult and very time consuming to apply that fix when you have hundreds or thousands of webi reports that need to be repaired.  The set of scripts below were created to resolve issues related to webi reports and universe mapping.  These scripts were designed to work with BOE XI R3.  They will not work in BI4.

Warning

The code given below can be very destructive if not used properly.  Please ensure that you have made a backup of your CMS database and your Input and Output FRS prior to running any code.

Note

This code is based on the batch scripting template found here: http://scn.sap.com/docs/DOC-38618

For other scripts and information on how to run these scripts see here:

http://scn.sap.com/people/shawn.penner/blog/2013/06/04/scripts-and-samples

Auto-Detect and Remap

This first script was created to fix webi reports that had no universe in the SI_UNIVERSE property.  It assumes that the SI_UNIVERSE property exists, but that there are no universes listed in it.  The script first loops through each dataprovider in the webi report, retrieves the universe name, and then searches the enterprise system for a universe with a matching name.  If it finds one, it maps the universe to the dataprovider.

Notes:
  • The script assumes that each universe has a unique name.  If you have multiple universes with the same name, then you will need to add additional checks to ensure that the correct universe is retrieved. 
  • The script also uses a logging function I wrote to save the output to a text file “C:\TestOutput.txt”.  You can modify this to whatever logfile name and path you prefer.
  • You will need to change the username, password, and CMS name to the values specific to your enterprise server.

Auto-Detect and Remap

<%@ page import = “com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
com.crystaldecisions.sdk.occa.report.*,
com.crystaldecisions.sdk.properties.*,
com.businessobjects.rebean.wi.*,
com.businessobjects.sdk.plugin.desktop.universe.*,
java.util.*,
java.io.*”
%>

<%
// User Credentials
String username = “Administrator”;
String password = “myPassword”;
String cmsname = “myEnterpriseServer”;
String authType = “secEnterprise”;

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService(“”, “InfoStore”);

// The SI_ID to start at when searching
int max_id = 0;

IProperty boProperty_SI_UNIVERSE = null;
IProperty boProperty_SI_TOTAL = null;
int parentFolderId = 0;

for(;;) {

// Loop through all objects
boInfoObjects = (IInfoObjects)infoStore.query(“Select * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_ID > ” + max_id + ” ORDER BY SI_ID ASC”);

// If there are no more objects then we’re done.
if(boInfoObjects.size() == 0)
break;

for(Iterator boCount = boInfoObjects.iterator() ; boCount.hasNext() ; ) {
  IInfoObject boReport = (IInfoObject)boCount.next();

  // Are there any universes mapped to this webi report
  boProperty_SI_UNIVERSE = boReport.properties().getProperty(“SI_UNIVERSE”);
  boProperty_SI_TOTAL = ((IProperties) boProperty_SI_UNIVERSE.getValue()).getProperty(“SI_TOTAL”);
 
  if (((Integer) boProperty_SI_TOTAL.getValue()).intValue()<=0) {
   try {
    // Print out some identifying information
    parentFolderId = ((Integer) boReport.properties().getProperty(“SI_PARENT_FOLDER”).getValue()).intValue();
    writeToLog(“Processing Report: ” + boReport.getID() + ” – ” + boReport.getTitle());
    writeToLog(“ParentFolderID: ” + parentFolderId + ” – ” + getFolderPath(infoStore, parentFolderId));
    
    // Right here – we have a report with no universe – so find the proper universe and re-map it.
    boolean universeChanged = false;
   
    // Open the webi report
    ReportEngines boReportEngines = (ReportEngines) enterpriseSession.getService(“ReportEngines”);
    ReportEngine boReportEngine = (ReportEngine) boReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);
    DocumentInstance boDocumentInstance = boReportEngine.openDocument(boReport.getID());

    DataProvider boDataProvider = null;
    
    // Now loop through all the dataproviders for the webi report
    for (int j=0; j<boDocumentInstance.getDataProviders().getCount(); j++) {
     boDataProvider = boDocumentInstance.getDataProviders().getItem(j);
  
     String dpName = boDataProvider.getName();
     String dpUnivName = boDataProvider.getDataSource().getName();
     
     writeToLog(“Looking at connection: ” + dpName + ” with universe name ” + dpUnivName);
     String query_universe = “SELECT TOP 1 SI_NAME, SI_CUID, SI_SHORTNAME FROM CI_APPOBJECTS WHERE SI_Kind = ‘Universe’ AND SI_NAME='” + dpUnivName + “‘”;
     IInfoObjects boInfoObjects_universe = (IInfoObjects) infoStore.query(query_universe);
     
     // If we find a universe that matches the universe listed in the dataprovider – then map the dataprovider to that universe
     if (boInfoObjects_universe.size() > 0) {
      writeToLog(“Matching Universe Found – changing”);
      IUniverse boUniverse = (IUniverse) boInfoObjects_universe.get(0);
      String newUniverseId = “UnivCUID=” + boUniverse.getCUID() + “;UnivName=” + boUniverse.getShortName();
      boDocumentInstance.getDataProviders().changeDataSource(boDataProvider.getDataSource().getID(), newUniverseId, false);
      universeChanged = true;
     } else {
      // No matching universe found
      writeToLog(“No Matching Universe Found”);
     }
    }
    if (universeChanged) {
     boDocumentInstance.save();
    }
    boDocumentInstance.closeDocument();
   } catch (Exception sdkEx2) {
    writeToLog(“Error:” + sdkEx2);
   }
  }
  max_id = boReport.getID();
}
infoStore.commit(boInfoObjects);
}
out.println(“Completed</br>”);
%>

<%!
String getFolderPath(IInfoStore infoStore, int folderId) throws SDKException {
String query = “SELECT SI_NAME, SI_PATH FROM CI_INFOOBJECTS WHERE SI_ID=” + folderId;
IInfoObjects boInfoObjects = (IInfoObjects) infoStore.query(query);

String folderPath = “”;

if (boInfoObjects.size()>0) {
  IInfoObject boInfoObject = (IInfoObject) boInfoObjects.get(0);

  if (boInfoObject.properties().getProperty(“SI_PATH”) != null) {
   IProperties boProperties_SI_PATH = (IProperties) boInfoObject.properties().getProperty(“SI_PATH”).getValue();

   for (int i=((Integer) boProperties_SI_PATH.getProperty(“SI_NUM_FOLDERS”).getValue()).intValue(); i>0; i–) {
    folderPath += “\\” + boProperties_SI_PATH.getProperty(“SI_FOLDER_NAME” + i).getValue().toString();
   }
  }
  folderPath += “\\” + boInfoObject.getTitle();

  folderPath = boInfoObject.getKind() + “</TD><TD>” + folderPath;
}

return folderPath;
}

public void writeToLog(String msg) {
try {
  // Set up Logging File
  FileOutputStream FSout;
  PrintStream pStream; // declare a print stream object
  FSout = new FileOutputStream(“C:\\TestOutput.txt”, true);  // Append
  pStream = new PrintStream(FSout);
  pStream.println(msg);
  pStream.close();
} catch (IOException e) {
  //error writing to log
    }
}
%>

Batch Search and Replace

This next script was designed to remap all webi reports that used a specific universe and point them at a different universe.  They key thing to note with this script is how it identifies which universe to look for.  The way it does this is by looking for something unique in the SI_WEBI_DOC_PROPERTIES infoobject.

e.g.
SI_WEBI_DOC_PROPERTIES LIKE ‘%UnivCUID=AVHuOHKBm5lFkr3KcJkfk_0%’

The % are wildcard characters which allow the UnivCUID string to occur anywhere in the SI_WEBI_DOC_PROPERTIES value.

Notes:

  • The SI_WEBI_DOC_PROPERTIES tends to change from version to version.  So if your webi reports were migrated from a previous version – make sure that whatever unique identifier you choose is still contained in those reports SI_WEBI_DOC_PROPERTIES.
  • The script also uses a logging function I wrote to save the output to a text file “C:\TestOutput.txt”.  You can modify this to whatever logfile name and path you prefer.
  • You will need to change the username, password, and CMS name to the values specific to your enterprise server.
  • You will need to change the “universeID” variable to the SI_ID of the universe you want to change to, and the “WebiPropertiesString” variable to the string containing something unique to your reports.
  • This script automatically changes all connections in the report to the specified universe – so if your report uses more than one universe, you may want to add some code to check for that.
Batch Search and Replace

<%@ page import = “com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
com.crystaldecisions.sdk.occa.report.*,
com.crystaldecisions.sdk.properties.*,
com.businessobjects.rebean.wi.*,
com.businessobjects.sdk.plugin.desktop.universe.*,
java.util.*,
java.io.*”
%>
<%
// User Credentials
String username = “Administrator”;
String password = “myPassword”;
String cmsname = “myEnterpriseServer”;
String authType = “secEnterprise”;

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService(“”, “InfoStore”);

// The SI_ID to start at when searching
int max_id = 0;

String universeID = “12345”; // Target Universe
String WebiPropertiesString = “%UnivCUID=AVHuOHKBm5lFkr3KcJkfk_0%”;  // The % marks are for wild cards
String newUniverseId = “”;  // do not touch – will be generated later

IProperty boProperty_SI_UNIVERSE = null;
IProperty boProperty_SI_TOTAL = null;
int parentFolderId = 0;

// Retrieve info about target universe
String query_universe = “SELECT TOP 1 SI_NAME, SI_CUID, SI_SHORTNAME FROM CI_APPOBJECTS WHERE SI_Kind = ‘Universe’ AND SI_ID=” + universeID;
IInfoObjects boInfoObjects_universe = (IInfoObjects) infoStore.query(query_universe);
     
writeToLog(“Obtaining universe details”);
IUniverse boUniverse = (IUniverse) boInfoObjects_universe.get(0);
newUniverseId = “UnivCUID=” + boUniverse.getCUID() + “;UnivName=” + boUniverse.getShortName();
writeToLog(“New Universe ID: ” + newUniverseId);

for(;;) {

// Loop through all objects
boInfoObjects = (IInfoObjects)infoStore.query(“Select * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_WEBI_DOC_PROPERTIES LIKE ‘” + WebiPropertiesString + “‘ AND SI_ID > ” + max_id + ” ORDER BY SI_ID ASC”);

// If there are no more objects then we’re done.
if(boInfoObjects.size() == 0)
break;

for(Iterator boCount = boInfoObjects.iterator() ; boCount.hasNext() ; ) {
  IInfoObject boReport = (IInfoObject)boCount.next();

  // Obtain Universe Information for this webi report
  boProperty_SI_UNIVERSE = boReport.properties().getProperty(“SI_UNIVERSE”);
  boProperty_SI_TOTAL = ((IProperties) boProperty_SI_UNIVERSE.getValue()).getProperty(“SI_TOTAL”);
 
  try {
   parentFolderId = ((Integer) boReport.properties().getProperty(“SI_PARENT_FOLDER”).getValue()).intValue();
   writeToLog(“Processing Report: ” + boReport.getID() + ” – ” + boReport.getTitle());
   writeToLog(“ParentFolderID: ” + parentFolderId + ” – ” + getFolderPath(infoStore, parentFolderId));
    
   ReportEngines boReportEngines = (ReportEngines) enterpriseSession.getService(“ReportEngines”);
   ReportEngine boReportEngine = (ReportEngine) boReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);

   DocumentInstance boDocumentInstance = boReportEngine.openDocument(boReport.getID()); 
   DataProvider boDataProvider = null;
    
   // Now loop through all the dataproviders for the webi report
   for (int j=0; j<boDocumentInstance.getDataProviders().getCount(); j++) {
    boDataProvider = boDocumentInstance.getDataProviders().getItem(j);
  
    String dpName = boDataProvider.getName();
    String dpUnivName = boDataProvider.getDataSource().getName();
     
    writeToLog(“Looking at connection: ” + dpName + ” with universe name ” + dpUnivName);
    boDocumentInstance.getDataProviders().changeDataSource(boDataProvider.getDataSource().getID(), newUniverseId, false);
   }
  
   boDocumentInstance.save();
   boDocumentInstance.closeDocument();
  } catch (Exception sdkEx2) {
   writeToLog(“Error:” + sdkEx2);
  }
  max_id = boReport.getID();
}
infoStore.commit(boInfoObjects);
}
out.println(“Completed</br>”);
%>

<%!
String getFolderPath(IInfoStore infoStore, int folderId) throws SDKException {
String query = “SELECT SI_NAME, SI_PATH FROM CI_INFOOBJECTS WHERE SI_ID=” + folderId;
IInfoObjects boInfoObjects = (IInfoObjects) infoStore.query(query);

String folderPath = “”;

if (boInfoObjects.size()>0) {
  IInfoObject boInfoObject = (IInfoObject) boInfoObjects.get(0);

  if (boInfoObject.properties().getProperty(“SI_PATH”) != null) {
   IProperties boProperties_SI_PATH = (IProperties) boInfoObject.properties().getProperty(“SI_PATH”).getValue();

   for (int i=((Integer) boProperties_SI_PATH.getProperty(“SI_NUM_FOLDERS”).getValue()).intValue(); i>0; i–) {
    folderPath += “\\” + boProperties_SI_PATH.getProperty(“SI_FOLDER_NAME” + i).getValue().toString();
   }
  }
  folderPath += “\\” + boInfoObject.getTitle();

  folderPath = boInfoObject.getKind() + “</TD><TD>” + folderPath;
}

return folderPath;
}

public void writeToLog(String msg) {
try {
  // Set up Logging File
  FileOutputStream FSout;
  PrintStream pStream; // declare a print stream object
  FSout = new FileOutputStream(“C:\\TestOutput.txt”, true);  // Append
  pStream = new PrintStream(FSout);
  pStream.println(msg);
  pStream.close();
} catch (IOException e) {
  //error writing to log
    }
}
%>

Remap Individual Webi Reports

This third script is designed to handle individual reports.  It allows you to pass in parameters specifying a webi report, universe ID, and the index of the connection that you want to change.  It is designed to accept those parameters via querystring so you can automate it a bit if needed.

An example URL to call this script would be:

http://localhost:8080/testApp/remapWebi.jsp?reportID=918&universeID=1093&ConnectIndex=0

You can specify default values in the script which will be used if you leave out the parameters from the URL.

Notes

  • You will need to change the username, password, and CMS name to the values specific to your enterprise server.
  • This script does not do any validation to check if the universe is the correct one – so you will need to ensure that you are passing the correct values prior to running the script.
Remap Single Webi Report

<%@ page import = “com.crystaldecisions.sdk.exception.SDKException,
com.crystaldecisions.sdk.framework.*,
com.crystaldecisions.sdk.occa.infostore.*,
com.crystaldecisions.sdk.occa.report.*,
com.crystaldecisions.sdk.properties.*,
com.businessobjects.rebean.wi.*,
com.businessobjects.sdk.plugin.desktop.universe.*,
java.util.*,
java.io.*”
%>
<%
// User Credentials
String username = “Administrator”;
String password = “”;
String cmsname = “localhost”;
String authType = “secEnterprise”;

// Default values
String docID = “12345”;    // SI_ID of the webi report
String universeId = “54321”;  // SI_ID of the universe to change to
int connToChange = 0;    // Index of the connection to change  (0 based)

// Get the params from the query string if they exist.  If not – use the values set above.
if (request.getParameter(“reportID”)!=null) {
docID = request.getParameter(“reportID”);
}

if (request.getParameter(“universeID”)!=null) {
universeId = request.getParameter(“universeID”);
}

if (request.getParameter(“ConnectIndex”)!=null) {
connToChange = Integer.parseInt(request.getParameter(“ConnectIndex”));
}

IEnterpriseSession enterpriseSession = null;
IInfoStore infoStore;
IInfoObjects boInfoObjects;

// Log onto Enterprise
enterpriseSession = CrystalEnterprise.getSessionMgr().logon(username, password, cmsname, authType);
infoStore = (IInfoStore)enterpriseSession.getService(“”, “InfoStore”);

String query_webi = “SELECT TOP 1 SI_ID FROM CI_INFOOBJECTS WHERE SI_INSTANCE = 0 And SI_Kind = ‘Webi’ AND SI_ID=” + docID;
IInfoObjects boInfoObjects_webi = (IInfoObjects) infoStore.query(query_webi);
IInfoObject boInfoObject_webi = null;

String query_universe = “SELECT TOP 1 SI_NAME, SI_CUID, SI_SHORTNAME FROM CI_APPOBJECTS WHERE SI_Kind = ‘Universe’ AND SI_ID=” + universeId;
IInfoObjects boInfoObjects_universe = (IInfoObjects) infoStore.query(query_universe);
IUniverse boUniverse = null;

String newUniverseId = null;

if ((boInfoObjects_webi.size() > 0) && (boInfoObjects_universe.size() > 0)) {

boInfoObject_webi = (IInfoObject) boInfoObjects_webi.get(0);
boUniverse = (IUniverse) boInfoObjects_universe.get(0);
newUniverseId = “UnivCUID=” + boUniverse.getCUID() + “;UnivName=” + boUniverse.getShortName();

ReportEngines boReportEngines = (ReportEngines) enterpriseSession.getService(“ReportEngines”);
ReportEngine boReportEngine = (ReportEngine) boReportEngines.getService(ReportEngines.ReportEngineType.WI_REPORT_ENGINE);

DocumentInstance boDocumentInstance = boReportEngine.openDocument(boInfoObject_webi.getID());
 
DataProvider boDataProvider = null;

out.print(“<TABLE BORDER=1>”);
out.print(“<TR ALIGN=CENTER BGCOLOR=KHAKI><TD>Data Provider Name</TD><TD>Universe Name</TD><TD>Old Universe CUID</TD><TD>New Universe CUID</TD></TR>”);
boDataProvider = boDocumentInstance.getDataProviders().getItem(connToChange);
out.print(“<TR>”);
out.print(“<TD>” + boDataProvider.getName() + “</TD>”);
out.print(“<TD>” + boDataProvider.getDataSource().getName() + “</TD>”);
out.print(“<TD>” + boDataProvider.getDataSource().getID() + “</TD>”);
boDocumentInstance.getDataProviders().changeDataSource(boDataProvider.getDataSource().getID(), newUniverseId, false);
out.print(“<TD>” + boDataProvider.getDataSource().getID() + “</TD>”);
out.print(“</TR>”);
out.print(“</TABLE>”);

boDocumentInstance.save();
boDocumentInstance.closeDocument();
}
out.print(“Process Complete.”);
enterpriseSession.logoff();

%>

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Great article.  This what we have been looking for for a long time.

      We are in the process of migrating to BI 4.0 SP4.  What will it take to get these scripts to work in 4.0?

      Author's profile photo Shawn Penner
      Shawn Penner
      Blog Post Author

      Unfortunately the SDK methods for remapping connections have been removed in BI4 - so it is not currently possible to run these scripts - or even to rewrite them in any way to make them work in BI4.

      This should hopefully change when SP6 is released - because that is when the Restful SDK is currently targeted for - and it is my understanding that the Restful SDK may have the ability to remap connections.

      Author's profile photo Shawn Penner
      Shawn Penner
      Blog Post Author

      It is now possible to remap webi reports using the Restful SDK.  I have published a sample here: http://scn.sap.com/docs/DOC-45986  Please note that there is an issue with using the Restful method which should be patched in 4.1 SP2.

      Author's profile photo Former Member
      Former Member

      Do you have the scripts that work on BI4?

      We are on BI4 SP5 P4.

      Author's profile photo Shawn Penner
      Shawn Penner
      Blog Post Author

      As mentioned above - what these scripts do is not possible using the SDK in BI4.  It may become possible using the RestFul SDK once SP6 / 4.1 is released - but at present - there is no sdk to do these tasks available in BI4.

      Author's profile photo Shawn Penner
      Shawn Penner
      Blog Post Author

      It is now possible to remap webi reports using the Restful SDK.  I have published a sample here: http://scn.sap.com/docs/DOC-45986  Please note that there is an issue with using the Restful method which should be patched in 4.1 SP2.

      Author's profile photo Former Member
      Former Member

      Hi Shawn,

      The script was very helpful.

      but is the script anyway can be modifed to remap DeskI reports universes?

      If not is there any other way to automate DeskI universe remapping?

      I'm using BO 3.1 SP7.

      Regards

      Md.Arrif

      Author's profile photo Former Member
      Former Member

      Thanks Shawn.

      May I know if the above supports the remap from a .unx to .unx universe for a WEBi report ?

      It looks like this only support inter-unx universe update / object remap.

      BR

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy

      You can use the RESTful Web Services to remap the following:

      .unv to .unv

      .unx to .unx

      .unv to .unx

      There is no way, though to remap a .unx to a .unv.

      -Dell

      Author's profile photo Former Member
      Former Member

      Could anyone please give me the steps where I can run these code and what are the prerequisites? I am completely new to webi 3.1. what are the information I need before I execute the code like source and target universe ID and so on.. I want to change the universe for 500 reports which is difficult to do manually.

      Author's profile photo Former Member
      Former Member

      Please use the following link:

      Shawn Penner's Blog

      You will be seeing How do I use / run the scripts & the Steps for executing jsp scripts for BOE XI R3.

      If you have any further doubts, feel free to open a new thread so that expert can you help you in more efficient way.

      Thanks,

      Shailendra