## 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:

 Relation Type Count AVERAGE 5 COUNT 109 EXIST 135 LOOKUP 291 MAX 9 MIN 14 SUM 415 -SUM 70 TABLEREL 5468 -EXIST 1 Grand Total 6517

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:

 Data type Count Code 6377 Decimal 2984 Text 2310 Integer 1603 Boolean 1452 Option 1279 Date 1025 Time 127 DateFormula 101 DateTime 57 BLOB 48 GUID 17 RecordID 6 Duration 2 TableFilter 1 BigInteger 1 Grand Total 17390

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:

 Filed type and length Count Code10 3387 Decimal 2984 Code20 2871 Integer 1603 Boolean 1452 Option 1279 Date 1025 Text50 973 Text30 672 Text80 253 Text250 188 Time 127 DateFormula 101 Text20 87 DateTime 57 BLOB 48 Text10 39 Code30 35 Code3 21 Text100 20 Code250 19 Code50 17 Code100 17 GUID 17 Text65 16 Text64 8 Text38 7 Text200 6 Text249 6 RecordID224 6 Text5 6 Code80 5 Text119 4 Text70 3 Text99 2 Text90 2 Text19 2 Text150 2 Text199 2 Code130 2 Duration 2 BigInteger 1 Text7 1 Text127 1 Text3 1 Text14 1 Text151 1 Text63 1 Code2 1 Text131 1 Text220 1 Code98 1 Code1 1 TableFilter 1 Text118 1 Text149 1 Text31 1 Text32 1 Grand Total 17390

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:

 Tables 918 Fields 17390 Fields per table (Avg) 18,94 Average count of relations per table 7,099 Percentage of fields referring other fields 37,47% Max fields in table 176 (Tab 39 - Purchase Line) 175 (Tab 27 - Item) Max table relations per table (All) 93 (Tab 18 - Customer) Max table relations per table (Table Rel) 66 (Tab 81 - Gen. Journal Line) Max table relations per table (FlowFields) 63 (Tab 18 - Customer) Most referred table 266 (Tab 349 - Dimension Value) 258 (Tab 308 - No. Series) 235 (Tab 15 - G/L Account)

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.

Posted by kine | with 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"?

"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 5x5 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? :-D

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 5x5 meters). Hart to edit etc. because the size and SVG source limitations...

Posted by kine | with no comments