Avero's Data Extraction
- Extracting Data - System Types
- How do I know what kind of system I have?
- Avero's DEU
Extracting Data - System Types
Avero gathers POS data in a three methods.
On-Premise POS Systems - These systems have a server on-site that runs the POS and stores the data. Installed on this POS servers is Avero's Data Extraction Utility - this utility runs once a day after your POS processes the end-of-day for your site. The utility connects to the database or records storage and runs a set of queries to pull relevant sales data, then compresses and transmits the files to Avero's servers. This data is then loaded into the Avero platform, where you can view it in your reports.
Export Systems - These POS Systems gather the POS sales data into an export containing the data points Avero needs, and sends them on a regular schedule. Avero's servers process these exports and load the data into the Avero platform, where you can view it in your reports. If exports don't arrive or are missing vital information, the POS provider may have to repair or resend the export for Avero to be able to read it.
Cloud Systems - These systems store the POS data in the Cloud and make it available to integrated partners via an API. Avero's software runs queries on this interface to pull the relevant POS sales data and load it into the Avero platform, where you can view it in your reports.
Common Cloud Systems: Toast, Square, Lightspeed
How do I know what kind of system I have?
View the POS Guide for your system in Avero's Knowledge Base to see the POS Type listed. Find your POS Guide by visiting our POS Integrations page and clicking on your system, or a system you are considering.
Avero's DEU, or Data Extraction Utility, is installed on your on-site POS Server to run a daily extraction of your data, then it prepares and sends the data to Avero, where it is processed and loaded into our web-based platform. It looks like this:
What happens on my server?
The Avero Data Extraction program requires that the information from the POS system be stored in an open database format in order to extract the data. Some common open databases include Microsoft SQL Server, Sysbase sqlAnywhere, Microsoft Access, standard DBF files, and text-based CSV files. A number of POS systems store their information in proprietary database formats. Most POS vendors who use proprietary databases provide an export utility that allows a user to take the information out of their proprietary format and export it to an open database format (most commonly DBF or CSV). Once the information is in an open database format (either natively or via a POS export utility), the Avero Data Extraction Engine can perform the actual extraction of data from these open database files.
The Avero Data Extraction Engine consists of three basic components:
Pre-processing Engine – Some POS systems store information from their database in a format that does not allow the Main Extraction Engine to efficiently gather the data. In these cases, the Pre-processing Engine first processes the data in the POS open database format and stores it in temporary tables which can be accessed directly by the Main Extraction Engine.
Main Extraction Engine – The Main Extraction engine runs a standard set of queries against the POS open database to generate the output files that will be loaded into the Avero data warehouse. For POS data, there are four output files generated. If labor data is also being extracted, a fifth output file will be generated.
- Item Sales Detail – a record for every item transaction that occurred on the POS system for the business day being extracted. The information in the Item Sales Detail file includes item number, check number, revenue center number, time of the transaction, sale employee information, quantity sold, order price, discount price (item level discounts only), void information, and authorizing employee information.
- Check Sales Detail – a record for every check level transaction that occurred on the POS system for the business day being extracted. The information in the Check Sales Detail file includes check number, revenue center number, check open and close time, employee information, check level discounts, taxes, and tender information. To ensure data integrity in Avero, check numbers should not be duplicated within a single revenue center in a business day.
- Item Number Definition – item definition and category information for all items sold on the business day being extracted. The information in the Item Number Detail file includes item number, item name, major category name, and minor category name (if applicable). Please note that once an item definition is loaded into Avero’s data warehouse, the information is defined as unique based on the combination of item name and item number. If the item name is changed or if the item is assigned a new number, Avero will not recognize the items as being the same.
- Meal Period Definition – information on the meal periods (sometimes referred to as time periods, or serving periods) stored in the POS system. The information in the Meal Period Definition file includes meal period name, revenue center, and meal period start and end time. To ensure data integrity in Avero, the following must be true of all meal periods within a single revenue center:
- 2 meal periods cannot exist with the same name
- Meal periods cannot overlap
- Meal periods must cover the entire 24 hour period. If an item is sold outside of any defined meal period, it will report in Slingshot as “Unknown” meal period.
- Labor Detail – The Labor Detail file contains a record for every employee clock-in and clock-out that occurs on the business day being extracted. The information in the Labor Detail file includes employee number and name, clock-in and clock-out time, regular and overtime hours worked, regular and overtime pay rates, job class information, and adjustment information when available. This is only available if you use your POS system as your primary timeclock.
Post-processing Engine – The Post-processing Engine makes any necessary final adjustments to the data extracted by the Main Extraction Engine before it is transferred to Avero. This may include calculating meal period end times, calculating labor overtime, or any other number of tasks.
What happens next?
The files generated by the Avero Data Extraction Engine are then prepared for transfer to Avero by compressing and encrypting them. The new file created by this compression and encryption process is referred to as a Compressed/Encrypted Data (CED) file. The CED files are then transferred to Avero for processing. If the Avero Data Extraction program is not able to establish any connection to transfer the data, the data will remain on the extraction machine until it is either manually transferred through user interaction with the Avero Data Extraction program, or until the next scheduled extraction. If the connectivity problem has not been resolved by then, the program will continue to store the extracted data until a connection can be established.
After data is transferred to an Avero server, the Avero Data Extraction program will automatically download a data request file if available to extract additional data.
Once the data files arrive at Avero, they are decrypted, decompressed, and loaded into the Avero data warehouse, and you see it in our web-based platform. Data is generally available within two hours after being extracted from the POS system and successfully transferred to Avero.
What are the common problems with the DEU?
- Firewall Blocker - Avero uses FTP, FTPS, and HTTP as available fil transfer methods, if your firewall blocks one type of traffic or any of the ports we use, the Avero DEU may not be able to connect to Avero and send the files. This is resolved by whitelisting the necessary traffic, addresses, and ports in use. You can find this information here: Troubleshooting - IT & Technology
- End-of-day is not processed: Most POS systems require an end-of-day process to run which closes out the business day and stores the data. Often the data our extraction reads is not available until this process completes so if it occurs late or doesn't happen, we may not be able to extract data on-time for this date.
- Administrator rights: It is important that the Avero DEU and the associated daily task run with sufficient permissions or the extraction may not run or be curtailed, resulting in missing data.