Page Comparison

...

DataSelf has been working and optimizing the Sage 100 Providex data extraction since 2005.

Time Required to Execute this Procedure

• Reading all related documentation first: hours

• Deployment procedure: 30 minutes

• 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 for ETL+ pre-installation.

Introduction to DataSelf

• DataSelf Value Proposition Diagram.

• DataSelf ETL+ Business and tech overview.

• DataSelf ETL+ tech diagrams.

Installation Checklist

• Click here to fill out the DataSelf Analytics Deployment Form.

• Click here for ETL+ pre-installation.

• 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.

...

1. If you haven’t installed ETL+ yet: ETL+ Deployment Instructions.

2. See Sage 100 Providex ODBC DSN Setup - Summarized for how to configure the ODBC driver.

3. Sign in (Login) to ETL+ (help here).

4. On the ETL main page, right-click Select the Sage 100 object on the left panel → Properties Source Objects panel.

5. Select /wiki/spaces/DS/pages/2001502390 on the Source Objects panel.

   • If

   a

   • the Microsoft SQL Server (instead of ODBC) page pops up,
     click

   the

   • Change Data Source (wrench icon

   →

   • ) > ODBC.

6. On the ODBC Data Sources page, select Select the correct Providex ODBC Name from the listdriver listed on the /wiki/spaces/DS/pages/2001502390 page.

   • Can’t find your ODBC? It’s likely because your Providex ODBC is 32-bit: Download the DataSelf ETL+ 32bits file from here. Then copy/paste the downloaded file into C:\Program Files\DataSelf ETL Plus. Confirm the overwrite prompt.

   ODBC requiring credentials: double-click the ODBC name → enter credentials (example: username or username|CompanyCode, and password) → Connect

   • If it fails, try adding the ODBC credentials.

7. Customize the Alias as desired.

   Image RemovedImage Added

8. Click Connect.

9. You’ll get back return to the ETL page and see tables under the Sage 100 source on the left panel.

...

• 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 Job (top right icon on the center panel).

• The Refresh Batch page (help here) Job page 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 Job now (optional): click the Refresh Batch Now Run Job (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.Select a Job → Job Properties (wrench icon) → configure the schedule panel → Save

• Click Close -> Close.

Level 1 Testing

...

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.

  

...

• 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.

• Issues loading Date columns with invalid values for SQL:

  • See Sage 100 Providex Dates

• 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 the SQL in the ETL+ Extract SQL Statement Panel .

Customizing the ETL+ Extract SQL Statement Panel for extracting from Providex.

The following are snippets of popular SQL statements for ETL+ Extract SQL Statement Panel 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

• See also Sage 100 Providex Dates

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

• Providex ODBC Syntax Functions and Data Types

• Providex ODBC Reference

Premapped ETL+ Tables

Sage 100 Pre-mapped Data Warehouse Tables

Related Pages

Premapped ETL+ Tables

Sage 100 Pre-mapped Data Warehouse Tables

Sage 100 Date Issues

Related Pages

• Sage 100 Technical Details – issues in more depth

• ETL+ for Sage 100 Providex

  • Sage 100 Providex Dates – Adjusting date values on the ETL+ Design Page, – How to process ‘bad’ dates

  • Providex SQL Syntax – SQL syntax only for use on the ETL+ Extract SQL Statement Panel

• Source Systems and IT

  • Sage 100 Technical Details

    • Sage 100 Providex ODBC DSN Setup - Summarized

    • Sage 100 Providex ODBC DSN Setup - Detailed

• ETL+ for Sage 100 MS SQL Server

• DataSelf Analytics For Sage 100

• ETL+ Load Replace with Date-time fields – special caution for Sage 100 users.

• – Guide to finding SQL documentation in this reference manual.

• PxPlus Documentation – Link to the reference manual itself

• SQL syntax for Sage 100
  

Links to Providex Pages

• Providex ODBC Syntax Functions and Data Types from http://manual.pvxplus.com

• Providex ODBC ReferenceSQL syntax to use in the ETL+ Extract SQL Statement Panel

Sage 100 Database Guide

• https://help-sage100.na.sage.com/2022/FLOR/index.htm#File_Layouts/File_Layouts_Overview.htm?TocPath=File%2520Layouts%257C_____0 – Sage 100 database metadata / database guide. See File Layouts tab for details on each file/table.

Search: S100, Sage100, database guide, file layouts