For those of you who attended my recent presentation in New York with Computer Futures and Microsoft, you may have remembered me promising that I would write a tutorial or two demonstrating how to hook the powerful GL into all the reporting goodies we talked about. Consider this part 1 of my promise to my fellow AX enthusiasts in NYC – enjoyed you all. No where have I seen more major changes in AX than with Financial reporting. First of all, I’m not even sure Management Reporter is needed anymore and I love Management Reporter. Microsoft made critical changes to the data model which have greatly sped up ledger based reporting. These changes to the data model have opened up the door for all kinds of things that I couldn’t possibly cover in one blog post. In this post, let’s review the most fundamental undocumented change of all (probably for some political reason but oh well), and see what’s happened.

Previously, I had blogged about how to get GL ledger data by demonstrating a big long complicated query that was valid for all of the AX 2012 versions. But something happened. Microsoft radically simplified the data access logic for General ledger data. Take a look at this.

Here is the ALL IMPORTANT DIMENSIONATTRIBUTEVALUECOMBINATION table in AX 2012 R3: Notice that it has no dimensions in it except for maybe MainAccount if that. It’s a pivotal table in nearly all the financial queries but it does not include the dimensions though it does include a display value that you can use.

Now look at this in AX 7 aka the new version of Dynamics AX… Notice very carefully that the values are there as well as the primary keys for linking into a star schema data mart!!!!!!!!!!!!!!! In other words, it has been rebuilt for reporting.

Oh snap.. This radical change now makes it easy to pull dimension data for anything. Microsoft did what many of us had been begging them to do for years and denormalized the schema somewhat for reporting which will radically improve speed of the General Ledger reporting and basically makes it so that we can use ANYTHING that we want to for General Ledger Data. Remember our discussions during the conference about Power Pivot.. the changes in AX 7 towards real-time reporting, and where things are going.. Compare this to the long queries in my previous post about AX2012 sql to get GL Data. This is straight forward and easy. I even added an extra table. Now, we can slice the GL for costs, Main Accounts, Relate it to other data and just watch stuff work quite easily.

I had planned on writing my next 3 or 4 posts on performance, but somewhere in there, I will sneak in a post about how easy it is to build a dashboard in AX 7 with our new found assumptions like I did in AX 7 to finish up my promise. Here is one that I built in Excel that took all of 4 hours for me to make. I gave a presentation on it in New York last week with Microsoft and other people as we had a ball discussing BI. For those of you who attended the presentation, you should have the real live version of the dashboard as I made it downloadable so you can take it and study it. I’ll guide you through how to do it even easier than I did with this one in GL Dashboard post somewhere in my next 2 or 3 posts.

It was fun.. And yes, I’ll be back in Manhattan to see you all again in the future. For everyone else, important points to capture here about the changes in AX 7. Many doors are opening.