Excel as an OPA Data Connection #2

So, following on from the previous post in this ” Excel as an OPA Data Connection” series, we have been investigating using Excel as both the Metadata and Data storage for a prototype Connection. The goal being that if I want to quickly model something and show it to a prospect I can do everything in Excel, and do not need any other software.

The previous post laid out the architecture, and highlighted that we would be putting the Excel on a Web Server and using a Web Service to open and manipulate the Excel file. Once again, this is just a bit of an interesting concept and is not at all for real-life use, especially as Microsoft has explicitly stated that remote automation with Microsoft Office is not a good idea (nor is it supported).

Excel as an OPA Data Connection : Common Points

In our Web Service code, the three main endpoints (GetMetadata, Load and Save) have a great deal in common. They all manipulate either DataTables (Load and Save) or MetaTables (GetMetadata) and in the case of DataTables they have Row(s) and DataField(s) whilst the MetaTables have MetaFields.  Essentially your job is to build these into a hierarchy, and they form the response that is sent back to Oracle Policy Automation.

In the screenshot below, the Excel data range is being parsed into a series of Metafields for the GetMetadata response, and we are setting the different tags of our SOAP response. I took a shortcut in the prototype and no matter which metadata you ask for you get all the fields back in the response.

 Excel as an OPA Data Connection GetMetadata

Excel as an OPA Data Connection : Context

In the case of the Load and Save, they receive either loadrequest.context or saverequest.context which will contain any URL argument items you want to pass into the Service (for example, in a Load, you will pass an ID which will correspond to the row of data you want). You can pass in as many as you want.

In the screenshot below, you can see that both the Load and Save have the context object available. In the code, since it is a prototype, we assume the only context element is the one we are looking for.

 Excel as an OPA Data Connection Context

Excel as an OPA Data Connection : Load and Save

In the case of a Load request, you will send back the Tables and Row(s) and Fields for the Interview. In the case of a Save request, since we are only handling updates in our prototype,  we send back the Row and Fields as the response, marking them as “input” fields. We are not using Load after Submit in our case, so there are no “output” fields to send back. If we were to handle inserts, we would have to send back the Row and Fields in that case as well, plus the ID of the new record for example in a Load after Submit.

In the screenshot below I am testing the Load Response, hard coding some values into the fields. Note the “TEST” value at the top. In the finished version of course, these are replaced with Excel Cell contents.

 Excel as an OPA Data Connection Load Example

Since the entire Web Service is written in Visual Basic, it is a great learning opportunity to be able to dump the XML requests and responses to files, in order to better understand what Oracle Policy Automation is sending or receiving. In the screenshot below, a Save Request being sent to the Web Service.

 Excel as an OPA Data Connection XML Example

Excel as an OPA Data Connection : Attribute Types

One area where you can get quite confused is the different ways to indicate what kind of attribute you are working with. In the request for a save, for example, there are field types for each attribute which are actually constants (0,1,2,3,4 and so on, one for each type). When building the save response I needed to then map that number to an AttributeTypeEnum (another constant value) and set the ItemElementName to get the “<date-val>” or “<text-val>” tags that you need. It took a while to work out the logic. Between MetaFields that say they are “STRING” and DataFields that say they are “text-val” it can get a bit boring!

Here’s the sort of thing I mean, checking the fieldtype and mapping to the AttributeEnumType:

Excel as an OPA Data Connection : Video

Rather than keep on writing, let’s have a video to put it all together and see the adventure in the flesh, doing what it is supposed to do!  This was great fun, and is definitely a cool way to learn more about the Connector API and what would be needed when building an integration. Of course, these days we have Integration Cloud and so many more managed tools and services but I am of the opinion that it is better to be over-informed than under-informed. Speaking of which, if you need to read the official Connector API Overview, you will find it here.

Of course there is more that could be done. As described above, the first thing will be to implement record creation. Then, perhaps, a child table or two using the same basic principle. Who knows, one day when I have more time I might come back to it.

If anyone wants the Visual Studio Project and code, then just leave a Comment. Have fun!