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:
- Use a Static Metadata Connection
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.
- Mantis OPA DB Connector
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.
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
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:
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.
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.