Management Reporter/Frx Roadmap

The big question has finally been answered, what is the future of Management Reporter, FRx Reporter, FRx Forecaster and Enterprise Reporting? Well MS has released this roadmap.

2010 - MR to Replace FRx Reporter as the reporting tool of choice for Dynamics AX. This will coincide with the release of AX2010 (6.0) and it will be called MR V2.
2012 - MR to be released as V3 with AX2012 (7.0) and will now include Forecaster.
2014 - MR to be released as V4 with AX2014 (8.0) and will include functionality from Enterprise Reporting.

Personally I would not move from FRx Reporter until 2012 having learned a very hard lesson over the last year or so with the current version of MR.

Regards

Paul

Dynamics AX 2009 - Half Way Review

I have spent the past 14 weeks with Dynamics AX 2009. In this time we have installed and configured it with a view to going live in 2 weeks time. I am reasonably happy with the product so far but, like all enterprise software solutions, one only really gets to grips with it once you go live. In these few weeks we have configured General Ledger (GL), Accounts Payable (AP), Accounts Receivable (AR) and Fixed Assets (FA). We have also written interfaces to and from our Stock & POS, Payroll, Bank Reconciliation and PO System. We are also in the process of finalising bespoke integration with Excel for uploading of journals and invoices.

Database Design

Believe me, I have asked the question, but no answer as yet. The design of the database flies in the face of database design principles. Very little attempt has been made to normalise the tables. The biggest sin of all is that the company code (DataAreaId, Char(4)) is stored against EVERY record in the entire database. This would have been the prime candidate for a surrogate integer key. Follow that closely with AccountNum and Dimensions and one can see the HUGE space savings and potential performance improvements. This design, in my experience, has 2 potential sources. One, the database was designed by front end coders. Two, the database was designed by someone who has little experience with large record sets. What is going to happen when my LedgerTrans table gets to 100 million rows? On that note based on all the answers that I have received back from Microsoft it would appear as though Dynamics does not have a stock standard archiving solution shipped with it. So this is what I'm thinking - "I have a database which is not normalised, check. My business processes approximately 40 Million Journal Lines per annum, check. Dynamics does not ship with an archiving solution, check. Question - What will happen to performance and database size over the next 3-5 years?". The answer? Upgrade the DB to SQL Server 2008 with compression set on. Talk about banking on technology improvements.

Something else that really baffles me is the apparent lack of referential integrity. There are also no stored procedures and very limited use of views. I understand that the product is also designed to run on Oracle but one has to ask the question. Why does a company that has developed a database and punted certain principles of design within that product group then write a product that forsakes all of them.

Accounting and Interface Design Principles

I have wrestled hard trying to come to terms with the design principles and how Axapta was conceived. It is both ingenious and ludicrous at the same time. The concept that one needs to get under the belt very early is that you can do just about anything from anywhere when it comes to journals. One is able to debit a supplier in Accounts Payable and credit a customer in Account Receivable directly etc etc. The system setup through it's posting profiles etc from all Subsidiary Ledgers keeps the General Ledger in balance at all times. With such abilities comes the inevitable framework to flummox even some of the most seasoned accountants. When configuring the system you have to keep your wits about you and really focus on the requirements. I cannot help but think that somebody with excellent systems design talent sat down with a bunch of accountants and when all was done and dusted he presented a system, not as the Royal Accounting Society would have done it, but rather as a Technological show piece. I like it, but it will take sometime for the business to fully come to terms with it.

External Interfacing

Dynamics AX is somewhat of a framework and does allow a number of ways to interface to it. The most prominent being through Web Services, the Application Integration Framework (AIF) and natively using X++. If you have read my prior blogs you will know that we hoofed the AIF due to bugs. We have used X++ to interface from our LOB Systems. (When I get a chance I will pen a full article on how we achieved this). After our experience with the AIF and BizTalk we have not endeavoured to test the Web Services.

Reporting

Dynamics AX does not ship with adequate report writing capabilities. Period. 

Frx Reporter is an additional cost and we all know that it is on it's way to the grave. PerformancePoint Management Reporter can be purchased BUT no standard direct data access at this point. My approach to this has been to keep the exact same PPS Financial Model as from our current ERP System and to just add to the data from AX after we go live. This way our Accountants will have one source of reporting data and it will have current data as well as years of history.

Overall Opinion

To date nothing in AX has wowed me. I am impressed with the development framework, disappointed with the reporting and pretty much neutral with everything else. We are not sure how it is going to perform in the wild but time will tell. Both our AOS and SQL Servers are way over spec'd so we do not really think that performance is going to be an issue. We followed the standard guidelines on setting them up. I do believe that we should however move to SQL Server 2008 as soon as we are comfortable with it. (Read- wait for SP1).

PerformancePoint Server - Management Reporter

Management Reporter is bundled with PerformancePoint Server 2007 (PPS) and is actually a re-write of Frx Reporter which has been around for quite some time. It now runs on SQL Server and connects to the PPS Financial Models. The team has also buffed the UI and it has the Outlook pane look and feel. Unfortunately they did not have enough time to give it the new "ribbon" Office 2007 look.

In short it is a tool for writing financial reports which looks like it has been designed by accountants for accountants. It comprises 3 elements, the designer, the viewer and the database that stores the report layouts and data. From within the designer one creates reports using a "building block" method. The rows, columns and reporting trees are defined and then pulled together in a report definition. This methodology allows a tremendous amount of re-usability and flexibility. All terribly easy to use. I had no Frx experience and read the help file which has a mini tutorial look and feel about it and within a few hours had reproduced our Income Statement with some complex calculations for PMO and GP etc etc.

One can publish the reports to MS Reporting Server, export it to Excel or just view them in the MR Viewer. Facilities exist to e-mail the report to users as well.

I have been using it for the past 6 months or so and one does need to be aware of certain issues surrounding the installation and usage thereof.

Installing

If you try and install the database remotely you will get an error. You must actually install the product on the server that houses the Management Reporter (MR) database. Only once you have done this can you install the client on your desktop. The documentation is not entirely clear on this.

Another "gotcha" is that you must have the PPS Excel Client (Planning Business Client) installed on your PC in order for MR to work.

PPS Hierarchies

If you thought that your PPS account and entity hierarchies were going to be respected you will be disappointed. The pop up selection box for accounts and hierarchies does not distinguish between those accounts that are at the leaf level or those that are parents and thus summary level accounts. If your accounts do not have a distinguishing feature within the account naming you will be lost.

PPS Member Properties

Something else I found a bit lacking was that none of the member properties that one creates for dimensions within PPS are exposed to MR for reporting purposes. This means that if you require them for your financial reporting make sure that they are full dimensions. I got caught on this with my entities. I created a member property called Country but could not use this in MR. I recreated the model and made the Country a full dimension. It is now available in MR for reporting on.

Performance

Performance is something that Microsoft are addressing as an issue from my side. MR has been re-written to pull from PPS and it uses MDX to bring back the data from the model cubes. For some reason the performance is not up to scratch. We are hoping for a solution shortly.

Report Generation

The current version only allows report generation one at a time. You cannot select multiple reports and run them simultaneously. You also cannot run them unattended on a schedule. Microsoft are not sure when this type of functionality will be included.

Multiple Reports on the Same Page

It is not possible to create a number of reports and have them co-exist on the same page. This is something that all Management Accountants will curse.

Conclusion

All in all this product looks neat and should just about cover most of the reports required by the Accountants.

I will teaching some Accountants how to use the product over the next few weeks and will report back on that exercise.

- Paul Steynberg