MI9 Data Pipeline — Import to Main Table

What is this?

This document walks through the complete MI9 data pipeline — from the tab-delimited export files your MI9 system produces, through import and team-based aggregation, to the final main table that Margin Master uses for pricing analysis.

MI9 is unique among Margin Master's supported POS systems: instead of building one main table per store, it aggregates all stores into team-based main tables (e.g., Core, NonCore). This means you analyze pricing at the team level rather than the individual store level.

Overview

MM*.txt files (one per team, from MI9)
       |
       v
  [Import & Parse]
       |
       v
  Mi9PointOfSaleData table (raw per-store rows)
       |
       v
  [Team Aggregation]
       |
       v
  StoreGroup_{TeamName}_mainTable (one per team)
       |
       v
  [Ace Catalog Enrichment]
       |
       v
  Final main table (ready for pricing analysis)

Step 1: Prepare Your MI9 Export Files

File Requirements

Place your MI9 export files in the configured import folder (set in Options > Miscellaneous > Default Import Path). Margin Master discovers all files matching the MM*.txt pattern in that folder.

Import File Fields

Every row in the file represents one SKU at one store. The complete set of fields in the import file:

Identifiers

Product Information

Retail Price Levels

Future Pricing

Cost

Vendor

Competitive Pricing

Inventory & Sales History

Status & Flags

User-Defined Fields

Dates

Discovery & Other

Store Product Status Codes

The StoreProductStatus field uses these single-character codes:

Step 2: Import Reads Files Into Staging Table

When you click Import Data, Margin Master:

1. Discovers all MM*.txt files in the import folder
2. Creates the Mi9PointOfSaleData staging table (drops and recreates each import)
3. Parses each file row by row using high-performance tab parsing
4. Maps each row to the data model using hardcoded field mappings
5. Bulk-merges rows in batches of 10,000 into Mi9PointOfSaleData
6. Creates indexes for fast lookups after all files are loaded

The staging table has a composite primary key of (StoreNumber, SKU) with duplicate-key handling, so if the same store+SKU appears in multiple files, the last value wins.

Large file handling: Files are processed in batches of 50,000 records for memory efficiency. Failed batches automatically retry up to 5 times with increasing wait times.

Step 3: Store Manager Controls Team Assignment

Before team tables are built, Margin Master checks the Mi9StoreManager table to determine which stores belong to which team.

Team assignment: By default, store 500 is assigned to "NonCore" and all other stores to "Core". Your support team can customize these assignments.

Step 4: Team Aggregation Builds the Main Table

This is the key step that makes MI9 different from other POS systems. Instead of one main table per store, Margin Master groups all stores in a team together and produces one row per SKU per team.

For each team (e.g., Core, NonCore), a StoreGroup_{TeamName}_mainTable is created.

How Fields Are Aggregated

Since multiple stores may carry the same SKU, fields must be combined. Here is exactly how each main table field is populated:

Identifiers

Product Information

Pricing — CurrentPrice Cascade

The CurrentPrice field uses a priority-based cascade across all stores carrying the item. Margin Master picks the first non-zero value in this order:

1. FuturePriceLevel2 — if a Level 2 future price is set and > 0
2. FuturePriceMain — if a main-level future price is set and > 0
3. RetailLevel3 — if retail level 3 is > 0
4. RetailLevel2 — if retail level 2 is > 0
5. RetailLevel1 — default fallback

The result is the MAX value across all stores in the team for the winning priority level.

Pricing — Other Fields

Cost Fields

Cost fields are averaged only across stores that have inventory on hand:

Why only stores with inventory? Averaging cost across stores that don't carry the item would dilute the true cost. Only stores with on-hand inventory contribute to cost calculations.

Inventory & Sales (Summed Across All Stores)

These are summed because you want to see total movement and total inventory across all stores in the team.

Vendor

User-Defined Fields

MI9 remaps user fields for its multi-store context:

Date Fields

Step 5: Ace Catalog Enrichment

After the team tables are built, Margin Master enriches each row with data from the Ace Hardware cloud catalog (synced separately via Cloud Sync).

Post-Build Updates

Zone Pricing (If Applicable)

If your stores have zone pricing configured, Margin Master applies zone-specific overrides from AceZoneRecords:

Step 6: Calculated Fields

After enrichment, Margin Master computes additional analysis fields:

Impact Calculation (MI9-Specific)

For MI9, impact is calculated by joining the team table back to the per-store staging data using the AltSKU field. This gives a more accurate picture because it uses each store's actual current price and sales history rather than the aggregated values.

Per-SKU Impact = SUM across stores of:
    (Team FuturePrice - Store CurrentPrice) * Store Last12

Tips

"G" store number: In the main table, you'll see StoreNumber = "G" on every row. This is normal for MI9 — it means the row represents the entire store group (team), not an individual store.

AceSku takes priority: When the import file includes an AceSku value, that becomes the primary SKU in the main table. The original MI9 SKU is preserved in the AltSKU field for traceability.

Blocked auto-changes: If the BlockedAutoChanges field is set for a SKU, it appears as User5 = "Y" in the main table. During strategy execution, these SKUs are skipped — their prices won't be automatically changed.

Store not appearing? Check the Mi9StoreManager configuration. The store's BranchCode must match the StoreNumber in the import files, and the Excluded flag must not be checked.

Common Questions

Q: Why do I see "Core" and "NonCore" as my teams? A: By default, Margin Master assigns store branch 500 to "NonCore" and all other branches to "Core". Your support team can customize team assignments in the Mi9StoreManager configuration.

Q: How are costs calculated when some stores don't carry the item? A: Costs are averaged only across stores that have the item on hand (OnHand > 0). Stores with zero inventory don't contribute to the cost calculation, preventing artificial dilution.

Q: What is the CurrentPrice cascade? A: MI9 has multiple price levels. Margin Master picks the first non-zero value in priority order: FuturePriceLevel2 > FuturePriceMain > RetailLevel3 > RetailLevel2 > RetailLevel1. This ensures you always see the most relevant current price.

Q: Where does vendor catalog data come from? A: Vendor catalog fields (Dept2, Class2, ProductGroup2, VendorNumber, VendorName, MAP pricing) come from the Ace Hardware cloud catalog, which syncs separately via Cloud Sync. These fields are populated after the team tables are built.

Q: Can I add custom fields? A: MI9 imports support User fields (UserS, UserO, UserP, UserE1-E5). These map to User1-User5 and UserE1-UserE5 in the main table. Contact support if you need to customize what data appears in these fields.