Performing Data Quality Testing - Advanced Analytics for Acumatica by DataSelf
Data Quality Testing
Let’s verify the data in DataSelf matches Acumatica.
The following script includes examples covered in the DataSelf’s Green Belt Certification training, but the adjustments apply to regular DataSelf deployments.
Log to the DataSelf Analytics portal, on the left panel, click on Explore -> the DataSelf site name (ex.: NFR1 or ClientName) -> Maintenance workbook.
Click AP Aging QA.
Under the report’s title, you’ll find tips on which Acumatica report to run to compare against DataSelf.
Open Acumatica and go to the Payables -> AP Aged Past Due report, run as of today. Go to the last page and compare the Grand Total, then the aged bucket totals, with DataSelf.
In the DataSelf Maintenance view, click on the AR Aging QA tab and check the Grand Total value in the upper right corner.
Open Acumatica and go to the Receivables -> AR Aged Past Due report, run as of today.
A common cause of differences is due to selection criteria of the Status field. In this AR scenario, DataSelf is including invoices on Credit Hold, Hold, and Voided. Select only Open status invoices and check the DataSelf total. It should now match Acumatica.
Note that Acumatica has different aging buckets than DataSelf. When you add the Acumatica 1-10 and 11-30 days, it should match the DataSelf 1-30 days.
A common discrepancy between DataSelf and Acumatica is the aging of non-invoice transactions (such as credit memos and payments). By default, DataSelf assigns the aging of those transactions to their transaction date.
For example, a credit memo issued 60 days ago is in the ‘61-90 Days’. Many companies apply such transactions to ‘On Time’. To fix this formula, go to workbooks that have AR Aging, and change the Aging formula to the following:
when 'INV' then
if [Work Day: Today]-[Due Date] <=0 then 'On Time'
elseif [Work Day: Today]-[Due Date]<=30 then '01-30 Days'
elseif [Work Day: Today]-[Due Date]<=60 then '31-60 Days'
elseif [Work Day: Today]-[Due Date]<=90 then '61-90 Days'
else '90+ Days'
else 'On Time'
In the DataSelf Maintenance view, click on the SA Sales QA tab.
The default comparison for sales are reports from Acumatica Receivables such as Daily Sales Profitability, or Sales Profitability by Salesperson and Customer.
Making these filter changes permanent by modifying the GI’s
To ensure that the DataSelf users can’t accidently see data that should not be in there, the best solution is to filter the data at the GI level.
In Acumatica, go to the Generic Inquiry screen and look up the DSL_SA_Sales GI. This GI was designed by DataSelf to work with the Advanced Analytics system. As the reseller, you can make changes to the GI’s to fit your client’s needs.
Refreshing the Tableau data set with your changes to the GI.
The automatic data refresh only brings in deltas since the last refresh. After changing GI filters, you’ll have to force a full data refresh so all historical data in DataSelf matches with the modified GI filters. If the full refresh is not performed, only newly imported records will match the modified filter criteria, while frozen historical records in DataSelf will keep the data that matched the previous GI filter criteria.
FYI, currently, the delta auto-data refresh happens nightly.
Requesting a full data refresh on demand:
Use the Maintenance view -> Full Refresh tab, click “Full Refresh” orange button to trigger an email to DataSelf.
Or, email firstname.lastname@example.org to do a mid-day refresh informing which Client site you want refreshed.
In this section