When independent analysts are asked to perform data analytics in a SYSPRO environment, the most common questions that are asked is:
- How do I get access to SYSPRO data?
- How can I get data out of SYSPRO?
- Which SYSPRO tables do I need?
- How do I prepare SYSPRO tables for analysis?
With our knowledge and expertise in SYSPRO, the following 5 Top Tips will help you to get analysing SYSPRO data.
Tip 1: Data Access
When requesting access to SYSPRO, the analyst can go with one of the two most common methods of accessing the data. Either a copy of the latest back up of the Microsoft SQL server SYSPRO database (.BAK file) may be requested, or alternatively a read-only user maybe be created for that database which may be accessed via a virtual private network (VPN).
This allows the analyst to independently extract all required tables for current and previous financial periods to perform the necessary analytics.
Tip 2: Extracting data
Once data access has been obtained and tested, the next step is to obtain the required data in a usable format to be analysed. If a back-up file has been received the database must be restored in Microsoft SQL Server to begin extracting data. It is of utmost importance that the back up file and the restored database is located in a secure environment so that client data is not compromised in any way or form.
Once the database is restored or accessed via VPN, data can be viewed using SQL’s SELECT query. This will allow you to view the data contained in a table to identify if the table is required for analysis. The results can be exported to a file (.csv format) which can be imported into your data analytical tool or even Microsoft Excel for analysis. Alternatively, one can connect to the database via Open Database Connectivity (ODBC) and extract the required tables.
We use Arbutus Software to create an ODBC connection to the data and using the SQL Import functionality allows us to execute the SQL query within the software itself. This can then be saved as a procedure to be used in future or on other SYSPRO databases.
Tip 3: SYSPRO Tables
The complete SYSPRO database contains a large number of tables, however, there a few crucial tables which are always needed despite the nature and size of the entity being analysed. SYSPRO table names are relatively easy to understand and therefore with some discretion and research, the required tables can be easily identified. In our experience, the following tables are essential to an analysis of any entity:
- GenTransaction – general ledger detail
- ArTrnDetail – Account receivable transaction detail which contain fields such as: invoice number, customer, stock items sold, sales and cost values, etc.
- InvMovements – Inventory movements for the year showing incoming and outgoing stock movements
- GrnDetails – contains a list of all good received notes for the period liked to the customer purchase order.
- GenJournalDetail / GenJournalCtl – contains the complete journal detail for all journals posted for the general ledger including user and date data.
Tip 4: Preparing SYSPRO tables for analysis
As can be expected in any database design, master and transactional tables are separated. However, in order to provide an in depth insight of the data, it is necessary to obtain master table data as well as data from all relatable tables. This will enable the analyst to not only to provide detail regarding a specific transaction but also understand the nature of the transaction regarding users, purpose of transaction and the source of the transaction.
Below is a simple illustration of the important relations created to GenTransaction (general ledger). The relations provide the following information:
- GenMaster – Account description and categories
- ArCustomer – Customer detail
- ApSupplier – Supplier detail
- GenJournalCtl – User and notation detail
- Reconciliations – SYSPRO data can be reconciled to the appropriate general ledger balances/movements by understanding the source of the transaction. For example, data with a “SA” (sales) source can be agreed to the relevant invoice data tables. Reconciliations help us verify the completeness and accuracy of the data obtained to the data being reported.
These basic analytics can help the analyst understand the data and business in a quick and effective way which provides the opportunity to create even more in depth analytics.
I hope these tips assists you in accessing and analysing SYSPRO data in both an efficient and effective way. Please contact us if we can assist you in analysing any SYSPRO data you may come across.
Salim has extensive experience as an auditor and data analyst. A large number of his clients operate within a SYSPRO environment across various industries. He was involved in numerous projects involving SYSPRO data extractions, reconciliations and providing analytics for external auditors.
Salim Mohamed is a qualified Chartered Accountant registered with the South African Institute of Chartered Accountants (SAICA).
Would you like to discuss this topic in further detail? Please send us your contact details, we would like to get in touch.