I haven’t been writing for a while and its New Year and something new I would like to share. It’s a 1.5 year Dynamics AX Reporting journey I have been trough. Difficult, full of disappointments and excitements. Everything described in this post is not related to the project I am part of, but mostly about the product itself and capabilities it provides in terms of Reporting.
I am lucky to be part of the one of the biggest Dynamics AX implementation in Asia-Pacific. I have been working on BI component of AX for quite a while and I hope my findings will help someone to make a right steps in delivering AX BI component. It’s important to say, that my team and me, we haven’t seen any version of AX before. And one day, number of Web Devs + SQL Devs have been tasked to develop all missing operational reports in AX, around 50+ reports. “Oh Reports in SSRS? Yep, boring, but easy staff. Who doesn’t know how to write SQL queries and build SSRS? Everybody, right?”. We have been so wrong…
Nobody told us before, that writing reports in AX is a heavy coding + 3% of SSRS styling… To write a report, you will need to become a guru in data discovery, code debugging, query optimisation, etc, etc.
For people who is not familiar with AX Reporting Architecture, imagine you need to build a simple web application. That Web Application is connected to SQL DB. All what your application does is using SSRS Report Viewer control to display reports. However, rather than using SQL commands to write report queries, you will need to create all your data sets in C#. You can use LINQ of course, but imaging complex SQL query for 100+ lines + set of complex multi value parameters. Trust me, its not an easy job even you know all fields and tables you need.
Anyway, after spending 3 weeks on learning X++ and building my first X++ SSRS report, I had only one question to Microsoft in my mind – WHY? Why it has to be so complex. Why logic of the report has to be part of the system? It is just a report. Why report has to be part of the complex deployment process with system outages etc. The whole deployment process is another discussion, may be for small companies its less stressful, but for the big implementation with 30+ legal entities and different languages, deployment becomes just a nightmare…
After finishing couple of the reports, I have started asking consultants around if there is any other way to build reports. Simple SSRS report connected to the live DB or replica? The answer was no – too much effort on rebuilding business logic + there are some good features of AX, which you just technically can not implement in out of the box SSRS.
So why reporting in AX has been build in such a way:
Reusing business logic. All business logic in one place. Using X++ for developing, gives you unlimited flexibility in terms of forming your data sets. You have all access to classes and tables. When you see a text filed in AX, it might look like a simple string filed in the table, but behind it might have 20 nested functions to calculate that string value. Using X++, you don’t need to know about those 20 nested functions, you don’t need to understand the logic behind, you are, as a developer, only interested in the final return. It really helps to minimize the development effort. However, it also might have a negative impact on performance. Imaging that this 20 nested functions return result for 10 fields, but you only interested in one field, you don’t care about other 9. The most interesting thing, your field might take only 2% of load for the whole function. 98% of the load take fields, which you don’t need. In this case you have to take the risk of your report to be timing out on production or rewrite/copy/duplicate the logic of those 20 functions, which will only return one field you need.
Complex Parameters. I would say, this is one of the best thing in AX compare to out of the box SSRS. Ability to display complex drop down controls, with filtering and sorting is something SSRS is missing.
Multi language. I can’t say you are not able to implement different languages in SSRS, but in AX it’s really straight forward with using Labels.
Dynamic Links. Another thing, which just wont work in SSRS is Dynamic Links. Have you seen Customer Account Number in AX SSRS is highlighted and allows you to open Customer Form directly from the report? Yep, this is a dynamic linkage. You report has to be run in AX for links to work.
Security. I should put it first actually, but everybody thinks about security as a last step. Having reports in AX, allows using the same security model. If you decide to use just SSRS, you have to recreate all your security. If any changes done in AX, you need to apply them to SSRS as well. Keeping two unsync security models is not good and will mean unnecessary maintenance.
If its so good, why get rid of X++?
Considering all good things about Reporting architecture in AX, it is still might not be the case for large implementations. This is why:
Skill set. How easy it is to find a good SQL dev? And how easy to find a good X++ dev? Try to seek it, you will get the answer straight forward. Remember, that your X++ devs wont be creating SQL which can be changed at any time, optimized, removed. Your SQL query is not part of the system, you x++ is. Developing in X++, you are writing into your system. Your report logic becomes part of the system. Now, remember a function with 20 nested functions. This time developer finds that function return only 10 fields, but for report we need one small extra field. What developer is going to do? He will make changes to all those 20 nested functions to bring that field from bottom to the top. How big is the chance that now the whole piece of code will work exactly the same way it used to work? You will need to have a really skilled devs, to make sure that any changes to the existing functionality wont break something somewhere…
Merging/Deploying/Releasing. Such a nightmare… Your report now is part of the model. You need to have proper merge/deploy/release process. You cant just create a report, give it to users to test and release it. Your report now can f** up the whole system OK, you release a report and users are asking you to change a label. How long will take for you to change a label and deploy a report in standard SSRS, so users can see the change? 5 minutes, 1 hour? How about a week or a month for changing a label? This is reality. Your X++ code becomes part of the model, together with SSRS file itself. Remember beautifully parameters form, AX using SSRS XML file to create a dynamic parameters form + deployment of the reports. As your ssrs xml file is part of the model, you can deploy your reports directly from AOT. Not sure if its a good or bad thing.
Performance. Performance in x++ is terrible. Even if you decide to use a SQL query inside your X++ (not a good practice, MSFT paper somewhere…) its still 5-10 times slower, than just running the same query in SQL Management Studio. I have got examples of reports, which are running in AX for 17 minutes, when SQL execute them in less than a minute, exactly the same query…
Actually, those 3 points are the major ones which lead us to think about changing the approach of reports development. We have started thinking about simple solution, which will enable rapid delivery, will keep good features of AX and will enable us to use latest SQL technologies to improve the performance. In the next post I will describe the solution we have came up with.