Hi All,
We run business adapter in connected mode(connected to db directly). I see we could simulate the connection before commiting the changes. if i simulate, how to find which records will get rejected and for what reason? i would like to get line by line status of the record that we upload. like we see in the systemtasks. can we get this information from database?
Thank you
Sasi
Feb 17, 2021 08:15 AM
Predicting errors that might happen in the future when certain data are imported into FNMS using Business Adapter Studio is like doing a weather forcast for Christmas in five years.
Business Adapter Studio will check for obvious issues, like if you did configure the "Use this property for matching existing data" for at least one property per object to be imported. It cannot tell you if any other data to be imported might be violating other constraints, like if data in required columns are empty or not unique where they should be.
During the import with MGSBI.exe, data will be written to [BusinessImportxxx] tables in the [FNMSCompliance] database though.
You can run a SELECT statement like the one below from Microsoft SQL Server Management Studio for analyzing the import data in detail.
/* Limit results to the the last four weeks. */
DECLARE @StartDateForReport DATE
SET @StartDateForReport = DATEADD(week, -4, GETDATE())
SELECT
bils.ImportID AS BusinessImportImportID
,bils.ImportName AS BusinessImportImportName
,bilo.ObjectName AS BusinessImportObjectName
,bilo.StartDate AS BusinessImportStartDate
,bils.ImportType AS BusinessImportImportType
,DATEDIFF(second, bilo.StartDate, bilo.EndDate) AS BusinessImportSecondsElapsed
,REPLACE(bild.RecordDescription,'''',NULL) AS BusinessImportRecordDescription
,bild.Action AS BusinessImportAction
,bild.Message AS BusinessImportMessage
,bild.TenantID AS BusinessImportTenantID
,t.TenantName + ' (ID=' + CONVERT(VARCHAR,t.TenantID) + ')' AS BusinessImportTenant
FROM [BusinessImportLogSummary_MT] bils
JOIN [BusinessImportLogObject_MT] bilo ON bilo.ImportID = bils.ImportID
AND bilo.TenantID = bils.TenantID
JOIN [BusinessImportLogDetail_MT] bild ON bild.ImportID = bils.ImportID
AND bild.ImportObjectID = bilo.ImportObjectID
AND bild.TenantID = bilo.TenantID
JOIN [Tenant] t ON t.TenantID = bils.TenantID
WHERE bilo.StartDate >= @StartDateForReport
ORDER BY
bilo.StartDate DESC
,bils.ImportID
,bild.RecordNumber
In an on-premises environment, you can also expose the results of this SELECT statement as a report to the FNMS Web UI. When working in a single-tenant environment, you can also omit the TenantID column and use the SQL Views instead of using the {xxx_MT] SQL Tables.
Feb 17, 2021 12:02 PM
Hi @erwinlindemann ,
Thank you for sharing the query! Query returns the details of the records with their import description.
I hope the given query can return the details only when we perform the Import and not the Simulate . But on the "UsingFlexnetbusinessadapter" document, it says on the business adapter UI->history tab we can check the details either if we do import or simulate. (attached screenshot for reference)
We are on multitenant environment and using mgsbi.exe(with tenantuid) for running the adapter. so we would like to use simulate and check\fix errors before we do import for making changes to DB directly. Do we have any query to fetch the details from DB using queries even for simulate?
Thank you
Sasi
Feb 18, 2021 05:07 AM
Afraid not ☹️. As I tried to explain, the 'Simulate' feature in Business Adapter Studio:
The [BusinessImportxxx] tables that the SQL query above is based on are populated when you perform the import. It is only after you selected "Tools > Import" in Business Adapter Studio AND confirmed the warning dialog that you 'actually want to do updates to the FNMS database' that data are written to the SQL Server.
The 'UI -> History' tab in Business Adapter Studio displays historical data written to the [BusinessImportxxx] tables during imports that you did previously.
The 'best practice' for confirming that the Business Data import will work on your FNMS production environment is running a test on a FNMS test environment that is based on a recent copy of the data used in the FNMS production system.
Feb 18, 2021 05:50 AM - edited Feb 18, 2021 06:57 AM