Microsoft Dynamics AX Users Seek Custom, Third Party Solutions to Business Intelligence Needs

Microsoft Dynamics AX 2009 and 2012 may both come with hundreds of standard reports, but among about 150 AXUG members assembled at AXUG Summit 2011 for a discussion on BI best practices, almost none reported using using either standard operational reports in AX or using FRx or Management Reporter for financial reporting.

The complaints for the standard tools varied - performance problems, requirements mismatches, unsophisticated financials, and a lacking easily available analysis capabilities. And beyond any real shortcomings of the products, several users admitted to simple lack of awareness about what those hundreds of pre-built reports can really do. Based on a show of hands, about 80 percent were AX 2009 users, about 15 percent were on AX 3.0 or 4.0, and just a handful were on AX 2012.

"For all the AX deployments I've done, I have yet to see a customer use a lot of the standard reports," said moderator James Gurney of ideaca. "The standard reports are good guidelines but expect to create new ones." But from what members of the audience shared, AX customers are typically looking to one of several alternative approaches- SQL Server Reporting Service (SSRS), Excel, a third party BI solution, or a full-fledged data warehouse (DW) or operational data store (ODS).

Perhaps most notable among the audience was the lack of enthusiasm for Management Reporter and its predecessor, FRx. "It's just not functional," complained one attendee. "If you're a top financial professional in a larger corporation, there is no way you're using FRx." Though the same attendee noted that she hadn't seen MR given the fact that its latest releases have only recently caught up with the functionality of FRx. ideaca's Gurney pointed out that there is very likely a threshold for companies in terms of size, complexity, and regulatory requirements that will rule out FRx or MR.

Other complaints with the older FRx included problems with multiple currencies. One attendee worked for a company that utilized twenty currencies in offices around the world - she quickly dismissed FRx due to performance issues. The final area of concern was the fact that these tools are limited to GL data and often companies needed a solution that gave them access to other data as well on a regular basis.

Since AX users are not using FRx or MR, what are they doing instead? The short answer - if they are not exporting data to Excel (which many are), then they are writing their own reports with SSRS or looking to third party solutions. About 30 percent of the room reported using SSRS to create the reports they needed.

While the flexibility is there with SSRS, it also brings more complexity to the report creation process. One user noted his challenges in building a multi-company report with complex SQL statements that became "unmanageable", although the power of the reporting engine made it possible to build such reports and give management the right view of the financial data. Another issue that has to be managed with SSRS is the decoupling from AX security rules.

The future may also lie in newer features like the Excel Add-In for AX 2012, which brings capabilities similar to Dynamics GP's SmartList to AX. And as familiarity with SSRS grows, one partner noted that use of SSAS and SSIS will also grow to leverage more of the analytics abilities of the SQL Server platform.

Attendees expressed mixed feelings about third party reporting tools. They adopt these tools seeking a way for IT to give the report writing power back to the business users, thus improving the users' control of the system and reducing the development load on IT. Developers and development managers in the room said that custom report development was a major drag on their productivity. On one side they fight with performance issues of standard reporting in AX (especially for custom AX reports on operational data). And the report development process often falls into a pattern of endlessly shifting requirements that waste time and effort as reports are reworked through many iterations.

While there were only a handful of users in the room who had actually deployed a data warehouse solution, those with experience were true believers in the idea of separating data analysis tools and architecture from ERPand its tools for business operations and transactional data. "AX is not meant to do massive data reporting. It's a scale issue, and once you get into a scale issue, you need to separate operational reports from analytics in a separate platform," said Michael Simms, director of business intelligence at Client Strategy Group.

Attendees identified other, less obvious benefits of running a DW or ODS solution related to redundancy and upgrades. One attendee noted that storing all historical transactional data in an external system simplified of the upgrade process, especially for bigger jumps like AX 3 to 2009. It also gives CIOs the confidence that their ERP data as new as yesterday is always going to be stored and accessible from a separate system.

The biggest challenge in the DW and ODS discussion was the challenge of building such a system. Given the expense and complexity of full fledged systems, most attendees had not done it and a few who were considering probed for guidance. The general response: DW/ODS is a complicated process with no easy answers or simple steps that fit every organization.

About Jason Gumpert

As the editor of MSDynamicsWorld.com, Jason oversees all editorial content on the site and at our events, as well as providing site management and strategy. He can be reached at jgumpert@msdynamicsworld.com.

Prior to co-founding MSDynamicsWorld.com, Jason was a Principal Software Consultant at Parametric Technology Corporation (PTC), where he implemented solutions, trained customers, managed software development, and spent some time in the pre-sales engineering organization. Jason has also held consulting positions at CSC Consulting and Monitor Group.

Read full bio...