Avero & Micros Simphony (On-Premise)

Micros Simphony is supported for Sales data only.

Avero processes and presents data from your Micros Simphony POS for your analysis and insight. This article details how to handle POS changes, make updates to this data, and how to handle discrepancies or problems you might encounter. For some things, such as adding meal periods or revenue centers, you must complete the action on your side in order for it to be reflected in Avero (maybe with the help of your POS reseller or Oracle Support) and for some changes, such as global category associations, you'll need to reach out to our support team. This article refers only to Micros Simphony On-Premise systems, meaning you have an on-site server hosting your POS. To read about the Micros Simphony Cloud system go here: Micros Simphony ASP Cloud System

In this article:

About Micros Simphony POS
- Versions & Database Types
- Differences between Micros Simphony & Avero
Installation & Reinstallation
Regular Maintenance: Making Updates and Changes
- Change or Add a Meal Period
- Change or Add a Revenue Center
Common Problems
- Missing Data
- Data Discrepancies - Avero's sales data is not matching POS
Frequently Asked Questions About Micros Simphony

About Micros Simphony POS

POS Type: On-Premise

Micros Simphony is an on-premise POS system, meaning that a server at your restaurant runs your POS. Avero's data extraction utility is installed on the machine in your restaurant and every day sends us your sales data.

Versions & Database Types

Micros has many different POS versions, and Micros Simphony is a term that can refer to different versions of their on-premise hardware/software and to their cloud POS product. It is important to understand which Micros Simphony you have to get support and guidance relevant to your system.

Micros Simphony may refer to:

Micros Simphony 1 - also called Micros Simphony First Edition, this is the first of the Simphony iterations and requires Avero's software to be locally installed on your POS Server. This version of Micros may use either a SQL or Oracle database.
Micros Simphony 2 - the most common of the on-premise Simphony versions, this is an on-premise Simphony POS system that requires Avero's software to be locally installed on your POS Server. This version of Micros may use either a SQL or Oracle database.
Micros Simphony Cloud Hosted - the Cloud Hosted version of Simphony differs considerably. It stores your POS data off-site and Oracle prepares and sends an export, instead of locally installed software like the on-premise versions. For this integration, Oracle is in control of the data flow and you'll work directly with them for setup, maintenance, and any data flow issues that may arise.

Additionally, either version of on-premise Simphony may use one of two types of database. These databases are fundamentally different, and since Avero queries the database daily to extract your sales data, the distinction is very important for installation and data support.

The two database types are:

MS SQL Server - The Micros Simphony and Micros Simphony 2 extractions use a Microsoft SQL Server ODBC to extract data from the MCRSPOS database by directly querying the database.
Oracle - The Simphony-Oracle-DB integration queries transaction-level data from the database using SQL*Plus, a utility provided by Oracle on all Oracle database installations. Avero utilizes SQL*Plus to retrieve the data with a read-only connection. SQL*Plus must be installed on your POS server for Avero to be installed and to run the daily extraction.

Of the two database types, Oracle databases are more complex to install and maintain so you may experience delays related to installation if the appropriate information and utility are not supplied in advance of the installation, and all escalations will need Oracle assistance which may cause further delays. The best way to avoid delays is to ensure you have accurate POS Version AND Database Type information, and to confirm all necessary steps are taken in advance of installation per our documentation.

If you are unsure of your POS version and Database Type, we recommend checking with your POS Provider to confirm these details before proceeding. You can read more about uncovering your relevant system information in our guide: Which Micros Simphony System do I have?

Differences between Micros Simphony & Avero

Tax amounts are lost on transferred checks, which may cause minor tax discrepancies, but the totals should still match up with your Micros reporting.

Micros Simphony allows for checks to be open across multiple business days, since Avero extracts sales data daily, this can cause problems. Usually it is rare, but if your business is frequently keeping checks open for multiple days or reopening checks from previous days, be aware:

