Electronic Data Conversions as a Religious Experience

Whether Yardi, MRI, JD Edwards, Newstar or other property management software you are using, data conversions are sometimes just part of running your business. Unfortunately, the conversion process can be cumbersome and painful. Howard Mednick, our senior programmer, recently gave a “tongue in cheek” slide presentation on this topic. The slide presentation is below:

Why Switch Your Faith In Computer Systems?
(What are the major faiths in property management computer systems?)

• Why are property managers always converting data to new computer systems?
  • Buying and selling properties
  • Technology improvements in software and hardware require upgrades to keep pace with all the new information processing requirements
  • The growth of the operations implies that databases must be upgraded in order to accommodate the processing volumes
  • The 3 major players in the property management and accounting systems market:
    • MRI Software
    • Yardi
    • JD Edwards

What Are The Differences In Beliefs?
(All faiths believe in charging rents, collecting money, paying bills)

• Technical – database software is the backbone of all the systems:
  • SQL Server – MRI, Yardi
  • Oracle – Yardi
  • AS400 – JD Edwards
  • MS Access – smaller systems
• Structural – how the data is stored in tables for the key elements:
  • Properties, buildings, units
  • Leases, tenants, recurring charges
  • Accounting transactions
• The conversion is an exercise in:
  • Extracting data from the source system
  • Re-organizing and manipulating it
  • Loading it into the new structures in the new system

Will I Be Able To Maintain My Identity?
(What are the road maps to salvation of the project?)

• The keys or codes used to identify the key elements must retain their character and uniqueness in the new system
• When converting into an existing operational system there must not be any collisions with existing data
• Mapping tables become the critical method of translating codes from the source system into proper values in the new system
• Mapping tables can also be used to clean up problem situations where code values may have become obsolete or redundant or in need of consolidation
• Every code must be mapped to ensure completeness for the data being converted

Will I Be Able Keep All The Data When Converting?

• How much data should be or needs to be converted
  • All the transaction history
  • Opening balances only
  • Summary or detailed financial activity
  • Inactive leases
• The answer is – it depends
  • How important is historical data for day to day operations
  • How much effort do you want to spend on the conversion process
  • More data to convert invites more potential problem situations to occur
• In general the procedures for the conversion process differ only modestly with the volume of data to convert

What Are The Tools Used For The Conversion?

• Utilities for extracting data out into manageable formats :
  • Text files
  • Comma separated variable files (CSV)
  • Excel spreadsheets
• Data often needs to be manipulated through a program before being loaded back into the database:
  • Accommodating the changes in data structures
  • Applying the mapping tables to convert codes
• Programs also check the referential integrity of the incoming data:
  • Every “child” code has a “parent”
  • No one is an orphan

What Are The Tools Used For The Conversion?

• Dates are critical elements in the converted data:
  • Make sure the date formats are consistent between the old and the new systems
  • When in doubt use a date format in a text field of YYYYMMDD
• Why is Excel so problematic:
  • It is easy to use – too easy, not enough rigour for a database
  • It is too forgiving and will generate errors in the converted data if you are not careful
  • Depending on the volume of data to be loaded Excel can be very cumbersome
  • Some code values have leading zeros which Excel will drop if the data is not managed properly as text

• Building code 00100 is not the same as building code 100

What Are The Procedures In The Actual Conversion?
(What can I expect come judgment day?)

• You must do at least one dry run conversion of the data before the actual go-live conversion:
  • Give yourself an opportunity to check the data
  • Compare balances between the old and new systems
  • Do a test rent up procedure and compare the results
  • When you have finished checking the data, check it again
• When the actual conversion is done the process is very simple – a matter of pressing buttons
• What is the best time during the business cycle to do the conversion:
  • It is wise to have the last month closed when doing the conversion and start the next month on the new system
  • When doing this, close the month a few days earlier than usual
  • Don’t ever do a conversion at a year end, everyone is too busy – that is blasphemy

What If There Is Missing or Incorrect Data?
(What if I have doubts in my new faith?)

• Don’t despair – problems occur even under the best of circumstances
  • Electronic conversions are a science and an art
  • There is always a logical explanation for what happened
  • Move ahead with the new system, mistakes can be corrected over time
• All conversion records should be easily recognized so that problems can be recognized and corrected
• When making an adjustment to transaction errors, manually or electronically, make sure the correction is transparent so everyone sees the reason and result of the correction
• Remember to keep things in balance