Dynamics GP Vs Dynamics 365 Business Central – Account Segments and Dimensions

Introduction

This is another blog in a series I’ve been writing comparing functionality in Dynamics GP to Dynamics 365 Business Central.

In this post I’m looking at account segments in Dynamics GP compared with dimensions in Dynamics 365 Business Central.

The configuration of Dimensions and Account Segments can become quite a in depth subject, so in this post I’m just going to briefly outline what I regard as the basics in both products.

**Please note you can extend the analysis in Dynamics GP with Multi Dimensional Analysis (MDA) or Analytical Accounting (AA) which I won’t cover in detail in this post. For example you might require additional analysis for a particular project which can be achieved with MDA or AA.

Dynamics GP – Account Segments

Dynamics GP supports a traditional account structure using account segments to describe a General Ledger code. You can create a maximum of 10 segments and the combination of those segments is the General Ledger code itself. For example in the demo data that accompanies Dynamics GP the General Ledger account code is comprised of 3 segments. This is shown below in the “Account Format Setup” window:

In this configuration there’s a “natural” segment, or main segment, and then there’s segments for “Division” and “Department”.

These additional segments can be named in the “Account Segment Setup” window.

Therefore the GL code below of 300-6170-00 can be described as the Repairs & Maintenance code for the “Sales” department. (the Sales department being department 300)

As I’m used to this configuration I do find reporting off the back of this configuration quite easy, however I’ve found one drawback is if I want to create a new department I’d tend to create this for all combinations of the natural code, so could end up with lots more codes that might not be used.

Dynamics 365 Business Central – Dimensions

In Dynamics 365 Business Central the main account code is made up of one segment, which is the natural code, and additional dimensions are created for analysis.

You can create a maximum of 2 Global dimensions and an unlimited number of shortcut dimensions.

Global dimensions are special as they are stored on the ledger entry tables so can be used to easily filter and analyse data throughout the system whereas shortcut dimensions are stored in a sub table.

For example below is a GL code (6110) from the Cronus data in Dynamics 365 Business Central which is a Sales Code described in one segment.

I can then assign dimensions at Customers/Suppliers/GL Code level to add my extra analysis. For example I’ve assigned the dimension “Customer Group” to the customer below with a value of “Large”. (you can also edit the analysis prior to posting)

Now when I post a transaction for this customer the dimension analysis will be posted onto the Ledger Entry tables for the transaction so you can perform additional analysis. (** As mentioned above Global dimensions are stored on the main Ledger Entry tables and shortcut dimensions on a sub table)

If I was replicating the demo company account setup from Dynamics GP I’d have the natural segment as the GL account, and two dimensions. One for the department and one for the division.

There’s also the flexibility to make the dimensions mandatory, require the same default code to be used, or have no code entered.

Finally, you can also set code mandatory or defaults at the table level for speed of setup. For example, I could setup a default for the customers table, so all customers require analysis. i.e. below I’ve set the system so every customer needs analysis to the CUSTOMERGROUP dimension

Conclusion

I’m used to the account segments in Dynamics GP, so seeing and using dimensions did take a little getting used to. However all in all dimensions offer great flexibility and if used correctly can keep the chart of accounts clear and concise.

Thanks for reading!

Dynamics GP Vs Dynamics 365 Business Central – Managing Financial Periods

Introduction

Dynamics GP and Dynamics 365 Business Central both have great options to manage financial periods therefore controlling what date ranges users can post into.

In this post I’ll explore how this is achieved in both products, and point out what I think are the key benefits of each solution.

Dynamics GP

In Dynamics GP you can open and close financial periods using the “Financial Period Setup” window as per below

There’s a couple of key takeaways here:

  1. This is a global setting therefore the configuration of this window affects all users of the system. i.e. you can’t allow some users to post into certain periods on an individual basis.
  2. You can close the financial period for one series, (or ledger) while leaving another series open. For example, in the configuration above you can post a Sales transaction with a GL posting date of March, however you can’t post a Purchasing transaction with a GL posting date of March as that has been closed.

Dynamics 365 Business Central

In Dynamics 365 Business Central you can control allowed posting dates in two separate places.

The first is by entering a specific date range in the General Ledger Setup as per below.

The next is at user level via the User Setup window:

If you haven’t specified a user as having a different “Allowed Posting Date” range in the User Setup window then they are bound to the allowed posting dates in the General Ledger Setup.

The key takeaways here are:

  1. You can have different allowed posting dates for different users.
  2. You can’t completely close one series (ledger) and have another still open.

Conclusion

I think both systems have great features.

I love the way you have the flexibility to only close down certain series (ledgers) in Dynamics GP, as I imagine in the real world departments aren’t ready to close at the same time.

I also like how in Dynamics 365 Business Central we can close a financial period using the “General Ledger Setup” window for the majority of users however you can configure some users, for example a financial controller, to have the flexibility of posting across a larger date range.

Perhaps one day one of the products will incorporate both ideas 🙂

Thanks for reading!

Brief overview and comparison of how summary values are stored and calculated in Dynamics GP, Dynamics NAV and Dynamics 365 Business Central

