SIFTing through the CRUD

CRUD (Create, Read, Update, Delete) has to be one of my all time favourite computer acronyms. I also like PICNIC (Problem In Chair Not In Computer) but that's a whole different story.

SIFT (Sum-Index Flow Technology) is a clever trick used by NAV to give you incredibly fast displays of aggregated totals. The Net Change and Balance fields on the Chart of Accounts screen are probably the best known examples. These fields are instantly drawn and can be filtered by dimension, posting date, etc. I have not seen this in the other ERP systems I have used and it is pretty impressive and powerful. However, with great power comes great responsibility and SIFT has its price. To get this fast performance, NAV makes a trade and instead of doing the work at the time of reading the data, NAV does the work when you create, update or delete the data. This can be OK for records that are not updated very often but you do need to be careful how many FlowFields and SumIndexes you add to tables. There's no such thing as a free lunch (unless of course you work in sales.)

I recently tried to make an update on a table with a relatively large number of records. My SQL script was simple and all it did was set one field to be the product of another two fields (e.g. Line Amount = Qty * Price). The dataset being updated contained just over 2 million records and I killed the query after 3 hours of execution. I couldn't understand why the thing was taking so long. Then I remembered SIFT! I don't pretend to be a SQL expert and there are lots of clever people out there that have written lots of clever stuff on database optimisation but what I do know is that even if my SIFT update takes a fraction of a second then 2 million fractions of a second can be several hours.

When you have a SIFT index on a table, NAV adds a SQL Trigger to the table with lots of fancy SQL code to update a number of SIFT tables with the aggregated totals. Fortunately, NAV provides the ability to disable the maintenance of SIFT tables in SQL. You can also fine-tune how many levels of data should be maintained which can again improve performance. When you go into the table designer and view the keys you can show an extra column called MaintainSIFTIndex. I went into my table designer and un-ticked this field for the SumIndex keys that were totalling the field I was updating. I compiled the table which dropped the table trigger that maintains the SIFT tables and deleted the SIFT table.

I then ran my SQL query again. It completed in 8 minutes!

I went back in to the table designer and re-activated my SIFT maintenance option and re-compiled. NAV put the triggers back and re-created my totals. This took 5 minutes. As you may know reads are the least expensive type of database operation with inserts, updates and deletes being considerably more time consuming. It is much faster to get the sum index totals updated all in one go rather than bit by bit over and over for every record that is updated.

I also have a data-conversion routine for a NAV upgrade that takes a very long time to execute – the next time I run it I am going to disable my SIFT maintenance on the tables first (and possibly disable the SQL maintenance of some of the indexes which also add time to transactions). Once the routine has completed I will rebuild the indexes and SIFT tables – it will be interesting to see how much of a performance gain I get by doing this.

I see from the posting on the Sustained Engineering Team Blog that the imminent SP1 release for NAV 5.0 will allow the Indexed Views feature of SQL 2005 to be used for SIFT instead of the current method. It will be interesting to see what affect this has on performance.

SQL Error when Importing FOB file.

I frequently get support calls from customers that get an error message similar to the following when trying to import a FOB that includes changes to tables:

The following SQL Server error(s) occurred while accessing the TestTable table: 1088,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "Cronus New Zealand Ltd_$TestTable" because it does not exist or you do not have permissions.

SQL:
ALTER TABLE "DATABASENAME"."dbo"." Cronus New Zealand Ltd_$TestTable" ALTER COLUMN "Message" VARCHAR(240) NOT NULL

The solution is simple and can be found in the standard documentation. In order to be able to make changes to table definitions, you need to be a member of the sysadmin server role or be a member of the db_owner database role for this database.

You may need to ask your SQL database administrator to grant these privileges to your user account and it is likely they will grant you the lower db_owner privileges rather than sysadmin and still grumble about it.

OK problem solved. Everyone’s happy, but…

If you’re anything like me, you may wonder why this is the case. Why do you need to be granted special rights by a database administrator for this task when all other rights are granted to you from within the NAV application? To be able to answer this question we need to understand a little bit about how NAV users access the database.

I first came across this issue when trying to access the NAV SQL database from other applications (such as Excel or Reporting Services) and I soon discovered that my NAV login did not allow me access to the data contained in the NAV database. New users added to Dynamics NAV through the NAV application will have no permissions on the database (well actually the user has been granted the public database role which means they have permission to connect to the database but not to do anything else.)

