Waldo's Blog
Microsoft Dynamics NAV

The Key virtual table

The "Key" virtual table contains information about the keys that are defined in each table in the database. At least, that's what the bible (in other, less popular words: The Application Designer's Guide) says. Actually, that's about all it explains about the "Key" virtual table ... and I'm talking about the latest w1w1adg.pdf (5.0) you can download from partnersource here. It only explains that you can get info from the table ... .

But there are also other, undocumented possibilities (at least, I didn't find any documentation about it). Well, let's document what we can .

What's in it?

The easiest way to look at the contents of a virtual table, is to create a new tabular form with the form wizard. This is because you can't see virtual tables in the object designer, and therefore, you can't "Run" them. Well, if you create a form, based on the Key table, put every field in the form, you get this:

These are the fields we're looking at:

• TableNo
• No.
• Tablename
• Key
• SumIndexFields
• SQLIndex
• Enabled
• MaintainSIFTIndex
• MaintainSQLIndex
• Clustered

The ones in bold are the interesting ones. You know them as properties of a key. Besides the "Keygroups" property, every property is present in the table. If you're working with versions below 5.0 SP1 (and chances are still high, off course), you're missing the SiftLevels property. But we'll talk about this in a seperate section.

So, can we only look at it?

No! You can edit the data in the table ... which means, you can change the properties of the keys by using this table. You can try this out in the form (see above) by just editing a property (like MaintainSQLIndex) and saving it by going to the next record.
One remark: you'll see that the object-properties have changed as well. This is no surprise. You changed the object's definition, so the object Date, Time and Modify-flag has to be changed as well.

Where could it be useful for?

Well, there are a number of things it could be useful for. Especially when you're doing SQL Tuning for customers. Let me try to line out a number of scenario's.

Scenario 1: MaintainSQLIndex

When I'm doing SQL Index Tuning, there are multiple ways to monitor "Index Usage". Out of these results, you can conclude which indexes are not used. It is quite easy to write some kind of codeunit to uncheck the checkmark in the MaintainSQLIndex field of a certain key. I did it once in a database for disabling about 160 indexes. The impact was quite heavy (in the positive sense...).

Scenario 2: Rebuild SIFT (versions below 5.0SP1)

In this post on mibuso, megawavez wanted to rebuild SIFT in some way. Mauddib went deeper into it in this post.

It comes down on playing around with the MaintainSIFTIndex property. If you uncheck it, the SIFT table is deleted. If you check it again, the SIFT table is created again, which means that it's there in the most "optimal" way (unfragmented, no zerosiftrecords, ...).

Scenario 3: Creating Clustered Indexes

In this download, kriki (Alain Krikilion) is using the key-table to fix the clustered key problem in 4.0Sp1. What was the problem: it could be that there were no clustered key's at all . Believe me, you don't want that. (I hope I'm not telling rubbish here. I downloaded it a long time ago and didn't doublecheck it when writing this piece).

Siftlevels

This is another pack of sleeves (like they say in dutch and probably makes no sense in English).

Up until version 5.0 (without service pack), "SiftLevels" was a fundamental part of SIFT. A snippet from the bible:

Understanding the relationship between buckets and SIFT levels is crucial to understanding the way that SIFT is implemented in the SQL Server Option for Dynamics NAV. The precalculated totals or sums for each SumIndexField column are stored in buckets in SIFT tables. The buckets correspond to the SIFT levels that are maintained and each SIFT level can generate many records that are stored with the same bucket number in the SIFT tables on SQL Server. The higher the bucket number the more detailed the SIFT level. The buckets and their corresponding SIFT levels can also be seen from within Dynamics NAV, even though they only exist in the SQL Server tables that are created to support SIFT: The precalculated totals from the different buckets are retrieved and aggregated to generate the sums or totals that are stored in the SumIndexFields.

In 5.0 Sp1 with the indexed views, there are no levels anymore! If you need more info, I handled this in one of my previous posts: What impact does my C/AL have on SQL - SIFT on 5.0 Sp1.

Back to topic: What does it have to do with the Key table? Well, the SiftLevels-property is not present in this table. You might wonder "hey, isn't it risky to play with the Key properties when you already tuned the SiftLevels? Isn't this going to "reset" the levels"? If you're working with the Key table, it is not!

I concluded this by disabling and enabling the "MaintainSIFTIndex". Both in the table designer and in the Key virtual table. Only with the key table, my SiftLevels remained intact. When you're tuning, this can be a big advantage, off course.

Is this the only virtual table where we can edit the data?

No, it is not. In my opinion, it's the most interesting one, but it definitely is not the only one. I did some brief tests with the field and object table, and there, it was also possible to change the content.

I have one side-remark with this. When you use the Key table, you notice that the object properties (date, time) are changed whenever you change a Key-property. You can extend your functionality by using the Object table as well: temorarily save the object properties, make the changes in the Key table, save it, and then, restore the Object properties by using the saved set. Easy, isn't it .

Final remarks

It's good to know undocumented possibilities. It can give you more flexibility in your daily work ... and you might be more "productive". I only have two "buts":

• But you have to know about its limitations as well.
  • For example: inserting in virtual tables is still not possible. You'll not be able to add keys, add fields ... . For the key table, it would have been nice to have had this possibility. Some of you might have noticed a bug in NDT. After you merge, some tables do not have any key anymore. Dynamically creating keys with the Key table might have been a workaround for this.
• But it's never a good idea to rely on undocumented features, in my opinion. I don't want to be the one that recommends to built business critical functionality on things like this, when the possibility disappears in the next release.

That's it, guys! Remarks are always welcome!

Microsoft Dynamics NAV 2009 Demo Contest

A new way to get familiar with the product ... and not a bad one.

A few months ago, we could read about the "Web Services Competition for NAV 2009" on the Microsoft Dynamics NAV UK Blog. If this is a serious competition or not, I don't know. But recently, I noticed Microsoft is organizing another contest: the Microsoft Dynamics NAV 2009 Demo Contest on Directions 2008 (Atlanta).

This contest seems a little bit more serious. There are even "official rules", prizes, guidelines, ... . As "cherry on the pie" or "nose of the salmon" or ... (whatever you English speaking people use as expression for something like "biggest opportunity") the top 3 finalists may present their demos at one of the keynote sessions during Directions 2008 in Atlanta (October 21st).

Not only partners are going to invest time to get familiar with the product, there will be 3 demo's where other partners can see and learn "how to sell NAV2009" . That's a double win for Microsoft.

Anyway, I'm not really into contests like this, but I sure like to be a spectator. It's may be the one reason that is going to get me out of bed to follow the (usually very early-in-the-morning-scheduled) Keynote sessions (as you might have figured - I'm not a Keynote fan either).

Besides having the honor to present in one of the Keynote sessions, there is also something to bring home - prizes!

• First Prize – Two Sony Vaio VGN-UX390N Micro PC, value $1,360 each
• Second Prize – Two Xbox 360 video game systems and 2 Grand Theft Auto IV games, $410 each
• Third Prize – Two 80GB Zune media devices and 2 DLO-Power Packs, $290 each

Above that, the top three finalists will have a free entrance ticket to Directions 2008.

If you want to participate and qualify, you'll have to provide Microsoft with a demo script and PowerPoint slides by 11:59PM Pacific Time, September 14, 2008. Since it's only for partners (obviously), I'll keep the necessary information to participate confidential. Here is the link where you can read all about it.

I'm sure many of you will be "teased" by this opportunity ... and are competitive enough to participate. Well ... good luck and see you in Atlanta [J]!

What impact does my C/AL have on SQL - SIFT on 5.0 Sp1

Normally, I would have done an article about Sumindexfields on versions previous to 5.0 SP1, but I decided not to do that. The Belgian version of Microsoft Dynamics NAV 5.0 SP1 is just released, and the Belgian release is always pretty late. In fact ... I just checked ... every localized version is available right now. That's why I decided to dig a little bit into this version's new SIFT-technology on SQL Server first. Who knows I'm in the mood to write about the SIFT-technology in the previous versions, when I'm in the hospital in a few days (yes, I'm waiting for an operation on my knee ) ... .

What is SIFT?

SIFT is an abbreviation for "Sum Index Flow Technology". Simply described: it's a technology that stores sums in the background to be able to quickly get summarized amounts. For the developers: SIFT tables contain records with summed values grouped by the fields of the key where you defined the SumIndexField on the table with the amountfield that should be summed (wow). For sales: SIFT is a technology that distinguishes NAV from any ERP system. For functional consultants: SIFT are sums. For the somewhat more experienced technical consultant who cares about performance: SIFT is a technology for speeding up the reading performance of summed fields, but should be carefully looked at because speeding up reading performance usually means slowing down writing performance.

Main difference with old SIFT

The biggest change they did in 5.0 SP1 is the fact that instead of SIFT tables, there are now SIFT views, which are indexed. This means, there is an efficient way to get results from the view (because of the fact that the results are 'stored' in a certain way), but it also means that it's a cost to maintain the view. May be not comparable with the dedicated SIFT table in the previous versions, but still ... . This also means that the triggers that existed to maintain the SIFT tables ... are gone ... which is a good thing!
Now, there has been written quite some posts about this by the Sustained Engeneering Team on their blog and also, there is a whitepaper available on partnersource. Definitely worth reading it.

Creating SIFT view

A SIFT view is created when you configure a SumIndexfield on a key ... indeed, just the same as before. With the property "MaintainSIFTIndex", you can disable or enable the SIFT view. If this property is FALSE, the SIFT view will disappear.

This example shows how NAV creates an indexed view:

CREATE VIEW GLEntry$VSIFT$1 AS

SELECT SUM(Amount) as SUM$Amount, AccountNo, PostingDate
FROM GLEntry GROUP BY AccountNo,PostingDate*

CREATE UNIQUE CLUSTERED INDEX VSIFTIDX
ON GLEntry$VSIFT$1(AccountNo,PostingDate)*

(pasted from the whitepaper)

In SQL Server Management Studio, a similar view looks like this:

It's very clear, isn't it? You can read it right away. It's just grouping by G/L Account and Posting date, and summing up all requested Amounts (SumIndexFields). We even have a 'Count' field, which indicates how many records exist for that grouping. But...

Hey, where are my SIFT buckets?

Gone! As described in the white paper: "The indexed view that is used for a SIFT key is always created at the most finely-grained level." That means: No SIFTLevels window in C/SIDE anymore, no buckets anymore, ... . Simplicity!

But what if you would like to create another level, which may be not as detailed. This could be beneficiary! Well, again, simple:

• Just create a new key with the fields of the level you would like the create the SIFT view on
• If you don't want to maintain the key as index in SQL, disable MaintainSQLIndex
• Add the fields for the sums in the SumIndexField

You see? It's just creating a new SIFT view on a new key. When you do this for the SIFT above, but only on "G/L Account No"-level, you get a SIFT view like this:

Now, when and how is it used in C/SIDE

In Flowfields, off course. How dare you ask that question?
Everybody knows the Chart Of Accounts. You know contains some flowfields that are totalling the "G/L Entry"-records on this form. If you monitor it, you'll see SQL Server receives statements like this:

SELECT SUM("SUM$Amount"),SUM("SUM$Amount")
FROM dbo."EN DEFAULT COMPANY$G_L Entry$VSIFT$1" WITH(NOEXPAND)
WHERE (("G_L Account No_"='705000'))

So, it's just querying the SIFT view for getting the sum. Because of the fact that this view already has summed amounts, means that it's going to sum less values. But keep in mind the levels. Only the most detailed level is maintained. The statement above is going to sum only on G/L Account. This is the least detailed level. This level is not maintained anymore in 5.00 SP1, which means that (by default) it's going to sum up more records then before.

What if I disable "MaintainSIFTIndex"?

If we disable the MaintainSIFTIndex of the SIFT above, we expect C/SIDE to query the G/L Entry table directly. And indeed, this is the statement it's sending:

SELECT SUM("Amount"),SUM("Amount")
FROM "EN DEFAULT COMPANY$G_L Entry"
WHERE (("G_L Account No_"='705000'))

You can imagine this statement will have to sum more amounts then the statement above.

Is there another way where the SIFT views will be used?

Yep, it's still there: A statement that is often forgotten is the CALCSUMS statement. It's a way to use SumIndexFields without using (or creating) a flowfield on a table. To keep the same logic as above:

CLEAR(recGLEntry);
recGLEntry.SETCURRENTKEY("G/L Account No.");
recGLEntry.SETRANGE("G/L Account No.",'705000');
recGLEntry.CALCSUMS(Amount);

You see I'm not using a flowfield, but I am using the SumIndexField of my G/L Entry table. This is the resulting statement in SQL Server:

SELECT SUM("SUM$Amount")
FROM dbo."EN DEFAULT COMPANY$G_L Entry$VSIFT$1"
WITH (READUNCOMMITTED, NOEXPAND )
WHERE (("G_L Account No_"='705000'))

So, What about my ZeroSiftRecords?

Probably everyone has heard of the ZeroSiftRecords-phenomenon. In the old days (version prior to 5.0 SP1 ... so really not that old ) it could happen that SIFT tables contained records with summed values all equal to 0. The reason behind this was that every record in the main tables was deleted for that particular summed group. Sums of non existing records is 0.

Now, we're working with views. These views are maintained by SQL Server automagically. It's actually some kind of projection of the main table in some way (a summed way). SQL Server is clever enough to exclude the zero-records. So, no ZeroSiftRecords anymore from now on. This is one maintenance job less .

So what's that $Cnt-field for?

As you can see, there is also a $Cnt-field present in the SIFT View. It counts the number of "summing" records. But where is it used for. I tested a "Count"-flowfield without result:

SELECT COUNT(*)
FROM "EN DEFAULT COMPANY$G_L Entry"
WHERE (("G_L Account No_"='640100'))

A normal "COUNT" in C/AL code:

SELECT COUNT(*)
FROM "EN DEFAULT COMPANY$G_L Entry"
WHERE (("G_L Account No_"='640100'))

An "Avarage" flowfield:

SELECT SUM("SUM$Amount")
FROM dbo."EN DEFAULT COMPANY$G_L Entry$VSIFT$1"
WITH (READUNCOMMITTED, NOEXPAND )
WHERE (("G_L Account No_"='705000'))

No use of the $Cnt-field whatsoever. May be it's used internally, I don't know. I couldn't think of any functionality or way to see where it's used (may be you got a clue?).

SQL Server 2000

My opionion about SQL2000 users is: "hey guys, we're in the year 2008"... . But that's only my opionion.

Seriously, in this article it's stated very clear that maintaining an indexed view on SQL Server 2000 can be a time consuming process (because of the clustered index scans etc. ...) . Therefore, their recommendation is, when upgrading to 5.0 SP1, you should always consider upgrading to SQL Server 2005 as well.

Do I need SQL Server 2005 Enterprise edition?

If you read the comparison between the SQL Server 2005 Editions, you could conclude you need the expensive Enterprise edition to run Microsoft Dynamics 50 SP1. This is a wrong conclusion!

Because of the fact the "Indexed View Matching"-functionality is not used by Dynamics NAV, this is no issue. This was beautifully explained in this blogpost by (once again) the Sustained Engineering Team as a response to my question a week earlier .

Ok, that's it, guys. I hope you enjoyed it. Please feel free to drop a comment with .. any .. uhm .. Comment .

Ps, after this article was finished, I noticed another very explanary blogpost from the UK Team. You can find it here.

Platform updates overview - 3.70.B - NAV2009 CTP3 (Update 2)

As you might have noticed, I changed the table a little bit. I think it's far more useful to see what has been fixed, rather than what are the prerequisites. You can always find the prerequisites at the URL of the KB article itself.

I also added some (very!) useful links at the bottom: Now you have access to all upgrade toolkits as well as all database versions . After the 'local functionality descriptions' (which have been extended since my blog article, by the way), this is a great initiative from Microsoft!

Manuals

• Installations manuals for Microsoft Dynamics NAV 5.0
• How to set up e-mail logging in NAV5.0
• Microsoft Dynamics NAV Local Functionality

Product Information/Updates

• Released code fixes for NAV 5.0 that are included in SP1
• Upgrade Information for Microsoft Dynamics NAV 5.0 SP1 
• Update of the ADCS feature...
• Jobs update for 5.0SP1. It's not a platform update, but rather important, so I just add it here... .

Interesting downloads:

• Demo Center for Microsoft Dynamics NAV 5.0 W1. Here you can find some demo VPC's.
• Microsoft Dynamics Mobile:
  • Product Release
  • Evaluation edition
• Database Archive for Microsoft Dynamics NAV: You need a db from Navision Financials 1.00? You can find it here.. .
• Upgrade Toolkit Archive for Microsoft Dynamic NAV: really cool site to give you access to all info you need to update form very early versions (Navision Financials 1.00) to 5.00 SP1.
• Microsoft Dynamics NAV Tools Overview: an overview of all Microsoft Dynamics Nav related tools, like: Tools CD, Upgrade Toolkit, Database Resource Kit, Developer's Toolkit, ...

Other interesting links:

• KB articles that were added in January 2008, February 2008 and March 2008
• Released platform hotfixes and rollup updates for NAV 5.0 (It seems MS is updating this list as well)
• Updates for general distribution for NA version of Microsoft Dynamics NAV
• Microsoft Dynamics PartnerSource RSS Feeds: Most interesting (imho) are:
  • RSS feed for the most recent Knowledge Base Articles
  • RSS feed for Microsoft Dynamics NAV News
• North American Support Site for Microsoft Dynamics (A LOT of links to find about NAV 5.0, NAV 5.0 SP1, NAV2009, SQL Server, Security, ...)
• More information about Certified for Microsoft Dynamics:
  • Fact Sheet
  • List of current certified products
  • Microsoft Dynamics Solution Finder for detailed solution descriptions
  • Customer information

Remarks

• The text in italic means that that peace was added or changed since last update.
• The "included in 4.0SP3!" means that the current download of 4.0Sp3 now includes update 1 to 6. Therefore, it has build 25143.

Microsoft Dynamics NAV 2009 - What's to expect

I was asked by MSDynamicsWorld.com to write a small piece about NAV2009. Not easy to do, because as MVP and as Partner (iFacto Business Solutions in Belgium), I'm quite bound to the NDA agreement with Microsoft. Therefore, I decided to base my story on the things I've seen on conventions (like Convergence ) and things I could find online. In the meantime they posted the article in two parts. You can find them here ... Or ... You can read it below .   

Here we go.

Introduction

When we're talking about Microsoft Dynamics NAV 2009, we're talking mainly about a completely new User Experience. But it's not only that. There is also a new architecture, and other new technical features, like web services. A lot to talk about, so let's try to cover this as interesting as possible.

The Role Tailored User Experience

So what can we expect from this concept. This is how Kim Ibfelt describes it: "The Role Tailored User Experience in Microsoft Dynamics NAV 2009 is not just a fancy new menu structure or a cool new UI with some bells and whistles. With Role Tailored User Experience the user gets the data and functionality they require, exactly where they need it and when they need it. Role Tailored User Experience is saturated throughout the solution and it implements all the findings on the individual and unique needs and requirements of the different user roles of Microsoft's Customer Model that is based on comprehensive user studies.".

In my understanding, it's a whole new concept then like we are used to today. Everything on User Interface Level, on User Experience level is "Role Based". We're talking mainly about the Role Centers here. When a user logs in, the Role Center is its "home page", the first page he sees. On this page, there is everything, or at least the most important things, he needs for his daily job. For an accountant, this can be the outstanding payments, while for an order processer, this can be the outstanding orders. For a Sales Manager, this can be a collection of only some KPI's, graphs and a top 10 sales of the last weeks. You see? The application seems to be "tailored" for his specific "role" in the company.

New architecture

Microsoft Dynamics NAV used to run in a 2 tiered mode. A client that connects to the database server. This is not the case anymore with the Microsoft Dynamics NAV 2009 Role Tailored client. Microsoft added a new level, namely the Service Tier (based on Windows Communication Foundation). This will be the middle tier that will execute the business logic. So, this means we'll have the (new) Dynamics Client (level 1), which connects to the service tier that executes the business logic (level 2). If the business logic needs data, it goes to the database server (level 3). You can see that this can have quite some scaling possibilities - may be not this release, but there is defenitely room for it. The business logic on the service tier will be managed C# code, which means that when you compile a codeunit in C/SIDE, it automatically creates some kind of managed C# module on the middle tier.

The first level (the dynamics client) is brand new as well. This client is much "thinner" then the C/SIDE client (because of the fact that the business logic is now executed in the middle tier) and is based on Metadata. This is new as well, and very important to understand. In the C/SIDE client, we had forms to present our data. In the new Dynamics Client, we have pages, which are going to create an XML of the data which it has to display (this is the metadata). The "thinner" client receives that metadata and represents it in a predefined way. This way of working will bring other possibilities. When the middle tier is already "producing" this kind of metadata to a dedicated client, it's not difficult to imagine that this is also possible to send to sharepoint. Therefore, in one of the next releases, we can expect a very flexible Microsoft Dynamics NAV Sharepoint Client as well, or a mobile phone/PDA, or... .

In my opinion, working with metadata has also some open questions. Determing the layout of a form (or page for this matter) has become less flexible. Therefore, working with colors, multiple subforms, matrix forms and a bunch of other things, will become quite challenging. Luckily, if you become a little bit familiar with it, there are great alternatives available.

Other New Feature: Web Services

Imagine you could publish any codeunit that you have ever written as a webservice by just enabling one checkbox. Yes, it's going to be as easy as that! Posting routines, calculations, ... all Dynamics NAV business logic will be available on the web by just one button click. Personally, I'm most excited about this feature, because of our own integration products.

And it will not be limited by just codeunits. We will also be able to publish pages. This sounds odd, but actually it's brilliant. It will foresee a predefined set of functions that you can use on a certain table. So if you publish the Customer Page, you will be able to view, filter, alter, ... (and much more) the data in the customer table.

Just imagine what integration possibilities this is going to offer. Communication with mobile devices, third party applications, web sites, ... . And all of his over the net ... . The sky is the limit.

So, what about the Developer Experience

At Directions EMEA 2008 in Paris, I followed a pretty interesting session about this. It showed clearly that Microsoft is doing its best to "keep things as simple as possible". Basically, there is not that much going to change for the developer (Basically!):

• We'll still be programming in the C/SIDE development environment
• We're still going to send fob-files (we will even be able to use XML files)
• We still can compile on te fly
• ...

But a new architecture brings some new things in the development environment as well:

• A page designer for the new Page-object