April 27, 2011

Saving Scenarios in Xcelsius

In most instances, Xcelsius is used to display data, and considered a ‘read only’ tool. But for those occasions where there's a requirement to have data be user-editable and persistent, I hope that the steps outlined below are helpful.

The most common scenarios that I've seen for this are:
  • I want to be able to update my KPI variables occasionally, but don't have them stored in a data-source.
  • I want to be able to have a comment shown on the dashboard that I can update occasionally.
  • I want to perform what-if analysis, and save the scenarios for later analysis.
  • I want to perform what-if analysis, and share this with other users.

      I won't go into the details of all your options, but there's some links at the end of this post for some useful alternatives.

      I’m Using:
      • Xcelsius and Business Objects Enterprise XI3.1 SP3 FP3.5 (this will only work if you’re viewing your dashboard through InfoView).
      • Tomcat web/application server. The logic in this post works with .NET/aspx, but the code won’t.
      I’m Assuming:
      • You know your way around Xcelsius
      • You know your way around a computer
      • You have permissions to get onto your BOBJ server
      • You know a little bit about tomcat, jsp and xml, but you will still be able to get through this if you don’t.

      Let’s get cracking.

      The Interface


      The example I’ve used is that of a hotel that wants to see what effect changes to room rates and room occupation levels have on overall revenue for a theoretical week.
      A user can change the nightly rates and the occupation percentages. They will then be able to manage the values that they have changed as a ‘scenario’, specifically:
      • Save the current state of the dashboard as a new scenario.
      • Load a previously-saved scenario (saved by them or another user of the dashboard).
      • Delete an existing scenario.

      The Process

      Rather than treat this as a how-to, I will step you through how the dashboard works. You can download the completed dashboard and code here.
      All of the data for the dashboard (on the Source Data tab) is in a condensed range of cells. This range of cells (outlined in red) is what will be saved/loaded as a scenario.
      On the Scenarios tab there is a URL for each of the four main tasks that the dashboard performs, Saving, Loading and Deleting a scenario, and fetching a list of the available scenarios:
      I’ll step through each of these four components in the sections that follow.

      1. Saving a Scenario

      The Connection

      When the user has changed some values on the screen, you want them to be able to save those values somewhere outside of the dashboard, so that they can be retrieved later. You do this by utilising and XML data connection that will take a range of cells and save them as an XML file on the Business Objects server. The connection details look like this:
      The XML data URL points to cell B3, which contains the text http://avantis-dg:8080/Xcelsius/SaveScenario.jsp
      In the Enable Send section, the Name field points to a cell where the user has typed the name they want for the scenario. The Range field points to the range where all of the scenario data is kept (on the source data tab). When this connection is triggered, Xcelsius will take all of those cells and send them to the jsp file in an XML format. The jsp file (think of this as a little Java application) then processes that data and saves is as an actual XML file. It will use the cell referenced in the Name field in the Enable Send section to name the XML file.
      The Enable Load section has a single entry that expects a value of Response. This can be used for troubleshooting to make sure that your jsp file is working correctly, but otherwise serves no purpose in the functionality of the dashboard.
      So, to re-cap:
      1. A user clicks Save Scenario.
      2. The XML data connection is triggered.
      3. It sends both the name of the scenario and the range of cells containing the data to a jsp file.
      4. The jsp file saves that data as an XML file on the file system.

      The Components

      The user is instructed to type a name for the scenario into a panel on the right of the dashboard. This cell is bound to cell B10 (which via a formula becomes the xml file name). The field will not accept characters that will cause trouble.
      The Save/Update button is actually a Connection Refresh component, linked to simply trigger the Save Scenario XML data connection mentioned above.
      The scenario notes are stored in the same area as the rest of the data in the dashboard, and hence become part of the body of the XML file.

      The jsp File

      The file is called SaveScenario.jsp and is saved in C:\Program Files (x86)\Business Objects\Tomcat55\webapps\Xcelsius
      This is the code that does the work. For the non-Java people, this is similar to when you submit a form on a web site. When you click Submit, the details of that form get sent to something like this jsp page, which then has instructions of what to do with that data.
      It doesn’t specifically need to be in the Xcelsius directory, any of the webapp folders would work, but wherever your jsp files reside (there will be three) you must have a folder named SavedScenarios at the same level (the jsp files DO NOT go in the SavedScenarios folder). When the dashboard saves, loads and lists the saved scenarios, it is referring to XML files that are stored in this folder; as such, you should not manually place any files in the SavedScenarios folder.

      The contents of the SaveScenario.jsp file are this:

      <%@page contentType="text/html" pageEncoding="UTF-8"%> <%@page import="java.io.*" %> <% // Set the path to save scenarios     String scenarioPath = request.getRealPath("/") + "SavedScenarios/";
      // Build a string from the input     InputStream in = request.getInputStream();     BufferedReader r = new BufferedReader(new InputStreamReader(in));     StringBuffer buf = new StringBuffer();     String line;     while ((line = r.readLine())!=null) {         buf.append(line);         }     String xmlString = buf.toString();
      // Write this to an xml file     int startPos = xmlString.indexOf("variable name=") + 15;     int endPos = xmlString.indexOf("\">");     String fileName = xmlString.substring(startPos, endPos);
          PrintWriter fileWriter = new PrintWriter (new BufferedWriter(new FileWriter(scenarioPath + fileName + ".xml")));     fileWriter.write(xmlString);     fileWriter.flush();     fileWriter.close();
      // Send a response     String responseXML = "<data>";     responseXML += "<variable name=\"Response\">";     responseXML += "<row>";     responseXML += "<column>" + fileName + "</column>";     responseXML += "</row>";     responseXML += "</variable>";     responseXML += "</data>";
          PrintWriter rsp = response.getWriter();     rsp.write(responseXML);


      If you’re following on/re-creating this setup, just copy and paste the orange text above into notepad and save it as SaveScenario.jsp and place it on the server. You don’t need any special software. This applies to the other two jsp files explained below.

      2. Loading a Scenario

      The Connection

      The connection used to load a scenario is much simpler.
      This is because Xcelsius will simply load an XML file, all you need to do is select the file to load and the cells to stick it in.
      The XML Data URL points to cell B4, which contains a formula that will show something like http://avantis-dg:8080/Xcelsius/SavedScenarios/scenario.xml
      Note that the MIME Type must be set to text/xml or the connection won’t work.

      The Components

      The Load Scenario connection is triggered when a user selects an available scenario from the list box component on the right of the dashboard. The selected row is written to cell B10.
      The formula in B4 will add a prefix of the file path and a suffix of the ‘.xml’ extension.
      The actual trigger for the connection is cell B9 because there is a check to see if the value has changed simply because a scenario has been deleted. In this instance the connection will actually fire, but send a blank string as the file to load, which will fail, but fail quietly.

      There is no jsp file for the Load Scenario action because the XML file is loaded directly.

      3. Loading a List of Scenarios

      The Connection

      The connection for loading a list of scenarios is also an XML Data connection.
      The XML Data URL points to cell B5 which contains the name of another jsp file. This connection is set to Refresh Before Components are Loaded, so that a list of saved scenarios are available as soon as the dashboard appears to the user.
      Enable Send is not activated since there are no conditions for the data that is returned. I.e. it will always be all scenarios.
      Enable Load is of course activate, and outputs the returned data (a list of scenarios) into the cell range B11:Bxxx depending on how many scenarios you want to handle.

      The Components

      There is only one component here, the list box that shows the names of the scenarios, bound to the range of cells that will contain the scenario names.
      As mentioned earlier, when clicked, the selected item will be inserted into the spreadsheet, and trigger the Load Scenario connection. If your scenarios are enormous, you may not want to load them automatically when clicked, and instead have a load button that your users must click to load the scenario. However this will save/load a scenario of 20,000 cells in about 2-3 seconds.

      The jsp File

      The file is called GetScenarioList.jsp and is saved in C:\Program Files (x86)\Business Objects\Tomcat55\webapps\Xcelsius
      As you saw in the connection setup, Xcelsius doesn’t actually send any data to this jsp file. Simply referencing the jsp file in the XML Data URL field is enough for the jsp to be ‘triggered’ and run through its code. That code simply loads a list of the files it finds in the SavedScenarios folder and returns the list of files. Note that it strips off the .xml extension before sending the list.

      <%@page contentType="text/html" pageEncoding="UTF-8"%> <%@page import="java.io.*" %> <% // Fetch a list of files     String scenarioPath = request.getRealPath("/") + "SavedScenarios/";     File scenarioFolder = new File(scenarioPath);     File[] arrayOfFiles = scenarioFolder.listFiles();
      // Send a response     String scenarioName = "";     String responseXML = "<data>";     responseXML += "<variable name=\"FileList\">";         if (arrayOfFiles.length > 0){         for (int i = 0; i < arrayOfFiles.length; i++) {             scenarioName = arrayOfFiles[i].getName();             scenarioName = scenarioName.substring(0, scenarioName.length() - 4);             responseXML += "<row>";             responseXML += "<column>" + scenarioName + "</column>";             responseXML += "</row>";         }     }else{         responseXML += "<row>";         responseXML += "<column></column>";         responseXML += "</row>";     }
          responseXML += "</variable>";     responseXML += "</data>";
          PrintWriter rsp = response.getWriter();     rsp.write(responseXML); %>

      4. Deleting a Scenario

      The Connection

      This connection points to cell B6, which contains the name of the jsp file used to delete a scenario (which, as you get by now, is an xml file).
      The Enable Send section is linked to B10 which contains the name of the scenario that has been selected. The range that is sent is relevant.
      The Enable Load section is only used to get a response, which is used to blank out the selected scenario (it returns an empty string, which is inserted into B10, effectively clearing the selected scenario that the user sees).
      The connection is only triggered when the user clicks Delete.

      The Components

      The Delete Selected button is actually a Connection Refresh button bound to the Delete Scenario connection. Note that the text field just above the delete button always shows the currently selected scenario, and this is what is sent to the jsp file.
      The tricky part comes after a user deletes a scenario, for two reasons.
      Firstly, that scenario needs to be removed from the list of saved scenarios shown to the user, so the Get Scenario List connection must be triggered, after the file has been deleted.
      Secondly, the Load Scenario connection is going to be triggered, since the selected scenario changed (from something to [blank]). For this reason there is a formula in the cell that defines the XML file to load, which will show blank if a valid scenario isn’t selected, so the connection fires, and fails quietly as described previously. If that doesn’t make sense, have a look at the formulas and all should become clear. The comments section is waiting for anyone that has a more elegant solution to handling this!

      The jsp File

      The file is called DeleteScenario.jsp and is saved in C:\Program Files (x86)\Business Objects\Tomcat55\webapps\Xcelsius
      This code is quite simple. It just takes the name of the scenario that is sent to it and deletes that file, then sends a blank response.

      <%@page contentType="text/html" pageEncoding="UTF-8"%> <%@page import="java.io.*" %> <% // Build a string from the input     request.setCharacterEncoding("UTF-8");     BufferedReader urlLine = request.getReader();     String xmlString = (String) urlLine.readLine();     while (urlLine.ready()) {            xmlString += (String) urlLine.readLine();         }     response.setContentType("text/xml");
      //Which File to Delete     String scenarioPath = request.getRealPath("/") + "SavedScenarios/";     int startPos = xmlString.indexOf("variable name=") + 15;     int endPos = xmlString.indexOf("\">");     String fileName = xmlString.substring(startPos, endPos);
          File fileToDelete = new File(scenarioPath + fileName + ".xml");
          if (fileName.length() > 0) { fileToDelete.delete(); }
      // Send a response     String responseXML = "<data>";     responseXML += "<variable name=\"Response\">";     responseXML += "<column></column>";     responseXML += "</variable>";     responseXML += "</data>";
          PrintWriter rsp = response.getWriter();     rsp.write(responseXML); %>


      OK, so that’s about it. Yes there’s a lot to take in, but once you’ve got an understanding of all of the parts, it’s only a few hour’s work to get all of this up and running, and a small amount of work to get it writing directly to a database (for those with better Java skills than me).
      Hopefully you can use the above to adapt for other uses, such as the ability for users to save a comment to a dashboard or change a KPI. Remember that you can have one dashboard used to write the XML files, and a second dashboard to read them. This then gives you the ability to lock down permissions.


      I'll be honest, there's a few reasons that may steer you away from this technique, so in the spirit of full disclosure…
      • You'll be saving data on a web server. This is bad practise and not as secure as saving data in a backed up database. Someone with Java skills could easily tweak to code above to write to a database rather than just drop the xml files on the server.
      • There is no security within the dashboard. That is, any user of the dashboard will be able to add/remove saved scenarios.
      • You’re using custom code that is not supported by SAP (or me, sorry). There is no guarantee that this will work in future versions.
      • Upgrades (even just a fix pack) will usually wipe the tomcat directory. That would wipe the scenarios and the files used to create them.


      Ryan Goodman's well-rounded explanation of some options:
      Centigon Solutions has a nice plugin that allows you to save to csv:
      ClearPeaks have a fairly simple solution that allows a user to generate an Excel file from a button on the dashboard: