jump to navigation

ERP Access – Getting Things Done 03/26/2012

Posted by TBoehm30 in Database.
Tags: , , , ,
1 comment so far

I need 20,000 products changed.  Can that be done this week?

If you went through the normal ERP screens, that would take forever (at 5 seconds each, that is almost 28 hours, or 3 ½ working days).  Who could spare that much time to get an important, but non-priority, project done?  Who would even have the patience to do that.

For most ERP systems, you wouldn’t have to worry about it taking up over 3 days.  This can be done reasonably easily using an import/export process.  You export the existing data and put it on a spreadsheet so that the new data can be entered easily.  Then, you save the spreadsheet in a format that can be used by the ERP system.  Importing the new data should be simple from there.  It can be done in just a few hours.

Even better would be someone who has direct access to the database.  Give him the raw data and let him create a quick query or program to insert the right data into the correct fields and tables.  That could be done in under an hour with the correct knowledge, skills, and access.

Who do you give that type of access to?  Who do you trust with the ability to make any type of change they want, without additional passwords and auditing?  Who would have both the access to make the change plus the knowledge of the risks of performing the change?

I have worked with many SMBs where the IT groups are great at some problems.  They can reset your password, diagnose network issues, and sometimes even troubleshoot the ERP.  Some problems, however, require more assistance than the IT department can provide.  Changing data in the database requires the knowledge of how fields are linked together and why.  You don’t want to permanently screw up the database because of integrity issues.  Normal databases have tables that should be linked together so that no data is repeated.  Most Normal databases don’t actually follow that to its final conclusion where absolutely no data is repeated (1NF to 6NF), so changing one table without the other might do some serious damage.

There is also a security issue.  If they can make any changes they want, what else can they do?  Would they be good enough to create new records?  Could they create the necessary records that would automatically pay someone some money?  Could they redirect money to themselves?  Some of those rules to separate duties are just for that purpose.  This project would definitely break those rules.

But, now we are back to having to manually update 20,000 records.  What do you do?  This won’t be a one-time occurrence.  You know that next week, someone is going to find another tweak for 20,000 records.

This is where you need someone trustworthy to work outside some of the rules.  You need someone who understands the data, has the access, and has the confidence to make changes to a production database.  Without that person, you are stuck with manually updating 20,000 records. 

You need someone who is good enough to say ‘no’.  If he can say ‘no’ to the boss when asked to make a dangerous change, then you can trust him with the access to make the important changes.  That is the deciding factor in trusting someone with access outside the rules.

Sometimes someone will ask for a change that seems logical, but could produce serious side effects to the system.  You need someone who can stand up, even to the boss, to say ‘no’ that won’t work.  Hopefully they are good enough to find a workaround, but the key is saying ‘no’.  I have worked with too many IT people who have a little knowledge, want to look good to the boss, and end up wrecking everything.

Another helpful tip is to test everything in a testing environment.  Don’t test in Production.  When you are hacking into the database to make a change, you need to double check everything.  Make the change in a test environment and then use the data.  Create some Work Orders, or Purchase Orders; solve some cases, or redirect calls.  You need to do regression testing on those changes.

If you don’t have someone at your company who is that good and that trustworthy, then good luck fixing the products by hand.  It should only take a couple of days without sleep. 

Tell me about your large tedious project; did you hack the system to fix it quickly, or have to spend days doing it the hard way.  I’d like to hear your stories, because it’s a global world out there and technology makes it happen.

Own your Data 11/29/2010

Posted by TBoehm30 in Data.
Tags: , , , ,
add a comment

“There is too much data at my company to be useful.”  “The data we have is old and out-of-date.”  “Users around here don’t enter good data.”  How often are these statements said or thought?

Data is important and should be controlled properly.  The only way to ensure that you have good data is to make someone responsible for it, and have them own the data.  They need to use the data as well as have authority over the information put into the database.  Only when someone relies on data will they take an active part in making sure that the data is valid.

I once had a meeting with users who didn’t like the process of putting in a caller’s first name and last name in the text boxes provide.  They wanted to put all of the caller’s information into the notes section.  I was supposed to figure out how to make that work.  You can’t report on notes, you can’t sort on them, you can’t aggregate them, they are practically useless as a management tool.  I had to tell the users to get in line with the processes of the day and fill in the caller’s information in the proper location on the screen.

