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.

Test Before Installing an Upgrade 10/29/2010

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

A new risk to your software is coming; it is time for an upgrade.  Remember when your system was new?  Remember that huge project to implement the software?  Now, it seems, you don’t know how you survived without it.  Don’t take unnecessary risks; make sure you are prepared for this upgrade.

An upgrade may just be a small change, it could be a large change, it could be a few patches, it could be a brand new user interface, and it could contain brand new modules.  No matter the size, you want to make sure that this upgrade doesn’t break any critical processes before you install it.  Even if you get experts to put it in, you will want to do some testing first.

There are several ways of getting an upgrade installed.  You can get the software company to do it for you.  This can be included in your support contract, or you might pay extra for it.  You could hire a consultant who is an expert on the software you are using.  If you are going this route it is best when you have a working relationship with the consultants so that they understand your business as well as the specific software.  You can also do it yourself.  Many software companies provide instructions for how to put in the new code.

The first step, however, is always going to be testing.  A good software company will test the upgrades themselves.  The reason you pay money for the software support is so that you can trust the software will do what it is supposed to.  If the software doesn’t do what you want, you can call the company for help.  A good consultant will schedule in plenty of testing before an upgrade.  They should know what the upgrade is meant to improve, and with a good understanding of your company, should know what to test.  Of course, if you do this yourself, then testing should be second nature.

There are several areas to test before upgrading.  Any customization to the software should be at the top of the list.  Since you changed the default behavior of the program, you hope that the underlying logic didn’t change.  If it did, you will need to either remove your customization, or re-create the custom code on the new version of the software.

The next areas to test require a little bit of study.  Usually the software company will publish documentation on the changes.  You should review those changes and mark any functionality that is critical.  Sometimes the documentation can be technical and confusing; if that is the case, simply write down the module, area, functionality, or process that needs to be tested. 

Last on the list of testing is any other area you think should be tested before an upgrade.  I have seen people just do a full validation before any upgrade. 

During the implementation phase for this software, you documented your business processes.  Hopefully, you had a documented test script.  Save those!  If you have good test scripts, they can be used again for this process.  If you don’t have test scripts, then make them now and you can use them for each upgrade.  Spend some time on those scripts, they are valuable.  If you have a good set of test scripts, then you understand your system; you have the ability to validate your system and your data.

Make a list of all the functionalities that are important to you.  Start out with broad categories such as A/P, A/R, Manufacturing, or CRM.  Then narrow the focus to specifics such as returning merchandise, or receiving a complaint from a customer.  If you document the steps you take for each of these specifics, and make screen shots of the results, you are on your way to a great set of test scripts.

Before letting anyone upgrade your software system, make sure you back up all of your data.  Make sure you can restore yourself to the previous version in case catastrophe strikes. 

If you have properly tested the new software, then you shouldn’t run into any surprises.  Remember, it’s a global world out there and Technology makes it happen.

Do you need multiple databases? 02/19/2010

Posted by TBoehm30 in Database.
Tags: , , , ,

Should you setup multiple databases for the company? No matter what your desire, you will always wind up with multiple databases. Whether they are backup databases, training databases or development databases, they will be needed.

The answer, however, is NO. You do not need multiple production databases for your company.

Using multiple database has its allure because it allows you to separate your data. You might have internal security issues which require separation of data and access. You might have reporting requirements which demand information to be segregated. You might have data issues which cannot exist on the same database. The easy, quick, answer is multiple databases.

There are too many problems with creating multiple databases. Let’s walk through an example that demonstrates the problems with the easy solution. You work at a company that wants a new ERP system for its 3 subsidiaries (it could be any database software – CRM, accounting, manufacturing, etc.). You want to make sure that each subsidiary doesn’t see any data from any of the others. You don’t want them poaching customers, or gathering data about the entire company.

You need a system that will work the same for everyone, but protect your security as well. You want to roll up accounting into corporate from the subsidiaries and have visibility from the top down. Multiple databases sounds ideal for that purpose.

Your company plans on creating or buying more subsidiaries in the future. Your plan for each new subsidiary is simple: Bring up a new database. As an added incentive, your subsidiaries use similar IDs for their data and would have to make significant culture changes if all their IDs had to change. [Think about a customer Id. If they don’t share data, then each of the 3 subsidiaries need a different Id for the same customer.]

Talk to the software vendor. Do you need to purchase extra licenses because of the extra instances of the software? Will they charge more for upgrades when it is not a single project? Will you have to replicate all customizations 3 or 4 times? Will you need extra hardware to handle the different databases? Can they exist on the same server or even the same instance of the database server?

Next look at your needs at corporate. You want visibility from the top which means logging into multiple databases. Are your executives savvy enough to handle that? Will they get confused logging into multiple databases? Is your IT staff savvy enough to handle the extra load? They will have to support all of them – that might mean simple password requests on 4 systems, or data inconsistencies from corporate reports.

