Data Export Setup for Micros 9700

Data Export Setup for Micros 9700

 

The extraction of data for DSTAT from the Micros 9700 depends on a utility program that runs on the Micros server.  This program, 8700sql, is supplies by Micros for the purpose of dumping data from the SQL server database into a text format.  Several tables must be dumped daily to get all the information that DSTAT needs.  Some of these tables are fairly static (such as the list of Revenue Centers and the Menus), but some tables contain check detail that is purged daily.  Therefore, it is critical that this utility is on a scheduled basis to capture the detail prior to summarizing and purging as part of the Micros End of Day process.

 

The setup will follow these basic steps:

  1. Create a CIA-Extract folder on the Micros server and copy the batch files and script files supplied by CIA.
  2. Setup a task on the Micros server to run the CIA_Queries.BAT file on a daily schedule 2 minutes prior to the Micros EOD task.
  3. After the first daily extract, send the output files to CIA for analysis and adjustment.
  4. Install the updated scripts from CIA to the Micros server.
  5. Share the CIA-Extract folder or a higher level folder so that it can be read from the DSTAT workstation.  Alternatively, modify the CIA_Queries.BAT file to copy the extracted data to a shared drive.

 

The following paragraphs describe each of the above steps in greater detail.

 

STEP 1 – Create CIA-Extract Folder and Install Scripts

 

The files CIA_Queries.BAT will be run each day on a schedule two minutes prior to the Micros EOD process.  This batch file runs the 8700SQL utility successive times to extract data from various tables within the Micros database.  Lastly, it runs a CIA program that creates a subfolder and copies the results into that sub-folder.  The name of the sub-folder is the date at the time of the extract.  This is done because the output file name do not change and therefore would be overwritten if not copied.

 

A sample of the CIA_Queries.BAT file looks like this:

8700sql -f clsd_chk_dtl.101.sql

8700sql -f clsd_chk_dtl.102.sql

8700sql -f clsd_chk_dtl.103.sql

8700sql -f clsd_chk_dtl.104.sql

 

8700sql -f clsd_chk_ttls.101.SQL

8700sql -f clsd_chk_ttls.102.SQL

8700sql -f clsd_chk_ttls.103.SQL

8700sql -f clsd_chk_ttls.104.SQL

 

8700sql -f cshr_ttls.101.SQL

8700sql -f cshr_ttls.102.SQL

8700sql -f cshr_ttls.103.SQL

8700sql -f cshr_ttls.104.SQL

 

8700sql -f dsc_def.sql

8700sql -f empl_tips.sql

8700sql -f mi_master_def.sql

8700sql -f rvc_cfg.sql

 

microssave

microsdb

 

STEP 2 – Setup Scheduled Task

 

At the end of the day as defined for each Micros configuration, all the check detail is summarized and cleared to ready the system for the next day.  The End-Of-Day (EOD) reports are stored on the Micros server in text format.  Unfortunately, these reports do not contain sufficient detail to meet the data requirements for DSTAT.  In particular, promo check detail and discounts are not included in these reports.  This detail would be lost if the data is not extracted prior to the EOD process.

 

STEP 3 – Send Extracted Files to CIA

 

One of the quirks of the Micros extraction process is the check detail can only be extracted for one revenue center at a time.  This means that the CIA_Queries.BAT file must make successive calls to the 8700SQL utility for each revenue center (i.e. F&B outlet).  Further, the revenue center number as defined in Micros must be known for each script to work.  Therefore, CIA will analyze the first day’s extracted data to determine what modifications need to be made to accommodate all the F&B outlets.

 

STEP 4 – Install Updated Scripts

 

Typically, CIA can make the modifications required after Step 3 within 24 hours.  The revised scripts must then be copied back to the Micros server.

 

STEP 5 – Shared the Data Folder

 

The last step is to make the extracted data available to DSTAT.  The most straight forward method to accomplish this is to make the CIA-Extract folder a shared resource on the server.  Then, allow DSTAT users access to that folder.  Then, DSTAT can be configured to read the F&B data directly.

 

© Copyright 2009, Computer Ingenuity Associates --- www.CIAsoft.com