Reporting & BI in AX
Business Performance Management 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 Business Performance Management solutions to enable employees across an organization gain deeper business insight and make informed decisions.
Microsoft BI Roadmap & Dynamics AX.
Microsoft Business Intelligence offers a complete suite of products that supports all facets of decision making. Through tight integration with the powerful, proven, and scalable Microsoft SQL Server™ 2005 platform, Microsoft Office® SharePoint® Server 2007, Microsoft Office Business Scorecard Manager 2005, ProClarity Analytics 6, and Microsoft Office PerformancePoint Server 2007.
The Reporting & BI features of AX 4.x has been improved with addition of new features which helps it interoperate with other BI & Reporting tools. Before taking a look at the AX BI & Reporting features, it will help if you look at the Microsoft BI & Reporting strategy and where AX fits in.
The AX 4.x Reporting features gives users a wide variety of choice and features which are as:
-Ad hoc Reporting
-Production Reporting
-Multidimensional Reporting and Analysis
-Business Scorecards
-Financial Reporting
Ad hoc Reporting
Ad Hoc Reporting capabilities enable application users to create, view, and save reports that satisfy their individual information analysis requirements. In AX 4.0, Ad Hoc reporting is enabled using Dynamics AX Reporting Server & SSRS.
The Architecture of AX & SSRS is as shown:
Basically you need to do the following:
· Install the AX Reporting Server role
· Configure SSRS & IIS for SQL Reporting Services
· Generate Perspectives
· Configure Reporting Servers in AX
· Generate Models
Once the Models have been generated, open us SSMS , expand the Dynamics Database, expand views and you will see that AX has creates views based on the perspectives which all end with “*WITHUSERIDS”. If you open this view, you can see the data along with the AX domain user id (This enables the AX security mechanism on SSRS).
Basically, model generation generates SDML. Expand reporting Server Database (ReportServer), navigate to tables and browse the catalog Table. You will see the AX Report Models.
Once done, you can view & create Ad hoc reports AX>Tools>Reporting Tools> Report Builder :)
This method gives users nice flexibility to create nice reports but you cannot execute any AX business logic (classes) using this method.
Here’s a sample report created using this J & it’s pretty cool.
Now say you have EP deployed and you want to make these reports available on EP rather than let users navigate to the SSRS URL. In WSS 3.0, you have a Report Viewer Webpart which lets you do this but if you have worked with previous versions of WSS, you must be aware there was also a Report Expolrer web part which let you choose all available reports and then displayed them in the Report viewer webpart. Well in WSS 3.0, the workaround to get both Report Explorer & Report Viewer is using WSS 2.0 webparts. The link below will explain how to proceed.
http://msdn2.microsoft.com/en-us/library/ms159772.aspx
Now once you have all that setup & configured, you will have SSRS in EP J. Kind of Cool
MSSQL Reporting services in EP Report Explorer & Report viewer Web part. (Using EP on WSS 3.0)
Production Reporting
Predefined reports in a business solution are known as Production reports. In AX you have the following options:
AX Native Report Wizard & Report features which lets you create precision production reports which are available from AX Rich clients.
You can also use the SSRS Reports as production reports and deploy them in EP.
You can also use Visual Studio .NET Report Server project. You get the option of Business Intelligence Dev Studio when you install MSSQL 2005. Using this, you can develop reports using VS Report Builder.
Multidimensional Reporting and Analysis
Multidimensional Reporting and Analytics features enable users to efficiently aggregate and analyze business metrics across core business dimensions to gain a deeper business insight into business trends and drivers.
The SSAS – AX architecture is shown below:
AX Business Analysis OLAP (You must have Business Analysis License Code in AX). You set up OLAP cubes and measures and can generate Pivot Charts from AX. You must have SSAS installed & configured. It’s a really nice feature for CXO’s who want to make decisions based on such analysis reports.
Once the AX cubes are generated, they are available as views in SSMS. Navigate to SSMS>Database>Dynamics database and expand views.
You will come across views named OLAP which are the views generated by AX for OLAP.
In MS Excel 2007, you can also directly connect to a SSAS where AX cubes are deployed. Open Excel 2007>Data> From Other Sources>From Analysis Service.
Select the SSAS Server.
Once the correct Analysis Server is authenticated, you will be able to see the AX Olap Cubes which you can select to generate Pivot Charts / Tables. Pretty cool :)
Business Scorecards
PPS is the latest offering from Microsoft to the Dynamics AX BI Stack. There were lots of activities at Copenhagen Convergence about AX-PPS Integration. (I’m in the process of learning PPS and will blog on AX-PPS later J)
The AX-PPS Integration architecture will help you get an overview. You can also refer the PPS website for more resources.
Financial Reporting
Apart from AX Standard reports, you also have FRx and MS Forecaster available for spooling out Financial Reports. The architecture will give you an overview on how these things work with AX.
Other Reporting FeaturesIn case you have SNAP installed, you can spool out data / reports in MS Word / Excel. Nice :)
You can also use Dundas charts for MOSS/WSS to get nice graphical reports for your EP. It is really very nice :)
HAPPY DAX-ing :)
Posts
