Reporting and BI Opportunities in Microsoft Dynamics AX: Part 1 - The Power of Ad Hoc Reporting

This article has also been published on MsDynamicsWorld.com
Business Performance Management (BPM) features in a business application enable users across an organization to access and analyze data relevant to their functional roles in a timely and secure manner to enable improved business insight and better decision making. The term Business Intelligence, or BI for short, is commonly used in the industry to label products and technologies that support implementing, integrating, and deploying BPM solutions to enable employees across an organization gain deeper business insight and make informed decisions.

The Microsoft Dynamics AX Reporting and BI framework enables a wide variety of reporting options that can be developed, deployed, and consumed with a great amount of flexibility. You can break down the primary uses of the framework into the following areas:

Ad Hoc Reporting
Production Reporting
Multidimensional Reporting and Analysis
Business Scorecards
Financial Reporting

Each type of reporting has implications for Dynamics AX developers, users, and administrators. In this article I will examine some of the business needs that ad hoc reporting for Dynamics AX can fulfill, as well as best practices for developing and deploying such reports. In upcoming articles we will look at some of the other report types that organizations can benefit from.
Ad Hoc Reporting in Microsoft Dynamics AX
Ad hoc reporting enables business decision makers and technical end-users to dynamically develop and run reports without the involvement of IT or software developers. Such reports, which rely on SQL Server 2008 R2 and SQL Report Builder 3.0, are powerful tools for day-to-day business analysis. There are two ways for Dynamics AX users to create ad hoc reports:

Use SQL Report Builder, a report-authoring tool in SQL Server Reporting Services.
Use Microsoft Excel for ad hoc analysis of OLAP cubes.
The main goal of ad hoc reports is to use the abstracted tools and services that do not require user knowledge of the underlying database schemas or experience in programming and advanced report authoring are required to enable this scenario. This scenario is also typically referred to as "self service" reporting. Application users locate, select, filter, sort, and group data, and they view and format the results using predefined layout templates.

Features for the Microsoft Dynamics AX Application User
Application users can start SQL Report Builder from the Microsoft Dynamics AX client to design ad hoc reports and publish them to a configured SQL Report Server. They can use all features of SQL Report Builder to navigate, select, group, sort, and filter data when they design ad hoc reports. Ad hoc reports designed in SQL Report Builder can be formatted to present data in tabular, matrix (cross tab), and various chart layouts.
Application users can also start the SQL Report Manager from the Microsoft Dynamics AX client to browse report server catalogs and view published reports.
Features for the Microsoft Dynamics AX Application Developer
Application developers define abstracted views of the Microsoft Dynamics AX data model to enable ad hoc end-user reporting. These abstracted views are materialized as SQL Report Builder Semantic Model Definition Language (SMDL) models and deployed to a SQL Report Server. Application users can then open a SMDL model in SQL Report Builder and use it to author Ad Hoc reports.

The Microsoft Dynamics AX Reporting framework includes developer features that shield the developer from the intricacies of building secure, production-quality abstracted views of the Microsoft Dynamics AX data model to enable Ad Hoc Reporting with SQL Report Builder.

There are steps that a Microsoft Dynamics AX developer has to execute to define views of the Microsoft Dynamics AX data model for Ad Hoc Reporting which we would not discuss here.

Features for the Microsoft Dynamics AX Administrator
The Microsoft Dynamics AX Reporting Framework includes a comprehensive feature set to support the deployment and administration of Ad Hoc Reporting.

The Microsoft Dynamics AX Administrator is responsible for the following tasks:

Configuring and Registering SQL Report Servers
Configuring Model Generation Options
Updating Ad Hoc Reporting Models
Securing Ad Hoc Reporting

Future direction:
The following are the core enhancements planned for Ad Hoc Reporting in future releases of Microsoft Dynamics AX:

Role based Ad Hoc Reporting Perspectives and Models shipped as part of the product.
Use of Dynamics AX Views when you define Ad Hoc Reporting Perspectives.
Selective publishing of Ad Hoc Reporting Models.
Integration and access of Ad Hoc reports from Application menus, My Favorites, and Forms
De normalized relational data marts for Ad Hoc Reporting.
Multi-dimensional Ad Hoc Reporting models that are based on SQL Server Analysis Services cubes.
How ad hoc reporting is different from other reporting
Ad hoc reporting is different from other types of reports because of its availability to users in the context of the AX modules in which they work. Ad hoc reports allow users to drill down into the data in ways that older ERP systems would have never supported. Here are key features that differentiate ad hoc reports in Dynamics AX:

Users can view report data in context with related Microsoft Dynamics AX database tables and fields. For example, a user can create a report that uses data from several customer tables in the context of the Accounts Receivable module.

Users can drill down in report data.
Report Builder supports rich formatting and charts.
Users can export reports to Microsoft Excel with the click of a button.
The Reporting Services platform lets users to create reports from Microsoft Dynamics AX data and most other common data sources.
Data security is automatically enforced according to Microsoft Dynamics AX user-group permissions. If a user does not have permission to view specific data, the report returns zeros or null values.
Depending on Microsoft Dynamics AX permissions, users can view report data (and create reports) for multiple companies.
Reporting Services supports multiple-currency reporting.
The management component of Reporting Services, which is named Report Manager, is a Web-based tool for storing ad hoc reports, scheduling report execution and delivery, and managing report security.