The Art of NAV –Dynamics NAV 2009 network Stats

Today I prepared some stats about network traffic in NAV 2009 RTC client. You can compare the results with C/Side client (classic client).

Measuring: for measuring I have used the Microsoft Network Monitor for catching the NAV communication between client and DB Server/Service tier. Than I create sum of Frame Length, TCP Payload length and count of frames for the communication for each process. The results are not exact, because there are many things I didn’t take into account (cold/warm buffers, transfers of objects into classic client, used another order for measuring etc.). It means take the stats as example, how it can look like, but not as precise bandwidth measuring. Sizes are in Bytes.

Results:

Conclusion:

Form the table you can see the effect of 3-tier system. When you are processing some data, RTC is much better, because no raw data are transfered. Classic client needs to read all data to process them. But problem for RTC are Reports. When you want to print something in RTC, the client need to transfer the Flat Table structure with all the data to process them in the Reporting System Client. And this table can be big. One page with order confirmation took 1MB of data. I do not want to see some 100page report… and I am not talking about bitmaps on the report (e.g. Company logo). But because printing is not critical operation in most cases in NAV, it is OK, because critical things like data processing are 90% of whole application. When you create sum for whole data transferred for both clients, classic client transferred around 10MB and RTC only 5.5MB. Still, RTC is much better with the transfers and this difference will be much bigger in real live when you are mostly posting and sometime printing. Another difference is the Frame count. RTC is sending much less frames but they are bigger. It can lead to change in performance too. But this is question for some Network specialist.

What is you opinion? Are you happy that I created this table? Was it helpful for you?

I am waiting for your comments…

Kamil (Kine)

October 9th, 2008 Posted by kine | Dynamics NAV, NAV 2009 | no comments

The Art Of Nav – NAV 2009 CTP4 stats

Today I made some statistics about NAV 2009 CTP4 tables and compared the result with the stats for NAV 5.00SP1. There are the tables:

Tab 1) Field relations by type:

You can notice, that the biggest difference is in COUNT flowfields. It is mainly because these new stacks in the new RoleTailored client.

Tab 2) Field data types:

Tab 3) Field data types including length:

Tab 4) Overall stats:

As you can see, there is no big increase of table count and field count. The change is mainly because the new functionality for RTC. There are new tables with flowfields for calculating counts of documents and other information.

September 24th, 2008 Posted by kine | Dynamics NAV, Uncategorized | no comments

The Art Of Nav – NAV 5.00SP1 Stats

Today I made some statistics about NAV 5.00SP1 tables. I know that the stats have no purpose, but you can see on them how complex the system is. It can help to someone, who just want to know, how much is something used in standard etc. There are the tables:

Tab 1) Field relations by type:

A you can see, there is 6517 relations between fields (TableRel, SUM, Lookup etc.) or tables (Exist, Count). Of course, most of them are table relations and SUM flowfields.

Tab 2) Field data types:

From this table you see that most used data type is Code – of course, because most of relations are made through some code and the code fields are used everywhere… Second is Decimal – yes, we are working with ERP which counts money, inventory…

Tab 3) Field data types including length:

Most used is Code with length 10 – all basic lists are referred by this data type. But newer the longer fields are used (Code 20), which have enough space for possible longer codes. I personally when I create new table, I use the Code 20 to precede possible problems with additional extension of the field in future. Very interesting are field length at the end of the table like Text118, Code1, Text63…

Tab 4) Overall stats:

I think that the last table doesn’t need any explanation. In average, if you change some field’s length, there is more than 1:2 chances that you need to change length of another field which refer to the first field.

I hope, that you made better picture of the complexity of whole Microsoft Dynamics NAV system. May be that you now understand, why the people around this system needs long time to gather enough experiences to understand the system. It is why the NAV is so great product, because regardless this complexity, it is easy to change and develop additional parts. Do not forget that these stats are just for the basic version of the system. If you add all addons which customer needs for his work, these stats can go much higher…

I want to congratulate to all consultants and developers, which are able to understand the system, which knows, how it works, and which are able to correctly do their every-day work with this huge amount of information. May be that this stats will help customers to understands, why sometimes there are some bugs in the system, why some things needs more time to think them out than making them.

We will see how these stats will change with new versions.

