Welcome to the OPA Hub!


Category Archives: Connector Framework

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

CheckAlive

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.

GetMetadata

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.

Oracle Policy Automation and Siebel Innovation Pack 16 #6

Oracle Policy Automation and Siebel Innovation Pack 16 #6

Oracle Policy Automation and Siebel Innovation Pack 16The final post in this series looks at some of the “extras” that facilitate the integration of Oracle Policy Automation and Siebel Innovation Pack 16. By “extras” I mean other Web Services provided by Oracle Policy Automation, which will need to be taken into consideration when designing how these two applications can best work together but that are not directly related to the subject of getting the two applications to integrate using Applets, Integration Components, Workflow Processes and so on. Some of the content that follows is license-dependent, but should be of interest to any Oracle Policy Automation person.

Overview

Given that there are a number of different services to review, this post therefore is necessarily a mixture of many things. To summarise, there are

  • Administrative Services : The REST API of Oracle Policy Automation allows the creation of users of all the main types (integration users as well as normal ones) and also for the automation of deployment, and retrieval of associated information.
  • Execution Services : Assess, Interview and Answer (and the Server service, although it does not really need to be covered here).
  • Batch Execution Service : The REST API for Batch Execution allows for batched execution of goal determination

Together these are referred to as the Determinations API. The API is version specific in the sense that features are constantly being added (for example, integration user management is new to release 18A) so make sure you are using the correct WSDL file. For specific Oracle Policy Automation rulebases you can download the WSDL easily and that is shown in the videos below.

Assess Service

The Assess Web Service is probably the most famous service from a Siebel developer perspective, since it allows Siebel Enterprise to call Oracle Policy Automation and obtain an XML response (in the manner of a typical SOAP Web Service). It is often used therefore when no user interface is required.

The above video provides a short overview of how to derive the necessary information from Oracle Policy Automation and to use it in standard Web Service fashion. Developers should note that the post-processing of the Response will most likely occur in a Siebel Workflow Process or Script, in order to parse the response and deal with it.

As such, accessing an Oracle Policy Automation rulebase with Assess can be done very simply indeed. If the Oracle Policy Automation rulebase you are working with has a Connection in it (to Siebel or anything else) then you may also wish to use the Answer Service (see below).

Interview Service

The Interview Web Service was heavily used in the Oracle Policy Automation and Siebel Innovation Pack 15 integration, in order to mimic the behavior of the standard Interview using the Siebel Open UI framework. This Service is best suited to applications needed to provide the Interview User Interface in another technology (a Java application, a Silverlight Client, a Visual Studio application or whatever). It has a number of specifics and developers must manage session control, as the short video below illustrates.

Answer Service

The Answer service is reserved for Projects where there is a Connection object in Oracle Policy Automation, and as such provides a SOAP-based tool to pass data sets to the Project and receive the response. Amongst other things, therefore, it can be used to test the behaviour of an Oracle Policy Automation project when the external application (for example Siebel Enterprise) is not available.

REST API Services

As outlined above, there are in fact two REST API areas of interest : the administrative platform and the Batch Assessment service. Both require OAuth2 authentication and session management.

What’s Left to Do with Oracle Policy Automation and Siebel Innovation Pack 16?

So what is there still to do, for the Siebel Developer who has followed all the different posts and videos in this series? Well of course it is not possible to show everything, so here are the main points that you will now need to finish on your own : but most of them are entirely non-specific to Oracle Policy Automation and Siebel Innovation Pack 16.

There are of course many different things that you might want to do with Oracle Policy Automation and Siebel Innovation Pack 16, so at the OPA Hub Website we are always happy to hear from our readers with comments and questions : all you have to do is post at the bottom of the article. We obviously cannot run your project from here (but if you want us to, just get in touch!)  but you should feel free to contact us with questions, ideas for articles or anything else that is Oracle Policy Automation-related.

As Siebel Developers will know, Siebel Enterprise is now in version 17 and the next big thing, Siebel 18, is expected soon. The good news is that almost all of the steps shown here are completely identical in the newer version, since the changes are architectural rather than functional for the most part. If you come across anything completely different then, again, just let us know. We do plan on providing an update to this post series as and when the Siebel 18 is made generally available.

Finally

The OPA Hub hopes you all enjoyed the different posts in this series. For your bookmarks, here are the other posts in the series:

OPA 12 - Oracle Policy Automation and Siebel Innovation Pack 16 Checkpoints 2

Oracle Policy Automation and Siebel Innovation Pack 16 #5

Oracle Policy Automation and Siebel Innovation Pack 16 #5

So, following on from the previous post in this series, where we looked at testing the Load and Save operations using Oracle Policy Automation and Siebel Innovation Pack 16 (as opposed to simple SOAP UI testing which is good, but will only get you so far), this post takes a slightly different turn and investigates two operations that are not strictly speaking required to be implemented.