Introduction

There was an interesting conversation on twitter over the weekend with regards summary values and Dynamics 365 Business Central. Much like myself the person asking the question came from a Dynamics GP background and was wondering where in the database Dynamics 365 Business Central stored summary values i.e. GL period balances.

Now this is one area of the system that I really like about Dynamics NAV and Dynamics 365 Business Central so I thought I’d write this up.

The Dynamics GP way

Dynamics GP stores summary values in a variety of different summary tables. For example the GL open years transactional data is stored in one table (GL20000) and its summarised in a summary balance table (GL10110).

This means you can quickly access the summary values in an enquiry window. For example below is a GL enquiry window showing the GL period balances for a cash account.

What’s great is as this window is accessing a summary table rather than summing and sub totalling the main transaction table the performance is great, so you have access to summary data very quickly. i.e. you could 10 million rows of transactional data for this GL code but its all nicely summarised into a handful of rows in the summary table.

As I mentioned this provides fantastic performance however anyone who has worked with Dynamics GP for any length of time will realise the one issue that comes with storing data in this way. Unfortunately if there is an issue when posting a transaction “sometimes” the summary values aren’t updated and therefore they become “out of sync” with the actual transaction data i.e. if you were to manually sum the transaction data it wouldn’t match the summary data. This can cause problems and prompt awkward conversations with clients as they (understandably) ask “why” this is the case, and (even worse) how they can prevent this in the future. (I hate it when this question crops up)

*** A wider discussion here would be why Dynamics GP doesn’t update all the SQL tables (transaction table and summary table) using one SQL transaction which would negate this issue however that is beyond of the scope of this article.

To compensate for this Dynamics GP has “reconcile” features for nearly all ledgers so you can re-align the summary balances with the actual transactions. For example below is the “reconcile” utility for the General Ledger. Running this will recalculate all the summary balances based on the transactional data and update the summary table I mentioned earlier.

The Dynamics NAV \ Dynamics 365 Business Central way

The first thing to say is that Dynamics NAV and Dynamics 365 Business Central doesn’t store summary values in specific SQL tables like Dynamics GP. Instead it uses something called “Flow fields” for the summary totals which are implemented using SQL Indexed views. (therefore technically the summary values are materialised in the database just not in actual SQL tables). In the Dynamics NAV and Dynamics 365 Business Central world they are referred to as SIFT Indexes.

So for example, when you view the GL balances in Dynamics NAV as per the screen shot below the system is running queries using the SQL indexed views to present the summary values and therefore performance is still extremely good. (technically its the SQL optimiser that will choose to use this index when asked to return the summary values for this page because its much more efficient)

Digging a little deeper if you go into the Dynamics NAV development environment you can see how the SQL indexed views (SIFT Indexes) are implemented.

For example if I select the GL entry table and click “Design”

And then select “View > Keys” to look at the indexes

You can now see the keys (Indexes) on the left and some of the keys have an associated “SumIndexField” (i.e. a SIFT Index which is implemented via a SQL Indexed View). In the highlighted example an SQL indexed view is created summing the fields Amount, Debit Amount, Credit Amount, Additional-Currency Amount, Add.-Currency Debit Amount, Add.-Currency Credit Amount and grouping by GL Account No and Posting Date.

Therefore if I open SQL Management Studio and browse to my Dynamics NAV database I can see all the SQL indexed views on the GL Entry table (there are four which matches the number of enabled keys with SumIndexFields columns in my development environment)

Finally if I take a look at the definition of the SQL indexed view named “CRONUS UK Ltd_$G_L Entry$VSIFT$1” we can see this is implementing the key that I highlighted earlier

CREATE VIEW [dbo].[CRONUS UK Ltd_$G_L Entry$VSIFT$1]
WITH schemabinding
AS
SELECT “17”.”g_l account no_”,
“17”.”posting date”,
Count_big(*) AS “$Cnt”,
Sum(“17″.”amount”) AS “SUM$Amount”,
Sum(“17″.”debit amount”) AS “SUM$Debit Amount”,
Sum(“17″.”credit amount”) AS “SUM$Credit Amount”,
Sum(“17″.”additional-currency amount”) AS
“SUM$Additional-Currency Amount”,
Sum(“17″.”add_-currency debit amount”) AS
“SUM$Add_-Currency Debit Amount”,
Sum(“17″.”add_-currency credit amount”) AS
“SUM$Add_-Currency Credit Amount”
FROM dbo.”cronus uk ltd_$g_l entry” “17”
GROUP BY “17”.”g_l account no_”,
“17”.”posting date”

I suppose you could argue the one draw back on this is that whenever a transaction is posted in Dynamics NAV and Dynamics 365 Business Central then SQL has extra CPU and disk work to do as it maintains the SQL indexed views associated with the transaction tables.

Conclusion

Although I love so many things about Dynamics GP I do think Dynamics NAV and Dynamics 365 Business Central has the edge here. By allowing the database to handle the maintenance of summary values through the implementation of SQL indexed views the data can never go out of sync with the summary values.

Thanks for reading.