It is possible that Net Payments and Promotions will not match Gross Sales in Avero. This is due to the possibility of checks being added to or split from checks from previous business days, or checks that are split and only one of the checks is closed. Transfer information in Avero cannot span multiple business days, and therefore even if the data files show a transfer, Avero cannot link it to the original check on a separate business day.
It is possible for a check to span 2 business days and have some payments applied on one business day and some payments applied on another business day. When this happens, Micros includes the payment totals on each days' reports, whereas Avero includes all the payments on the day the check was closed.
If a check is reopened on a day other than the day it was closed, the revenue will be double counted as the check will appear on the original date and the reopened date. If you rerun the Avero extraction for the original date, the original check will be removed and the revenue will no longer be double-counted, however, Avero will not match Micros reports.

Item Sales for Micros Simphony is determined by Check Open time, this may mean that your Sales by Hour Report does not exactly match the hours in which individual items were sold.

Transfers

For check transfers:

The source check will have the original items, qty, and price.
The destination check has the transferred items at $0, 0 qty and the total transfer amount from Micros data.
The transfer total may be $0 in some cases due to the way Micros stores the data.
- - In this case, the transfer checks do not have a transfer link in Avero

When items are transferred from one to check to another in a Micros Simphony system, the individual checks that are part of the transfer may not appear to total correctly in Avero. However, if you take the total value of all the checks involved in the transfer you should get the correct total. The differences in totals are directly related to how Micros stores the transfer totals in its POS database. The transfer total includes the tax amount.

For example: if you had a $10 item plus $1 tax on a check, the transfer amount would be $11. In order to more accurately track sales, the item remains on the original check in Avero, but the tax total remains with the payment. In our example, the original check would reflect the $10 item and an $11 transfer, resulting in a balance of -$1. The destination check would have no item on it, an $11 transfer on, and $1 in tax, resulting in a +$1 balance. When combined together the checks balance out properly, but this representation may not be intuitive to new users at first, but by displaying the checks in this format Avero is able to show a greater level of detail that includes all void, discount, transfer, and split activity.

Unfortunately, due to how tax information is stored in the Micros POS database and how Avero breaks tax out into individual tax types for reporting purposes, there is no way to accurately calculate how much tax is included in the transfer amount. Avero reads the total tax amount from the sale_dtl table in the Micros POS database and it is impossible to remove the tax from the transfer amount to try and balance the check. Often most, if not all, of the tax will end up on the final check in the sequence, even if that check was not the primary check where the payment was made.

Installation & Reinstallation on Micros Simphony

Micros Simphony is installed by a technician on Avero's Installs team. You don't need to be installed to start using Avero! Even though data isn't flowing yet, you'll be invited as an Avero user right away to begin setting up your Logbook and exploring our platform!

If you are a new customer, the Avero Sales Associate who sets up your subscription will open a ticket for your install on your behalf, and you will be sent a technical form to provide us with the necessary credentials on configuration information to remotely access your on-site machine and install our data extraction utility. Once you've returned the form, you'll book an appointment via our automated system to select an available timeslot. At the appointed time our installer will access your machine and complete the necessary work to pull data, including connecting our data extractor to your Micros Simphony database. The next day, once we've received the scheduled transfer, your install will be confirmed.

If you are switching to Micros Simphony from another system, Open a Reinstall Ticket with Avero's Support Team and they will get you started! Otherwise, the process is the same. Be sure to let us know before installing your new system to avoid missing data!

You will also prepare for the installation. This means following our Pre-Install Checklist which will make sure you have the access needed and that our interactions will be marked safe by your system security by whitelisting Avero in your firewall (IT or your POS reseller can assist with this!). Remote access to your onsite server must be confirmed prior to your scheduled appointment, or it will be rescheduled.

Creating Access to Simphony Data - ALL Database Types

Regardless of which database type you use, you must enable the legacy tables for Avero to extract data:

Enable Legacy Tables

There are legacy tables that, if not populated, can impair the full Avero data extraction. You can easily correct this in Micros by following these instructions.

Log into your Simphony EMC.
At the Enterprise Level, under Parameters select Enterprise Parameters
Select the EMC Options tab
Ensure that Options 20 and 21 are enabled. If they are not enabled, please enable them and select SAVE.

Please make sure the checks table and check_detail legacy tables in Oracle

You will also need to use your SYS or SA user to create a read-only database user for Avero's queries. This user MUST have access to all of the following tables.

Tables Used in Avero Queries