The definition of the Oracle Policy Automation Connector Framework contains a boolean tag as to whether checkpoints are enabled in a given Connection. And these checkpoints are the subject of this post. Firstly, what is a checkpoint?

A checkpoint is a point in an interview, after which the contents of the Screens (Controls, for example data you have entered) is saved in a specific format, namely as an encoded Base64 string. This string of course needs to be saved somewhere : for example in a table in your Siebel database. Once it is saved, it can be used to open the Interview once again, through the integration between Oracle Policy Automation and Siebel Innovation Pack 16, and the session can be resumed. Obviously this has a great advantage of being simpler than trying to save all the data you have into Siebel Business Components, especially given that the Interview might not be complete yet.

So you can think of checkpoints, and their two operations SetCheckpoint and GetCheckpoint, as sort of temporary saves. When you save the checkpoint you do so with an identifier (so, an id as in previous operations). But the process of SetCheckpoint and GetCheckpoint is completely separate from Load and Save : they are two different mechanisms to handle two different business needs.

Here is a screenshot of what it looks like residing in a Siebel Table, which you will learn more about in the videos and presentation:

Oracle Policy Automation and Siebel Innovation Pack 16 - Checkpoints

The use of the Siebel Row Id means that it is relatively simple to create an Applet that sits on top of the Business Component, because you might use it as a Child Applet with the Obj Id Val as your key to finding stored sessions for your Customer or whatever it is.

The usage of these stored sessions requires a slightly modified URL to open the Interview, which you will learn about in the videos as well. In both cases (starting or resuming an Interview) a Symbolic URL, or a JavaScript embed, will be enough to call the Interview from the Siebel side.

From the Design perspective, implementing Checkpoints in your Screens is very simple, assuming you have selected a Connection that supports them. For example, the screenshot below illustrates the options available when designing the Interview. Note how you can select the relevant Screens or all of them. Selecting all Screens ensures that the Base64 string is pushed to the storage table after each Screen.

OPA 12 - Oracle Policy Automation and Siebel Innovation Pack 16 Checkpoints 2

Now that you have the details of this new part of the Oracle Policy Automation and Siebel Innovation Pack 16 integration, here are the videos to help you go further, and the links to the other parts of the series.

In this topic, learn about the two optional (but very useful methods) called Get and SetCheckPoint. This presentation explains the prerequisites and pitfalls.

Presentation

In this topic, learn how to implement these methods in Siebel, build them into your Oracle Policy Automation Project and how to test and verify their functionality.

Implementation

Links to Oracle Policy Automation and Siebel Innovation Pack 16 Series

Next…

In the next part of this series we will look at other Services available to Siebel developers in Oracle Policy Automation and Siebel Innovation Pack 16.

Oracle Policy Automation and Siebel Innovation Pack 16 #4

Oracle Policy Automation and Siebel Innovation Pack 16 #4

Welcome back to part four of our ongoing series about Oracle Policy Automation and Siebel Innovation Pack 16 . This post continues with the setup and testing that began three posts ago. For reference here are the links to the previous parts of the series:

Oracle Policy Automation and Siebel Innovation Pack 16 Load and SaveThis particular article continues working on the core data transfer operations, namely Load and Save. I also have a tendency to call the Save operation Submit, because it reminds me that not only must the request be submitted to Siebel to save any mapped out data, but a response needs to be sent back from Siebel to Oracle Policy Automation to, for example, display a message in Oracle Policy Automation confirming that the save was a success (or whatever).

This need for a two step approach (Save in Siebel and Respond to Oracle Policy Automation) means your Workflow Process is likely to have both typical Siebel Operations to update the database but also typical transformation and response creation like the previous operations.

The example Workflow Process for Save will require, therefore, quite a bit of work before it is fully functional. In the video I try to highlight this, but it is worthwhile mentioning the key issues again here:

  • You will need to extract any data from the hierarchy sent to your by Oracle Policy Automation
  • You might well need to use scripting if the hierarchy you receive has multiple entity instances (for example, the Oracle Policy Automation Project infers multiple vehicles and you want to save each of them in Siebel).
  • You will need to make sure that you create a Response that updates one of the input mapped, load after submit attributes to show it in the Interview.

In this video which follows on from the previous set of SOAP UI tests, build and troubleshoot your Save operation with Siebel CRM to check for errors. There are lots of places where you will need to put in a bit of work on the example Workflow Processes (since they do not actually save much at all) and more complex (and therefore more interesting) business requirements may require a Business Service approach, namely to iterate through multiple instances of data returned to Siebel.

Whilst the videos cannot give you all the details, they definitely will put you in the right direction!

Oracle Policy Automation and Siebel Innovation Pack 16 Load and Save Testing in Siebel

Remember you can find the White Paper and associated files  (at time of writing) at this Oracle Website location.

Next…

In the next part of this series, we look at two supplementary operations, GetCheckpoint and SetCheckpoint : whilst a Connection does not have to support these operations, if you plan on allowing users to stop and resume their interview before it is finished then you definitely need these operations. See you next time!