Finally, look to the future. Could you combine your purchasing department to get better volume discounts for shared suppliers? How would you do that on multiple databases? How about centralizing the sales department? Could that be done with the setup you’ve chosen? The same goes for most of the functions that could be centralized, but are not today.

How do you solve these problems? Yes, there is extra work in that. You’ve got to setup security around each subsidiary so they don’t see other’s data. You’ve got to figure out a scheme for setting up IDs such as customer IDs and Supplier IDs that won’t conflict and won’t cause too much disruption. You’ve got to create a plan to bring up new subsidiaries within the existing system.

Again, talk to the vendor. You are going to save money on the software by only going with a single integrated system. You should need fewer licenses, and less expensive hardware. Your single database will be able to consolidate corporate data quickly and effectively.

Your IT department will be relieved from all the extra work. They will only need to support one system. They will only need to create 1 set of reports; even if they have extra security around them. They will have one system to have problems with and solve. There will never be integration problems between the systems. They’ll never have incompatible parameters, or global indicators that don’t match.

Talk to your executives and find out what information they need at their fingertips. Can you create real-time dashboards across multiple databases? Can you provide real-time reports on cash, inventory value, A/R, A/P, etc.? Do they need to export data from the system, and would that work if it was exporting from multiple systems?

The bottom line is that a single database makes sense for a single company. Don’t let the easy answer to tough questions change your outlook. It might take more work to set it up, but in the long run, it’s worth it. As we all know by now, it’s a global world out there and Technology makes it happen.

The Right Tool 03/02/2009

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

We all know that when all you have is a hammer, everything looks like a nail.

My database skills gravitate toward MS Access because that is what I have the most experience in. If somebody needs something quickly, I point them toward Access because it would take longer for me to learn a new tool.

Oh I know that there are plenty of database tools out there – Toad, SQL, Oracle, Table, etc. (Forgive me if I don’t know your favorite.) I know that MS Access may not be the best simple tool for my problems, but it is the one I can use the quickest because I already know it.

So, when I got an IM from a colleague with the query “How good are you at Access?”, I had to wince. Do I say the truth knowing I’ll get roped into some project, or do I brag so I can help my friends, seem really good and get on another billable project? OK, I answered ‘Great’. Of course, I got roped into the project – luckily it turned out to be pretty simple.

She needed a form to enter data that would create MTM records. MTM is Many-To-Many. For anyone who doesn’t study databases for a living, here is the explanation. If you have 2 tables in a database and want to link them (for example link the state table to the country table) in a 1 to many relationship (each country has multiple states, but each state only has 1 country) then that is simple. The first table has a field which points to the ‘many’ table (the state table has a country field).

However, if you want to create an MTM relationship, then you need a new table. The new table can be small because it just points to each of the original 2 tables. For example, you have a Teacher table and a Student Table. You would need an MTM to list out the students who are taking a class with a single teacher, or list the teachers any given student is taking a class from.

She already had her database design with relationships defined and data in some of the tables. She just needed an easy way to select in ‘initiative’ and link it to several project records. She had spent some time in Access, but felt she was burning too many project hours providing no value to the client. Since she is a good responsible consultant, she called in for help.

MS Access has a fantastic wizard. If you are building a form for a 1 to Many relationship system, it will create your form with numerous options. This is out-of-the-box. After a couple of false starts, I used the wizard to create a form that had the initiative table on a form with the MTM table on a subform. Then I created a form with just the project table on it, and added that as a subform to my first form. Now I could choose an initiative, and easily scroll through the projects to find the one(s) I wanted to link to.

The only problem was that when I selected a project, it didn’t automatically feed the ID to the MTM table. For that I needed code:
[Forms]![ExistingProjectInitiatives]![InitiativeProjectMapping Subform]![Project_Title].Value = [Project_Title].Text
That only took a few minutes of research on the web plus some experimenting to get right in Access.

As soon as I started testing it, I realized that the MTM table was getting edited when it should have been creating a new record. I needed to automatically move the cursor to the new record in the MTM table whenever I changed the initiative record. Back to the web. It turned out I needed 3 more lines of code:
DoCmd.GoToRecord acActiveDataObject, , acNewRec

Viola a simple form that she could use for her purposes. I did all of that in under 2 hours, even though I hadn’t so much as looked at Forms in Access in around a decade. I love having good technology.

Having the right tool for the job makes all of the difference. When software works well, it just makes me happy, because It’s a global world out there, and technology makes it happen.

Followup: I have already had to spend some time in Tech Support mode fixing a problem with her database. She made a change to the schema which made one of my new forms misbehave. When will I learn? No good deed goes unpunished.