Skip navigation

The Micros 9700 data extract for Food-Trak integration is a step in the Micros 9700 ETL process.  The core transformation extracts Micros 9700 transaction data from MCRSPOS database.  The individual sales transaction is identified in the totals table. The Food-Trak format required for the Micros 9700 interface must include the revenue center, the menu item master number, the menu item definition sequence number, and the menu item  price number. The transformation merges these dimensions with the transaction record from MCRSPOS.Totals.

Pentaho Data Integration transformation

Pentaho Data Integration transformation

The entire extraction and transformation is written with Pentaho Data Integration. Pentaho Data Integration is an open source Extraction, Transformation, and Loading application. Using the graphical user interface of Pentaho Data Integration allows rapid application development. It is relatively easy to customize the extract using simple SQL statements in the transformation table input objects.

A required step for  Micros 9700 and Food-Trak data integration is the creation of product records for each product sold. Each product record is attached to either a recipe or a purchased item in Food-Trak. The Micros 9700 Food-Trak interface requires the revenue center number, the menu item master number, the menu item definition sequence number, and the menu item price number. Creating product records is a very time consuming process and requires access to Micros 9700 EMC and a deep understand of the products sold.

My experience with my company is that our restaurants actually have several thousand products.  Remember from my last article that one of our requirements is fine granularity. Therefore, we can have several product records for one item. For example, a food menu item might have a regular price, a half off student price, and a late night menu price. A draft beer will have a double draft regular price, a double draft happy hour price, a double draft Monday Ladies’ night price, a double draft half off price, a pint regular price, a pint happy hour price, ….

We also have very dynamic menus. Each restaurant changes their menu at least seasonally. Seasonal dishes are offered with various themes or featuring fresh local food. New wines are offered monthly, new beers and liquors appear weekly. Our business strategy requires us to change our menu mixes often, while, of course, maintaining guest favorites. Our business strategy also requires us to manage our food and beverage assets at a very granular level. The Micros 9700 and Food-Trak interface is in a near constant state of flux. Thus, menu changes to Micros and Food-Trak product record updates are all handled by Corporate IT.

Especially at the beginning of the Micros 9700 Food-Trak data integration project, exception reporting is very useful. The Food-Trak Micros 9700 interface creates a results report. Exceptions are flagged <Not Found>. By copying and pasting this report into a text file, it can be merged with Micros data via Pentaho Data Integration. The transformation below reads in the results report and attaches the menu item name to record as well as appending a quantity column.

Food-Trak Micros 9700 data integration exception report

The Micros 9700 transformation is run automatically using Pentaho Data Integration Kettle application. The automated transformation saves a date stamp variable which is inserted into the SQL code of the table input object. A batch containing a one line kettle command is scheduled via Windows Task Scheduler.

Advertisements

One Comment

  1. A very elegant solution for the business problem.

    The Pentaho app looks interesting. I’ll have to check it out and see what other transforms it offers.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: