SPUR GROUP PTY

DATA WAREHOUSE – DATA COLECTION

SPUR GROUP PTY

DATA WAREHOUSE

DATA COLLECTION

5 May 2010

PREPARED BY:

Sedick Isaacs

Winslow Momberg

In Duminy

TABLE OF CONTENTS

Introduction 3

Objectives of Document 3

1 BackUp Store Data 4

2 Restore Store Data 4

3 Update Site Info 4

4 Import DBF 6

5 Update Staging 7

6 Export Staging 8

7 Compress CSV 9

8 Delete CSV 10

9 Transfer Compressed Files 11

A. Appendix – DBF file structure(s) 12

1 ID – Invoice Detail Lines 12

2 IM – Invoice Totals 13

3 Purchases 15

4 Waiters 16

5 MaxiTran 17

B. Appendix – Data Warehouse Framework 18

C. Appendix – Logging 19

D. Appendix – Process Diagram 19

Introduction

This document outlines the Data Collection process from the IronTree server onto the Spur Head office server.

Objectives of Document

The objectives of this document are for Spur IT development:

  1. To provide management and developers a reference document when needing to understand the process.
  2. To highlight known issues and limitations.
  3. To assist with project management.

1  BackUp Store Data

IronTree backs up store data daily at 11.00am. This backup, which also contains the previous day’s transactions, is used for disaster recovery in store and for the Spur Data Warehouse data collection.

2  Restore Store Data

IronTree restores the following DBF (Dbase files) daily at 13h30 from the IronTree server onto the Spur Scrubber server:

·  IM e.g. IMApr10 – Invoice Totals

·  ID e.g. IDApr10 – Invoice Line Detail

·  Purchase e.g. Apr10 – Purchase Detail

·  Maxitran e.g. Maxitran10 – Loyalty monetary transactions

·  Waitrons e.g. Casname – Waitron Details

·  System – System configuration data e.g. POS version etc.

Note: Historical data, for Spur and John Dory’s, dating from July 2007 to end January 2010, was backed up onto a portable hard drive and sent to Spur Head Office for processing. The historical data for Panarottis will be transferred via the “data collection” process.

3  Update Site Info

The purpose of this process is to determine which sites to process and what files need to be imported. The design attempts to answer the following questions:

  1. Which sites are backing up their data?
  2. Which DBF files must be imported
  3. What if we ONLY wanted to import certain sites?
  4. What if we ONLY wanted to import certain files?

Tables SiteDBF and SiteFileDBF are updated daily at 16h00:

Data Collection Version 1.2 May 5, 2010 Page 19 of 19

SPUR GROUP PTY

DATA WAREHOUSE – DATA COLECTION

SiteDBF
Site / Nvarchar
SiteName / Varchar
FilePath / Varchar
Include / Bit
Comment / Varchar
Status / Varchar
DateCreated / Datetime
DateUpdated / Datetime
SiteFileDBF
SiteFileID / Int
SiteID / Varchar
FileName / Varchar
HasChanged / Bit
DateCreated / Datetime
DateUpdated / Datetime

Data Collection Version 1.2 May 5, 2010 Page 19 of 19

SPUR GROUP PTY

DATA WAREHOUSE – DATA COLECTION


Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Prerequisites / 1 / UpdateSitePath / Exec ExecUpdateSitePathDBF / 1

How to run this step?:

  1. SubmitJobs ‘PreRequisites’
  2. ExecuteJobs ‘PreRequisites’

Logging Sample:

Eventlog
ID / Site / EventType / Event / JobName / Status / StartTime / EndTime
1 / XX / Job / Prerequisites / Prerequisites XX / Complete / 2010-05-05 11:29:20 / 2010-05-05 11:42:30
EventDetailLog
ID / EventLogID / EventDetail / Status / StartTime / EndTime / RowCount
1 / 1 / ExecUpdateSitePathDBF / Complete / 2010-05-05 11:29:21 / 2010-05-05 11:42:30 / 17240
2 / 1 / InsertSitePath AA-ABENAKI / Complete / 2010-05-05 11:29:43 / 2010-05-05 11:29:43 / 0

Known Issues & Limitations:

  1. After running this procedure the hasChanged field in the SiteFileDBF table is set to 1, which indicates that file(s) need to be processed. However if we run this process again without running the other steps first, then the program sets hasChanged to 0. None of the other steps i.e. Import DBF, Update Staging, etc will run.

4  Import DBF

This process imports DBF files as indicated in the SiteFileDBF table. The table structures have been based on the latest release of Pilot software. This process has been designed for change i.e. what if a column is added or deleted.

Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Import DBF / 1 / Import IM / Exec Staging.Import_Sales_DBF 'IM', / 1
Staging_Spur / Import DBF / 2 / Import ID / Exec Staging.Import_Sales_DBF 'ID', / 1
Staging_Spur / Import DBF / 3 / Import Purchase / Exec Staging.Import_Purchase_DBF / 1
Staging_Spur / Import DBF / 4 / Import Waiter / Exec Staging.Import_Waiter_DBF / 1
Staging_Spur / Import DBF / 5 / Import Maxitran / Exec Staging.Import_Maxitran_DBF / 1

