High Level Headaches

I recently worked on a high level upgrade estimate for a client who is in the process of budgeting IT expenses for the upcoming fiscal year. Simple enough, right? But when I get a request for a "high-level" estimate, I always struggle with how to address it. Yes, it may be understood that "high-level" means "ballpark" means "give it your best shot" but at the same time, the numbers presented need to be realistic particularly when they are needed for budgeting by the client.

I am pretty sure I test the patience of my coworkers because I tend to be on the over-prepared end of spectrum, so with the request for something "high-level" I have to remind myself to scale back my desire to do a full blown proposal and estimate. But, still, when the project is completed, what is a fair variance from the high level estimate/rough order of magnitude? 10%? 25%? 50%? And what does the client consider fair? Their expectations may be different than mine.

Of course, there are standard parts of an upgrade. Those high-level tasks serve as a starting point for the estimate, as they are things we have done time and time again.

• Pre upgrade meetings
• Upgrade and test customizations and modifications
• Upgrade and test integrations
• Complete test upgrade
• Complete server and company upgrades
• Complete workstation upgrade
• Perform testing and review
• Complete training

And many of those high-level tasks generally fall within a range of hours assuming no extenuating circumstances. Now, in my scenario, the upgrade would be to GP2010 which introduces another level of complexity and additional questions since it is not yet released. We simply don't yet know what issues may come out of the upgrade process.

So, as I went about pulling together some numbers, here are the guidelines I came up with for myself to keep the scale in check with the request:

• Identify what is considered in scope and out of scope for the upgrade. In this case, it meant identifying the customizations that would be upgraded and those that would not. It also meant specifically excluding upgrades to crystal reports, but including upgrades to existing modified Dynamics GP reports. Not necessarily task by task, but area by area thinking about the project at a high level.
• Keep estimates at a high level, striking a balance between assuming complex issues would occur and hoping that everything goes smoothly. Yes, this is easier said than done. So, I reviewed the modified reports to make sure there was nothing extreme involved but I did not go field by field to determine what would need to be addressed in the upgrade. Considering past upgrades, I then came up with a number that will hopefully allow for some issues without being blown away. The customization functionality is reviewed relative to upgrade enhancements. If a customization interacts with new/upgraded functionality, then chances are the upgrade of the customization will be more intensive than if it affects functionality that has stayed the same.
• Gather all of the basic information for the upgrade including number of companies, number of workstations, and training requirements. Estimates for these items usually come easier to me, given that there aren't dramatic changes in the amount of time it takes to upgrade a company or install a workstation.
• Ask for feedback from those that do the work. I know this sounds obvious, but I will admit to sometimes thinking that I know more than I do :) So, I ask the developer, the upgrade person, etc for their feedback on whether the numbers look realistic.
• Disclose my expectations of the high level estimate to the customer, and ask for theirs. I don't know that there is much more to add :) But if we are on the same page, it minimizes the chance of confusion and frustration if the full project planning for the upgrade results in a different estimate.

So, in the end, I strive for realistic numbers but not necessarily identifying every potential risk and task that the project may include. The result being an estimate that is hopefully close to the end result for the scope proposed (but not including additional items, tasks, or revision to scope). And I had a bit of an epiphany as I completed it. A high-level estimate is only as good as our relationship with the client. If both parties feel that it is approached in good faith, and a relative level of due-diligence is applied on both sides, then the estimate becomes an initial step in the project planning for the upgrade. If the relationship is not positive, then the estimate simply becomes a trap for both client and consultant which can lead to frustration and distrust during the upgrade process and on future projects.

What do you think? What are your tips and tricks for preparing high level estimates without spending a disproportinate amount of time on them? Customers, what are your expectations when you ask for a high level estimate?

Consultant Tools Series: WinDirStat

If you have ever had a hard drive fill up unexpectedly, or desperately needed to free up additional space on a hard drive, you will likely appreciate WinDirStat. It is a free, light weight application that provides disk usage information.

Although the massive, inexpensive hard drives available today are making disk space less of an issue, laptop drives and expensive server drives are often relatively small and do sometimes get filled up with large files.

