This is a scenario where a Insert event in SQL server causes the table data to be published. The Oracle resource forms the target for the document published by the SQL server adpater.
SQL server=> Oracle
The operation also involves filling in some of the fields to be cross reference from the oracle target for some of the document values.
I am able to perform this operation technically. But i am not sure of how a canonical structure shoud be.
What should be the criterias to be taken in to before building the canonical Document.
My assumption for the Following Scenario
SQL server Table has the following columns from “Table_A”.
Employee ID
Salary
salary_month
and this data is to be transformed to a table “Table_B” in oracle with the following Columns :
EmployeeID
EmployeeName
AGE
4.Salary
salary_month
The columns EmployeeName and Age will be evaluated by cross -referencing another table in Oracle “Table_C” where the employee details are stored.
My assumption is the document shoud represent the target system.
By having the following in the document:
EmployeeID
EmployeeName
AGE
Salary
salary_month
How is the canonical model to be implemented for this type of scenario
I am not able to get into terms with this.
Could somebody throw some light on this.
Hi Ravi,
Your Canonical doc should be a superset of the attributes you are trying to keep in SYNC.
It might happen you have some extra attributes in source system which you are not going to be used directly in target side but they will be used for XREF purpose or other things.
e.g.
You have employeeID and employeeType in the source. And you have only employeeID in target.
You might want to concate source employeeID and Type to populate employeeID in target OR you want to concate these two alongwith a sequnce#.
So it is better to carry both source employeeID and Type in the canonical doc which will give you a great flexibility to put your business logic in the target side before you insert it.
From my experiences-
If XREF is going to happen only in target side (as in your case), then carry all the required source system attributes and publish as canonical doc. Once your target system subscribes the doc, do the required mapping and populate the target side doc (specific to your target system) and do XREFs and use the target side doc to pupulate your target table.
Which will be helpful when You add more target systems to your intergration (as mapping and XREFing could be different for diff systems).
So you do different mapping for different target systems withour disturbing your source components.
In your case you need not carry attributes like employeeName and Age in your canonical events as they are never going to be populated by the source side components.
Canonical documents are intended to be application independent. Ideally there would be no application-specific codes, no application-specific constraints on field contents, lengths, etc. In other words, define a PO/employee rec/invoice/etc. as the company really wants to define it, in it’s own terms, not the terms that one of the specific applications has. Then map the apps to/from this definition.
In practice, the canonical is usually based on the most prevalent/dominant system and extended from there to accomodate additional needs.
The Canonical document is idependent of the implementation System. Its Structure should form a generic document for the Business.
Then the canonical for the Employee Record could be PayRoll with more then actual transactional fields. Some implementation will use the field and some will not.
I that case i have a Query if i am using a canonical that is being used for more then one implementation.
I generate a PODetail canonical according to the Business requirement of the Organization and the canonical is used by more then one integration.
i.e Integration 2, Integration3 are subscribing for the canonical under different scenarios.
Reads a flat file and creates a canonical document.
Integration 2: (Oracle Server)
Subscribes for the canonical and does some XRef on some fields
Integration 3: (SQL Server)
Subscribes for the canonical and updates the document to the Backend system.
As the Integrations 2 and Integration3 use the same canonical should a flag for the target integration also be included in the canonical . So that even if the Integrations 2 and Integration3 subscribe for the same canonical by conditional evaluation of the field from the canonical the processing sequence be controlled.
Ravi,
You are correct, if you do not want all the published events to be inserted in every target systems. In this case, you need to carry a flag in your canonical DOC based on that your target integration can decide whether to use the event or not.
e.g. one particular type of PO you want to load in SQL server and rest you want to insert in Oracle system.
In this case, the same canonical is subscribed by both the integrations. And inside your integrations you have a check for that flag whether to use the event or not in that particular integration.
This is probably a matter of terminology but I would think one would want to avoid generic flags that indicate intended target. Instead, one should probably use document state field(s).
Presumably, in a chain of processing that Ravi describes, each step is performing some action that changes the state of the business document being passed around. Thus a field carrying this state value would be advisable (e.g. new, approved, stored, rejected, etc). For the scenario Ravi describes, perhaps a filter on the presence of a key XREF field in the document would do the trick.
I guess my real point is to try to avoid using target specific flags if possible and try to use document state to control routing.
Quick question from a webMethods novice… After some data analysis I defined a few canonical documents (as a ;proof of concept;). What’s the best or fastest way to create these in the Integration Server (preferably I’d like to use a GUI/wizard) or something.
If you are using the WmEnterprise package, the Integration Server will convert the canonical documents existing in an Enterprise Server environment into Integration Server Records.
You do not need to define the canonical (also referred to as UDM and Document) in the Integration Server environment.
If this does not answer your question, please describe the task you are facing and we’ll see if we can get you the help you need. Thanks.
What would be the best practice to include event-type (e.g. insert,update,delete) information attributes in the canonical document?
Example: A canonical document is made up of the following fields:
employeeID
employeeName
salary
Event flow:
new employee record created (insert)
employee name changed (update)
salary changed (update)
employee record deleted (delete)
Currently we use a single action flag for all attributes. This works well for insert and delete but for update we do know that something changed but we do not know which value (employee name or salary).
This is only a simple example. It is even more funny with complex tree structures. Currently we include an action for every node.
We might implement a different approach where we do not include action flags but carry a “business action” in the canonical document. E.g. “record created”, “name updated”, “salary updated”, “record deleted”. With this solution a single “business action” field can contain all the information needed even for complex documents.
What do you think. What are your solutions to the problem?
I’m not sure there’s actually a best practice for determining what has changed in a document. I’ve seen it done in a variety of ways, including an action flag on every node and the “business action” approach. The most common method seems to be to include old data and new data both when an update occurs, and do a comparison to see what has changed.
My favorite is to include the action flag for each data element that you might be interested in monitoring. Then you can filter your canonicals based on which flags were updated.
I have a xref table, when i insert data in source table, i need to get the canonical id for the source from the native id and thereafter the native id of the target. what will the flow services i need to implement and how.
thanks and regards