Sunday, November 22, 2009

IM - Integration Manager skipping records despite proper query relationships

Just when you thought there wasn't anything more to learn about Integration Manager something else comes along to demistify that theory. I have been involved in a JD Edwards on DB2 and AS/400 systems migration for almost 4 months now and last month I blogged about how Integration Manager can be a powerful tool in multiplatform systems integrations.

Part of disengaging JD Edwards is to write a series of integrations to existing systems running on the AS/400 platform. In the process, the client needed a simple to use tool which required limited programming and maintenance, hence the choice of IM. After unit testing the integrations, everything was A-Ok to begin with the week long systems testing, which would exhause the integrations while allowing the customer to get the overall "feel" for GP and how it would address the existing business processes.

One particular business process -- Expense Reimbursements -- required an integration to Payables Management. How difficult could this be, right? I have done over 100 PM integrations in the past so this could not be any different. During the system testing, the end-user reported incomplete distributions throughout the integrations of hundreds of expense reports into GP and provided the following log file:



Integration Log
Integration: SC-ExpenseReimbursement (ID: 116)
Action: None
Start Time: 11/20/2009 12:09:14 PM

11/20/2009 12:09:15 PM Source: IIntegrationEngine_Run, Status Code: 0 Opening source query...
11/20/2009 12:09:16 PM Source: IIntegrationEngine_Run, Status Code: 0 Establishing source record count...
11/20/2009 12:09:28 PM Source: IIntegrationEngine_Run, Status Code: 0 Beginning integration...
1: 2 Insert Succeeded 5.31 Seconds
2: 3 Insert Succeeded 1.53 Seconds
3: 4 Insert Succeeded 1.86 Seconds
4: 5 Insert Succeeded 2.25 Seconds
5: 6 Insert Succeeded 3.56 Seconds
6: 7 Insert Succeeded 1.47 Seconds
7: 8 Insert Succeeded 1.44 Seconds
8: 9 Insert Succeeded 1.47 Seconds
9: 10 Insert Succeeded 1.34 Seconds
10: 11 Insert Succeeded 1.91 Seconds
11: 12 Insert Succeeded 1.53 Seconds
12: 13 Insert Succeeded 1.64 Seconds
13: 14 Insert Succeeded 2.63 Seconds
14: 15 Insert Succeeded 2.94 Seconds
15: 16 Insert Warning 3.22 Seconds
DOC 15 WARNING: Distributions for this transaction contain errors.
DOC 15 WARNING: This transaction will not post; it includes distributions with errors.
16: 17 Insert Warning 0.95 Seconds
DOC 16 WARNING: Distributions for this transaction contain errors.
DOC 16 WARNING: This transaction will not post; it includes distributions with errors.
17: 18 Insert Warning 1.17 Seconds
DOC 17 WARNING: Distributions for this transaction contain errors.
DOC 17 WARNING: This transaction will not post; it includes distributions with errors.
18: 19 Insert Warning 1. Seconds
DOC 18 WARNING: Distributions for this transaction contain errors.
DOC 18 WARNING: This transaction will not post; it includes distributions with errors.
19: 20 Insert Warning 0.98 Seconds
DOC 19 WARNING: Distributions for this transaction contain errors.
DOC 19 WARNING: This transaction will not post; it includes distributions with errors.
20: 21 Insert Warning 1.83 Seconds
DOC 20 WARNING: Distributions for this transaction contain errors.
DOC 20 WARNING: This transaction will not post; it includes distributions with errors.
21: 22 Insert Warning 0.95 Seconds
DOC 21 WARNING: Distributions for this transaction contain errors.
DOC 21 WARNING: This transaction will not post; it includes distributions with errors.
22: 23 Insert Succeeded 2.66 Seconds
11/20/2009 12:10:50 PM Source: IMProv.IntegrationContext.IIntegrationEngine_Run, Status Code: -2147221495 ERROR: Integration canceled during document integration.
11/20/2009 12:10:50 PM Source: FinishIntegration, Status Code: 3 Integration Failed
11/20/2009 12:10:50 PM Source: FinishIntegration, Status Code: 3 Integration Results
307 documents were read from the source query.
22 documents were attempted:
15 integrated without warnings.
7 integrated with warnings.
0 failed to integrate.

Finish Time: 11/20/2009 12:10:50 PM

Source Total: 22
Successfully Integrated: 22
Integrated With Warning: 7
Failed: 0

At first glance this would seem typical of transactions that are actually missing distributions, but I checked against the source staging table in DB2 and all distribution records for each transaction reported as missing were actually present. The only distributions in the source were those for the expenses. In addition, this integration had been setup to default any missing distributions in the source recordset, which would automatically create the payables side.