Old SQL Server backups and bloated SQL Server mdf and ldf files are just two examples of files that can consume a fair amount of space on a server hard drive. And there are lots of culprits on desktop or laptop computers.

I found WinDirStat several years ago while looking for an application that would help me identify the largest files on my hard drive and enable me to clean up files that I no longer needed. What I find particularly powerful and efficient is the visual representation that it provides, allowing you to quickly see large files based on their size, and also see files of the same type based on their color.



I was recently using a video application that occasionally becomes "confused", and continuously writes to a temp file on my C drive, quickly consuming 20 or 30 gigabytes of disk space, eventually filling my C drive. The temp file is always buried in some windows temporary directory, so I always have to search for it. With WinDirStat I can quickly pinpoint it based on it's graphical size, while also reviewing any other large files to determine if I can clean anything else up.

In the process, I found several gigs of other video files that were created by another video editing application. Since they were all the same color, and grouped together, it was easy to identify them and move them to a different drive with more space.

Enjoy your spring HDD cleaning!

Formatting Dynamics GP Phone Numbers in a SQL Query

I'm sure this has been addressed thousands of times over the years, but since I just had to write the script, I thought I would post it here for posterity.

By default, Dynamics GP stores phone numbers as a long string of zeroes. This format is based on the GP Format Definition assigned to phone number fields in GP.

I recently had to write a SQL query that would be used to export vendor information to a CSV file. Because the system receiving this data has a free form phone number field, we wanted to make sure that the phone number data coming from GP was formatted consistently.

I'm sure there are several other ways to do this, but this is the script that I created to put the phone numbers in US phone format. It's pretty basic, but gets the job done.


SELECT
CASE RTRIM(PHNUMBR1)
WHEN '00000000000000' THEN ''
WHEN '' THEN ''
ELSE '(' + SUBSTRING(PHNUMBR1, 1, 3) + ') ' + SUBSTRING(PHNUMBR1, 4, 3) + '-' + SUBSTRING(PHNUMBR1, 7, 4)
+ CASE WHEN RIGHT(RTRIM(PHNUMBR1), 4) <> '0000' THEN ' x' + RIGHT(RTRIM(PHNUMBR1), 4) ELSE '' END
END AS PHONE
FROM PM00200


If you are having to store international phone numbers in GP, then it probably makes sense to edit the "Format" resource in Modifier to use a Fill of Space, and no format string.

Or if anyone has any other tips or tricks on querying and formatting GP phone numbers, post a comment!

Where, Oh, Where Has My Budget Gone?

As we approach the end of the first quarter, many users are starting to revise budgets and forecast for the remaining part of the year. Normally we get at least one call, if not a couple, from users who have accidentally deleted or modified their budgets unintentionally. You may think it could never happen to you, but believe me-- it happens to those who least expect it. So I thought it might be a good idea to review some guidelines regarding the budget windows in Microsoft Dynamics GP.



First, let's talk about the Budget Maintenance window, accessed from Cards>>Financial>>Budget and then choosing Open>>Using Microsoft Dynamics GP or New>>Using Microsoft Dynamics GP.



When you use this window, it is important to note that you are modifying the ENTIRE BUDGET unless you stipulate a range using the Ranges button. So, for example, if you click the Delete button at the top of this window, you will delete the ENTIRE BUDGET. Or, if you click the Methods button an apply a 10% increase, it will increase the ENTIRE BUDGET. This catches users off guard because the window does allow you to select a single account and modify the amounts manually. But keep in mind that all other buttons- like Delete and Methods- apply to the entire budget unless you have restricted the range using the Ranges button.



One way to avoid accidents when using this window is to mark the Preliminary button BEFORE you make any changes, this will allow you to close the window and choose to Discard changes. If actual is marked, you will not get the option to discard changes- you only get the option to Save or Delete. And, remember, Delete will apply to the ENTIRE BUDGET unless (repeat it with me) you have restricted the range using the Ranges button.



So, have I scared you yet? :) But, seriously, it doesn't have to be scary when you need to modify a budget in Microsoft Dynamics GP. Some hints I use in training:

