business adapter - simulate and find possible rejection records

[sasikumar_r]

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

[erwinlindemann]

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.

[sasikumar_r]

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 

[erwinlindemann]

Afraid not ☹️. As I tried to explain, the 'Simulate' feature in Business Adapter Studio:

• Only does a basic check of the prerequisites.
• Does not actually write ANY data to the [FNMSCompliance] database.

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.