Pinpointing the problem

Now that all the data is in one place, we can look for the source of the discrepancy. Comparing the number of rows in each sheet will help us pinpoint the problem. Let’s take a closer look at each situation.

Both sheets contain the same number of rows

If both systems have the same row count and the Revenue metric isn’t matching the source data, then the order_total must be off somewhere. It’s possible that the order_total field has been updated in your source database and Magento BI isn’t picking up these changes.

To confirm this, take a look at whether or not the order_total column is being rechecked. Head to the Data Warehouse Manager and click the orders table. You’ll see the recheck frequency listed in the ‘Changes?’ column. The order_total field should be set to recheck as often as it is expected to change; if it’s not, go ahead and set it to your desired recheck frequency.

If the recheck frequency is already set correctly, then something else is wrong. Refer to the Contacting Support section at the end of this article for next steps.

The source database has MORE rows than Magento BI

If the source database has more rows than Magento BI and the gap is greater than the number of orders that you can expect to come in during the length of an update cycle, there may be a connection issue. This means that Magento BI isn’t able to pull in new data from the source database, which can happen for several reasons.

Navigate to the Connections page and take a look at the status of the data source containing the order table:

  1. If the status is Re-auth , the connection isn’t using the correct credentials. Click into the connection, enter the correct credentials, and retry.
  2. If the status is Failed , the connection may not be setup properly on the server side. Failed connections usually arise from an incorrect host name or the target server not accepting connections on the specified port.Click into the connection and double-check the spelling of the hostname and that the correct port is entered. On the server side, make sure that the port can accept connections and that your firewall has the Magento BI IP address (54.88.76.97/32) as allowed. If the connection continues to fail , refer to the Contacting Support section at the end of this article for next steps.
  3. If the status is Successful , then the connection isn’t the problem and RJ support needs to get involved. Refer to the Contacting Support section at the end of this article for next steps.

The source database has FEWER rows than Magento BI

If the source database has fewer rows than Magento BI, then it’s possible that rows are being deleted from the source database and Magento BI isn’t picking up these deletions. ** Deleting data can lead to discrepancies, lengthier update times, and a slew of logistical headaches** , so we strongly recommend you don’t ever delete data unless it’s really necessary.

If, however, rows are deleted from the table, take a look at the recheck frequency on the primary key. Rechecking the primary key means that the table will be checked for deleted rows.

In the Data Warehouse Manager, primary key columns are marked with a key symbol. In our example, the primary key is the order_id column:

If the primary key is already set to be rechecked or rows are never deleted from this table, then you’ll need RJ support to pinpoint the problem. Refer to the following section for next steps.

Contacting Support

If you aren’t able to pinpoint the source of the problem, you’ll need to loop in RJ Support. Before you submit a ticket, please do the following:

  • If your source database and Magento BI have the same number of rows and recheck frequencies are set correctly, perform a VLOOKUP in your spreadsheet to find which order_id values have a different order_total value between Magento BI and your source database. Include these values when you submit your ticket.
  • If your source database has MORE rows than Magento BI and the connection shows as Successful or continues to fail, we’ll need to know the name of the connection and the error message you’re seeing, if there is one.
  • If your source database has FEWER rows than Magento BI, rows aren’t deleted from the table, and recheck frequencies are set correctly, perform a VLOOKUP in your spreadsheet to find which order_id values are in Magento BI but not in your source database. Include these values when you submit your ticket.
Previous pageAdobe Commerce 2.3.5 known issue: virtual product multi-ship orders
Next pageComposer plugin against Dependency Confusion attacks

Commerce


Connect with Experience League at Summit!

Get front-row access to top sessions, hands-on activities, and networking—wherever you are!

Learn more