• If you need to make a change to a single account, use the Single Account Budget Maintenance window which can be accessed from Cards>>Financial>>Account>>Budget button. This window only affects the selected account's budget, and allows you to apply a percent change to the account's budget.


• After making changes to your budget, back up the budget by exporting it to Excel from Cards>>Financial>>Budget. That way, if something does happen to your budget you can easily re-import it from Excel.

Feel free to share any tips/tricks you have learned when budgeting in Microsoft Dynamics GP. And with the coming of GP2010, we will get the ability to enter transactions to a budget, and combine budgets :) In the meantime, happy budgeting....

Consultant Tools Series: Password Management

How many passwords do you have to know or remember? I think if you took the time to count them all, you would be pretty surprised.

Multiple e-mail accounts, web site logins, client VPN connections, RDP server logins, GP logins, GP system passwords, and FTP sites are just some of the passwords I have to manage.

Multiply these by the number of clients you have to manage, and you will quickly exceed your ability to remember them, especially if you haven't used them for several months.

I used to track most of the common passwords in memory, and then used a password protected Excel spreadsheet for reference.

But when I added in all of my personal passwords (bank web site, credit card site, health insurance site, retirement account site, dozens of e-commerce sites, ATM pins, frequent flyer sites, etc.), it was clear that all of those passwords were too cumbersome to manage.

I spent a few weeks evaluating different apps that help manage passwords. I tried common free and open source options and I tried a few commercial products. I think I eventually went to CNet Reviews to see what product they ranked as the best password manager.

At the time, they recommended a product called RoboForm, so I downloaded the trial version. At first, I was pretty puzzled by the product, and somewhat disappointed. It didn't work at all like the other password products I had tried, so it took me a while to figure out how to use it properly. But once I realized how it worked, and how well it worked, I was hooked.

I now have over 300 different entries stored in RoboForm, all encrypted. Over 200 of those are web site logins, which include the URL, username, and password. I also have just over 100 "Safe Notes", which are RoboForm's version of an encrypted free form note. Bank account information, frequent traveller account numbers, client network logins, VPN information, you name it.

I have no idea how I managed this information previously. It was scattered in multiple locations, unencrypted, disorganized, incomplete, and not always current.

Now I consistently store all passwords, logins, or any other sensitive information in encrypted RoboForm entries.

Although there are several very good password management applications available, there are a few reasons why I chose RoboForm over other products.

1) Mobile support. RoboForm has both a Blackberry app and an iPhone app (and Android and Windows Mobile and Palm and Symbian), so I can always have access to my passwords and encrypted information, even when I don't have my computer. This is a critical feature for me. I was at the rental car counter at the airport and they couldn't find my frequent renter number (ironically), so I pulled out my Blackberry, typed in my password, and opened my secure note for that rental car company. At the pediatrician's office, I can quickly pull up my daughters SSN. When I'm out of the office, I can pull up client network or GP configuration information, all on my phone.

2) Web based remote access. RoboForm now offers online access to your encrypted password files. Just setup an account and you can synchronize your encrypted files with their site.

3) Seamless browser integration. RoboForm installs an unobtrusive toolbar for IE, Firefox, and Chrome. With a search box, you can type the first few letters of a web site, and it will find your associated site password file. With a single click, it will open the site URL, and automatically log you in.

4) Profiles and Auto Fill feature. Whenever I register on a new web site or have to fill out my contact info, I can now click on a single button on the RoboForm toolbar and it fills out the form for me. I can fill out my name, address, phone numbers, full credit card info, e-mail, you name it. It's amazingly accurate, and also allows me to have multiple profiles, so I can use either my personal information or my work information.

Other apps that I tried had some of these features, but not all, and typically they weren't as refined. And for $40 for two licenses (one for my laptop, one for my desktop), it was a bargain.

If you aren't using a password management app, I would definitely recommend at least trying something so that you can conveniently and securely store and organize your personal and password data and your clients' password data.

ERP Implementation Strategies Survey