Do you have documentation on how data flows through your company?  Do you know where your data comes from?  How good is your data?  How often is it refreshed?  Do you have duplicate information?  What about duplicate sources of information?  If somebody came to me and told me that a report was wrong, I can trace it back to the source to figure out the problem.  Maybe he is looking at an old report, maybe the data is coming from the wrong columns, and maybe a data point or two are bad.  Whatever the problem, I know how to find the original data, determine the refresh rate, evaluate the quality of the data and explain any variance.

Once data gets old, it becomes difficult to validate.  You will need to de-dup your data, or find and remove any duplicates.  This is impossible if you don’t have good data.  How will you know if 2 entries with the same name are the same person or different people who just happen to have similar names?  I don’t know many people who can go through thousands of records to find duplicates and finish without going crazy.  If you find someone like that, keep them around.

Software exists that can do a lot of the validation for you.  You might have to give up control of your data to use it; or it might cost a lot of money.  The last time I looked at that kind of software they gave us percentage rates on the validity of the results.  I wouldn’t use the cheap stuff that had less than an 80% chance of being right.

I once worked with a guy who had to de-dup a huge database.  He explained that very few people with the same last name were born on the same day.  So, if you had their last name and birth date, you could be reasonably certain of duplicates.  Of course, twins and multiples are a problem.  In that case you add in the first name and it is extremely rare to find duplicates that were in fact more than one person.

He told me about twins with the same first name, but different middle names.  Why would a parent do that?  He told me about a time when he had a name with two addresses and two Social Security Numbers that were one digit off.  He just knew that the SSN was a typo for one record, but he couldn’t prove it.  He had to list the two records as two people and it was bugging him.  So, he drove by the guy’s house to see if the addresses were correct (he wasn’t allowed to contact anyone because of privacy).  Sure enough the addresses were on a corner and looked to be the same house.  What could he do about it?  Nothing – he was not allowed to fix the data.

What sort of data issues do you wish you could fix at your company?  Do the right people own the data and take care of it?  Do you have governance to control the quality of the information?

Companies need to value their information, validate it often, and use it to their advantage.  After all, it’s a global world out there, and Technology makes it happen.

Loading ERP Data 08/06/2010

Posted by TBoehm30 in ERP.
Tags: , , , , ,
1 comment so far

One of the first tasks in the go-live plan for a new ERP process is loading the legacy data.  Your old systems have the data you need to keep the company going and needs to be loaded into the new system.  So what do you need to remember?

Start with the parameters of the new ERP software.  You need to make sure they are set in such a way to allow you to load data.  You might want some of your data to have the ID from the legacy system; or at least set the Id yourself.  You might need to set the parameter to bypass requirements like approvals for your POs.  Many of these parameters will need to be discovered during the process of testing the data load process.

In order to load data, you’ll have to understand how to get new data into your new database.  You will need to create a template to transform the old data into a format that the new database can accept.  You might want a program that can easily transform an export from the legacy system into an import into the new system.  You might want connections directly into the database and figure out how to put the data directly there.

This will require mapping the old data to the new data locations.  You will need to figure out what information your new system requires and compare that to the information your old system has.  Next you’ll have to figure out what data the new system doesn’t require, but that you are required to keep.  You may also need to store old data that doesn’t have a location in the new system.

So what data should you care about?

Start with Master Data.  There are several categories:

  • Partners: Customers, Suppliers, Contacts, Addresses
  • CRM data: Contracts, Installed Products
  • Manufacturing data: Products, BOMs, Routers, Carriers, WorkCenters, Locations
  • Sales data: Price Lists

Then move on to the transactional data:

  • Accounts Receivables (A/R) – money your customers owe you and has already been invoiced.
  • Accounts Payables (A/P) – money you owe your suppliers and has already been invoiced.
  • Sales Orders (SO) – the orders from your customers
  • Purchase Orders (PO) – the orders for your suppliers
  • Work Orders (WO) – The document needed to start the production process
  • Balances – The general ledger balances by account
  • Inventory – Quantity of Products on hand

You’ll also find that you need lists and types; for example Product Lines, Invoice types, charge elements, etc.

 If you pay attention to the data going into the new system, your go-live has a good chance of going well.