Excel ConsultantSolutionsSample “Smarter Microsoft Solutions”: From Our Clients’ PerspectivesSample Solution: Data Migration From Microsoft Access to Microsoft SQL Server for Honda

Sample Solution: Data Migration From Microsoft Access to Microsoft SQL Server for Honda

Sample Solutions: From Our Clients' Perspectives

The Client's Initial Need

Why They Reached Out For Microsoft Data Migration Services

♦  The client reached out in a panicked state. They had just came to terms with an operational blunder in their accounting and finance departments.  This was a big deal. This was a HUGE DATA Issue, literally. Their historical data was at risk of loss or inaccuracies.

 

♦  The reason the client reached out for data migration services was because they came to terms with the astonishing fact that their data was not housed in a proper data warehouse or ERP system but was rather housed in dozens of one-off Microsoft Access databases. Their data spread across hundreds of Access files.  This is their detailed transactional data for their North American operations, all of it.

 

♦  A second concern was this, the databases were saved on the local drives of the persons responsible for running the Microsoft Access databases. The data was not secure nor necessarily accurate. It was like the wild west of database development.  Databases built by accountants, financial staff, directors, etc.

 

♦  The client was very concerned that each Access database was either built by and/or maintained by the person sitting at that specific desk, by job title.  To make the matters worse, the person sitting at the desk was the only person that knew how to run the file. Forget about proper documentation.

 

♦  To make matters worse, each database was unique in design; there were no standards or common traits in the various Access databases. A cluster mess is what it is.

 

♦  The Client’s Initial Need Was Met: The client’s initial need was to put all of their financial data into a properly built and maintained data warehouse, a new ERP system, and with our data migration services they were able to accomplish their goal.

 

 
The Technical Details

What They Wanted From The Effort

♦  The technical details were troubling.  This was a massive undertaking and it was to be expensive.  They had so much data to migrate and setting up and populating the ERP system took scores of people a two year effort.

 

♦  Each Microsoft Access database can be up to 2 gigs in size, based on Microsoft’s size limit.  That is not a lot of data, definitely not “big data”.

♦  But that is on an individual file basis, the data in totality was massive, it was HUGE Data.

♦  The client had unintentionally spread their data across hundreds and hundreds of independent Microsoft Access database files, going back more than two decades.

♦  This was an oversight of the managers of these departments, allowing their staff to build one-off databases that were not properly built nor documented.

 

♦  Technically, they first wanted to create new and properly normalized data tables in both Access and SQL Server. SQL Server would house the data migrated from Microsoft Access and it would support the new ERP system.

 

♦  Consolidating the data from all of those accounting and finance Access databases for all of those years in hundreds of MS Access files with their different layouts and data types was technically challenging.

 

♦  Technically speaking, this would turn into a huge undertaking.  Migrating the data from Access to SQL Server would ultimately take running data migration action queries on four computers at the same time. Processing and scrubbing the data would take 9 months of working six to seven 10-hour days work per week.  There was just so much data to migrate, HUGE Data.

 

 

 
The Challenges

Technically Speaking, the data was in too many files in too many different formats and the computers were weak and processed slowly.

♦  As I have said, there was so much data in so many different files, that this was not is termed “big data”, but rather what I call “HUGE Data“.

♦  Each database was unique, as such custom action queries had to be written for each and every file, and then tested.

♦  Four computers running 10 hours a day, 6 days a week, churning the Access data into the SQL Server tables shows the technical challenges.

♦  If I could have had 6 computers I could have kept those just as busy.  Too much data, one person scrubbing it, on four computers.  This went on for 9 months.

 

 
Our Smarter Microsoft Solution

Build It Properly, Based on Advanced Techniques

♦  There are so many ways to do the most common tasks in the various Microsoft desktop productivity applications such as Excel, Access, Office and even SQL Server.

Programming the optimal solutions for our clients is what we do when we build a better solution, a “Smarter Microsoft Solution”.

 

 

♦  Our Data Scientists and Data Visualization Experts will build you powerful Microsoft Excel dashboards, advanced macros and even data migration.

 

 
The Results

Did We Deliver The Goods

♦  We did what we came to do, we captured all of the necessary data, we transformed it into the new formats, and we retired all of those messy Access databases.

♦  After nine months all of the Access databases were closed for the last time, and the client now used their new ERP system.

♦  Another successful data migration project for the Microsoft Excel and Access experts.

 

♦  The Client’s Initial Need Was Met: The client’s initial need was to put all of their financial data into a new ERP system.

♦  With our Microsoft database migration programming services they were able to accomplish their goal, on time, on budget.

 

 
Sample Solution: Data Migration From Microsoft Access to Microsoft SQL Server for Business - Honda North America

 

Sample Solutions From Our Clients’ Perspectives: Massive Data Migration Project From Microsoft Access to Microsoft SQL Server for Honda North American Finance

 

♦  If you have worked with Microsoft Excel for a good amount of time it is likely that you have heard the trendy catchphrase “Big Data“. 1) Big Data, 2) Dashboards, 3) Power BI are the top three MS Excel catch phrases in the world of Microsoft programming, consulting and training. “Visualization Experts” is a close fourth.

♦  Big Data is as data scientists and data visualization experts like to describe it as a significant amount of data. How much that is no one knows or agrees, but it is really just a large amount of data, 30 mb or 300 TB, your perspective is what makes the data small, big or huge data.

♦  In reality databases are built to house excessively large amounts of detailed data so the phrase big data is not really all that meaningful or important, marketing really. But if you want to use the phrase, use the phrase. If big data is a a whole lot of data, then huge data is like big data on steroids.

♦  For this data migration project there was so much data that needed to be scrubbed and migrated that it took almost a year to complete. Now that is HUGE data!

 

♦  The client had allowed their data to get away from them, and it become unruly. What the client discovered after a lengthy and in-depth analysis of their North American operations was this, they were exposed to large and possibly critical data integrity, security issues as well as privacy concerns. In a nutshell, what they had were hundreds and hundreds of one off databases that housed all of their historical transaction data, going back decades, tens of millions of rows of data, millions and millions of records.

♦  This mixed up mess of databases was in fact the client’s data warehouse, across hundreds of CD/DVD files. This was a rude awakening and the alarms went off.

♦  A costly new ERP system was the solution. A costly multiyear project to migrate their systems from on-off Access databases to an expensive ERP data warehouse

 

  • Over the years, dozens of one-off Microsoft Access databases were built to house client transaction data
  • Each of the Access databases was unique; no simple standards of properly normalized data was visible
  • The Access databases were saved to the desktop on the local hard drive, and then stored on CD/DVD.
  • There was little to no documentation on how to use and to update the databases
  • A single individual knew how to run each independent database, say a Senior Financial Analyst, or an accountant
  • If a person was not at work, the work would not get done, period
  • If a CD/DVD was lost, so was the underlying data

 

A little background, we have had so many clients over the years who were in very similar situations. And they all got to the same place by doing the same thing; they bypassed IT and built their own databases using Microsoft Access. All one-off databases, usually poorly built, built by the accountant or financial analyst responsible for outputting the data. Documentation, data integrity, security, all at risk due to lack of controls and standards. It was like the wild west of data rangling

 

♦  If you would like to discuss our data scientists and visualization experts to build one for you, give us a call at 877-392-3539.

 

Get A Free Consultation

Contact Us
  • This field is for validation purposes and should be left unchanged.