Houston Neal has an article on his Software Advice blog asking readers to share their experience with different implementation strategies. He discusses the three general implementation strategies, and offers interesting pros and cons for each.

Personally, I think he left out the best option, which is so brilliant that no textbook or best practices white paper could possibly cover it.

I call it the "historical data migration disaster". You know the one.

It's where the client wants to migrate at least 3 years of data from their old system to Dynamics GP. We're talking individual journal entries, full purchase orders, receipts, sales invoices, inventory balances, bank recs--the works.

This is the ERP implementation that every consultant relishes, the one where you beg your client at least two dozen times to reconsider, and then they give you several painfully valid reasons why they have to have the historical data in their new Dynamics GP system.

If you have never done such a detailed data migration, you simply have not lived life to the fullest, and you cannot possibly call yourself a veteran consultant.

I say we lobby Houston to add "Complete Historical Data Migration (Disaster)" to his survey.

The Curious Case of the Disappearing Dex.ini Data

I recently received an inquiry about a GP 10 installation that suddenly stopped working. While posting a batch, GP had crashed, and would not launch again.

When the user tried to launch GP, they would receive a message saying that GP Utilities need to be run first: "You need to run Microsoft Dynamics GP Utilities before you can run Microsoft Dynamics GP."



But when GP Utilities launched, it then said that the Dynamics system database did not exist, and it had to be setup. "The Microsoft Dynamics GP system database has not been created for this installation."



It was as if GP had just disappeared from the machine and SQL Server.

Puzzled over this, I then checked the SQL Server to confirm that the databases did in fact exist. The database files were present, SQL Server Management Studio displayed them, and I could query them without issue.



Hmmm. So everything looked okay to me, but clearly GP didn't agree.

So I then decided to run SQL Profiler while launching GP Utilities to see what Utilities was doing that caused it to think that the Dynamics database didn't exist.

What I found explained part of the issue, but was puzzling--Utilities was looking for a blank database name, which is why it couldn't find the Dynamics database.



I then tested Utilities on another machine to confirm what the query should look like.



Sure enough, Utilities was not only issuing completely different SQL statements when launched, but it was also not properly querying the Dynamics database.

To continue this process, I walked through the initial steps of the Utilities setup process to see if there were any more clues. And when I got to the System Database setup window, I received another clue.



The database file names were missing the usual "DYNAMICS" name.




So, here's the million dollar question:

What could suddenly give both GP and GP Utilities complete amnesia, to the point where neither even knew the name of the Dynamics database?

Well, the problem was clearly happening before any SQL queries were issued, and clearly the SQL Server and databases seemed okay, so I figured that the problem must be with the Dynamics GP client.

After pondering for a few seconds, I took a look at the Dex.ini. And sure enough, there was something curious going on. This is all that I saw:



When I pulled up a Dex.ini on another GP server that worked, this is what it looked like:



So, somehow, when GP had crashed during posting, it had mysteriously wiped out the majority of the contents of the Dex.ini file.

This in turn gave GP and GP Utilities complete memory loss, and caused the unusual symptoms.

I was able to copy the contents from the working dex.ini file and make a few tweaks, and GP worked fine again.

Another bizarre problem solved, and yet another example of why you should have a good BACKUP PROCEDURE for your Dynamics GP environment.

