A Better Mousetrap! Dimension Sets in Dynamics NAV 2013 (Navision)
My 14-year old daughter recently burst into my office with resentment at our sour cream container. Had it gone bad? No. Does she not like sour cream? No. The problem was that they had, according to her, “spammed” her with a picture and advertisement of the Grand Canyon on the foil seal under the plastic container lid. “What does sour cream have to do with the Grand Canyon? This is outright SPAM!” she shouted in outrage.
She is, by her own confession, an over-thinker, and that’s one of the things I love about her. Under-thinkers are willing to let things exist as they are, but an over-thinker is continually analyzing, researching, wondering, and creating. Ralph Waldo Emerson once said “Build a better mousetrap and the world will beat a path to your door.” Well, my NAV friends, the software developers at Microsoft must have an over-thinker on their development team, because they have built for us a better mouse trap in NAV 2013!
If you’ve ever debugged a NAV posting routine with the breakpoints set on triggers, you are aware of how many times the Dimension Management Codeunit has to get involved in the posting routine. This is primarily because of how dimensions are stored, and the fact that the entries are tied to the table, document type and document number. As the table, document type and document number change, the dimensions have to follow. And as entries are created in other tables, such as the G/L Entry table or the Value Entry table, additional entries have to be created as well. All these entries for dimensions start in the NAV Document Dimension table 357.
Now brace yourself for this one! Tighten your seatbelt! Hold on to the arms of your chair! Are you ready? The Document Dimension table is GONE in NAV 2013. That’s right, it no longer exists. And neither does the Posted Document Dimension table 359, nor the Ledger Entry Dimension table 355. In fact, here is the complete list (as best as I can tell) of the dimension tables that have been retired:
“Holy Cow!” you say. “What are those NAV software developers thinking taking away MY dimensions?” Well, simmer down now, relax, and take a “chill pill” as my daughter would say. There’s a new mousetrap. And here it is:
These are the new Dimension tables in NAV. The Dimension Set is the new way to handle dimensions. Dimensions are now just a number. That’s right – one single integer to describe all of them. Here is how it works, and why this is a “better” mousetrap.
Let’s say in NAV 2009 and past versions you wanted to create a very simple, non-tax, sales order with only one item, and 5 dimensions. It would look something like this in the Document Dimension table:
That’s 5 records written to the Document Dimension table for the Sales Order Header. We have one line on the order, which creates an additional 5 records in the table for the Sales Line. That gives us a total of 10 records written to disk for dimensions on this single sales order.
Now when you post the shipment and invoice for the order, the original 10 records in the Document Dimension table go away because the order no longer exists. But, in turn, you get a total of 20 new records for the Sales Shipment Header, Sales Shipment Line, Sales Invoice Header, and Sales Invoice Line that were created. These are inserted into the Posted Document Dimension table 359.
Additionally, we create General Ledger Entries when we invoice the Sales Order. That creates another 10 entries in the Ledger Entry Dimension table 355.
We also create a Customer Ledger Entry, and that yields 5 more entries to the Ledger Entry Dimension table.
That’s a total of 35 dimension records written to disk.
Why is the number of dimensions written a concern? Because it lengthens the time it takes to post a document, and therefore, increases the chances of a record lock occurring. And even though record locks are a good thing, users really hate them! It also means more disk storage, which isn’t as much of a problem now as it used to be in the days when disk space was costly. Additionally, all the records have to be retrieved to report against them, which means more disk reads.
Now, if we create the same order in NAV 2013, with the same dimensions, it looks like this:
That’s right! We now store only one integer on the Sales Header in the new field “Dimension Set ID”. We didn’t write any new records. We already were writing the Sales Header for the order. And what is “149”? The Dimension Set Entry table 480 can be filtered by Dimension Set ID to show what the set consists of:
Each set of dimensions is assigned a “Dimension Set ID”. When you enter the dimensions for your order, NAV locates the “Dimension Set ID”, and adds it to the Sales Header and Sales Line table.
And when the document is posted, the Sales Shipment Header, Sales Shipment Line, Sales Invoice Header, and Sales Invoice Line are treated the same way with only the “Dimension Set ID” written to the record. The posted ledger entries are also given just the “Dimension Set ID” on the record.
When you consider how much faster the posting will process without the addition of those 35 records, you see why this is a better way to handle dimensions. And the first time you have to debug a posting session in NAV 2013, you’ll be thanking that over-thinker on the Microsoft Dynamics NAV development team for the better mousetrap!