How to run this step?:

  1. SubmitJobs ‘Import DBF’
  2. ExecuteJobs ‘Import DBF’

Dependencies:

  1. VFP OLE DB driver
  2. Jet OLE DB driver

Known Issues & Limitations:

  1. DB7 files are currently not being imported. This is only applicable for Purchases
  2. The columns are hardcoded in the create table procedures. If new columns get added, then these procedures need to be changed accordingly.
  3. There have been occurences where folder names containing site ids do not match the site id on the Ross system
  4. Link server has run out of memory once in the past and the SQL server agent needed to be restarted
  5. It assumes that the datatypes will remain the same in the future. What if the datatypes change?

5  Update Staging

This process updates the staging tables. We have used different SQL schemas, staging and live, to differentiate between imported data and staging data. This makes it easier to identify the new records that have been added to the staging tables.

Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Update Staging Tables / 1 / Update Live IM Site / Exec Live.UpdateIM / 1
Staging_Spur / Update Staging Tables / 2 / Update Live ID Site / Exec Live.UpdateID / 1
Staging_Spur / Update Staging Tables / 3 / Update Live ID Group / Exec Live.UpdateID_GRP / 0
Staging_Spur / Update Staging Tables / 4 / Update Live Waiter Site / Exec Live.UpdateWaiter / 1
Staging_Spur / Update Staging Tables / 5 / Update Maxitran Site / Exec Live.UpdateMaxitran / 1
Staging_Spur / Update Staging Tables / 6 / Update Purchase Site / Exec Live.UpdatePurchase / 1

How to run this step?:

  1. SubmitJobs ‘Update Staging Tables’
  2. ExecuteJobs ‘Update Staging Tables’

Dependencies:

  1. None

Known Issues & Limitations:

  1. None

6  Export Staging

Exports new imported (status = Extracted) data from staging tables.

Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Export Staging / 1 / Export Staging IM / Exec ExportStaging 'IM', / 1
Staging_Spur / Export Staging / 2 / Export Staging ID / Exec ExportStaging 'ID', / 1
Staging_Spur / Export Staging / 3 / Export Staging Waiter / Exec ExportStaging 'Waiter', / 1
Staging_Spur / Export Staging / 4 / Export Staging Purchase / Exec ExportStaging 'Purchase', / 1
Staging_Spur / Export Staging / 5 / Export Staging Maxitran / Exec ExportStaging 'Maxitran', / 1

How to run this step?:

  1. SubmitJobs ‘Export Staging’
  2. ExecuteJobs ‘Export Staging’

Dependencies:

  1. User needs permission to execute command shell from SQL

Known Issues & Limitations:

  1. None

7  Compress CSV

This process compresses (zips) the CSV files which helps to reduce the bandwidth required to transfer the data.

Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Compress CSV / 1 / Compress Staging IM / Exec CompressStaging 'IM', / 1
Staging_Spur / Compress CSV / 2 / Compress Staging ID / Exec CompressStaging 'ID', / 1
Staging_Spur / Compress CSV / 3 / Compress Staging Waiter / Exec CompressStaging 'Waiter', / 1
Staging_Spur / Compress CSV / 4 / Compress Staging Purchase / Exec CompressStaging 'Purchase', / 1
Staging_Spur / Compress CSV / 5 / Compress Staging Maxitran / Exec CompressStaging 'Maxitran', / 1

How to run this step?:

  1. SubmitJobs ‘Compress CSV’
  2. ExecuteJobs ‘Compress CSV’

Dependencies:

  1. User needs permission to execute command shell from SQL

Known Issues & Limitations:

  1. None

8  Delete CSV

This process deletes the CSV that have been compressed.

Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Delete Staging / 1 / Delete Staging IM / Exec DeleteStaging 'IM', / 1
Staging_Spur / Delete Staging / 2 / Delete Staging ID / Exec DeleteStaging 'ID', / 1
Staging_Spur / Delete Staging / 3 / Delete Staging Waiter / Exec DeleteStaging 'Waiter', / 1
Staging_Spur / Delete Staging / 4 / Delete Staging Purchase / Exec DeleteStaging 'Purchase', / 1
Staging_Spur / Delete Staging / 5 / Delete Staging Maxitran / Exec DeleteStaging 'Maxitran', / 1

How to run this step?:

  1. SubmitJobs ‘Delete CSV’
  2. ExecuteJobs ‘Delete CSV’

Dependencies:

  1. User needs permission to execute command shell from SQL

Known Issues & Limitations:

  1. None

9  Transfer Compressed Files

Copy compressed files from IronTree server onto Spur Staging server using a web service.

Add the following steps to the framework:

Database / Step Group / StepID / StepName / Command / IsActive
Staging_Spur / Transfer Staging / 1 / Transfer Staging / Exec TransferStaging / 1
Staging_Spur / Transfer Staging / 2 / Update Transfer Status / Exec UpdateTransferStatus / 0