August 20th, 2008 Posted by kine | Dynamics NAV | no comments

The Art Of Nav – Big Picture of NAV

Today I have for you something, what everyone wants to have, but it was nearly impossible to create. After you see it, you will know why. I prepared it for you in more formats. You can look at it as PNG file, you can walk through it with some VRML viewer, you can open it as Visio file. You can read it and import it as CSV file. You can browse it as HTML file. You can view it as SVG file. It is on you, how you will visualize the result. I must say, I have tried many ways, but in most cases the tools were weak and my 2GB of RAM was not enough.

What’s the hell is this “line hell”?

Ladies and Gentleman,

“Big Picture of NAV” is there. You can download the files on the “The Art of NAV” project page. And what the files are about? The files are generated from data, describing ALL NAV TABLE RELATIONS WITH CONDITIONS AND FILTERS. In the visualizations, the tables are represented by boxes where first line is table name and rest are all fields in the table. The relation descriptions are part of the edge text (source conditions, target filters). All is based on NAV 5.00SP1 W1 objects. When you take the result files and you will want to print it in 1:1 scale, the output will be over 5×5 meters in size. Visio cannot save the diagram as bitmap for 1:1 scale (it seems because the size). You cannot see whole diagram in Visio (on common display resolution), because zoom cannot be less than 1%. My computer had problems to generate any graph from the sources. My 3GHz Core 2 DUO worked on it many minutes. I have used the excellent software Graphviz to make the graphs, but as you can see, it is too much for any tool on the world to make some nice readable chart (or I didn’t find the correct settings :-)).

I prepared for you NAV objects, which I used to extract the relations from object text file. It will fill the NAV table with all necessary data and you can browse it in prepared window. You have list of all fields in the database on top, related tables for selected field in middle, and fields relating to the selected field on bottom. You need to use these objects on the database, from which the object file is created, because the form is based on the virtual tables of the database. The objects are extracting 100% of relations in the database, which are defined. You can use them to extract relations from you own databases. Just export all tables as text and run this tool in the same database.

All files can be found in the download section of project “The Art of NAV“.

Do you understand now, why we do not have any official diagram with the NAV table structure?

File description and viewers I tested:

SVG – vector format – ZGRViewer – sometime needs to set bigger java heap by adding parameter “-Xmx512m” (the size is on you, this example is 512MB) when calling the java package

HTML – HTML exported from MS Visio – Internet Explorer – use the Internet explorer, you can search within the graph. Firefox is showing just plain bitmap.

VRML – the graph in 3D world – FLUX Player – needs big memory, you can look at the graph in 3D space and if you use the FLUX studio, you can add cameras, interactivity, animations etc. Welcome into “NAV Space”.

VSD – MS Visio file created by importing the SVG file – MS Visio – You can look at the graph in Visio and print it from there (over 5×5 meters). Hart to edit etc. because the size and SVG source limitations…

August 4th, 2008 Posted by kine | Dynamics NAV, Uncategorized | no comments

Where to place my code?

That’s the question… Many rookies have problems with decision where to put the code (or from where to call it). When you watch some beginner in NAV where the code is placed and from where it is called, you can find out that in many cases used triggers are the triggers, which in 99% are not used in the application and many Pro developers nearly do not know what the triggers are doing :-).

I had a thought. To create small decision tree which can help developers to find out, which trigger will be best for calling the code, and where to place the code in (table, codeunit etc.). I placed the first file to google code repository site under project named “The Art of NAV“. I will try to keep this site as the Home page of this project. We will see, maybe I will find better tool for that.

I am waiting for your opinions and comment. I will try to keep the project live and up to date. We will see, if it helps you.

This is a first step I am doing and I am thinking about it as an initial step for something I am calling “NAV Art” or “The Art of NAV”. My target is to help all developers to make the code as clear as possible and make some order in the NAV word. Of course, there is no simple manual or something for that, but I want to open some discussion about that and give some ways for the specific problems. I hope that I am not alone in that and you all will try to participate in this.

I am Waiting for your comments…

July 28th, 2008 Posted by kine | Dynamics NAV, Uncategorized | no comments

Pay attention to permissions when upgrading DB from SQL 2000 to SQL 2005

Story

