Reporting from Dynamics AX can be a challenge, especially since the release of AX 2012 started the introduction of a much more complex underlying data structure, taking the table count from an average of 1,800, all the way up to 6,400. In working with hundreds of implementing partners and users all over the world, the feedback we are hearing about reporting and analytics in Dynamics AX 2012 is that it’s time consuming, expensive, and unreliable at the best of times. In this blog post, we are going to explore the reasons why and how to get around those issues (without upgrading).
The most typical, native tool used for reporting from Dynamics AX 2012 is SQL Server Reporting Services (SSRS). As the default tool, all standard out-of-the-box operational reports require a technical resource that is familiar with Visual Studio, SSRS report design, and the Dynamics AX database structure. If you have been using Dynamics AX 2012 in your day-to-day operational tasks for any amount of time, you are very familiar with the process of building and sharing reports. You’ve faced delays, whopping consulting fees, and general confusion when trying to make changes to an existing template or design a new one. You are also fully aware that there are a lot more programmers out there than there are Dynamics AX data experts.
But why do these issues exist? Here are some of the most common reasons:
- Complexity of underlying AX 2012 data
- Lack of experienced AX developers on staff
- Reliance on consultants and cost of services
- No easy way to consolidate additional data sources
- Inability to access one accurate, real-time view of your data
- Limitations in the out-of-the-box cubes available
As these headaches start to add up, it begs the question of change. Should you upgrade? Should you implement a new reporting solution? How much time and effort, on top of what you’ve already spent on reporting, is that going to cost you?
On top of the fact we don’t all have unlimited budgets, most of us don’t have the luxury of tying up internal resources on disruptive projects that can actually impair the ability to access data for quite some time. Plus, if a business management system is currently working well in every facet apart from reporting, then a big ERP upgrade or software implementation is simply not a feasible option. An ERP upgrade also won’t necessarily solve reporting and data visibility challenges. So, while a big overhaul is not necessary, there are ways of improving the places that need help.
Here is an overview of the 4 key areas in Dynamics AX 2012 reporting and analytics that consume the most time, resources, and energy. After explaining the core issue, we will share a few ideas and tools that can help save your Dynamics AX users time, while delivering better reports and dashboards.
1. Programming in the SSRS Environment
PROBLEM: Slow and expensive
When it comes to reporting in Dynamics AX 2012, the traditional use of programming (SSRS) can oftentimes be slow, bottlenecked, and expensive thanks to the way the large volume of data sits in the system and the required linking properties. In fact, some companies have spent up to $40,000 on a single report! And that’s a success story in many cases, believe it or not, because other companies are not able to get the report they need because it simply doesn’t work.
SOLUTION: Better organized data with an easy-to-use, faster front-end reporting tool built for Dynamics AX. A data warehouse that automates and organizes all the data from Dynamics AX (including taking care of those complex linking properties) combined with a user based front-end reporting tool, like that offered by Jet Analytics from Jet Global, makes it fast and easy for non-technical users to create and share reports and dashboards.
2. Building On-Line Analytical Processing (OLAP) Cubes
PROBLEM: Time consuming and expensive
Building or modifying data cubes in Dynamics AX 2012 is slow and expensive because of the complex tools and resources required to use them. When you want to write a new query, usually in Power BI, and it only has 7 out of the 12 fields you need, you have to customize the out-of-the-box cube by adding multiple data items to it. You can’t begin to do this without the right resources who understand both SSAS data cubes and Dynamics AX 2012. Most people don’t have this unicorn on staff because it’s not something you need to do all the time.
SOLUTION: Easy backend cube management
Jet Analytics (formerly Jet Enterprise) has a much easier and faster way of managing cubes in Dynamics AX 2012 using the Jet Data Manager, which automates building the SQL code and data movement tracking for you. Any database administrator on your team use the drag-and-drop or point and click functionality in Jet Analytics to modify data cubes, allowing you to become more efficient getting the data you need, when you need it, without expensive consultants or delays.
3. Managing Ungoverned Data in Excel
PROBLEM: Fragmented and unreliable data
Excel is the best, most widely installed reporting tool but unfortunately, it’s not governed or secure. Without control over Excel, every person in an organization has their own spreadsheets, that have been compiled with data their own way, that exists only on their PC; therefore, everyone has their own version of the truth. While the tool itself is great, the lack of governance results in fractured data and no communication.
SOLUTION: Get Excel under control
Implementing MDM (Master Data Management) and a data warehouse to govern your data will secure it and essentially lock it down, acting as a single operating venue for your reporting across unlimited users. Jet Analytics allows you to still use Excel for all your reporting needs, but it provides a single source and data management system to manage the distribution, security, and run-time of these reports.
4. Putting Views Together in Power BI
PROBLEM: Hard to use and maintain
Power BI is a leading visualization tool but without the right underlying data solution, it’s really hard to use and maintain in Dynamics AX 2012. It’s challenging to put together views because of all the data joins and mapping it takes to create one single view. If you change the way you look at something, you don’t just rewrite your data warehouse in one place – you have to rewrite 70 different views. It also doesn’t have data segmentation.
SOLUTION: Implement an underlying data solution
Jet Analytics gives you the right structure to take advantage of Power BI’s powerful visualization tools, using easy to access, organized data cubes to prepare the data first. With all your data in one place, it removes the needs for joins or locks and uses links instead. A process that would take upwards of 2 days only takes a half hour with Jet Analytics, and any typical user can do it themselves.
Help your Dynamics AX users become more self-sufficient with a reporting and analytics tool designed for ease and reliability. Jet Reports for Dynamics AX is a simple, flexible self-service reporting tool that gives every employee in your organization the ability to create and share financial and operational reports directly inside Excel.
As a solution that is purpose-built for Dynamics AX, Jet provides multi-database consolidation for full visibility into performance. And the best part of all is, it’s quick to implement! No complicated software implementation that takes months to see any results – Jet Analytics can be installed in a few hours, and have you building reports shortly after. Stop relying on costly Dynamics AX developers and trust your reporting in Dynamics AX 2012 to Jet Reports – and your users!