Tag: Excel

Excel as an OPA Data Connection #2

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!




Excel as an OPA Data Connection

Excel as an OPA data Connection

In the English-speaking world there is an expression about the month of March that I like very much : “Mad march hares” (referring to those big goofy rabbits-on-steroids that start running about and looking for a partner right about now).

Well, we have been engaged in some madness here at the OPA Hub Website this week too.

It starts with a simple statement. We like to do demonstrations, and we often need a Data Connection to be able to do a proper demonstration “end to end” so to speak. And the scenario of the demonstration can change every day as we do things for different customers. So we are always looking to find ways to make it as easy as possible to do that. Let’s look at some of the options:

This feature allows us to create “fake” Connections simply by copying and pasting a template GetMetadata Response into the dialog. Nice, but this only handles the basic modelling and mapping experience. There is no data.

Readers will know that we are great fans of this product, and Mantis are also sponsoring our Website this half year (full disclosure). It should be evident from the videos we’ve done that we love it. But it requires a database, and that takes a little time to set up.

  • Excel as an OPA Data Connection

Yes, well, I will admit I was sceptical when I thought about it. But why not? The big advantages are obvious : if we have the metadata in the Excel, we can change field names and table names in 2 seconds. And creating test data is as easy as copy and paste. And you already have Excel on your machine!

Let’s think about the basic mechanics of how this works. I am afraid that this solution is easiest  to implement if you are in Private Cloud. Public Cloud is possible but it takes a bit more work. A local installation (where you have a local Virtual Machine for example) is also possible. We decided to go for the Private Cloud solution for our proof of concept. What do we need?

  • A Web Server – in our case Internet Information Server.
  • A Web Service – in our case written in Visual Basic as an old-fashioned ASMX file.
  • Microsoft Excel – accessible to the Web Server.
  • Oracle Policy Automation Hub – that can access the Web Service URL mentioned above.
  • Oracle Policy Modeling – that can access the Oracle Policy Automation Hub mentioned above.

Great Learning Opportunity

The great thing about doing something like this, however crazy, is the learning experience it gives us. If you want to get down and dirty to understand the nuances of DataTables, UpdateTables and MetaTables and all the other myriad structures in the Connection API, this is the way to get to grips with it – line by line and XML dump by XML dump.

Before going any further, yes, we know it is definitely not supported to do remote automation with Microsoft Office but this is a proof of concept! There will inevitably be some setup challenges to ensure that the Identity used by your IIS Application has also access to Excel, and you probably will spend a fair amount of time staring at DCOM error messages. We used this article as our debugging tool.

Starting Out – Getting the Stub Code

In Visual Studio, the best way to leverage the standard Connection API WSDL is to download it from your OPA Hub Connections Detail Page. Drill down on an existing Connection and find the download in the Actions menu.

Excel as an OPA Data Connection 1

Then you can use the Microsoft SD tool wsdl.exe to import a WSDL (like the one you have just downloaded) and turn it into a bit of stub code. You can generate it in Visual C# or Visual Basic, using a command line like this one:

WSDL.exe [/language:] [/protocol:] [/namespace:] [/username] [/password] [/domain] [/out:] <url or path>

Language can be VB, VC or even JS. Once you have your file generated, then you can create a new Web Service Project in Visual Studio (Community Edition or a more professional one if you have it). At this point you should have all of the standard service endpoints (CheckAlive, GetMetadata and so on) all in the code marked up as not being implemented. If you deploy your Web Service, sure you can call it with Soap UI but nothing will happen.

As you probably know, the basic set of endpoints for a useful Connection are

  • CheckAlive
  • GetMetadata
  • Load
  • Save


These will be our target for the prototype. CheckAlive is super simple since it returns an empty CheckAlive response. This is the trigger for the lovely green tick mark you see in the Connections list. If the web service responds with a CheckAlive response, you get a green tick:

Excel as an OPA Data Connection 3

The code is two lines long, thanks to the stub we imported earlier.


Obviously to be able to use the Connection, we need to be able to Model our data source. So the Excel spreadsheet is going to contain two tables. One for data, one for metadata. To keep things simple, the Excel spreadsheet just has two named ranges, and a place to name our object:

Excel as an OPA Data Connection

For our proof of concept, we are going to assume that the user of the Excel sheet remembers to keep the metadata and data synched (if the name of a field changes in the metadata, then the headings need to change in the data table). Not very hard to remember! The data table can contain as many rows as you want since it is just a named range.

Now that we have this structure built into the Excel, we need to implement the GetMetadata endpoint, so we can use these fields in our Modeling.

The obvious thing is to read the metadata from Excel, and then format it into a GetMetadata Response. And it works. The IIS Web Service opens Excel (!) and reads the information before formatting it and sending it back.

Excel as an OPA Data Connection 6

Since this is running from the Web Server, we can access this Connection Web Service from anywhere, and nobody would know there is an Excel spreadsheet behind it. Edit the Excel, change the field names and data, refresh the Connection and ready to go!

We’ll look at the Load and Save endpoints, as well as discussing more about the actual mechanics in the next post.

Conditions & Conclusions in Oracle Policy Automation Spreadsheets

Conditions and Conclusions in Oracle Policy Automation Excel Spreadsheets

Since this came up in the recent Oracle Policy Automation discussions I was having with a customer, it seemed a good idea to summarize it for people who are Getting Started with Oracle Policy Automation. There are two basic ways to use the column headings in an Excel file.

Using Column Headers with Condition or Conclusion Text

This is the usage that is taught first in the Oracle Policy Automation Essentials training. Using a small fragment of a fictitious speeding fine table, you can see the Condition Heading and Conclusion Heading formats are populated with Attribute Text.

Oracle Policy Automation - Using Table Headers with Attribute Text

Using Generic Column Headers

The second choice is to use generic headers, in fact these are the default headers inserted into the Excel file when you add it to your Project. In this example below you can clearly see the condition and conclusion texts in the relevant columns and the generic headers. This can be useful if you want, for example, to assert that different booleans are true as a result of a table row being true.

Oracle Policy Automation - Condition and Conclusion Generic Headers

Excel Limitations

There are some limitations which can occur when you are unfamiliar with Excel spreadsheets. Although these are not both directly related to generic column header usage I thought I would add them here for new starters to remind of the importance of using multiple columns where appropriate.

Oracle Policy Automation - Generic Conclusion Attempted Negative Error

As you can see in the example above, the use of a negative sentence in the generic conclusion is not allowed. We would have to reformat our table to use different, specific columns for our conclusion.

Oracle Policy Automation - Non Generic Conclusion Examples

Similarly, attempting to squeeze too much into a condition column will create another validation error:

Oracle Policy Automation - Generic Conditions Attempted Multiple Conditions

Hopefully this helps clarify what is possible, and what will need to be considered when getting started with Oracle Policy Automation Excel Spreadsheets. The official documentation can be found online here.

Have a nice day!

Logo by Southpaw Projects LLC