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.
Tuesday, March 16, 2010
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.
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.
Monday, March 15, 2010
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...
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
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.
Tuesday, March 9, 2010
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.
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 '[_]%'
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 '[_]%'
Monday, March 8, 2010
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
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
Subscribe to:
Posts (Atom)