I then checked each vendor (employee) record in GP for a missing payables account. After all, this would explain the case of the missing distributions. All vendor records were properly setup. Now comes the actual troubleshooting process after checking the obvious. My source queries were very simple too:

ExpenseHeader

SELECT DISTINCT VENDORID, INVOICENUM, AMTDUEEMPLOYEE, LEDGERDATE, INVOICEDATE
FROM GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0 AND COMPANY = '10'


ExpenseDetail

SELECT VENDORID, INVOICENUM, GPACCOUNT, AMOUNT AS DEBIT, 0 AS CREDIT, 6 AS DISTTYPE
FROM SDDTA.GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0

I proceeded to limit the source queries to just a specific employee and expense document number reported in the log as failing and re-ran the integration. Voila! The record went through to GP without any issues. Now the question was, why is IM kicking back valid records for the integration without any apparent reason?

I also checked to make sure the UseOptimizedFiltering switch was set to False and that we were running IM service pack 4 -- more on IM switches here. After a while of looking at the source records in the staging table, I realized that the first records that IM imported were in the same physical order in the table were data was being retrieved from. This was very simple as DB2 tables are VSAM tables, making them very different from SQL Server tables in the way the store data physically. However, for the records that failed, IM was attempting to locate a distribution record in the same order specified by the records being read from the header query.

To correct the problem, I decided to enforce the order of the records in both source queries by adding an ORDER BY clause to the queries, by VendorID and InvoiceNum. This way, as IM advanced through the header source query reading the records, it would consecutively find these in the distributions source query tables as specified by the query relationships (1..M). Now my source queries looked like this:

ExpenseHeader

SELECT DISTINCT VENDORID, INVOICENUM, AMTDUEEMPLOYEE, LEDGERDATE, INVOICEDATE
FROM GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0 AND COMPANY = '10'
ORDER BY VENDORID, INVOICENUM


ExpenseDetail

SELECT VENDORID, INVOICENUM, GPACCOUNT, AMOUNT AS DEBIT, 0 AS CREDIT, 6 AS DISTTYPE
FROM SDDTA.GP_REIMBURSABLE_EXPENSES
WHERE INTEGRATED = 0
ORDER BY VENDORID, INVOICENUM

Upon executing the integration once more, all expense transactions were properly integrated without any missing distributions.

Now, I cannot say I experienced this before with SQL Server sources, but this happens to be a DB2 source and just maybe the ODBC engine works differently, but I can say that by reordering the source queries, I was able to resolve these missing distributions issues and improve performance two-fold.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC.
http://www.maximumglobalbusiness.com/

4 comments:

Anonymous said...

Hi Mariano-
i'm getting this error message. we just upgraded to Dynamics GP 2010 from GP 9 and we started getting this error message.

do you happen to know more about it. i've three sources all using queries and with the same ordering clause. verified data types and relationships, all looks fine.

issue is, it was working but now throwing an error after the upgrade.

you can email me at lnyere@gmail.com if you know something.

regards

Mariano Gomez said...

@lnyere,
There are two .ini files: one for the IM.EXE and the other for the IMRun.EXE. Make sure the two files are using the same settings for UseOptimizedFilter. If unsure, copy the IM.INI file and rename to IMRun.INI.

Keep in mind I am simplifying the names as in v10 the files are Microsoft.Dynamics.*.ini.

MG.-

Lisa Sorensen said...

Hi Mariano,

Just to let you know,this blog entry was very helpful for me in resolving an issue I had with an Integration.

Per one of your replies to a post on the Community boards, I needed to total the Debit Amounts to get a Total Purchase Amount for the integration. The only difference was I was using a *.csv source file. Per your instructions, I created 2 Advanced ODBC source adapters and built the integration in a test environment with no issues importing the transactions.

The interesting thing is when I imported the integration in the Production environment, I kept receiving the error "The BATCH ID does not have a default value..." message. If you want details on the issue, you can read it here: http://social.microsoft.com/Forums/en-US/partnerdynamicsgp/thread/fdfb8fd6-6507-41f1-a73e-f6612142ba94/

So, even though the symptoms pointed to the source file (and how *.csv file have format issues in Excel), ultimately, the issue was the lack of the ORDER BY line in the SQL Statement in the Details source adapter.

So, as always, thank you for the informative postings. They saved me hours of additional troubleshooting and having to use a ticket with MS Support.

Abhishek Ram said...

Thanks a lot, your solution helped me figure out why some of the orders imported by a GP integration were missing items. Cheers!!!!