Optimize Microsoft Dynamics GP Data for Powerful Visualizations in Power BI

To better measure financial performance across departments, our client asked us to create a dashboard in Power BI that displays year-over-year general ledger (GL) account balances, using the GL data they have stored within Microsoft Dynamics GP. This seemed simple enough, but turns out tables using Dynamics GP datasets require a lot of data manipulation to produce helpful visualizations in Power BI. I’ve provided the methodology we used to optimize Dynamics GP data to produce an intuitive table for creating a GL account summary dashboard.

Creating an Efficient Table to Load Into PowerBI

I started with this resource to first create a GL account summary table that includes the Dynamics GP datasets I wanted in my table: the beginning balance, ending balance, and net change throughout the months.

Typical GL Trial Balance

Although the table above contains the datasets I desired, the formatting of the data would require Power BI to perform row level aggregation and unnecessary data manipulation in a SQL view to produce dashboard visualizations that made sense to the end user. Instead, it would be more efficient and user-friendly to retain data structures from Dynamics GP and perform the aggregations within SQL views on the backend, as opposed to making Power BI and its end users do the work on the front end.

When attempting to write a SQL view to perform the aggregations, the first issue I ran into was that not all ending balances for accounts are calculated the same. For example, all income statement accounts are to be calculated starting at $0.00 on the first day of the fiscal year; whereas balance sheet accounts have rolling balances from year to year. To further complicate our scenario, account specific information, which is normally available in the Dynamics GP table, was not in their system—it was provided to us as a separate flat file. In this scenario, the relevant account information (balance sheet and non-balance sheet) was included in the AccountType column within the separate flat file.

With this knowledge, I created a SQL view and divided it into two parts: balance sheet and non-balance sheet accounts.

SQL used to create my STG view:

The first part of the view is a common table expression (CTE) that calculates the cumulative balance of all non-balance sheet accounts. This CTE was broken into two parts to get both current and historical accounts summary information. To arrive at the cumulative balance, I performed a windowing function SUM on the Dynamics GP field PERDBLNC, with a partition by ACTINDX and year being ordered by period. As a result, when the user filters on period 12 (final period of the year), the dashboard displays the cumulative balance. Noting that PERIODID = 0 is used for balance sheet accounts, which Dynamics GP stores the previous year balance, I chose to filter out all period 0’s and any balance sheet account types for this CTE.

STG View Part 1: Non-Balance Sheet Aggregation

CREATE VIEW [STG].[v_Fact_GLSnapshot] AS 

WITH CumulativeBalance_CTE AS (

–All non balance sheet

Click to view full SQL

The second part of the view is a CTE to calculate the cumulative balance for balance sheet accounts. Almost the same calculations noted above are being performed in this CTE; however, I am no longer excluding PERIODID = 0, because this period contains the cumulative balance of the previous year and is needed in the current year calculation. Additionally, this CTE is filtered to only include account types defined as balance sheet.

STG View Part 2: Balance Sheet Aggregation

CumulativeBalance_CTE2 AS (

–All balance sheet

Click to view full SQL

The final part of the view is to combine the above two CTEs while also adding: an Active column, ISNULL to PeriodEndBalance, and PeriodBeginBalance.

STG View Part 3: Non-Balance Sheet & Balance Sheet Union

SELECT 

    CBAL.FiscalYear,

Click to view full SQL

It’s a hefty query, but once executed, the resulting view provides me with all the columns I want. Here’s an example output for a non-balance sheet and balance sheet account:

STG View Part Output Example

The Issue: Missing Periods

The query works great for the non-balance sheet and balance sheet calculations; however, now we see that the accounts have missing period information for 2, 4, 5, 7, 10, 11, and 12 (on non-balance sheet account) and 1-12 (on the balance sheet account). In this example, we need period 12 because we want Power BI to filter on that period to get the GL account summary for the year. After digging, I found that periods do not exist in Dynamics GP when there is no account activity for the period.

To work around this Dynamics GP feature, I added two SQL queries that will execute in the ETL process I have in place to load the staging table from the view.

1.) The first query is related to non-balance sheet accounts. The query will search for any accounts that do not have a period 12, then roll up the most recent period and create a period 12 with the most recent period end balance. This ensures when Power BI is looking at period 12 for the year summary all accounts will be included even if they had no activity in period 12.

ETL Query #1: Non-Balance Sheet Missing Period 12

with

CTE1 as

    (SELECT 

Click to view full SQL

2.) The second query is related to balance sheet accounts. The query will search for any accounts that do not have a period 12, then will update the period net change as a 0 in the created period 12. This is needed because balance sheet accounts have a rolling balance from year to year, and the period net change will affect the balance total in future years. As such, the period net change for the created period 12 is to be 0, accurately reflecting the actual net change for those periods when there was no activity.

ETL Query #2: Balance Sheet Missing Period 12

with

CTE1 as

    (SELECT 

Click to view full SQL

After executing the above view and two insert queries during the ETL process, we get a more complete table with period 12 now accounted for (since there is no change over periods that do not exist, I chose to not worry about those and only focus on period 12 for purposes of the dashboard):

Post ETL Table Output Example

Changing Negative Values to Positives

There is one more cosmetic change I wanted to make for the BI dashboard to display the data more clearly. I noticed for all accounts that are increased in Dynamics GP by a credit, they show up as a negative value which could make for a confusing dashboard. To reflect these accounts as positive values, I created another view and added a calculation to the PeriodEndBalance and PeriodBeginBalance. This calculation multiples the total value by “-1” for any liability, retained earnings, and revenue account types. See below for the query I used in this view.

Final Table Cosmetic Changes

CREATE VIEW [FACT].[v_GLSnapshot] AS

Click to view full SQL

Loading a Well-Designed Table for an Intuitive Data Visualization

From this view, we’re ready to load the table into our BI dashboard. Once loaded, we set the filter to Period 12, and the summary total for all accounts (balance sheet and income statement) are accurately presented. In the below example, you can clearly see revenue, expense, and retained earning account types, and the values are all positive.

Final Data Visualization with Optimized Data Load

No aggregations were performed in PowerBI to create this dashboard, and only one filter is needed. If the original Standard GL Trial Balance table above was loaded, Power BI would have had to aggregate the data, and multiple filters would have been necessary to arrive at such an intuitive dashboard. In this case, with a simple Period filter set to 12, we see GL account totals for the year.

We created an optimized GL account summary table for Power BI, and our client now has the visuals they need to quickly understand their financials and make timely business decisions. I hope this helps anyone running into issues when creating a GL account summary table in Dynamics GP. Please share other effective approaches you may have used!

Need help with software evaluation and selection?

With the analytics industry changing at such a rapid pace, we know it’s a challenge to keep up with new features and technologies. As data and analytics consultants, we have experience with a wide range of BI products and can help you cut through the noise to determine which tools can solve the problems you’re facing.