Getting Started with Dimensions in Microsoft Dynamics NAV                     

Hello and welcome to The Coffee Mug Tutorials. Today I want to talk about Dimensions. Dimensions are a really powerful concept inside NAV – it is sort of like a hybrid business intelligence and it will connect to business intelligence tools once you start using those.

So the issue we have is we are making sales; we actually took up a new product – coffee grounds – we are selling coffee grounds all over the place, and we are going to be selling coffee grounds from different areas and different regions in the world. 

We want to track the sales by the type of coffee grounds that we are selling and we want to track to which customers we sell the most of what type.

So this is sort of a business intelligence problem, so analysis and slicing & dicing of data and I’m going to be demoing how to set this up. I’ve already set up a few things. It’s going to be a bit off the cuff so hopefully I won’t hit any issues on the way. And probably a bit longer video than normal.

Example based on Spreadsheet
So basically here in the Excel sheet I have a list of how I want to set up the regions. So the coffee is grown in all of these regions around the world.

They are grouped by Central America, South America, Africa and Islands & Island Nations. Underneath Central America I have Costa Rica, Guatemala, Mexico and Panama, South America has Brazil, Colombia etc. 

So I want to replicate this list inside NAV as a dimension because I want both the costs and the revenue and even anything I book against these groups to be recorded in NAV and I want to be able to take the totals and see them sliced and diced as an output. All right, so how do I do that?

Go into Dimensions
I go into NAV, go right in here, go into Dimensions. 

I see I have here dimensions.

I’ve already created something called Coffee Region here. 

Create a New Dimension
got a bunch of dimensions here that came actually default out of the demo company and I just hit New. 

I created a new dimension called Coffee Region.

I made sure that the name is spelled out right here, so it actually defaults Coffee Region one word, so I fixed that, and that’s important because for example this will go onto the caption of the fields that you are entering the values into and you’ll see that later. 

So this is a dimension, now I actually need to specify dimension values – so those are the values that the user will select for the coffee region. Notice that I can actually create any dimension that I want; call it whatever I want; and put values to that dimension so it’s really a lot of freedom. 

And there is in reality no limit to the amount of dimensions you can have in NAV.

 All right so if I go in here and click on Dimension Values.

I’ve set up the values as I had them in Excel into my Dimension. 

Now the code I used is decimals. This is an alpha-numeric field and you can call the code whatever you like however I suggest that it sorts like this by using Dictionary Sort. So the best way to make sure that that’s ok is actually using decimals then it will sort nicely and you can squeeze numbers in between.

If you use something like ABC, DEF etc, then it might be hard to put things in between and it might also sort off if for example the code is one letter and the next code is two letters. Dictionary sort will sort on the first letter of the word and it could throw it off. So just a little bit of a note on how to arrange this nicely. 

Now I also used here Begin-Totals and End-Totals, so this way I can group up my dimensions. 

So in Central America that’s not a value the user can select – Begin-Total – however all of the values in between the Begin-Total and End-Total are selectable and they are of type Standard. 

So in order to… or the reason why I set up the Begin-Total and End-Total is so I can get totals for the groups as well. So I have my dimensions here.

Assigning the Dimensions
Next what I want to go through is where we actually assign the dimensions, sort of like what type of dimension it is in the system. So if I go into General Ledger Setup. 

Global and Shortcut Dimensions
We have a tab down here called Dimensions. In this tab you can specify what we call the Global Dimensions and the Shortcut Dimensions. 

The Global Dimensions are dimensions that are stored in the table in NAV, so what do I mean by that? They are an actual field in the general or GL entry table and item ledger entry table etc. 

So if you are doing some data crunching and you extract a table out of NAV, you will see the Global Dimension as available there, the values for the global dimension.

If it’s a Shortcut Dimension (3 and above, all the way up to 8) then it’s going to be in a sub-table.

So you will have to extract another table that references the dimension for the entry. This is a little technical and don’t worry too much about this if you don’t quite get it we’ll have further videos on that.

Basically what it means is put the most-used dimensions into the Global Dimensions. 

That’s going to be the easiest ones to extract out of the table. However, if you use decent BI tools it should not matter whether it’s a Global Dimension or Short Dimension, maybe only slightly on performance but it should not be that detrimental.

So what I have done is put my Coffee Region as a Global Dimension 2. 

How did I set this – you notice I can’t select anything in here – I actually go up to Actions, Change Global Dimensions.

And there I just select what I want for it, hit OK and the system updates.

Don’t do this in a system that’s already running with a lot of data unless you know how long it’s going to take and you’ve tested it, sort of in a test company. It might lock things up and it has to do a lot of updates. However, in my demo company it only took a few seconds. Click OK, so we’re done with that. 

Assign Dimensions to Items
Now I want to go into My Items and actually assign these dimensions.

If I go into the Item List, I’m just going to go into the Item Category Code.

Enter Coffee.