Avero will need to be provided with a database username and password with read-access to the tables below (the whole table, please do not limit column access). SQL Server authentication is preferred over integrated Windows authentication. If integrated Windows authentication must be used, then the user configured for database access must also be a local administrator with permissions to install software, configure scheduled tasks, and create system ODBC connections.

Note: The provided user for Avero installation and query must have access to the following tables and should not be your SYS or SA user.

Database User - Must have access to these tables

AUTHORIZATION_LOG
CASHIER
CHECK_DETAIL
CHECK_DETAIL_TAX
CHECKS
DESCRIPTORS
DINING_TABLE
DISCOUNT
DISCOUNT_ALLOC_DETAIL
DISCOUNT_DETAIL
EMPLOYEE
EMPLOYEE_CLASS
EMPLOYEE_INSTANCE
FAMILY_GROUP
HIERARCHY_STRUCTURE
HIERARCHY_UNIT
JOB_CODE
LOCATION_ACTIVITY_DB.GUEST_CHECK
LOCATION_ACTIVITY_DB.GUEST_CHECK_LINE_ITEM
LOCATION_ACTIVITY_DB.GUEST_HIST
LOCATION_ACTIVITY_DB.GUEST_CHECK_LINE_ITEM_HIST
LOCATION_ACTIVITY_DB.TAX_GUEST_CHECK_DETAIL
LOCATION_ACTIVITY_DB.TAX_GUEST_CHECK_DETAIL_HIST
LOCATION_ACTIVITY_DB.TAX
MAJOR_GROUP
MENU_ITEM_CLASS
MENU_ITEM_DEFINITION
MENU_ITEM_DETAIL
MENU_ITEM_MASTER
ORDER_TYPE
ORDER_TYPE_TAX
PERIOD
PERIOD_INSTANCE
REQUIRED_CONDIMENT
SERVICE_CHARGE
SERVICE_CHARGE_DETAIL
SERVING_PERIOD
STRING_TABLE
TAX
TENDER_MEDIA

TENDER_MEDIA_DETAIL
TOTALS
VOID_REASON

REQUIRED - ONLY IF USING POS FOR TIMEKEEPING

TIMECARD

TIMECARD_DETAIL

PAY_RATE

Create access for an Oracle SQL Database - Specific Steps

Step One: You MUST ensure SQL*PLUS is installed and available on the machine where Avero will be installed.

The following command needs to be executed on the 'sys' view of the Oracle database, where <login> is the user given to Avero to connect to the Microsdb database, for each Location_Activity_db table listed below

GRANT SELECT ON Location_Activity_db.GUEST_CHECK TO <login>;
GRANT SELECT ON Location_Activity_db.GUEST_CHECK_HIST TO <login>;
GRANT SELECT ON Location_Activity_db.GUEST_CHECK_LINE_ITEM TO <login>;
GRANT SELECT ON Location_Activity_db.GUEST_CHECK_LINE_ITEM_HIST TO <login>;
GRANT SELECT ON Location_Activity_db.TAX_GUEST_CHECK_DETAIL TO <login>;
GRANT SELECT ON Location_Activity_db.TAX_GUEST_CHECK_DETAIL_HIST TO <login>;
GRANT SELECT ON Location_Activity_db.TAX TO <login>;
GRANT SELECT ON Location_Activity_db.MENU_ITEM TO <login>;

To confirm these steps were successfully completed and you are ready to schedule installation, perform the follow checks on access:

Confirming Access for Oracle SQL Database

Open SQL*Plus and login as the user you created (usually "avero")
- ```
sqlplus username@tnsname
```
Confirm basic access using this query in SQL*PLus
- ```
select count(*) from schemaname.CHECKS;
```
- If a number is returned, continue! If you receive an error or failure such as "table does not exist" review the above steps to ensure all access was granted.
Confirm Legacy Tables have current data
- From a SQL*Plus query get the most recent check closed date from CHECKS and the most recent posting date from CHECK_DETAIL (substituting your system value for schemaname):
  
  select max(checkclose) from schemaname.checks; select max(detailpostingtime) from schemaname.check_detail;
- You should return today's date if the tables are actively recording data

