Rashed Amini

The ara3n weblog

Archive for February, 2009

The Green Initiative - NAV Address Verification

19th February 2009

I saw this video on Dynamics AX on channel9. They are talking about a new feature they’ve added to Dynamics AX that allows you to measure a company’s carbon footprint. I thought that in order to use this in NAV, it has to come by default with the product. For an ISV to build it and maintain it adds cost upgrades and future maintenance, and clients will not voluntarily purchase it, so that they can enter more data as they run their daily transactions. I thought of other ways that I could build some tool that would improve the efficiency of companies in NAV and lower their global carbon footprint. Most companies ship goods to customers, and if they can verify the address before sending the goods. They would not only save money, but would also lower the carbon footprint. There are many online web services that provide address verification. Most of these services cost money. I looked into UPS. UPS provides address verification, as stated on their website. So I created a new account with UPS, registered to get a developer ID, read the manuals on how to interface with the web service and wrote a simple codeunit. The result was not what I expected. Their address verification only returns the city, State, and zip code. So it only verifies at that level.

Here is the Code for interface to UPS.

Next I started to look for other option and USPS (united State Posted Service) also provided web service for address verification. I followed the same process. Registered with USPS and eceived my developer ID. Studied the web service interface and built my code and tested with the test server. It was giving me the correct information and I was happy that I have finally found a free solution. I applied for production server so that I would test it against actual data and received permission error. After couple of email back and forth and being on phone with USPS, I found out that they only provide the address verification if you are using “USPS SHIPPING SERVICES ONLY”. That’s a big requirement for companies out there that use other shipping systems. Also you cannot batch process multiple addresses. Address verificaton only should be done at order entry time. They did mention that they provide CD’s that you can use to verify your Address for 3800$ per year. Here is the code snippet on how to interface with USPS.

 

The story continues. I was disappointed again. I looked at google maps. But all the interface are built for web applications. Yesterday I stumbled across yahoo Geocode. What is Geocode? Goecode is latitude and longitude of a location on earth. Yahoo provides a web service that returns these two coordinates.

Well how does that help in address verification? Well the API request and address and returns the latitude and longitude. In addition if it doesn’t find the address it suggest addresses or simply states that it could not find it. It is a free service. You need to apply here https://developer.yahoo.com/wsregapp/ for the applicationID. You are limited to 5000 query per IP address per day.

I suggest applying and getting your ID. It’s quick and on the next page you will be provided with ID, which you can use in the following solution I’ve provided.

I’ve basically written this report that loops through customer records and verifies the address with yahoo web service.

 

You can use this report to evaluate your existing customer records. I suggest that once you are done with this, you should implement it at order entry level. So that as orders are entered the user can click on a button that will verify the address for new customers. After implementing this solution. I found out that google also provides Geocode webservice. I believe they have better address database than yahoo. I will leave it up to your to implement that. You can find out more about it here. http://code.google.com/apis/maps/documentation/geocoding/index.html

 

And here is the report 50200 that you can run on your database. Please get the Developer ID first from yahoo and change the following line.

 

The attached report is 50200.
Address verification Report

Posted in Dynamics NAV | 4 Comments »

SQL 2008 Compression on NAV Database

17th February 2009

SQL Server 2008 has introduced a couple of new features. The feature that interested me most is filtered indexes and compression. In this blog I will talk about my experience with compression. I deal on daily basis with many clients. So I keep a copy of their database on my local pc. As most of you know NAV databases can grow pretty quickly. And keeping all these copies becomes a challenge. I decided to test compression on a 22 gig database. I used the following query to compress all the tables and indexes in the database. This is a 4.0 database with 2009 executables.

DECLARE
@T varchar(100),@SQL varchar(255),@INDEX Varchar(255)

DECLARE T_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = ‘U’

SET NOCOUNT ON
OPEN T_cursor
FETCH NEXT FROM T_cursor INTO @T

WHILE (@@FETCH_STATUS -1) BEGIN
Set @SQL = ‘ALTER TABLE [’+@T+’] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’
Set @INDEX = ‘ALTER INDEX ALL ‘+’ ON [’+@T+ ‘] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’
Exec(@SQL)
Exec(@INDEX)
FETCH NEXT FROM T_cursor INTO @T
END

CLOSE T_cursor
DEALLOCATE T_cursor

 

The query ran for an hour and database size dropped to 7 gigs. That’s 68 percent drop in space. I’ve used the database for two weeks and have not noticed any difference or issues. SQL Server 2008 allows for data compression in tables, indexes, and partitions, which can save disk space and allow more data to fit into RAM, increasing query performance. Changing objects in NAV does not remove the compression. I would definitely compress test and development environment database. As for production, I would create a separate partition file group and put old data into the file group and compress the file group. SQL 2008 also allows compression for backups as well. I have not tested it yet. There is a trade- off when use compression. You will notice higher CPU usage. For my test I saw 5 to 15 percent more CPU usage, which really not a trade-off considering that on most system the SQL Server CPU utilization is in 15 to 20 percent. The reason is that majority of NAV queries are very simple and do not contain any joins.

Posted in Dynamics NAV | 5 Comments »