|
|
Rank: Member
Joined: 10/5/2010
Posts: 2
|
The SData specification is a little ambiguous regarding this and there is no example XML of how you practically sync an association, i.e. the contents of the payload. (that I am aware of) There are a number of possible options, one is that the associations could be represented as a list of reference types and ID’s, or could just be ID’s - <association name= blah blah> <name = SalesPerson UUID = 2333243223423 someprimacyflag = true> <name = SalesPerson UUID = 2333243223423> <name = SalesPerson UUID = 2333243223423> <name = SalesPerson UUID = 2333243223423> <association> Or that the association would be a link which would be called from the target app, this would be very inefficient though as it might mean another X number of http requests to sync an account. For me the problem is that the SData specification itself describes the relationship represented by an association as an arbitrary one, which can mean anything. I could be wrong about this, but I get the impression Gobi and SLX have implemented associations on the understanding that the associated record has already been synchronised, and that the associated record always contains a link back to its associates? So nothing is sent in the payload of an Account for its associated relationships? That is however only one type of relationship. From what I can see in the Schema we are using the “association” type of relationship to represent what is really a series of multiple foreign keys, or references, in a relationship that is not bidirectional. So as an example use case – You have accounts in your CRM system. In CRM they all have one tax code only. In ERP X they have 5. In the UI in both applications there is a dropdown list to allow a user to select one / or a multi select field in ERP. The tax codes are synchronised to a table in CRM. In the CRM database this is a single foreign key on Account to the tax code table mentioned above. The tax code does not know about, or have any relationship to the account. The relationship is one way. We would never sync the thousands of account ids associated with this tax code as part of the tax code sync. A new account is created in ERP, it is sent to CRM - What is in the XML for the taxcodes node which is an association list?
|
|
Rank: Member
Joined: 10/5/2010
Posts: 2
|
I think there are two specific examples we are trying to solve here:
1) Associations that are 1-many relationships.
Example SalesOrder. You can have multiple SalesOrders for a single tradingAccount but a SalesOrder can not be associated with more than one tradingAccount. In this case, the relationship should be included in the SalesOrder feed as such:
<salesOrders ....>
<tradingAccounts uuid.../>
.
.
<salesOrders/>
2) Associations that are many-to-many relationship.
Example taxCode and salesPerson. These can be associated with multiple resources of the same or different types and can have multiples within on resource. Example: tradingAccount can have multiple taxCodes associated with it and the same taxCode can be associated with multiple tradingAccounts. This relationship does not belong in either resource, but should be its own resource that simply maps the two resources together. That is standard practice for this type of relationship.
|
|
Rank: Member
Joined: 11/18/2009
Posts: 16
|
Here is how I would handle this:
1) If the relation is 1 to N, for example tradingAccount (source) to salesOrders (target), there is a reverse relationship, of type "reference" (the reference from any sales order to its trading account). So, the association is updated when we update the target (the salesOrder in the example). We don't need a mechanism to update the association together with the source.
2) If the relation is N to M, we have two cases
2a) there is an intermediate "association entity" which is exposed as an SData entity.
2b) there is an intermediate "association table" but it is not exposed as an SData entity.
Case 2a) brings us back to case 1) because the full association can be decomposed as follows:
source -> (1 to N) -> association entity -> (M to 1) -> target
So, the proper way to update the association is to update the association entity. The association entity has two "reference" relations: one to the source and one to the target. For example with taxCodes and salesPersons, we have an intermediate "salesPersonTaxCodeAssignment" association entity. Each salesPersonTaxCodeAassignment resource references 1 salesPerson and 1 taxCode. So we update the association by updating the salesPersonTaxCodeAssignement resource.
Case 2b) is the only one that the really causes problem and that the SData specs does not handle yet. I think that we first need to distinguish two things in this association:
i) the association itself, i.e. the list of references to target resources (the list of tax code ids)
ii) the target resources referenced by the association (the tax codes themselves).
I think that an SData POST or PUT operations should modify the list (i) but not the target resources themselves (ii).
This is consistent with the way we handle references (an SData PUT can modify the reference and make it point to a different resource, but it cannot modify properties of the referenced resource).
If we want the POST or PUT to modify both the list and the target resources, then we should define the relations as a parent/child (sme:relationship="child" sme:isCollection="true").
This implies that the target resources have been synced separately and that we only need to pass the list of keys (or urls) when we sync the source.
I think that this is pretty much in line with Paul's reply, except that Paul does not address case 2b)
Bruno
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
I think we are making things way too complex here - introducing association entities/tables everywhere make our (already complex) schema even more complex. For the sake of simplicity we need a better solution. So here's another suggestion..... This assumes resource-kind sync, not resource instance sync (described later)..... For case 1 in Bruno's example, when the first resource (tradingAccount) is synced it contains a list of associated (salesOrder) UUID's (correlation is always by UUID -> http://interop.sage.com/daisy/sdataSync/Link/512-DSY.html). These UUID's won't point to anything as the second resource (salesOrder) does not exist yet. However they will point to something once the second resource (salesOrder) is synced. Additionally the second resource (salesOrder) will only contain 1 referenced UUID (that of the tradingAccount). This process works irrespective of what is synced first (salesOrders could be done before tradingAccount). The situation for case 2 in Bruno’s examples is the same (or maybe even easier). We would sync the first resource (tradingAccount) containing a list of associated UUID's (salesPerson, taxCode, priceList). If the associated resources don't exist yet it does'nt matter. The UUID's will point to something once the subsequent resources (salesPerson, taxCode, priceList) are synced. Additionally the subsequent resources do not need UUID's to the referencing resource (tradingAccount) as this is not in the schema. Again this process works irrespective of the sync order - salesPerson, taxCode, priceList can be done before or after or around tradingAccount. There are two potential problems with this approach - orphans and referential integrity. Orphans are where the first resource (tradingAccount) has been synced and is associated with a second resource (salesOrder) that subsequently fails sync. Referential integrity problems occur when an application cannot create the data it is passed because it violates referential integrity rules (e.g. tradingAccount must exist before salesOrder can be created). Orphans should be easy to handle - when the second associated resource (salesOrder) fails, we use the UUID to find the referenced resource (tradingAccount) and we back out the relationship. Referential integrity violations must be managed by the application creating the data and would be handled by holding the synced data in temporary storage and then creating the data based on the applications own sequencing rules (which are different by application). We could avoid referential integrity problems by allowing an application to publish its preferred sync order, as has been suggested, but again this makes the SData sync protocol more complex to solve application-level problems (in my opinion). Another suggestion has been to ‘walk the schema’ to find the correct sync order from the schema itself but again, this may not match the application. All of the above assumes resource-kind sync (e.g. all tradingAccounts before all salesOrders, etc), but has anyone considered resource-instance sync? This may make the situation even easier (if perhaps slower on bulk syncs). In resource-instance sync the payload for the resource that is being synced contains the UUID’s to all associated, referenced and child data (this approach is basically parent-child sync with associated and referenced data thrown in). So the first instance of the first resource (tradingAccount(‘AAA001’) contains the UUID’s to the associated data (e.g. salesOrders, salesPersons, taxCodes) any referenced data (e.g. operatingCompany) as well as any child data (e.g. salesOrderLines) and any onwards referenced data (e.g. commodities on salesOrderLines). In this scenario we have a sync ‘transaction’ that contains all the data necessary for an application to avoid orphans (if one resource fails, all should fail) and avoid referential integrity issues (if a taxCode must be synced before the salesOrder then fine, the taxCode data can be retrieved via the UUID in the payload and created first). This also works whatever the sync order (e.g. a salesOrder could be synced first but it would also contain all the necessary UUID’s to maintain the ‘transaction’). The downside of this is the potential for round-trips back to the sync source. This would be large on first syncs but over time should be minimal (most referenced/associated data would exist, so the sync target would not need to re-create it). It maybe that the best approach is to combine the two approaches - for incremental/ongoing syncs use resource-instance sync, for bulk/catch up sync use resource-kind sync. Thoughts?
|
|
Rank: Member
Joined: 12/7/2009
Posts: 2
|
Craig,
Perhaps I am missing something, however upon first reading your your solution, I feel is quite a bit more complex for someone to implement than a simple association resource.
I would much prefer we take the pragmatic approach and the first thing that comes to mind for an M:N relationship to those familiar with relational data models is an association table. Given practically all products implementing the contract come from a SQL back end, the association resource is very likely to map to an equivalent table. Sync requires no change, and the defined order for syncing would be the two "parent" resources followed by the association resource, to avoid orphans. If the association resource fails, due to a key violation, it would be easy to log and report the missing resources. Adding or removing an association is very natural, as it is simply the appropriate POST or DELETE to the association resource.
Cheers,
Stuart
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
Seems as if we are turning an engineering problem into a schema maintenance problem. If we add 'association resources' for all the M:N relationships in the schema then we will have a schema that is truly massive and impossible to implement.
|
|
Rank: Member
Joined: 10/5/2010
Posts: 2
|
I tried to add a post earlier and the wiki went down, the main point of it was as follows. As we dont already have a well defined implementation of how this should work I think Association relationships should be kept for complicated many to many relationships and not used as the lowest common denominator relationship type. I think we should have references for many of the relationships unless we get information from a majority of the ERP teams that they want to impelment them as associations. I agree with Craig that there is a risk of the schema becoming somthing that will frighten away prospective ERP teams,
|
|
Rank: Advanced Member
Joined: 10/13/2010
Posts: 117
|
Stuart Carnie wrote:I would much prefer we take the pragmatic approach and the first thing that comes to mind for an M:N relationship to those familiar with relational data models is an association table. Given practically all products implementing the contract come from a SQL back end, the association resource is very likely to map to an equivalent table. Sorry to add to the problems but.... It might be the first thing that comes to mind but I don't think that we can make this kind of assumption. Not all applications implementing the contract are SQL based. I can't speak for other implementations but certainly Accounts 50 isn't. Even if all applications were SQL based, or could otherwise support many to many relationships, it is unlikely that they will currently implement this for all associations defined in the contract. Also, given that it would not just impact the data layer, it would be impractical to expect all applications to be altered to implement many to many support for every association. I agree with Sean. If we were to include association resourses in the contact it would likely create a barrier for anyone looking to adopt it.
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
Paul Zeimet wrote:
Based on the finding below, we have a vast number of many-to-many associations defined in the contract today. The list below has some of the main ones (there may be a few additional not listed here).
What I discovered is this: Most of these are not an issue for synchronization because none of the CRM teams have a requirement to sync these resources. I have bolded the resources that are synchronized today. Of those, SalesPerson and TaxCode by far have the most relationships. I believe these can be handled in sync by sending them early in the sync cycle. The document on eteam has them and Location as 1, 2, and 3 in the sync order. This would work well because they have not direct reverse relationship and could therefore be applied without any concern for integrity issues. The related resources can then be synced later with the TaxCode, SalesPerson, and Location references included in their payload. This follows the suggestion made by Bruno.
The resource highlighted in Italics below, are the exceptions to this rule because they have relationships in both directions. If the references are included in both payloads there will be referential integrity issues when these are applied.
SalesOrder and others listed below TradingAccount have a property called CarrierTradingAccount. This property is marked as an association even though TradingAccount itself is listed as a reference. Was this by design, or is this a mistake in the Contract?
PriceList has an association to Commodities listed even though this can be retrieved through Price. Price in this example is actually the reference resource that maps PriceList and Commodity. A Price has both the PriceList reference and the Commodity reference. Since you can get the commodities on a Pricelist through PriceList.Prices, do we need this relationship?
TradingAccount and PriceList do present a problem however. Both are required and both have reference to each other. Since TradingAccount syncs before PriceList, if TradingAccount contains its list of PriceLists associations this will cause issues when a new PriceList and TradingAccount are sent in the same sync cycle because the PriceList will not yet exist when the TradingAccount is applied. I don’t believe this will be a problem for ERP teams because PriceList is read-only in CRM so we would never be sending them a new PriceList and a New TradingAccount. However CRM could receive these from ERP. For SalesLogix, this will not be an issue because we do not enforce referential integrity at the database level. If this is an issue for other CRM teams then we will need to resolve how this gets synced.
As for the remaining resources that are not synced, I don’t believe these will pose any issue to ERP teams implementing their CRUD logic on these feeds because only on resource would be being updated at a time anyway.
Example: A new Department is created for a new SalesInvoice. Likely the department would be created first and saved then the SalesInvoice would be created with its relationship to the new Department.
Syncing resources are in bold.
TradingAccount (Carrier TradingAccount property on resources below)
• SalesOrder
• SalesInvoice
• SalesReturn
• SalesCredit
• PurchaseOrder
• PurchaseInvoice
• PurchaseReturn
• PurchaseCredit
• Project
PriceList
• TradingAccount (A Relationship resource between these two would prevent a race condition)
• Commodity (Is this relationship really necessary. This can be accessed through Price.)
SalesPerson (SalesLogix does not sync this resource)
• TradingAccount
• SalesOrder
• SalesQuotation
• Opportunity
• SalesInvoice
• SalesCredit
• SalesInvoice
• Receipt
TaxCode (SalesLogix does not sync this resource)
• TradingAccount
• SalesOrder
• SalesOrderLine
• SalesQuotation
• SalesQuotationLine
• Commodity
• PriceList
• SalesInvoice
• SalesInvoiceLine
• SalesCredit
• SalesCreditLine
• SalesReturn
• SalesReturnLine
• SalesOrderDelivery
• SalesOrderDeliveryLine
• SalesReturnDelivery
• SalesReturnDeliveryLine
• PurchaseOrder
• PurchaseOrderLine
• PurchaseRequisition
• PurchaseRequisitionLine
• PurchaseInvoice
• PurchaseInvoiceLine
• PuchaseCredit
• PurchaseCreditLine
• PurchaseReturn
• PuchaseReturnLine
• Receipt
• Payment
• Project
Location
• SalesOrder
• SalesQuotation
• Price
CostCentre (not synced)
• TradingAccount
• Contact
• SalesOrderLine
• SalesQuotation
• SalesQuotationLine
• Commodity
• CommodityGroup
• SalesInvoiceLine
• SalesCredit
• SalesCreditLine
• SalesReturn
• SalesReturnLine
• SalesOrderDelivery
• SalesOrderDeliveryLine
• SalesReturnDelivery
• SalesReturnDeliveryLine
• PurchaseOrder
• PurchaseOrderLine
• PurchaseRequisition
• PurchaseRequisitionLine
• PurchaseInvoice
• PurchaseInvoiceLine
• PuchaseCredit
• PurchaseCreditLine
• PurchaseReturn
• PuchaseReturnLine
• Receipt
• ReceiptLine
• Payment
• PaymentLine
• Project
• ProjectLine
• BankAccount
Department (Not Synced)
• TradingAccount
• Contact
• SalesOrderLine
• SalesQuotation
• SalesQuotationLine
• Commodity
• CommodityGroup
• SalesInvoice
• SalesInvoiceLine
• SalesCredit
• SalesCreditLine
• SalesReturn
• SalesReturnLine
• SalesOrderDelivery
• SalesOrderDeliveryLine
• SalesReturnDelivery
• SalesReturnDeliveryLine
• PurchaseOrder
• PurchaseOrderLine
• PurchaseRequisition
• PurchaseRequisitionLine
• PurchaseInvoice
• PurchaseInvoiceLine
• PuchaseCredit
• PurchaseCreditLine
• PurchaseReturn
• PuchaseReturnLine
• Receipt
• ReceiptLine
• Payment
• PaymentLine
• Project
• ProjectLine
• BankAccount
FinancialAccount (Not Synced)
• TradingAccount
• Contact
• SalesOrder
• SalesOrderLine
• SalesQuotation
• SalesQuotationLine
• Commodity
• CommodityGroup
• SalesInvoice
• SalesInvoiceLine
• SalesCredit
• SalesCreditLine
• SalesReturn
• SalesReturnLine
• SalesOrderDelivery
• SalesOrderDeliveryLine
• SalesReturnDelivery
• SalesReturnDeliveryLine
• PurchaseOrder
• PurchaseOrderLine
• PurchaseRequisition
• PurchaseRequisitionLine
• PurchaseInvoice
• PurchaseInvoiceLine
• PuchaseCredit
• PurchaseCreditLine
• PurchaseReturn
• PuchaseReturnLine
• Receipt
• ReceiptLine
• Payment
• PaymentLine
• Project
• ProjectLine
• BankAccount
Receipt (Not Synced)
• SalesInvoice
Case (Not Synced)
• Contact
• SalesOrder
• SalesQuotation
• SalesInvoice
• SalesCredit
• SalesReturn
• SalesOrderDelivery
• SalesReturnDelivery
• PurchaseOrder
• PurchaseInvoice
• PuchaseCredit
• PurchaseReturn
Project (Not Synced)
• TradingAccount
• SalesQuotation
• SalesInvoice
• SalesCredit
• SalesReturn
• PurchaseOrder
• PurchaseRequisition
• PurchaseInvoice
• PuchaseCredit
• PurchaseReturn
• Receipt
• Payment
Interaction (Not Synced)
• Contact
• SalesOrder
• SalesQuotation
• Commodity
• Opportunity
• SalesInvoice
• SalesCredit
• SalesReturn
• SalesOrderDelivery
• SalesReturnDelivery
• PurchaseOrder
• PurchaseRequisition
• PurchaseInvoice
• PuchaseCredit
• PurchaseReturn
• PurchaseOrderDelivery
• PurchaseReturnDelivery
• Receipt
• Payment
SalesInvoice (Not Synced)
• Interaction
• SalesCredit
• SalesReturn
• SalesOrderDelivery
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
Craig Pearson wrote:Paul Zeimet wrote:
Of those, SalesPerson and TaxCode by far have the most relationships. I believe these can be handled in sync by sending them early in the sync cycle. The document on eteam has them and Location as 1, 2, and 3 in the sync order. This would work well because they have not direct reverse relationship and could therefore be applied without any concern for integrity issues. The related resources can then be synced later with the TaxCode, SalesPerson, and Location references included in their payload.
The sync order is something currently held in "documentation", be it eteam or the Wiki. There is a request to make a sync protocol enhancement to handles this e.g. smeSyncOrder, with numeric values representing the sync order (low values being synced before high values). It is logged on GForge tracker see -> http://gforge.sageinternal.com/gf/project/gcrmcontract/tracker/?action=TrackerItemEdit&tracker_item_id=5261Do we want to introduce this additional piece of sync metadata? The problem I have with this is whose sync order is put in the schema? The sync order to suite CRM or to suite ERP? We may get the situation where ERP, as the provider specifies a sync order in its $schema that is not suitable for all consumers.
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
Craig Pearson wrote:Paul Zeimet wrote:
SalesOrder and others listed below TradingAccount have a property called CarrierTradingAccount. This property is marked as an association even though TradingAccount itself is listed as a reference. Was this by design, or is this a mistake in the Contract?
This is by design - for example a sales order may be delivered in multiple deliveries with different carriers and so the relationship must be 1:N association. I'm guessing this is not an issue for Sync as all tradingAccounts will be synced before salesOrders are synced, so the salesOrder could contain the reference(s) of the associated carrierTradingAccount(s) - right? Also, the business rules of ERP would insist that a new carrierTradingAccount is created in ERP before a salesOrder can be created referencing the new carrierTradingAccount. Again the carrierTradingAccount would be synced before the salesOrder (again to formalize this do we need the sme:syncOrder introduced? see above).
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
Craig Pearson wrote:Paul Zeimet wrote:
PriceList has an association to Commodities listed even though this can be retrieved through Price. Price in this example is actually the reference resource that maps PriceList and Commodity. A Price has both the PriceList reference and the Commodity reference. Since you can get the commodities on a Pricelist through PriceList.Prices, do we need this relationship?
I think this is because the schemas are designed from a user/workflow perspective. During order/quotation creation typically a user selects a tradingAccount, then (in any order) a commodity and a priceList. This combination results in a price. As you say price is the reference resource that links a priceList and a commodity (and a tradingAccount as priceLists may be speific to certain tradingAccounts). You could remove the relationship (between priceList and commodity) to improve sync but I'd be concerned about the impact on 'other' non-sync consumers. I see two issues: 1. you couldnt find the commodities on a priceList using a resource property URL (as per http://interop.sage.com/daisy/sdata/AnatomyOfAnSDataURL/187-DSY.html) i.e. you cannot do http://www.example.com/sdata/myApp/myContract/-/priceList('Retail')/prices/commodities - this is not allowed unless you (impossibly) qualify every price resource - whereas http://www.example.com/sdata/myApp/myContract/-/priceList('Retail')/commodities is valid. 2. In a large ERP system there may be (tens/hundreds of) thousands of individual prices so for someone to use price as a look-up would be terrible for performance. That said, if this is an issue that effect sync only maybe there is a way of excluding some relationships in the schema from sync, e.g. something like a property-level sme attribute such as sme:doNotSync (TRUE/FALSE). Thoughts?
|
|
Rank: Advanced Member
Joined: 11/11/2009
Posts: 33
Location: Newcastle-upon-Tyne, UK
|
Craig Pearson wrote:Paul Zeimet wrote:
TradingAccount and PriceList do present a problem however. Both are required and both have reference to each other. Since TradingAccount syncs before PriceList, if TradingAccount contains its list of PriceLists associations this will cause issues when a new PriceList and TradingAccount are sent in the same sync cycle because the PriceList will not yet exist when the TradingAccount is applied.
Thinking about this particular case it would be unusual, in my opinion, for any navigation to take place from priceList TO tradingAccount (or even from priceList TO commodity). Normally its the other way round. In fact this makes me think a relationship property sme attribute such as sme:doNotSync would be very helpful for sync - it effectively turns sync-unfriendly N:M association-type relationships into sync-friendly 1:N association-type relationships. The N:M relationship however is only preserved in those applications that support it and can still be implemented by those SData providers that want it for non-sync purposes (e.g. http://www.example.com/sdata/myApp/myContract/-/priceList('Retail')/commodities OR http://www.example.com/sdata/myApp/myContract/-/priceList('Retail')/tradingAccounts). For this example, priceList would need to have its sync order changed (that sme:syncOrder attribute again) so it comes before both tradingAccount and commodity. The sme:doNotSync attribute on relationship properties may work for all N:M relationships in the schema during sync. It effects sync only, does not effect normal SData feeds, and allows an application database to process synced data using its own design.
|
|
|
Guest |