Incidentally, when you create a Windows login (as opposed to a Database Login) from the Dynamics NAV client, the system will create the SQL Login and map the user to the database with the public role. If you are trying to use a Database login, you must create the SQL Login first through SQL Server Management Studio.

So the question remains: how can we read the data from the tables when we have no rights to read the data? The magic happens through something called an “Application Server Role”. If you look under Security for the database you are using in SQL Server Management Studio, you will see groups for Users, Roles, Schemas, Asymmetric Keys, Certificates and Symmetric Keys. Open the Roles and then expand the Application Roles and you will one called $ndo$shadow. This role has all the permissions needed to read from every table in the NAV database.

An application server role is used to solve exactly the problem we are investigating, that is, how can you give users full access to a SQL database through your application (in this case NAV) but not let them do anything when they try and use Excel to query the data? Since security is controlled by NAV, when the user tries to access the data directly they are by-passing the permissions that have been given to them by the NAV security administrator.

The $ndo$shadow application server role is created by the application and is given a password that is too big for users to remember even if they knew how to find it. When the user runs the application, one of the first things that NAV does is run a stored procedure to set the application server role for the current session using this big password. The SQL documentation tells us that this will allow the user to use the rights allocated to the application server role for the current session until the session is terminated.

In a mysterious move, Microsoft changed this security model in version 4.0 and introduced a whole bunch of $ndo$shadow application server roles with a funny GUID thing on the end. It looks like these new roles were used for each user instead of having one for all users. I am a bit unsure as to how these roles worked since they didn’t actually work very well and I soon stopped using them. Any changes to security needed to be synchronized to these roles and many users complained of the system locking up for long periods of time. In 4.0 SP3 (I think) Microsoft introduced the ability to switch this new security mode off and revert to the good old single $ndo$shadow role for everyone. Interestingly the option allows you to select your security as being either “Enhanced” or “Standard”. I think they would have been better off calling these options “Rubbish” and “The one that works”.

So now we know how we get access to the data when our user account doesn’t have any rights, but the question of why we need dbo rights on a user account (that is not being used) to change tables is still unanswered.

I spent a few hours trying to figure this out using SQL Profiler and stepping through various scenarios. After a long frustrating session, I gave up and went to bed. Then I figured it out. Before NAV changes to use the $ndo$shadow application server role, it first checks to see if your user is a dbo and if you are a dbo it doesn’t switch to the application server role. Simple really – but worth noting that if you are a dbo, you are using your own credentials and permissions and not those of the application server role so adding permissions to the $ndo$shadow role will have no effect.

I just installed Snap Shots on my Blog

Introducing Snap Shots from Snap.com

I just installed a nice little tool on this site called Snap Shots that enhances links with visual previews of the destination site, interactive excerpts of Wikipedia articles, MySpace profiles, IMDb profiles and Amazon products, display inline videos, RSS, MP3s, photos, stock charts and more.

Sometimes Snap Shots bring you the information you need, without your having to leave the site, while other times it lets you "look ahead," before deciding if you want to follow a link or not.

Should you decide this is not for you, just click the Options icon in the upper right corner of the Snap Shot and opt-out.

F5 Fumbles Find Fat-fingered Frustration

Let's face it the C/AL editor is a dog. And I don't mean the "man's best friend" type of dog. I mean the type of dog that chews your slippers and pees on your carpet. The only improvements I have seen over the last 6 years are the ability to find functions using the search command and the new version 5.0 ability to indent and outdent blocks of code using the tab key.

Visual Studio programmers may have difficulty reading now as their eyes are filled with tears of laughter. Vjeko has some interesting theories as to why the C/AL editor is so bad. I wish I knew why the IDE takes a back seat for the Dynamics NAV team. Even the much-anticipated NAV "6.0" uses the same development tools (as far as I can tell from the screen shots.)