If these are successful, you are ready to schedule an installation appointment!

Create access for a MS SQL Server Database - Specific Steps

The Avero extraction utility will connect to your Micros Simphony database via ODBC. Please make sure the server Avero will be installing on has the latest Microsoft SQL Server ODBC drivers and confirm with your IT team that the Micros Simphony database server can accept ODBC connections. (Link to Microsoft latest drivers download: https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server (copy and paste link into a browser on the server to download and install).

For Micros Simphony 2 versions 2.8 and newer, there are 3 preparatory steps you'll take. Use your sys/sa user to create the user you will send to Avero (reminder, we cannot install using the SYS/SA or SYSTEM users, you'll need to create a user with access for Avero to use).

Step 1: Create the User

If you/your IT support is unfamiliar with setting up users in SQL Server Management Studio, login as ‘sa’ and run the following:

USE [master]
GO
CREATE LOGIN [avero] WITH PASSWORD=N'EXAMPLE', DEFAULT_DATABASE=[MCRSPOS], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [MCRSPOS]
GO
CREATE USER [avero] FOR LOGIN [avero] WITH DEFAULT_SCHEMA=[microsdb]
GO

This will create a user/pwd of avero/EXAMPLE

Substitute your desired password for EXAMPLE

Step 2: Grant Rights in SQL Server Management Studio

Select the Avero user and right-click, access Properties

Update the General settings as seen here:

Make Server Roles = public

For User Mapping, activate the both Public and DB_DataReader for EACH database:

NOTE: If you encounter an error later - you'll need to map the AVERO user to [master] but you do not need to assign any rooes to it under [master]

Under Securables grant access to Connect SQL / sa

Finally, Grant access to the database and Enable the login

Step 3: Grant access to `Location_Activity_db` database

You'll lastly need to make sure Avero has access to the Location_Activity_db database, take the following actions:

USE [master]
GO

GRANT SELECT ON Location_Activity_db.dbo.GUEST_CHECK TO avero;
GRANT SELECT ON Location_Activity_db.dbo.GUEST_CHECK_HIST TO avero;
GRANT SELECT ON Location_Activity_db.dbo.GUEST_CHECK_LINE_ITEM TO avero;
GRANT SELECT ON Location_Activity_db.dbo.GUEST_CHECK_LINE_ITEM_HIST TO avero;
GRANT SELECT ON Location_Activity_db.dbo.TAX_GUEST_CHECK_DETAIL TO avero;
GRANT SELECT ON Location_Activity_db.dbo.TAX_GUEST_CHECK_DETAIL_HIST TO avero;
GRANT SELECT ON Location_Activity_db.dbo.TAX TO avero;
GRANT SELECT ON Location_Activity_db.dbo.MENU_ITEM TO avero;

Step 4: Create the Avero Data Source

Locate the SysWOW64 folder (usually C:\Windows\SysWOW64) and open the ODBC Administrator (odbcad32)
Select the System DSN Tab and select Add
Select SQL Server from the picklist > Finish
Name it Avero and point to the Database Server > Next
Select SQL Authentication - do not use Windows authentication - enter the User and Password for the Avero user you set up > Finish
A pop-up with the ODBC Data Source Details will appear, click Test Data Source
Test completed successfully! You should receive this success message, if not verify the database user credentials are correct.

Regular Maintenance: Making Updates and Changes for Micros Simphony

When changes or updates are needed who is responsible? If something appears incorrect or out-of-date, how should it be updated?

Change or Add a Meal Period in Micros Simphony

Whenever meal periods are changed in Micros Simphony using Simphony Serving periods they will be reflected in the next export we receive from Micros Simphony. Nothing needs to be done on the Avero side to approve/accept/map your meal periods. Feel free to make an update or add a meal period and see the data flow straight to Avero. Note that changes reflect data going forward.

Important to note - Meal Period changes/additions will not automatically update your logbook or saved reports.

Change or Add a Revenue Center in Micros Simphony

You can change or add revenue centers to your Micros Simphony configuration and see them reflected in Avero as soon as the next day. Changing a revenue center's name will only affect the data moving forward.

To add a revenue center, or move a revenue center from one outlet to another in Avero, use our Business Mapping Instructions

