Skip navigation

Monthly Archives: October 2010

Food-Trak Import Exception Reports

Last year my post about using Pentaho Data Integration to extract Micros 9700 sales data for importing into Food-Trak discussed exception reporting. Well a year has passed and I am pleased to report that I have made a large improvemet to the way we handle exception reporting. (And also, the Micros 3700 extracts are I wrote about in August are working perfectly.) The last couple of months I have been mentoring two very talented individuals in order for them to take over all my Micros 9700 and Food-Trak operational responsibilities so I can focus more on Business Intelligence and Dashboarding using the Pentaho Business Intelligence Suite.

Our company’s restaurants must keep their menus fresh and relevant. This means that quarterly major revisions are done and monthly new items are added and old ones removed. We have utilized the menu engineering capabilities of Food-Trak for two of our biggest restaurants to help us do this. On the data extract side of things, it is very challenging to keep up with all of these changes. It is relatively quick to add new menu item to the Micros 9700 and Micros 3700 systems. The time consuming part is entering a plate recipe into Food-Trak and completing the sales record, especially when there are 15 new items in one restaurant, 10 new items in another, and another adds a few drink specials!

The way I was handling the exception reports before was by copying and pasting the audit report created by Food-Trak. Then, I would save the text file and run it through a Pentaho transformation to create an Excel Spreadsheet, which included the menu item name from Micros. This was still labor intensive, but it was easier than searching through Micros EMC for each menu item number and name.

This month, my two proteges handled entering all the new menu changes that rolled out for 4th Quarter. There was a major over haul of the menu in our largest restaurant, and several changes in the others. I was able to spend my month creating dashboards, cubes, transformations and jobs with the Penthao Business Intelligence Suite.  One project that was completed this month was automating the Food-Trak exception reports. As well, I created a current check analysis dashboard for all the restaurants and prototyped a KPI dashboard for our hotel division.

The Food-Trak exception reports are created using Pentaho Data Integration. Instead of copying and pasting the audit report and filtering out the ‘not founds’, I query the POS Audit table in each Food-Trak database (we use three) for the ‘not founds’. Then, I simply join the results to the Micros 9700 and Micros 3700 menu item masters to extract the menu item names and output the results to an Excel file and a text file.

Food-Trak requires a specific format for the POS code. To make it easer to enter the code into the Food-Trak sales record, I create the POS code formatted to the Food-Trak specifications. Then, our Food-Trak admins just have to copy and paste the code into the sales record, saving a little time and effort for each entry.

Using Pentaho Data Integration, I then created a job which runs the transformation and emails the files to the Food-Trak admins, their manager, our F&B Director, and to myself. I also send a results email to myself so I know if the jobs and transformations worked. Thus today, we automatically upload hundreds of sales records each morning for every restaurant(there are currently six in all and a few more in the works), and autocreate the exception reports which are delivered to the appropriate parties before the work day starts.

Advertisements