Business Central Dimensions Explained

Everyone who worked with Dynamics NAV or Business Central heard about the dimensions, almost every consultant mentioned something about them in the analysis and the implementation phase.

Every time that someone post a document in the system, use them, but this doesn’t mean that they understand what the dimensions are.

Let me try to explain what the dimensions are and how we can use them.

The simplest way to try to explain what a dimension is, is that they are some kind of tags in every record in the table. You can filter the table with these tags and make pivot tables or sums based in those tags.

Let’s think, we define that we want to add a “tag” every time when we sell some item. Later, we can go to the general ledger entry and make a filter of those records to make a pivot table with the sales GL Account with the item tag. So, you can have the sales account integration by item.

The first time that I have some knowledge of the dimensions was when I purchased NAV for the company I was worked for.

Let me explain what type of company is.

We import and sell wine for many countries, France, Spain, Germany, Netherlands, Italy, etc. all from Europe. Also, we bring wine from Chile and Argentina.

You can get wine from different wineries and some wineries has different brands.

We sell that wine through many customer channels, big stores like Walmart, wine stores, restaurants, hotels, bars and more.

Remember that the wine has a lot of types but, the initial type is the color, white, red, pink; and for every color we can have a mix of grapes (coupages) or if the wine was made with more than 85% of only one grape (varietal).

Finally, we can have different sizes, from 187 ml to 5 liters.

The people in the company wants to know:

How many boxes we sold by winery, brand, grape type, wine type, customer channel and the different combinations.

Now, we need to talk about the different dimensions in the system.

We have two global dimensions. Global Dimension 1 and Global Dimension 2. Those dimensions always are used in the GL records, always. You can assign those dimensions the master data like the customers, vendors, items, jobs, GL Accounts, Fixed Assets, Salespersons, resources, ok, you´ve got the point.

We have another 6 dimensions called shortcut dimensions.

Those dimensions can be used in almost the same way as the global ones, but they have one big difference, those tags are not stored in the GL entry directly.

With these dimensions, we can build analysis cubes with 3 dimensions (like SSAS) and reach our goal, get the most analytical system in the ERP ecosystem without an external BI tool.

What must we do to achieve this analysis capacity?

Well, that depends in the most important thing for the company, are they looking for the sales analysis? The cost center analysis? The jobs?

We are going to build the dimensions thinking in my old job, like a wine sales and distribution company.

The most important thing for the company was, bought and sell wine. This operations cause purchases (costs) and sales (incomes) and the difference between both are the profit.

Let’s start with the item analysis.

As I mentioned, the most biggest option to analyze wines is the color, red, white, pink but we can analyze by wine type , coupage or varietal type, we can add, fortified and rules of appellation (like Champagne or Cava).

So, after talking with the salespeople, we decided to start with color and type as dimension X and the origin as dimension Y

What is the origin? We started with old (Europe) and New (outside of Europe) world, then we added the country, the winery and the brand

With these dimensions, you can “mark” some wine as:

Dimension x

• Grape, Red
• Type, Varietal
• Specific Grape, Cabernet Sauvignon

Dimension y

• Origin, New World
• Country, Chile
• Winery, Winery 1
• Brand, Brand 1

Let me show you how I define the dimension (in Excel of course)

As you can see in the screen, we use a 4-char structure for the dimension code and the Begin-End totals to summarize everything

Also, Sales department asks for a customer channel for sales operations, we define the Dimension z which define the customer type like Wholesale, Department, Wineries, Hotels, etc.

Also, we need to split the customer in two groups:

On Trade (you sell the wine and the customer drinks it right there).

Off Trade (you sell the wine to go).

For the origin

Now, we need to think more things.

First, with this setup, we can have different reports about:

How many bottles (or boxes) we sell by customer or customer channel, which wine for which origin is the best seller

how many wines of brand 1 from winery 2 is sold in hotels or bars.

In which period is when we sell more white or sparkling wine

Can you see the possible options that can be created? Well, let’s create a Sales Analysis view to show sales price, cost and profit, only for New world totals

Then add the colums

And finally, we can have the information, how much we sell in money for every product dimension but only when applies to a New World wines and the cost of goods sale with the profit in amount. You can always calculate the % adding another column

Like the dimension capabilities?

Well, we can get more of this, we have seen the options for products sales by quantity and amounts, the same applies to purchases. Now, let's review the General Ledger Setup settings

As I´ve explained before, we have 2 global dimensions and the others are shortcut dimensions.

We defined the Prod as dimension 2, and the others as shortcut dimensions.

Why this and what is the CC dimension 1?

Well, we have Cost Centers and those cost centers are the different company branches.

Every branch has its own location, its own personnel and its own expenses, so, when you make a purchase, electric energy as an example, or maybe office supplies, you can post the expense to the GL Account and assign the expense to a Cost Center.

Then, you can create an account schedule with the income and expenses GL Accounts

For the columns, we can do the next column layout

And you can have a balance for every Cost Center

Or the sales account integration for the Red Wine Sales

Or maybe the sales account as the last one but only for one Cost Center (just adding the CC to the filter)

As we see in this blog, when you spent enough time in the dimension design, you get one of the most powerful tools in the ERP market for data analysis, and exists in application without any more cost than the time that you invest.

Your company does not sell items? You can define a “Service” dimension and use the GL Account “Service Sales” adding the Service Dimension to every sales line (quantity one for every sales line, write the unite price as the total line amount and assign the service dimension)  and later, you can have how many sales you have, how much and how many for every “service”.

Afraid that the user does not assign the dimension to the sales line? You just need to set the GL Account with the Service Dimension defined as Code Mandatory and the user cannot post the invoice if he or she do not provide the required input.

Mix this information with the Salesperson Dimension and you can have which is your best salesman. Add the “Service” dimension to the general journal where you put the payroll amounts splitting the amount by that service (you are distributing the payroll by service).

It’s a little complicated sell services as GL Account because you sell projects? Don’t worry, you can always create a dimension called Jobs and create a new dimension value for every project, add that dimension to the job lines, to the job expenses, to the time allocated for resources and you can have a full project analysis.