Forecaster Database Creation Wizard Failure :(

Creating a new database in Forecaster is generally a straightforward affair. Launch the Forecaster Database Creation Wizard, connect to the desired SQL Server, enter the information for the new database, and there you have it. Or, sometimes, you don't. Last week I ran in to an issue where the wizard would "quit unexpectedly" after I had specified the new database name and clicked Next. Odd. I tried it from another machine, same result. But, then, I tried creating the database on a different SQL Server and it worked. Hmmm. I checked the Application Event Log, and sure enough there was a .Net Framework error (not very descriptive, but an error nonetheless).

Turns out, the issue was the original SQL Server we were using had offline databases. I would have never thought to check that, the tech assigned to my case mentioned that they had seen similar issues caused by offline databases on the SQL Server. So we removed the offline databases, and the wizard runs without issue.

Just thought I would share this with you all, as it's not something that I would have thought of on my own :) Happy forecasting...

Cheap Check and Signature Control

There are a variety of third party products out that offer enhanced security control for check printing in Microsoft Dynamcis GP. However, the vast majority of it comes bundled with MICR capability which some clients do not need/want for a variety of reasons including the cost of MICR printing and purchasing a third party product. So, what are the options when someone wants to control the printing of checks from Microsoft Dynamics GP?

Enable Batch Posting Control

• Tools>>Setup>>Posting>>Posting: Enable option for batch approvals and enter a password for Series: Purchasing, Origin: Computer Checks
• Batch must now be approved within GP before it can be printed and posted, Transactions>>Purchasing>>Batches.

Additional Considerations for checks on the fly (checks printed directly from Payables Transaction Entry to pay a single invoice "on the fly"):

• If users have access to the windows used to print "checks on the fly", you will need to consider how to handle this process.
• You can opt to take away security to the Print Payables Transaction Check operation, Tools>>Setup>>System>>Security Tasks, Security Roles, User Security
• Or you can enable batch approvals as noted above for the origin: Payables Transaction Entry. With this approach, you will also need to unmark the Allow Transaction Posting option so users are required to use a batch in Payables Transaction Entry. Also, you need to consider that all Payables Transaction batches will now require approval, not just those that contain "checks on the fly". Batches must be approved before they can be posted (although not before the check can be printed).

Once you have your security controls in place, you can add the signature to your check form in Report Writer. It just needs to be a bitmap format, and you must be using a graphical (not text) check form.

• Open the signature bitmap in an editing program (e.g., Paint) and copy to the clipboard (you can do this in paint by going to Edit>>Select All, then Edit>>Copy)
• In Report Writer, open the report layout
• In the report toolbox, click on the picture icon (looks like a landscape picture) then click on the layout where you wish to place the signature
• In the Pictures window, click New
• In the Picture Definition window, enter a name for the signature. Then click in the picture area and use Ctrl-V to paste the signature in to Report Writer.
• Click OK to return to the Pictures window. Make sure your signature picture is selected and click OK to insert it to the report layout.
• You can click on the picture to move it and resize it as needed.

So there you have it :) Feel free to share your tips regarding check control as well as other processes in GP that you control in a similar "low-tech" manner.

Off Roading and Hindsight

I'm sure I'm the only GP consultant that has ever been part of a project that has gone over budget or missed a deadline.

But to help those of you who have a perfect record delivering on-time and under budget projects, I thought I would share some thoughts about two observations I have noticed about projects that either took longer than anticipated or ran into unexpected complications.

The first is a concept I like to call "Off Roading". I grew up in an area where a 15 minute drive could put you out in the middle of the desert, with not a building or car or person in sight. When I was in high school, a friend of mine fancied himself as an off road type of guy, so he would take his parents' Chevy Suburban out in the desert, pull off the paved road, and just hit the gas.

This wasn't just any Suburban with fancy off road suspension, big nobby tires, and extra clearance around the wheel wells. Nope, this was the kind of Suburban that would hold 2 baby car seats and have groceries in the back. It was the basic model with no special suspension and city road tires. In short, it was not made to be driven at 40 miles an hour across the middle of the desert. Flat tires, cracked windshield, and alignment mayhem were the result.

That's a dramatic analogy for what sometimes happens during a Dynamics GP implementation.

The customer may request a modification to Dynamics GP to accommodate a unique business requirement, and after a little thought, you may figure out how to tweak, bend, bolt, weld, or duct tape something together that will meet the requirement.

There is definitely a need for workarounds, customizations, or an occasional band-aid to handle certain business processes and requirements, since no software package will accommodate every client's requirements out of the box. And often times the workarounds or customizations are trivial or simple. But sometimes they aren't.

In general, I think it's good to at least recognize and acknowledge when you start to go "Off Roading" so that you can begin to assess the risks and plan accordingly.

One project where I had to Off Road was when a client needed to import thousands of GL Clearing Entries. Unfortunately, there is no standard integration for Clearing Entries, so we had to reverse engineer the clearing entry transaction in GP and create our own custom integration. There were a few tricks we have to figure out, but fortunately that one went relatively well.