I have Colombian, Hawaii and Mexican coffee, these are the 3 that we have so far, we will have lots lots more and obviously we have more values in the dimensions. However, just for the sake of speed and time we created 3.

So here on the Colombian coffee if I go into Navigate and in the master data I have something called Dimensions. 

I can go into Dimensions-Single here. 

And I have already assigned the Colombian one to Coffee Region 22, which if you remember was Colombia.

Now here this Value Posting field is sort of interesting. I’ve left that blank which basically gives flexibility in the system.

Basically whenever I’m going to use this value in an order it will automatically put in the ‘22’, however if I put anything in this Value Posting, it could some restrictions based on that.

So for example Code Mandatory, that means there has to be a coffee region for this item but it does not necessarily have to be 22.

Same Code means that it has to be and it has to be 22 and No Code means that I cannot use a code for Coffee Region for this item.

Technically here, Same Code would have been a good one to use however for the sake of argument I’m going to leave it blank to show you how it flows through.

I’ve done the same thing for the Hawaiian coffee like that. 

And for the Mexican coffee here.

Now what I want to do is go ahead and buy that.

Create a New Purchase Order
So I’m going to go into a Purchase Order and show you how the transactions actually flow with the dimensions.

Just create a new one. 

And we’re going to get that from Custom Metals, they have a side business in coffee, so the Colombian, get into the Blue location, 1000 bags, $5 each.

Let’s also get the Hawaiian. 

And let’s see…the Mexican one into the Blue location and 1000 bags of that. And that’s going to be $4.50.

Post
Ok, so I’m actually going to post this onto the 5^th of January 2017. 

As you probably remember from previous demos we are in the year 2017 in the future, this is very interesting. Now I’ve set these to be received.

Now I’m just going to go ahead and Post.

Receive and Invoice. 

I should have, sort of, ran through this purchase order quickly. The whole idea behind that is – well I’m not teaching you how to create purchase orders in this demo – rather just generate the data so we can see the business intelligence.

Create a New Sales Order
Ok so now I’m also going to sell it, so let’s go to a Sales Order. 

Create a New Sales Order. 

Make it to the Cannon Group right here; yes, they’re overdue. Did pay some in the last demo but clearly not enough. Ok so this is the Blue location, sell 500 Colombian, let’s get some profit on this, $7, not taxable.

And some Mexican, 300 for 6 dollars and finally Hawaiian 400 for $6.5, not taxable, ok good enough. 

Ok before I post this I want to show you something.

If I go here into Lines, Dimensions. 

Notice that the system actually throws in a lot of Dimensions.

That’s because we’ve set up dimensions for more things than just the coffee grounds. But it does put in here the Coffee Region for this line. 

If I go into that line and I look at Dimensions.

I have a different coffee region.

So it’s already tracking the dimensions on this order and when I post it’s actually going to break up that data and post it into the ledgers.

Also the header will have dimensions that are not line based.

So here obviously I don’t have anything for the lines because that would be ambiguous.

Post
Now what I’m going to do is go ahead and post this making sure that I’m in the future as well so that’s 01-10-17.

And let me put in the posting date as well.

Now I want to go ahead and hit Post.

Oh, I’ve got to send it through the approval process. 

That’s fine and it’s released and post. 

Ship and Invoice.

So now it’s posted.

Analysis by Dimensions
So we have something called Analysis by Dimensions in NAV and the whole purpose behind this here is to take a look at dimensions.

And we have something called Sales Revenue here where we are analyzing the business group and the coffee region.

So basically in order to create one of these, you just hit New, give it a name, pick the dimensions, I’m going to hit Update here so it updates the transactions.

And I’ve also set that the account filter is only the sales accounts, it compresses by month and these are the two dimensions I’m interested in.

In further demos we’re going to go more in detail on these and how we can set them up with budgets etc, and what basically this is doing is gathering the data for the dimensions and giving you a view to take a look at them in various different ways, kind of slicing and dicing.

So if I go here and I go into Edit Analysis Views.

It asks me ok what do you want to show as Lines.

And I actually do want to see my Coffee Region as Lines.

Then what do you want to see as Columns and I’m going to say Period is fine.

And here is the Column Set and its view by month.

When I put 01-01-17 in the Date Filter it gives me a warning that it wants an upper bound. 

Put the 2017 year as the date filter so we’re only looking at that.

Then the Column Set is set to that. 

View Analysis by Dimensions Matrix
Now I can hit Show Matrix. 

And now you can see that we have sales by month by where the coffee is from and that is exactly what we wanted to do. 

We wanted to get this data funnelling up into these categories.

Now what I can do is I can close this and say I don’t want to see Period.

I’m interested in seeing it by the Business Group.

Show Matrix.

Now I can see here that I have down the region and across the business group. 

So why am I not getting any data here? It’s probably because the Cannon Group is not in any particular business group, so that’s something that is probably important to take care of.

And that was all I wanted to show for the Dimensions. This topic is really big so I’ll probably have another video on Dimensions but this quickly gets you to this view where you are going to see the Coffee Region by period like that.