Introduction
We typically start projects with a one-off data extraction before implementing a live data connection to the application or data warehouse (for continous daily delta loads).
Performing data extractions through application reports can be cumbersome, create challenges with completeness, and often do not follow a fully standardised format. This is where an API-based extraction (for cloud applications), or a database based extraction (in-house or dedicated cloud), is preferred.
This guidance covers how to extract data from a database such as Oracle or MSSQL using Intellifold data extraction scripts. This instruction is intended for IT personnel who will perform the data extraction.
The steps
The key steps, explained in further detail below.
Step 1 - Data Dictionary information
A quick SQL statement is used to get a list of all tables with their fields and number of records from the application database. Schema restrictions can be applied where relevant. Please select the relevant database:
Based on this information we will provide the relevant data extraction script for your system.
Step 2 - Confirm script restrictions and date range
Before running the full extraction script (which is created based on the data dictionary details), consider the following:
- Entity restrictions (company, unit etc.) configured in the script. These get applied to tables where such field is present.
- Data range constraints are applied to all large transactional tables. For financial information this is typically applied to the posting date, for other transactional date the creation date is used.
Please note that restrictions can by application specific. For example, JD Edwards stores a Julian date format which therefore requires a different date constraint format. - Permissions to read from the relevant database and write to an output directory. MS PowerShell scripts are used for MSSQL extractions and require execution permissions.
Date range example for Oracle EBS:
Date range example for TechnologyOne:
Date range example for JD Edwards:
Date range example for MS D365:
Step 3 - Check disk space and set directory
To avoid extraction delays, we always recommend extracting data to a location on the same server. Files can be compressed and shared after.
Also check the following:
- Specify the Output folder where the files should be stored. If multiple scripts were provided, please use different subfolders to ensure all output per script execution is clear.
- Available disk space in output directory. We can provide an approximation of size based on the data dictionary information.
- User account used for performing the data extraction. This should have the permissions to read all tables and write to the output folder. For MS PowerShell scripts it should also have PowerShell access.
- Time out limitations. Some clients have limitations on how long jobs can run. This can also be different depending on the user account used. Please verify the script can run for several hours.
- Extraction time. While these script are light and can be run in the background it might be preferred to avoid peak hours. Also make sure the extraction occurs after the end of the confirmed period (e.g. financial close).
Specification of the output folder for the data extraction for a database script:
Specification of the server, database, and output folder for a MS PowerShell script:
Step 4 - Run extraction script
After preparation of the scripts, copy and paste the contents of the modified SQL file into your SQL database management tool and execute it against the production database to start the data extraction process. If you have recieved more than one Data Extraction script, run each script in sequence with a different output file path.
For MS PowerShell scripts run the script in the Windows command line (‘R’ for run once). It will call the database and perform the select statements for the tables and restrictions specified in the extraction script. MS PowerShell scripts are more reliable during run-time and significantly faster than pure T-SQL.
In the output directory it will create a file per extracted table and a logfile with the record counts. In case of a PowerShell scripts (MSSQL) also a seperate header file is created per table extracted.
Step 5 - Share data
When the data extraction has completed, compress the output folder with all files using a program like winzip or 7-zip. Since all files are flat text files, the zipped file size will be significantly smaller and easier to transfer.
We accept secure file transfer options, including:
- Intellifold fileshare application. Our secure fileshare as shown in the image allows for easy upload of data to our servers. For security reasons files can only be uploaded and not downloaded through this application.
- Client provided fileshare (SharePoint, GoogleDrive etc.) depending on client tools and policies.
- A SFTP location as provided by the client.
- Intellifold Data Integration. While typically used for building a continuous data flow from source application to the Intellifold platform, it also allows flat file uploads in predefined format.
Troubleshooting
If you receive an error during the execution of the extraction scripts, please contact us with the following:
- Process step when issue occured
- Error message if available
- All files extracted in the output directory
- Any follow-up checks and steps performed
Common issues:
- Incomplete access to read the database tables or write to the specified output folder.
- Insufficient hard disk space. Data volumes can range from a few GB to potentially hundreds of GB for large systems.
- Time-out of the script execution due to connection issues or automated tools that may disconnect long running jobs.
- Tables or fields specified in the script do not exist in the database. The scripts are created based on the data dictionary output.
- Rows are all truncated to a specific number of characters (e.g. 1000 characters). This is a setting in the database tool and needs to be removed before script execution.
