By: John Ellis, Business Central Consultant

Dimensions are important reporting tools in Dynamics 365 Business Central. With Dimensions in place, you won’t have to set up separate accounts within the General Ledger for important areas of the business such as customer groups and departments. Instead of tracking customer groups and departments as General Ledger accounts, you can track them simply as additional categories.

Ledgers within Business Central process transactions for an unlimited number of Dimension values. Default Dimension values are assigned to master data records such as General Ledger accounts, customers, vendors, fixed assets, resources, and items. These Dimension values are used in Business Central filters, in financial statements, and in data sent to Power BI.

The following is a Power BI Dashboard containing five visualizations, based on the Dimensions called “Customer Group” and “Department”:

The Slicer, in this Dashboard, allows the end user to select all or some of the Dimension values.

For example, here is what the Dashboard looks like when only the Sales and Administration departments are chosen:

The Slicer filters the other four visualizations, on “Sales” and “Administration”.

 

Designing a Dimension Dashboard

These tables are used to create this Dashboard: Chart_of_Accounts, DimensionSetEntries, and G_LEntries.

The DimensionSetEntries table is particularly important. Using the DimensionSetEntries table is a must if you are to report on Dimension values in Power BI.

 

Fields Used for Dimension Reporting

Chart_of_Accounts

• Account_Category (renamed as “Account Category”)
• Balance (the basis for the “Sum of Balance” in the summary Table visualization)

DimensionSetEntries

• Dimension_Name (renamed as “Dimension Name”)
• Dimension_Value_Name (renamed as “Dimension Value”)

G_LEntries

• Entry_No (renamed as “Entry No”)
• G_L_Account_Name (renamed as “Account Name”)
• G_L_Account_No (renamed as “Account No”)

 

Facilitating Dimension Reporting

The key to providing accurate Dimension value reporting in Power BI lies in the table relationships shown in the screenshot below.

Shown here, a “Many to Many” relationship exists between the “G_LEntries” and “DimensionSetEntries” tables based on the on “Dimension_Set_ID” field.

Of course, the “G_LEntries” and “Chart_of_Accounts” tables are joined within a One-to-Many relationship based on the “G_L_Account_No” and “No” fields respectively.

Issues with Presenting Pie Charts

The Dashboard contains both a Pie Chart and a Column Chart and it will illustrate the two important concerns in presenting Pie Charts in analytics.

Even outside of the use of Power BI, Pie Charts are not very readable.  This is especially true, when many categories of data are being presented.  Visually, it’s very difficult for the recipient of such a report to tell the difference between a slice of the pie that is 7% of the data and another slice that is 8%.

And, in the case of our Dashboard, you may have noticed that the Column Chart displays data for two Dimension values that the Pie Chart does not – “Large Business” and “Medium Business”. This occurs, because of one important caveat of Power BI – negative values aren’t supported in Pie Charts.  In these cases, negative values are displayed as zeroes.  This, in essence, means that they are not seen in the Pie Chart.

Power BI Developers should rarely use Pie Charts and should instead make use of such visualizations as Column Charts, Bar Charts, and Line Charts.

For more information or any questions you have about Dimension Reporting in Dynamics 365 Business Central please reach out to us at Support@BondConsultingServices.com or click here to schedule a free consultation with one of our experts.