How to run this step?:

  1. SubmitJobs ‘Transfer Compressed Files’
  2. ExecuteJobs ‘Transfer Compressed Files’

Dependencies:

  1. Install web service
  2. Install c-sharp dll

Known Issues & Limitations:

  1. Updating the status to transferred takes very long. This functionality has been de-activated in the framework.

  1. Appendix – DBF file structure(s)

1  ID – Invoice Detail Lines

Column / Data Type / Length / Live / Staging / Mapped / Comments
SITE / varchar / 2 / ✓ / ✓ / ✓
OUTM / varchar / 15 / ✓ / ✓ / ✓
PLU / varchar / 15 / ✓ / ✓ / ✓
QTY / decimal / NULL / ✓ / ✓ / ✓
ITEM / varchar / 30 / ✓ / ✓ / ✓
PRICE / decimal / NULL / ✓ / ✓ / ✓
VALUE / decimal / NULL / ✓ / ✓ / ✓
DISCNT / decimal / NULL / ✓ / ✓ / ✓
PTAX / varchar / 1 / ✓ / ✓ / X
TAX / decimal / NULL / ✓ / ✓ / ✓
DTAB / varchar / 6 / ✓ / ✓ / X
CASMON / varchar / 1 / ✓ / ✓ / X
PDEST / varchar / 8 / ✓ / ✓ / X
PLUCOM / decimal / NULL / ✓ / ✓ / X
STAB / varchar / 2 / ✓ / ✓ / X
IQTY / decimal / NULL / ✓ / ✓ / X
PRNC / varchar / 1 / ✓ / ✓ / X
DCDE / varchar / 1 / ✓ / ✓ / X
CASID / decimal / NULL / ✓ / ✓ / X
UID / decimal / NULL / ✓ / ✓ / X
SPLIT / decimal / NULL / ✓ / ✓ / ✓
PSEQ / decimal / NULL / ✓ / ✓ / X
MEALTYPE / varchar / 20 / ✓ / ✓ / X
PNTS / decimal / NULL / ✓ / ✓ / X
ROYALTY / varchar / 2 / ✓ / ✓ / X
EXCOMM / varchar / 2 / ✓ / ✓ / X
INVDATE / datetime / NULL / ✓ / ✓ / X
INVTIME / varchar / 20 / ✓ / ✓ / X
PMNTTYPE / varchar / 15 / ✓ / ✓ / X
PREF / varchar / 10 / ✓ / ✓ / X
USERDEF1 / varchar / 300 / ✓ / ✓ / X
USERDEF2 / varchar / 300 / ✓ / ✓ / X
PLUPNTS / varchar / 50 / ✓ / ✓ / X
FILENAME / varchar / 25 / ✓ / ✓ / ✓
RECORDMONTH / varchar / 10 / ✓ / X / X
STATUS / varchar / 10 / ✓ / ✓ / X
COMMENT / varchar / 100 / ✓ / X / X
EventDetailLogId / int / NULL / ✓ / ✓ / X
DateCreated / datetime / NULL / ✓ / ✓ / X
DateUpdated / datetime / NULL / ✓ / ✓ / X

2  IM – Invoice Totals

Column / Data Type / Length / Live / Staging / Mapped / Comments
LiveID / int / NULL / ✓ / ✓ / X
SITE / varchar / 2 / ✓ / ✓ / ✓
AMOUNT / decimal / NULL / ✓ / ✓ / ✓
IDATE / datetime / NULL / ✓ / ✓ / ✓
TYME / varchar / 5 / ✓ / ✓ / ✓
MGR / varchar / 3 / ✓ / ✓ / X
DISCNT / decimal / NULL / ✓ / ✓ / ✓
PTYPE / varchar / 2 / ✓ / ✓ / ✓
INVNUM / decimal / NULL / ✓ / ✓ / ✓
NONTAX / decimal / NULL / ✓ / ✓ / ✓
TAX / decimal / NULL / ✓ / ✓ / ✓
STATION / varchar / 3 / ✓ / ✓ / ✓
ICNT / decimal / NULL / ✓ / ✓ / ✓
CASID / decimal / NULL / ✓ / ✓ / ✓
TABLE / varchar / 5 / ✓ / ✓ / ✓
CUST / decimal / NULL / ✓ / ✓ / ✓
STYME / varchar / 5 / ✓ / ✓ / X
CHEK / varchar / 3 / ✓ / ✓ / X
OUTM / varchar / 15 / ✓ / ✓ / X
BDISCNT / decimal / NULL / ✓ / ✓ / ✓
CTIP / decimal / NULL / ✓ / ✓ / ✓
ACNT / varchar / 10 / ✓ / ✓ / X
TAID / varchar / 10 / ✓ / ✓ / X
COMM / decimal / NULL / ✓ / ✓ / X
CTEN1 / decimal / NULL / ✓ / ✓ / X
CTEN2 / decimal / NULL / ✓ / ✓ / X
DCDE1 / varchar / 20 / ✓ / ✓ / X
DCDE2 / varchar / 20 / ✓ / ✓ / X