Remapping Universes for Webi Reports

[former_member203619]

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:

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(); %>