The Analytical Accounting (AA) module within Microsoft Dynamics GP enables the setup of dimensions to analyse financial data beyond the general ledger accounts, without having to increase the records in your chart of account structure. The versatility of AA lends itself to many different uses and can solve complex transaction analysis requirements.
We have found that the AA module works well in the following use cases:
- Grant income and expenditure for education and medical research organisations.
- Project profitability and employee expenditure.
- Tagging other non-financial data to financial transactions.
How do I analyse Analytical Accounting data in Dynamics GP?
You have all this powerful transaction analysis captured in your Dynamics GP, but how do you surface this data in a way to discover insights and make more informed decisions?
Customarily, prior to Power BI being available, organisations extracted Analytical Accounting data out of Dynamics GP by using SmartLists, Management Reporter, or SQL Server Reporting Services (SSRS).
SmartLists are an awesome tool, being easy to use, efficient, and fast. SmartLists work very well for a one-off AA data analysis, but the process can become quite cumbersome when the same report is required on a regular weekly or monthly basis.
Within Dynamics GP, a SmartList is selected and then exported into an Excel spreadsheet. The data is then scrubbed, sorted and filtered. A lot of time is spent transforming it to an easy to read format. Maybe graphs and charts are added too. The process is repeated every time the report is required. It becomes outdated very easily as the data is not being automatically refreshed.
Management Reporter is another option. It is a great tool for static reporting, however when the requirements get more complex, management reporter may not be flexible enough. When users are seeking answers to questions, they need to be able to filter data, make ad-hoc queries, and drill-down to source transactions. Using dashboards in Power BI, users can better interpret the data with visualisations that highlight areas of concern, surface patterns, and trends in data that can be investigated. The outcome is better insights driving the necessary action.
SQL Server Reporting Services (SSRS) is the third tool we highlighted above. Besides the fact that SSRS is a more technical tool to use and requires users to have some knowledge of writing SQL scripts, the process also requires users to take the data from the SSRS report to a spreadsheet for further transformation before it can be analysed. As with SmartLists, the data is not automatically refreshed so the process needs to be repeated every time a user needs the report to be updated with the latest figures.
Why Power BI?
Power BI is a natural fit for surfacing data and analysing dimensions such as Analytical Accounting transactions. It is a low maintenance tool to use. The business rules to extract and transform the data are set once and are automatically reused when new data is loaded into Power BI.
The data is refreshed automatically multiple times a day, so it does not need to be manually extracted weekly or monthly. This means that when a new general ledger account is added or a new analytical accounting dimension is created within Dynamics GP, the related data is also loaded into Power BI the next time the report is automatically refreshed. When using other reporting tools, users may need to modify the reports to allow for the new addition.
With Power BI, data consolidation is quick and easy. One Power BI report lends itself to many different views of the data. Users can easily look at values or units at a summary or a detailed level with full drill-down to data entry level. Data can easily be filtered by multiple dimensions such as codes, projects, grants, employees, etc.
Power BI is a great way to share data with users who do not have access or a user license to view Dynamics GP data. Sharing Power BI reports with operations teams outside of finance is a safe and agile way to grant users the ability to self-serve reports without relying on finance to answer their data related questions.
In summary, Power BI is a low-maintenance approach to extend self-service analysis of analytical accounting data to teams and users across the organisation in a secure way. The real time views of the data, from any device, coupled with the automated scrubbing and transformation of data delivers huge time savings.