Today I solved one “mystery” which leads to data loss. But from the beginning…

We have upgraded HW and MS SQL to version 2005 on 64bit platform. Everything without problems, because SAN was used, transferring the DB was just Detach/Attach and running one SSIS task to transfer user logins. No problem. After upgrade all is working without problems. But…

Some users started to complain that specific functionality is not working correctly. What’s going on?

From one card you can open another form through button. This form shows some related records, and if there are no related records, they are generated. It’s simple. But now, user opens the form and there is nothing in it. I tested it and it works ok, records were created. Ok, I have something to think about and something to solve.

After few rounds of “you’ll try it, I’ll try it” we noticed, that when the user open the form, there is just one line in the form, looking as inserted (no asterisk on left side of the table). But after we moved cursor or just opened table filters and returned back, there is no record in the table and we are on the new line. 8-|

Ok, code looks correctly, no conditions or something else preventing the record insertion process. I started client monitor and there are all the inserts statements, but still no records in the table.

I will not go deep into the analysis of this problem, if you are interested in what I did to discover the source of the problem, post comment and maybe I will wrote another article about that. Now, I will jump to the time, when I found out what’s the problem.

As everytime, the SQL Profiler helped to discover the problem. In profiler I enabled the tracing of Server errors and exceptions and after I catched the process, I saw few red lines saying this:

Exception 74 Error: 262, Severity: 14, State: 4

User Error Message 74 SHOWPLAN permission denied in database ‘blablabla’.

Ok, it confirmed one thing I noticed at very beginning – if you are db_owner, it is working. If not, you have the problem. After looking into permissions and comparing the permissions from DB created on SQL 2005 and on the transfered one, I found out that there are no Database permissions “Show Plan” and “References” for the application role $ndo$shadow.

Result

Why they are not there? Because these permissions doesn’t exists in MS SQL 2000. They are assigned correctly when you create the DB on MS SQL 2005, but not when you transfer the database by Detach/Attach or Backup/Restore process. These permissions are not created even when running Synchronize all process from within NAV (using Standard security model of course).

Another thing I noticed is that this bug makes visible problems only if you are working with Maximized NAV windows, if you are working with form in normal size, all seems to work correctly.

Conclusion

After upgrading MS SQL 2000 to MS SQL 2005, if you are not creating new DB but using Detach/Attach or SQL Backup/Restore process to transfer the database, you need to grant “Show Plan” and “References” permissions for the application role “$ndo$shadow” on the database. Do not forget this else you can experience mysterious data lost and behavior.

Details of the bug

The data are generated in OnRun trigger of the second form. After this trigger is finished, the first form started to be updated, because the OnAfterGetCurrRecord trigger of this first form is called and there is CurrForm.UPDATECONTROLS command in it. Because this process is still running in context of the transaction started with the OnRun trigger of second form, and because there are SHOW PLAN statements in it and the SHOW PLAN permissions are not granted to the app. role, SQL server raise exception “permission denied” and the transaction is rolled back. But NAV client doesn’t catch this exception, and this is the main problem. The first form refresh process is not started if you do not have maximized forms. But the permission exception is triggered every time the forms are using internally COUNTAPPROX to read expected record count (may be because the scrollbar size?) and these calls are canceled by this. User does not know anything about this permission error…

This bug is rare to experience, but the permissions problems can be source of another bug which is not so easy to find or even notice it.

Repro steps

1. Create new DB on MS SQL 2005, restore Cronus DB into it
2. Remove “Show Plan” permissions from $ndo$shadow app. role on the DB. (or you can detach come cronus database from MS SQL 2000 and attach it in MS SQL 2005 - the permissions will not be there, because they are not exist in MS SQL 2000)
3. Import attached objects
4. Run form 90001, maximize it (this bug makes a problem only when the windows are maximized!)
5. Click “Test - Run Form”
6. Second window will open (maximized) but the window has no entries (or just one, until you open filter window or move the cursor, after that there are no lines in the form)
7. Restore the windows size, close the window opened in step 6
8. On restored window (not maximized) click again “Test - Run Form”
9. Second window will open but this time with entered entries - it means correctly.

Objects for download.

February 6th, 2008 Posted by kine | Bugs, Dynamics NAV | no comments