But then there was the project where a client wanted to fully automate the entire Dynamics GP AP check printing process across over 20 companies. We're talking a single button click to select checks, print a selection report, print the checks, post the check batches, and then save the posting reports for over 20 companies! This was like Off Roading on Mars.

At the time, we worked out a detailed project plan that we thought covered all of the tasks and included sufficient time to handle unexpected complications, so we thought we were playing it safe. Although we did finally deliver a solution that miraculously worked, despite our best efforts trying to plan the project, I think we ended up spending over twice as much time as we had originally estimated. It was vastly more complex and treacherous than we anticipated, with very specific implementation issues that we could not have possibly foreseen, since such a project had literally never been done. It allowed the client to decrease their AP payment cycle from 3 full business days to just a few hours, but it was a challenging project, to say the least.

There are many corollaries to these lessons, such as recognizing your limitations, having the fortitude to decline high risk projects, and developing better project management practices (such as using the Dynamics SureStep Methodology), but I'll leave it at that for now.

And that brings us to Hindsight.

"Why didn't you..." "You should have..." "Why wasn't this..."

It's always easy to see the mistakes in hindsight because you've already made them, they seem obvious in retrospect, and you sometimes have scars to show for them.

Although it would be ideal to avoid the mistakes in the first place, projects don't usually go perfectly, so I think the opportunity is to use hindsight not just to beat ourselves up over the mistakes, but to try and figure out what new practices, procedures, or tools we can adopt to try and minimize some of those mistakes in the future.

You would think that this is obvious, and therefore easy. But it isn't. Despite understanding Off Roading and Hindsight, I recently worked on a GP customization that seemed pretty straightforward during the (non-billable) requirements gathering stage. But the development and implementation was much more complex than I had anticipated. Was there any way for me to fully assess the complexity in advance? Perhaps partially, but not fully, as I can't spend 20 non-billable hours to write a proposal for a 40 hour project. Could I have put in a ludicrous amount of "buffer" time to handle the complexities that I discovered? Highly unlikely, as I would have been unable to justify, in advance, the hours for such a budget, and couldn't have possibly guessed the appropriate number of hours.

So in hindsight, I have learned several important lessons about recognizing when I'm Off Roading, and hope that I better assess the risks the next time the pavement ends and the desert looms in front of me.

So, remember to fasten your seat belt and make sure to check that rear view mirror.

SQL Tip: Searching for 'special characters'

It's impossible to follow a post about the now famous, future GP guru, Aubrey, but I just wanted to share a quick SQL tip.

It seems that I rarely need to write queries that search for characters such as "_" or "%", so this morning when I had to search for all customer IDs that begin with an underscore, I couldn't remember how it was done.

A quick Google search will yield many results showing at least two simple ways to write such a query. I found this one handy:

http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html

I personally find the 'bracket' approach to be easier to read, so with that reminder, I was able to quick produce this simple query:

SELECT * FROM RM00101 WHERE CUSTNMBR LIKE '[_]%'

Returning to the swing of things

It's been a while since I posted on this lovely little blog of ours. And I wanted to update all of our faithful readers, on what I have been doing with my time. On January 10th, we had our first child - Aubrey Jean. So the past couple months have been filled with diapers and lack of sleep, but with plenty of joy as well (she just started smiling a lot in the past couple weeks).



So I was on maternity leave for 6 weeks, and I am now starting my third week back at work. I feel like I am getting in to the swing of things, so a return to the blog is in order. I am also working on a project for Microsoft Learning, updating the installation and configuration curriculum for the upcoming release of Microsoft Dynamics 2010. I am also prepping a What's New in GP 2010 presentation for the upcoming MCT Virtual Summit in April. So I should have plenty to share with you all over the coming weeks!

I know Steve has been keeping up with some great development posts, and I keep seeing all the conversation going back and forth which is the whole reason we started this blog. So I look forward to getting back in to the conversation from an application perspective soon!


Take care,
Christina