Epicor MySQL Compass Import

What is this?

The Epicor MySQL Compass import reads inventory data from a MySQL Compass data warehouse running on your local network and loads it into Margin Master for pricing analysis. This is an alternative to the Epicor FTP import for stores that have a Compass data warehouse available. It supports both Ace Hardware/Emery Jensen and Do it Best vendor pairing.

Walk through it in the app: guide 4001 - Connecting to Epicor Eagle. Margin Master can walk you through these steps live: turn on Training / Guide Mode from the Help menu, then type 4001 into the Lessons & Guides box - or ask Margin Master support to run it with you.

Import File Format

MySQL Compass does not use a file. Margin Master connects directly to the MySQL database on your local network.

• Database: The Epicor Compass data warehouse, a MySQL database (typically on your local network)
• Port: MySQL port 3306 by default
• Connection details: Pre-filled on the Epicor tab in Options; the standard values work for most Compass installations
• Access: Read-only
• Total columns: 78 fields read from the Compass inventory table

Key Fields

How Import Works

1. Connect via MySQL / Compass - checked.

2. MySQL Connection - Server, Port, Database, Username, Password, Auth/SSL and Prime Vendor.

3. Compass Options.

4. Test & Save Connection.

5. Troubleshoot Connection..., Restore from Cloud... and Reset to Defaults.

6. Store Manager - scroll down for Open Store Manager...

7. Main Table Processing - the single main table setting, described under Main Table Processing Mode below.

8. Margin Master connects to the MySQL Compass data warehouse on your local network using the server, port, database, and credentials configured in Options.

9. On the first import, all rows are downloaded. On subsequent imports, only rows changed since the last sync are downloaded (incremental sync).

10. Downloaded rows are saved into Margin Master's database.

11. Store filtering matches each store's character to the Vendor Store # you assigned in Store Manager.

12. For each store, the store's main table is updated with the latest data.

13. Vendor-specific enrichment is applied based on your vendor configuration:

    • Ace Hardware / Emery Jensen: Catalog data, zone pricing, and MAP pricing from Ace cloud data.
    • Do it Best: Vendor data, RSC codes, and unit-of-measure scaling from Do it Best cloud data.

14. Ace Direct SKU handling: items with SKU numbers ending in "D" have the suffix stripped to match Ace catalog records.

15. SKU reconciliation removes any local rows for items that no longer exist in the MySQL source (if enabled).

Main Table Processing Mode

The Epicor tab under Tools > Options > POS / Connections has one setting, Use a single main table (one row per SKU per store). It chooses how the main tables are laid out; it does not change what the strategies do or the prices they produce. It is offered on both Epicor import routes; the FTP import has it for per-store processing (store grouping there stays multi table for now).

• Off (the default) — multi table processing. Margin Master builds one main table per store, or one per store group when store grouping is on. Selecting a store picks that store's table.
• On — single main table processing. Margin Master builds one table with one row per SKU per store. Selecting a store filters that table by store number instead of choosing a table, and one set of indexes replaces one set per store. Store grouping still works: group rows (one per SKU per group, cost averaged and on-hand summed across the group's stores) live in the same table, and the Use Store Grouping? switch on the Store Information tab chooses which set your strategies price.

Changing the setting does nothing until you close the Options window. You are then asked to confirm, and the confirmation names every main table that will be replaced. If you answer Yes, Margin Master builds the new table first and removes the old ones only once the build has succeeded, so a failed rebuild leaves everything as it was. No keeps the current mode; Cancel returns to Options.

Either way the rebuild reads every store's Compass data already on this computer (it does not connect to MySQL again), applies the vendor catalog and zone pricing per store as the multi table build does, can take several minutes, and discards all current pricing work — future prices, applied rules and locks. Re-run your strategies afterwards. Later imports rebuild whichever layout is selected: with store grouping off, the per-store rows; with it on, the group rows (the other set is left as it is, exactly as the multi table rebuild leaves the other tables).

The single table is built one batch of stores at a time, so the database log stays small even for the largest Compass data warehouses. For Do it Best stores each store's rows are priced from that store's own RSC catalog, and the MSU retail is read from the same RSC.

Tips

Incremental sync saves time. After the first full import, only changed rows are downloaded. This makes subsequent imports much faster, especially for large inventories.

MySQL connection settings are in Options. Go to Options, then the Epicor tab and check Connect via MySQL / Compass to configure your MySQL server address, port, database name, and credentials.

Store configuration is required. Each store must be matched to a Vendor Store # in Store Manager before its data can be imported. A store with no Vendor Store # (a Needs Setup badge) imports 0 SKUs. If a store imports nothing, see Troubleshooting: Epicor Import Brought In 0 SKUs.

SKU reconciliation is optional. Margin Master can remove local rows for items deleted from the MySQL source. This keeps your data clean, but be aware it will remove any locally-added items. Margin Master support can turn it on or off for your store.

Works with both Ace and Do it Best. Unlike the FTP import (Ace only), MySQL Compass import supports both Ace Hardware/Emery Jensen and Do it Best vendor data, depending on your store configuration.

Common Questions

Q: What is the difference between FTP and MySQL Compass import? A: FTP downloads a binary file directly from the Eagle server. MySQL Compass reads from a local MySQL data warehouse. Both provide the same inventory data. Use MySQL Compass if your store has a Compass data warehouse on the local network.

Q: Why does the first import take longer? A: The first import downloads all rows from the MySQL database. Subsequent imports only download rows that have changed since the last sync, making them much faster.

Q: What vendor data can I use with MySQL Compass? A: MySQL Compass supports both Ace Hardware/Emery Jensen and Do it Best vendor pairing. The vendor enrichment applied depends on your store's vendor configuration.

Q: What happens if an item is deleted from Epicor? A: If SKU reconciliation is on, Margin Master will detect items that no longer exist in the MySQL source and remove them from your local main tables during reconciliation.

Q: What are the key settings for MySQL Compass? A: On the Epicor tab in Options: Connect via MySQL / Compass (on/off), Server (server address), Port (normally 3306) and Database, plus the username and password.