ETL+ for Sage 100 Providex
See also https://dataself.atlassian.net/wiki/spaces/DS/pages/1666482584
DataSelf has been working and optimizing the Sage 100 Providex data extraction since 2005.
Time Required to Execute this Procedure
Deployment procedure: 30 minutes
Reading all related documentation first: hours
Data load: you can run the data load unassisted. It can take minutes up to an hour or so depending on your data volume and network speed.
Installation Checklist
Click here to fill out the DataSelf Analytics Deployment Form.
Set up a Sage 100 Providex ODBC DSN. ETL+ uses ODBC to extract data from Sage 100 Providex. Set up the ODBC on the same computer where ETL+ will be installed.
ETL+ Connection to Providex ODBC (single company)
If you haven’t installed ETL+ yet: ETL+ Deployment Instructions.
See https://dataself.atlassian.net/wiki/spaces/DS/pages/7831651 for how to configure the ODBC driver.
Login to ETL+ (help here).
On the ETL main page, right-click
Sage 100
on the left panel → Properties.If a Microsoft SQL Server page pops up, click the
Change Data Source
wrench icon →ODBC
.On the
ODBC Data Sources
page, select the correct Providex ODBC Name from the list.Can’t find your ODBC? Be sure ETL+ and the ODBC have the same 32/64bit build.
ODBC requiring credentials:
double-click the ODBC name
→enter credentials
(example: username or username|CompanyCode, and password) →Connect
.
You might leave the Alias as is, or customize it.
Click
Connect
.You’ll get back to the ETL page and see tables under the Sage 100 source on the left panel.
Testing a Table Load
Right-click a table on ETL main page’s center panel → Load Now
.
Failure? It’s likely that the prior step wasn’t properly configured. Go back and review it.
Click the
Log
icon (top right) to view data load results. Log page help here.
Schedule the Data Load
On the ETL main page, click
Refresh Batch
(top right icon on the center panel).The Refresh Batch page (help here) usually comes pre-configured to your source system(s).
Overview this page’s configuration. On the right panel, feel free to uncheck tables and Tableau extracts that don’t apply to your reporting needs.
Run a Refresh Batch now (optional): click the
Refresh Batch Now
(play icon) on the left panel.To schedule the data load (optional):
Click
WTS Settings
-> enter the WTS parameters →Apply
.This step might require you to run ETL+ app as “Run as administrator” in Windows.
Click
Close
->Close
.
Level 1 Testing
The Level 1 Testing checks if the data extraction process finished successfully and row count per extracted table matches with Sage 100’s table row counts.
ETL+ must have already loaded your data manually or scheduled.
Steps
Login to ETL+ (help here).
On the ETL main page, click the top-right
Log
icon. Log page help here.Scroll down on the log page and double-check that no table load failed, and the row count matches with the Sage 100 tables. Following is an example highlighting two tables and their loaded row count.
Troubleshooting
Failed to load tables:
Check that the user assigned to DataSelf has reading rights to that particular data set.
Click
Back
from the Log page, on the ETL main page, select the failing table on the center panel, check that its SQL Statement on the right panel has correct information.
Tables with record count different than in Sage 100:
It might be just that new records have been populated in Sage 100 since the DataSelf data load. Reload the data. Click
Back
from the Log page, on the ETL main page, select the table with incorrect row cound on the center panel,right-click
the table on the center panel again ->Load Now
. This might take a while if the table has a large data set.The user assigned to DataSelf might have access to limited data sets, such as only data from one Division. Check the user’s security credentials.
Providex SQL Syntax Tips
This section is only for troubleshooting and/or optimizing ETL Providex SQL Statements.
The following are snippets of popular Providex SQL Statement customizations:
ETL+Replace using Parent Date Filters
AR Invoice History
H.InvoiceDate AS InvoiceDate
FROM
{ IJ AR_InvoiceHistoryHeader H INNER JOIN AR_InvoiceHistoryDetail D ON
H.HeaderSeqNo = D.HeaderSeqNo AND H.InvoiceNo = D.InvoiceNo }
SO History Header & Detail
H.OrderDate as OrderDate
FROM
{IJ SO_SalesOrderHistoryDetail D INNER JOIN
SO_SalesOrderHistoryHeader H ON D.SalesOrderNo = H.SalesOrderNo }
Converting Date to String (to deal with dirty data, invalid dates)
{fn convert(OrderDate,SQL_VARCHAR)} as OrderDate
Single Table Join + Table Prefix + Filter
FROM { IJ IM_ItemWarehouse D INNER JOIN CI_Item I ON D.ItemCode = I.ItemCode }
WHERE D.DocDate>={d'2018-01-01'}
Double Table Join
FROM { IJ (PO_PurchaseOrderDetail D INNER JOIN PO_PurchaseOrderHeader H ON D.PurchaseOrderNo = H.PurchaseOrderNo ) INNER JOIN CI_Item I ON D.ItemCode = I.ItemCode }
Providex ODBC References
https://dataself.atlassian.net/wiki/spaces/DS/pages/7831651 DataSelf’s guide
Premapped ETL+ Tables
Sage 100 Pre-mapped Data Warehouse Tables
Related Pages
https://dataself.atlassian.net/wiki/spaces/DS/pages/71565370
Providex ODBC Syntax Functions and Data Types from http://manual.pvxplus.com