MAS 500 Migration Troubleshooting

1
MAS 500 Migration Troubleshooting

• Darrick Coles
• Development Manager MAS 500

2
Agenda

• Introductions
• Overview
• What is Data Migrator?
• Goals
• Architecture
• Key concepts
• Existing migrations
• Migration preparation

• Data tips
• Performance tips
• A typical migration

3
MAS 500 Data Migrator Overview

• Currently supports MAS 90 and MAS 200 versions
  3.60, 3.61 and 3.70
• MAS 90 and MAS 200cs migrations use ProvideX ODBC
  driver
• MAS 200 SQL migrations can be from a DB on the
  same server or any other server
• 3.71 support in MAS 500 version 7.0
• Supports ad hoc migration and is extensible
• Part of Assisted Company Setup
• Multi-step process
• Extraction
• Review, clean-up, etc. of the extracted data
• Insertion into MAS 500
• Insertion report available for detailed feedback

4
Assisted Company Setup
Icons for available activities in the current
step
Have sales and implementers read thisits packed
with MAS 90 to MAS 500 migration tips
5
Enabling an Extraction
If a migration exists, the Migrate button will
be enabled
If an extraction exists, this button will be
enabledyou can enable it, too
6
Goals of Data Migrator

• Provide competitive advantage when upgrading
  customers from supported products
• Provide migrations of critical and/or voluminous
  data
• Provide informative feedback
• Provide a mechanism to fix problems
• Provide a means for others to plug in their own
  migration extracts
• Publish the insertion APIs
• Allow in-the-field extension of Data Migrator
• Make the delivery of additional migrations easier
• Enable customers to modify data using existing,
  well-known tools (Excel)

7
Supported Migration Configurations
MAS 500 Client PC
Source System SQL Database
MAS 500 Database
MAS 500 Client PC
Source System SQL Database
MAS 500 Database
MAS 500 SQL Server
MAS 500 SQL Server
Source System SQL Server
MAS 500 Client PC
MAS 500 Client PC
MAS 500 Database
MAS 500 Database
MAS 90 or MAS 200cs Files
MAS 500 SQL Server
MAS 500 SQL Server
Other Source Server
Source System File Server
8
Key ConceptsStaging Tables

• Minimal referential integrity (RI)
• Really, only data type is enforced
• IDs instead of keysboth foreign and surrogate
• Most enumerated types replaced by character
  strings to allow for named values
• Yes, No for 0, 1, etc.
• Flattened, de-normalized structure

9
Other Key Concepts

• Extraction
• Copying data from a source system to our staging
  tables
• Staging Table Scrubbing
• Manipulating staging table datawe support
• By hand, directly in the staging table
• Using Microsoft Excel
• Applying insertion steps before our insertion
  steps
• Insertion
• Applying business rules to staging table data
  and inserting all records that satisfy the rules

10
Other Key Concepts (cont.)

• Data migration links
• Store information about where the source data
  for a migration resides
• Example \\CompanyServer\AccountingFiles\MAS90\Dat
  a
• Metadata
• Data that helps define functionality

11
Key Points

• We have an API for every migration
• All have the same interface
• Extractions grab all data at once (usually)
• Insertions process a subset of records and
  respond back in a few seconds (usually)
• ACS allows the user to interrupt the asynchronous
  insertion process
• We support multiple steps on both extraction and
  insertion
• Our extractions are DLLs, and insertions are
  stored procedures, but we support both on either
  side

12
So, What Do You Actually Migrate?
13
So, What Do You Actually Migrate?
Major Entities

• GL Accounts
• Vendors and classes
• Customers and classes
• Items
• Inventory
• Bill of materials and routings
• Tax schedules
• Pricing, including
• Price sheets
• Contract
• Product group pricing
• Customer/product group pricing
• Sales commission plans

• Customer items
• Vendor items
• Warehouses
• Transactions and balances
• Open sales orders
• GL transactions
• GL account history
• Posted invoices (open and closed)
• Posted vouchers (open and closed)
• Inventory cost tiers and bin quantities
• Inventory history
• Bank transactions

Including custom fields
14
So, What Do You Actually Migrate?
Other Entities

• Tax classes and codes
• Processing cycles
• Product categories
• Commission classes
• Natural accounts
• Account segments
• Budgets
• Sales tax accounts
• Journals
• Allocations
• Banks
• Bank accounts
• Tender types
• Buyers

• Shipping methods
• Product price groups
• Purchase product lines
• Sales product lines
• Bins
• Vendor payment terms
• Sales teams
• Sales persons
• Sales territories
• Customer payment terms
• Work centers
• Operations
• Tools
• Manufacturing employees

15
Migration Preparation

• Sales preparation
• Sales
• Set expectations
• Know their business and how they use MAS 90
• Whats their favorite thing?
• What do they take for granted?
• Familiarize yourself with the differences
• Dont assume this is a simple upgrade PLAN,
  TEST, CHECK PERFORMANCE, SPOT CHECK VALIDATIONS

• MAS 90, MAS 500 market space
• Prepare!
• Read the documentation MAS 500 Assisted Company
  Setup has lots of information and hints about the
  differences between MAS 90/200 and MAS 500
• Clean up before migrating
• Know and use available resources sales and
  implementation

16
Preparation The Differences
17
Preparation The Differences

• Work Centers
• Differences
• In MAS 90 and MAS 200, costs are maintained at
  the operation level in MAS 500 they are
  maintained at the work center level. There are
  several additional differences between how MAS
  90 / MAS 200 and MAS 500 handle costs including