To add a new outlet for revenue centers to be routed to, open a ticket with Avero Support to create the new outlet. They will provide you the information to complete Business Mapping Instructions.

To change an outlet name, open a ticket with Avero Support. Changing the name of a revenue center must be done in Micros Simphony, but the business it reports to ('Business' box in report generator, name on the logbook) will be changed by Avero.

To remove an outlet, you can remove an outlet from a user's view by adjusting their access in User Management, to remove an outlet complete for the whole company open a ticket with Avero Support to request it be removed from Avero. Doing so will remove access to the historical data which you may want for reports on previous years and cannot be undone, so be sure this is what you want before requesting.

Categories & Items in Micros Simphony

Avero provides the following Global categories: Beverage, Food, Misc., Other, and Retail. The major and minor categories come from your POS.

To make changes to items you'll need to update Micros Simphony - changing an item name, price, or moving it between categories.

To make changes to major/minor categories you'll need to update Micros Simphony - changing category names, updating which items are in a category, making categories more specific.

To make changes to global categories, you'll need to open a ticket and request Avero makes the change. For example, if Appetizers are appearing under Beverage and need to be moved to Food.

When creating new Categories, they may default to Other and require a ticket to update this mapping.

Common Problems

It is very important to keep an active connection to your POS server should you ever need Avero Support to access the server and assist. Please visit our article on Remote Access to On-site Servers.

Micros Simphony Missing Data

Missing data happens. We invest ourselves continuously into making this process faster and more reliable, but it is not, and will never be, error-proof. The internet goes down; equipment fails, bugs pop up, etc. Occasional delays are to be expected. For Micros Simphony systems, we provide you the ability to recover your own data on-demand, any time you notice it missing, in just a couple of clicks. This data is processed and loaded into the Avero platform automatically within 2 hours, so you don't have to wait for a support agent, have any back and forth, or wait an entire day to rerun your reports. You are able to select the date on a calendar and send it - which means you have your data when you need it.

Simple instructions that take less than three minutes are found here, see On-Premise Data Recovery: Missing / Incorrect Data

If you are experiencing repeated or extended missing data:

Follow the instructions here to attempt to extract and send data: Missing / Incorrect Data, note any errors received.
1. If you receive any ODBC errors, you'll need to obtain the current database credentials, as this indicates the credentials have changed / the correct credentials are no longer stored in the Avero DEU.
  - Once you have the correct credentials, open the Avero DEU and select Options.
  - In the Options window, navigate to POS and update the database credentials.
2. If you extract data successfully and receive an error related to sending the data to Avero, the most likely cause is your firewall is blocking the transmission of data to Avero, try changing the format in which the data is sent by following these instructions: Change File Transfer Format

Data Discrepancies - Avero's sales data is not matching Micros Simphony

If you notice your sales data is not matching up with your Micros Simphony reports, there are three actions you need to complete:

Make sure the data discrepancy exists in Avero by logging in and running a sales report. If you notice incorrect data in an emailed report, it may be related to email settings and not the data itself. See our troubleshooting guide: Troubleshooting Email Reports with Missing or Incomplete Data
Review the Differences between Micros Simphony & Avero to make sure this isn't the discrepancy you are noticing.
Make sure you are using the most up-to-date version of our data extractor by performing an update: DEU Update On Demand, then rerun data for the affected date. If your issue is not resolved, proceed to step 4.
Run a Revenue Center Financial Report from Micros Simphony for the date in question, Open a Data Ticket, attach the report and explain the discrepancy. Make sure to include the report, the date, and revenue center or location if you have access to several, as well as any examples you can provide.

Our team investigates all data discrepancies - due to the nature of our daily data extraction a single date and single-day sales report must be provided. We cannot investigate date ranges, but it is helpful to know if the issue is recurring and you can provide multiple dates.

Frequently Asked Questions About Micros Simphony

Why don't Avero's sales look the same as my Micros Simphony reports?

Simphony uses meal periods to calculate the sales data, but Avero can only extract check open time. This means we may not match up with your Simphony reporting regarding sales by hour.

Tax amounts are lost on transferred checks, which may cause minor tax discrepancies, but the totals should still match up with your Micros reporting.