Today for the first time I realised how crazy it is to put the most useful key for C/AL developers (the F5 key which brings up a kind of poor-man's intellisense) right next to the killer F4 key which is used to delete the current line of code with no prompt or undo. Thanks for that! Save your work frequently or be very careful when reaching for the F5 key.

So what can we do to help ourselves since improving the development tools does not appear high on the list of proposed new features?

I have found my life easier since starting to use a variable naming convention that identifies if a variable is a parameter, local or global. I know this is pretty radical and against the standard programming conventions (something that should not be necessary if the IDE provided good visual cues for the developer.) For me it's simple. I put p_ in front of my parameters, g_ in front of my globals and l_ in front of my locals and I then find it so much easier to follow my code. Programming logic errors of using a local variable by mistake when you meant to use the global variable of the same name disappear in an instant.

The other thing which the NAV coders don't seem to be big on, which I find very important, is to write code for people not computers (something Vjeko has also written about) which I recently re-discovered when flicking through my old copy of Steve McConnell's excellent book "Code Complete."

Steve talks about writing "self documenting" code through the use of good function and variable names and clarity in preference to efficiency. For me the most important thing is to comment the intent of the code. How many times have you struggled to find a solution to a buggy piece of code because it is not really clear what the original programmer intended the code to do? Steve talks about designing your code in PDL (Program Design Language) which seems to be a kind of Pseudo-Code. He says it is a good idea to make you program with the PDL as comments and then fill in the code around the comments. Hey presto! You now have commented code with "headlines" of comments that can be read to illustrate the intent and purpose of the code without forcing the hapless support engineer to decipher your code.

Avoid smart programming that is just hard to follow. I recently had to go through the 3.70 to 5.00 upgrade codeunit and I was really struggling to understand why the main update routine was called twice with a slightly changed variable until I realised that the first time was just to count how many records we needed to process so we could display a progress indicator. Now this may be common practice for NAV upgrade routines but to me it was a frustrating waste of my time to have to figure this out. If two different functions had been used with names like CountRecordsForUpdate() and UpdateRecords() then I would not have had to spend time understanding what was going on.

So I would change Vjeko's rule number 2 from...

Rule No. 2: Make sure others will know what you did.

to...

Rule No. 2: Make sure others will know what you intended to do.

Dynamics NAV IDE team - I think it's time you surveyed your partners to get a list of features they want in their Dev tools for C/AL.

Dynamics NAV Application Developers - I think you need to use comments and write code that is easier to follow.

Now it's time for me to get back to my C/AL editor. Here boy!

Dynamics NAV 2009 – A Paradigm Shift for Business Solutions

Dynamics NAV is already a highly-successful, award-winning ERP solution but the forthcoming release is set to bring something quite extraordinary to the middle-tier business solutions market.

The Dynamics NAV team promises a ground-breaking user interface, a state-of-the-art technology platform, and web-services enablement while remaining true to their core product values of simplicity, adaptability and ease-of-use. You have to admit that when it comes to telling you how good their products are going to be, nothing can compete with the Microsoft marketing machine.

To read more, click this link to my posting on the Intergen Blog site... The Changing Face of ERP

Who is Blogging on Dynamics NAV? (in English)

I thought doing a roundup of blogs on Dynamics would be a nice easy way to make a post. I can’t believe how many there are. You can get a larger list of blogs on any subject through Technorati or doing a blog search in Google.

I have ignored any blogs that are in funny languages (i.e. not English) and any that seem to have only just started, given up after one post or not posted for a long time. The order of the blogs is the order in which they appeared in Technorati at the time of writing and then some others that I know of that didn’t appear. I am being very subjective and different people want different things from Blogs. Personally I want some form of entertainment around the subject matter that doesn’t make me think too much. If I learn something from reading the post – this is a bonus. If I missed your blog, I’m sorry. Drop a comment on the end of this post with the URL and I’ll add it (unless I don’t like it – in which case I’ll pretend I never got your comment.)

Plataan
http://plataan.typepad.com/microsoftdynamics/
I think this is a team effort but I can’t find details about the writers in English. The blog has fairly regular posts on Dynamics NAV. Technical stuff and news. I don’t read this blog too often but I do have it on my RSS Feeds. The content I have read seems good, solid, but not really that entertaining.

Kine’s info
http://msmvps.com/blogs/kine/default.aspx
I think this is the first Dynamics NAV blog I read. Not a regular poster but almost guaranteed to teach you something. This is on my RSS list.

Waldo’s Blog
http://dynamicsuser.net/blogs/waldo/default.aspx
Ahh Waldo, what would we do without you? Eric Wauters is a frequent blogger and often has the most read posts on the dynamicsuser.net forum. I really appreciate the time Waldo puts into his blog. Some of the content is a roundup of information that is available elsewhere, but that’s not necessarily a bad thing. If it’s about NAV and worth knowing, you can be almost certain to read about it on Waldo’s blog. Some postings are very technical but I often learn stuff.

Microsoft Dynamics Sustained Engineering Team
http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/default.aspx
Microsoft Dynamics Sustained Engineering Team. Regular posts direct from the guys that know. You should definitely add this to your regular feeds. Often technical in nature (as you would expect) it is good to hear from the Dynamics Team.

Gaspode’s Brain Dump
http://gaspodethewonderdog.blogspot.com/
The blog from the dog. I try to post the maximum amount of entertainment for the minimum amount of effort. I try very hard not to “re-post” content. I tried having a blog on dynamicsuser.net instead of blogspot as this gave me a very large number of hits but I felt a bit constrained. I didn’t feel I could really rant about Microsoft or post non-Dynamics stuff there. I like being able to review Google Analytics stats on my site – something I don’t think I could do on the other site, so I put up with the low readership and lack of comments.

Bart van Beek (Dynamics NAV Dev)
http://mibuso.com/blogs/bartvanbeek
I’d not come across this before writing the article but the site looks nice and contains details of a whole bunch of NAV add-ons you can download from MIBUSO.

Revolution1210.com
http://www.revolution1210.com/nav.asp
This is another one I had not heard of before researching this post. I like this one because Ian has posted a link to my blog in his top 10 of NAV blogs. This is a great blog. Lots of content that are just the right length. I am definitely going to take some time and read through the old posts and add this one to my RSS.

Microsoft Dynamics NAV Team Blog
http://blogs.msdn.com/nav/default.aspx
Another blog from the Dynamics Team. You have to subscribe to this. Infrequent posts but it’s always good to get it straight from the horse’s mouth.

Mark Brummel
http://dynamicsuser.net/blogs/mark_brummel/
Not a regular poster but I like Mark’s blog. One of the first ones to make it onto my RSS list. Mark’s series of posts from TechEd 2007 were a real stand out. A great effort and real contribution to the community.

Confessions of a Dynamics NAV Consultant
http://dynamicsuser.net/blogs/alexchow/default.aspx
I think Alex Chow gets the award for the best blog name. Not many posts but I love the posting on a question of morality. There needs to be more of this sort of thing. Blogging on NAV doesn’t have to be all about code and solving problems. Sometimes it’s good to just blow off some steam. The other posts look very useful and some handy tips in there that could save you some pain.

DynamicsUser.NET Admin’s Blog
http://dynamicsuser.net/blogs/admin/default.aspx
Not many posts but Erik P. Ernst is the administrator of dynamicsuser.net and an MVP. I have learnt interesting stuff and come across other good blogs through Erik’s posts.

Nuno Maia’s Dynamics Blog
http://dynamicsuser.net/blogs/nunomaia/default.aspx
Not many posts but a lot of good stuff. This guy knows his stuff. This one is on my RSS list.

Navigate Into Success
http://www.navigateintosuccess.com
I like this guys blog. Funny. Informative and he clearly knows what he is talking about. Plus he’s added comments on my blog and linked to it on his blog roll. What a guy! Definitely worth a look.

Thanks for the comments! Here are some of the ones I missed:

Belgian Dynamics Community
http://dynamicscom.be/blogs/
This seems to be largely syndication of other blogs with more than a few appearances of posts from Waldo. But there were some original posts on there and I particularly liked the post from Fontini on certification. Not all posts are in English (funny that being Belgian!)

Singleton
http://dynamicsuser.net/blogs/singleton/
David Singleton is well known within the Dynamics NAV community for his extensive work and posting on the DynamicsUser forum and for his The Dynamics Book project that is now a wiki site. The posts go way back to 2006 but they average at just over one a month. Maybe the comment is enough: The Blog of David Singleton (Need I say more?)

Microsoft Dynamics NAV UK Blog
http://blogs.msdn.com/uknav/
I guess it's hard for an official blog like this to post anything interesting. It is typical to get lots of posts about news that is available on PartnerSource and links to official downloads. Not my cup of tea.

Navision-Girl
http://www.navision-girl.com
This was one of the first blogs that regularly appeared on my Google Alerts. I must admit I didn't like it that much because of the "obvious" nature (to me) of some of the posts. Having said that I took a look for the first time in a long while after it was added to the comments and I really liked what I saw. I think I will be re-subscribing to this one.

Here are some others that I had on my RSS feeds that I didn't mention earlier:

Navision Freak's Blog
http://navisionfreak.blogspot.com/
No posts for a while but there is some good stuff on here. All the cross-over NAV and SQL topics are particularly interesting to me.

Navision Tips & Tricks
http://navitips.blogspot.com/
Again no recent posts but I really like what is there. Worth a browse.

Stefano Demiliano
http://demiliani.com/blog/Default.aspx
Regular posts of the highest technical quality from a guy who really knows. I can't beleive I missed this one from my first post. There are a fantastic variety of topics on technologies that are going to become more and more important for Dynamics consultants. Definitely subscribe to this one!

Tips dBits - Tricks Navision / Dynamics NAV
http://tricksnavision.blogspot.com/
Good solid content but nothing new since October last year. Some good tips in the old posts are worth a read.

Dynamics Blog - Check this out!

I have a "Google Alert" set up to keep me up to date with news and blog postings that mention Dynamics NAV, Navision, etc. It is rare that the links are that interesting, but today it came up with a link to a blog that shows there are some real gems out there! I have even added the link to my Favourite Blogs links on the right. You can find the blog at http://dynamicsblog.wordpress.com/.

The first handful of posts were very useful, well written and funny. Presenting technical and useful material in a manner that is interesting to read is not an easy thing to do. The author also writes about nearly giving up due to a lack of comments or readers. I know what that's like too!

Nice one Vjeko! Let's hope you keep the posts coming.

Why doesn’t my filter work?

I was going through some code for an upgrade and I came across some code that I thought contained an error. The code was looking at some setup fields that contained a range expression (in the format 1000..1999) and was trying to determine if a given G/L Account No. was within the range.

The code had a comment that said “cannot use SETRANGE as this does not work!” The code then went on to find the position of the two dots in the filter string “..” and then take the minimum and maximum parts of the range and use a set range function in order to determine if the record was within the range. Sadly it was also fetching each record that matched the range and comparing that to the current G/L Account No. to see whether the number matched.

“Aha!”, I thought. “Aha! – They need to use a SETFILTER and not a SETRANGE – the fools!”

So in order to prove my point (to no one in particular), I created a quick test form on which I tried to determine if a G/L Account No. was within some range fields I had created. I intended to use the SETFILTER function on a new record variable for the G/L Account table. My plan was to use this with the FINDFIRST command to see if I could find any records that matched the filter and were equal to my current G/L Account.

I got a real shock when my filter did not work.

My filter looked something like this:

SETFILTER(“No.”, ‘%1&=%2’, “Rev. Acct Range”, “No.”);

It is using the string substitution capabilities of the SETFILTER command together with my revenue account filter variable and my current account number field. If my “Rev. Acct Range” variable is set to 1000..1999 and my current G/L Account No. field is 1222 then I would expect my filter to be 1000..1999&=1222 which means “is between 1000 and 1999 and is also equal to 1222”. This, I hope you agree, should be true and I can certainly find a record that matches this filter, therefore I know that my record is within range.

So why doesn’t this work?

In order to find out, I needed to enable the debugger and look at the filter that was actually being set on my table record. When I debugged it I saw that the string substitution was putting single quotes around my range so my filter looked like this:

‘1000..1999’&=1222

This filter string means “is equal to the string ‘1000..1999’ and is equal to 1222.” This can never be true.

I couldn’t believe it! The person that had written the original code was right – it doesn’t work. But then I had a cunning idea. I could build a string containing my combined filters and use that instead of the %1 and %2 substitution parameters.

The following code is so simple but works and is much more efficient than the original code. It is also more robust since any valid filter can be used in my setup fields and not just one that contains two dots.

GLAcc.RESET;
TempFilterString := "Rev. Acct Range" + '&=' + "No.";

GLAcc.RESET;
GLAcc.SETFILTER("No.", TempFilterString);
IF GLAcc.FINDFIRST THEN
  "Revenue Acct." := TRUE
ELSE
  "Revenue Acct." := FALSE;