• MAS 90 / MAS 200 does not allow for setup costs
• MAS 500 does not allow for per-piece costs
• MAS 500 does not allow for cost as a percent
• Before migrating your MAS 90 / MAS 200
  manufacturing data, carefully plan your new MAS
  500 manufacturing system to take the different
  costing methods into account.
• Migration Issues
• The work center ID in MAS 500 is created by
  combining the MAS 90 / MAS 200 work center and
  operation codes to create a unique identifier.
  The full MAS 90 / MAS 200 work center and
  operation codes are used for the work center
  description in MAS 500. No schedule information
  is migrated from MAS 90 / MAS 200.
• List of Work Centers and OperationsFrom Work
  Order Setup, select Work Center Maintenance, then
  click the Printer button. From Work Order Setup,
  select Operation Code Maintenance, then click the
  Printer button.

18
Choose the Right MAS 500

• Small Business Edition
• Fewer than 10 users
• Microsoft SQL Server Desktop Engine (included)
• Up to four companies and four warehouses
• Standard Edition
• Fewer than 30 users
• Microsoft SQL Server Standard Edition
• Up to ten companies and ten warehouses
• Enterprise Edition
• Unlimited users
• Microsoft SQL Server Enterprise or Standard
  Edition
• Unlimited companies and warehouses

19
Set Customer Expectations

• Not a simple upgrade
• What functionality is different
• What functionality is GAP
• What behavior is different
• Fast screen painting in MAS 90 versus fast
  processing in MAS 500
• Dont migrate from a moving target

20
Preparation Technical

• PSG APIs
• Other tools
• Documentation
• Data cleanup
• Server prep
• Know data and performance tips
• Bring in-house for review and possible migration
• Backup
• Validate
• Security

21
Data Tips Whats Being Mapped

• Youll find detailed answers to all your
  mapping questions in the code
• SQL to find extract routines
• select name from sysobjects o WITH (NOLOCK) WHERE
  name like 'spMASExt
• Whats in the routines
• INSERT StgReasonCode (ReasonCodeID, Description,
  ProcessStatus, SessionKey)
• SELECT Cancellation_ReasonCode, Description,
  _at_NOT_PROCESSED, _at__iSessionKey
• FROM SO_09CancellationCode
• WHERE Cancellation_ReasonCode IS NOT NULL
• --------------------------------------------------
  ----------------------------
• INSERT StgSalesTeam(SessionKey, SalesTeamID,
  Description, ProcessStatus)
• SELECT _at__iSessionKey, Division,
  Description, _at_NOT_PROCESSED
• FROM ARB_DivisionMasterfile
• WHERE NULLIF(Division, '') IS NOT NULL

22
Data Tips Historical

• Closed invoices, vouchers need history to migrate
• MAS 90 customers often purge
• Due to long history with the product
• 2 Gig MAS 90 file limit
• Purge helps performance (re reporting)

23
Data Tips Quirks?

• MAS 200 SQL upgrade may parse some MAS 90 data
  that MAS 500 cannot handle
• Cant figure out whats going on?
• Ask your geek to look at the transform thats
  occurring on extract (spMASSOExt)
• MAS 90 Custom Fields ? MAS 500 Custom Fields
• MAS 500 APIs are not always appropriate for
  ongoing insertion of data into MAS 500

24
Hey, I Heard that Migrations Are Slow
...is that true?
25
Migration Performance Tips

• Extraction
• Be patientthere may be lots of records
• Do you need all that history?
• Is all that data being used?
• Insertionmonitor the feedback
• Validations occur for each record
• Report gives detailed feedback, but costs cycles
• Do you need report on good records?

26
MAS 90 ODBC Driver Performance
Dirty ReadCheck this, but only do reading with
this checked!
Cache Size Bigger is better
Burst ModeWill reduce number of times file is
locked
NOTE Version 3.7 ODBC driver performs faster
than 3.6 and 3.61 See documentation menu ODBC
3.x at http//pvx.com/ for ODBC setup
27
MAS 500 Migration Performance

• Get other traffic/users off the SQL box if
  possible
• TempDB is highly utilized in migration
• Make it large before starting the migration and
  avoid resizing by small amounts (or any amount)
• Start with 1 Gig?
• More server memory -- faster migration
• Check if other SQL Agent jobs are running

28
Whats Involved in a Typical Migration?

• What are some of the issues?
• Sales detects a pain
• Customer decision
• Gap analysis
• Not just an upgrade
• Feature gap
• Behavior gap
• Project plan
• Proof of concept
• Migration
• Verification/sign off

29
A Typical Migration

• Set up a migration link to the source system
• Move through the ACS steps in order
• Backup DB before critical steps
• Set up options as necessary
• Enter data for steps not having migrations
• Run migrations
• Compare reports from source and destination
• Pilot the migration to gather any additional
  supporting data

30
MAS 500 Data Migrator Work Flow
Current Accounting System Database
MAS 500 StagingTables
MAS 500 Tables
31
Recap

• Become knowledgeable about the similarities and
  differences
• What Data Migrator is and what it isnt
• Existing migrations
• Data Migrator architecture
• Tips and tricks

32
IIS
DDRK
VB
SDK
TCP/IP
HTTP
MMC
ODBC
OLTP
DLL
QA
BIOS
C
ASP
SCSI
.Net
ROLAP
SNTP
VM
Mouse
WBS
DML
ERD
XP
SMTP
DDL
OLE DB
AIS
OLAP
LDAP
FTP
Win32
UML
SQL
ASCII
OOP
ADSI
IE
QOS
ANSI
NetBIOS
USB
WinSock
VGA
COM