Extending SimCorp Dimension

There will naturally come a time when there is functionality that is outside the scope of SimCorp Dimension. This could be an interface that is unsupported or a format that SimCorp Dimension does not natively support. In this blog post I will use the example of saving extracted data in an Excel spreadsheet; that is an xlsx file and not simply a csv file. The traditional way of exporting to Excel in SimCorp Dimension would be through Crystal Reports, but I’d like to explore a more general way of extending SimCorp Dimension, no matter what the business requirement might be.

My assumption here, is that Visual Studio and the Communication Server are available and able to be used as part of the final solution. My requirement is to transform data extracted from SimCorp Dimension through the Data Extractor and save the data in an Excel spreadsheet (xlsx file). To avoid having Excel installed on servers in order to achieve this goal, I have opted to use OpenXml which is an open format of the Office file formats developed by Microsoft. Make sure you download the correct version of OpenXml as different versions support different .NET Frameworks.

Two obvious candidates spring to mind when extending SimCorp Dimension to be able to save data in an Excel spreadsheet. The first being a Console Application called from the Communication Server through the Cartridge statement “execute”, the other being a Web Service called from the Communication Server through the Cartridge statement “webrequest”. The Web Service I will demonstrate in this post is an old fashioned asmx Web Service. I have not been able to make an ASP.NET Web API work through the Cartridge statement webrequestcustom, hence the asmx Web Service (an ASP.NET Web API may or may not be supported in SimCorp Dimension).

I will be extracting my data in SimCorp Dimension and then using message type “Extract data - Point-2-Point” in the Communication Server Setup in order to direct my data to the Communication Server. Depending on the volume of data being sent from the Data Extractor to the Communication Server this may happen in a single or multiple blocks, which can be determined from the two attributes “first” and “last” in /Extract/Data/Parameters/Block once received in the Communication Server. Both attributes are set to either “yes” or “no”, so some logic will have to be in place to control this, e.g. adding the data to a file or persisting the data before converting the whole data set once the last record has been received in the Communication Server.

For a Console Application I have setup a simple example with the following syntax.

txt2xl [-f|--file] source -o|--output spreadsheet [-h|--help]

The assumption is that data has been saved in a tab delimited file above and that the program will be called as follows from the Communication Server.

<execute parameters="-f @var(textFile) -o @var(xlFile)" executable="txt2xl.exe" exitcode="ExitCode" />

The data variable ExitCode can then be used to further control the logic in the Communication Server once the Console Application has executed. An outline of the Console Application can be seen below.

private const int NO_ERROR = 0;
private const int ERROR = 99;
 
static int Main(string[] args)
{
    try
    {
        Switches command = new Switches();
 
        command = Parse.Command(args);
 
        // Has the user asked for help
        if (command.Help.IsSet)
        {
            Console.WriteLine(command.HelpText);
        }
        // Convert file to Excel
        else
        {
            Convert.File(command.File.Value, command.Spreadsheet.Value);
        }
 
        return NO_ERROR;
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
 
        return ERROR;
    }
}

The disadvantage of using a Console Application is that data will have to be stored in some form or other before executing the program, a tab separated file in the above example. Better then to use a Web Service, where data can just be past on. Handling multiple blocks, as described above, could even be handled by the Web Service, simplifying the logic in the Communication Server.

Because I am creating an asmx Web Service there is a limit to the .NET Framework I can use. .NET Framework 3.5 being the highest. As a result I will also have to make sure that OpenXML is at a version that can use .NET Framework 3.5, which is version 2 of the SDK https://www.microsoft.com/en-us/download/details.aspx?id=5124

If, like me, you are using a newer version of Visual Studio (2015/2017), you will need to set the .NET Framework to 3.5 when creating a New Project. In Templates, select Web and then you should be able to select a type called “ASP.NET Empty Web Application”. I have named my solution SCDWebService. Once the new ASP.NET Empty Web Application has been created, add a new item by right clicking the SCDWebService project, select Add and then New Item… Now select the item “Web Service (ASMX)” from the list and name the new item SCDWebService.

I can now implement the same logic as in the Console Application in order to convert and save the data as an Excel spreadsheet.

[WebMethod]
public bool SaveXL(string content, string spreadsheet)
{
    return Convert.Data(content, spreadsheet);
}

As long as the Communication Server is running on the same machine as you are using to develop your Web Service on, testing the solution is straight forward. Simply run the service from within Visual Studio. My Communication Server config will need an entry to my new Web Service.

<WebRequest Name="SCDWebService" Endpoint="SCDWebServiceSoap" ServiceDescription="http://localhost:<port>/SCDWebService.asmx?WSDL" RecallOnError="true" />

Change the <port> above in the ServiceDescription to whatever number comes up on your machine in the browser. Set up two variables in your Cartridge, one for the content you are passing to the Web Service and one for the name and path of the spreadsheet you want saved and then simply call the service as follows.

<webrequest port="SCDWebService" operation="SaveXL" output="WebServiceResult" initBeforeCall="true" >
  <argumentvar name="content" input="content" />
  <argumentvar name="spreadsheet" input="spreadsheet" />
</webrequest>

That concludes this post on how to extend SimCorp Dimension through either a Console Application or a Web Service.