It is possible that Net Payments and Promotions will not match Gross Sales in Avero. This is due to the possibility of checks being added to or split from checks from previous business days, or checks that are split and only one of the checks is closed. Transfer information in Avero cannot span multiple business days, and therefore even if the data files show a transfer, Avero cannot link it to the original check on a separate business day.
It is possible for a check to span 2 business days and have some payments applied on one business day and some payments applied on another business day. When this happens, Micros includes the payment totals on each days' reports, whereas Avero includes all the payments on the day the check was closed.
If a check is reopened on a day other than the day it was closed, the revenue will be double counted as the check will appear on the original date and the reopened date. If you rerun the extraction for the original date, the original check will be removed and the revenue will no longer be double counted, however Avero will not match Micros reports.

Is there any information Micros Simphony doesn't export to Avero?

There are some data limitations you may notice based on what we receive from Micros Simphony.

Micros Simphony does not supply Item Transaction Time, so Check Open time is used for sales by hour.
Payment reference information, like Customer Name or last 4 of CC, are not available
Tax amounts may be lost when transferring checks, depending on your version and configuration of Micros Simphony.
Micros Simphony 2 only sends data that a transfer occurred, but not the items or amounts transferred, these will be represented as -.01 or a one penny transfer.
Checks that span multiple days can cause discrepancies.

Why am I seeing .01 transfers?

Some systems, such as Micros Simphony On-Premise, send data showing that a transfer happened, but without details on the transfer. Items on transferred checks do not come across in Avero. The only thing we can detect is a transfer which is represented by a -.01 but not the items, or item amounts.

Why do I recover missing data myself?

We've invested in improvements to our data extraction capabilities and documentation so that it is easy for users to recover their data without relying on a back-and-forth with support. We believe this creates a better user experience. Now, when users on-site follow the instructions to recover data (it takes less than 1 minute) they get immediate results and the data is loaded into the Avero platform within 2 hours. You can also open a ticket to recover missing data, but it will not load until the next day.

Does Avero pull credit card information from my Micros Simphony system?

No. Avero pulls payment type information, such as what type of card is used and the amount of the payment from all systems. We do not have access to, collect or store, full or partial credit card numbers or Customer Names associated with credit card payments.

Can I pull back / historical data from my Micros Simphony server?

Yes! If you are a new customer, are missing recent data, or have just added a new revenue center that already had sales that weren't loaded to Avero, you can run the data extraction for previous dates. The data that is available and extractable on Micros Simphony servers varies, but typically at least 45 days is available, in some cases up to 90 days may be available.

Instructions on running the data extraction are here: Missing / Incorrect Data

Avero & Micros Simphony (On-Premise)

Micros Simphony is supported for Sales data only.

About Micros Simphony POS

Versions & Database Types

Differences between Micros Simphony & Avero

Transfers

Installation & Reinstallation on Micros Simphony

Creating Access to Simphony Data - ALL Database Types

Enable Legacy Tables

You will also need to use your SYS or SA user to create a read-only database user for Avero's queries. This user MUST have access to all of the following tables.

Tables Used in Avero Queries

Database User - Must have access to these tables

Create access for an Oracle SQL Database - Specific Steps

Confirming Access for Oracle SQL Database

Create access for a MS SQL Server Database - Specific Steps

Step 1: Create the User

Step 2: Grant Rights in SQL Server Management Studio

Step 3: Grant access to Location_Activity_db database

Regular Maintenance: Making Updates and Changes for Micros Simphony

Change or Add a Meal Period in Micros Simphony

Change or Add a Revenue Center in Micros Simphony

Categories & Items in Micros Simphony

Common Problems

Micros Simphony Missing Data

Data Discrepancies - Avero's sales data is not matching Micros Simphony

Frequently Asked Questions About Micros Simphony

Why don't Avero's sales look the same as my Micros Simphony reports?

Is there any information Micros Simphony doesn't export to Avero?

Why am I seeing .01 transfers?

Why do I recover missing data myself?

Does Avero pull credit card information from my Micros Simphony system?

Can I pull back / historical data from my Micros Simphony server?

Related articles

Step 3: Grant access to `Location_Activity_db` database