Before going to the Dynamics AX world, I spent years working as an Enterprise BI architect. One of the most common things that I have to explain to companies when I am consulting is how a Dynamics AX BI Specialist differs from a traditional Microsoft BI Specialist?

NOTE: the following is not an Official Microsoft statement but merely me giving my experiences from having seen way too many BI Implementations. In this guide, I talk about the pros and cons of the current BI approach while presenting a non-sales fluff, real-world view of what I frequently face as Dynamics AX BI specialist.

1^st, why are so few companies within Dynamics AX unaware of the requirements for a BI specialist?

After having spent years integrating various systems into the Microsoft BI Suite, such as SAP, I had the opportunity to work with various companies. Most of the large enterprise companies understood that BI was a separate skill and had a good feel for the length and complexity that often goes into crafting a good BI solution. When done correctly, BI will pay for itself again and again while becoming an essential component in a company’s long-term health and survival.

In Dynamics AX, many companies are quite surprised to discover the work that it takes to make a good BI solution. The reason for this has to do with the fact that Dynamics AX has only recently gone to a full Enterprise Solution. Traditionally, Dynamics AX was considered an ERP solution for Mid-sized companies and the BI framework was simplified to avoid many of the concerns of a true BI solution. Many BI configurations were simply done out of the box, and when they weren’t, they were written by hand with custom code. As a result of this, once Dynamics AX became an Enterprise Product, the BI solutions were in need of changes – a process that is very much ongoing.

****Starting with the BAD****

2^nd, how does report writing differ for a Dynamics AX BI Specialist as opposed to a traditional Microsoft BI Specialist?

Yes, they are different though it may seem clumsy at first. In the past, Dynamics AX report writing was developed to be tightly integrated with the X++ code language. It was completely independent of traditional SQL Server SSRS reports and development driven. The problem with this is that SSRS developed rapidly over time and came to significantly surpass the Dynamics AX reporting suite. Realizing this, the Microsoft team decided to go with full-blown SSRS in the 2012 version with one caveat. They customized it so that existing Dynamics AX developers could leverage the old Dynamics AX way of development within the new framework.

However, there was a cost. The current version of Dynamics AX SSRS is about 6 years behind the current version of SSRS in terms of functionality and all kinds of clumsy errors with the Dynamics AX reports that I won’t discuss here. There are two compelling cases where I’ve found the need to use traditional Dynamics AX reports – record security and currency awareness. For the rest, I just use traditional SSRS and amaze my users with pretty reports.

3^rd, how does Dynamics AX handle data cleansing?

There is no data cleansing within BI for Dynamics AX. Understand, that data cleansing was part of Integration Services which doesn’t exist in the Dynamics AX World yet (it will one day. You can see it moving towards it). The Dynamics AX BI framework hasn’t evolved to this level. To do true data cleansing, you need a very experienced Microsoft BI person with an understanding of Dynamics AX since it will completely change the out of the box methodology.

If it sounds sad to think of an enterprise BI system without data cleansing, you are correct in your thinking. But remember the history, this is the first version of Dynamics AX that is for Enterprises. Very impressive gains were made in a number of areas, but Rome wasn’t built overnight.

4^th, how does Dynamics AX handle Extraction, Transformation, and Loading

Experienced BI people know that this is where the real work occurs when it comes to building a data warehouse. It is the most complicated step. Unfortunately, Dynamics AX is currently 14 years behind when it comes to ETL. First, most of the ETL is done through AIF, involving heavy code with way less benefits than a traditional ETL tool like SSIS or Informatica. Developers often spend months writing code that isn’t nearly as efficient as the built-in tasks within SSIS.

The second way that Microsoft provides for ETL with Dynamics AX is through the Data Import/Export framework. While promising, this still has a long way to go. It reminds me a lot of the old SQL BCP tool at current.

Don’t be surprised if we see some sort of tie in with Integration Services and Dynamics AX in the future as we did with Analysis Services. It is just too big of a win for Microsoft to take its flagship ETL tool and tie it into the Dynamics AX system. This isn’t to say that someone couldn’t use SSIS. It’s just that the Dynamics AX ECO system isn’t even aware of this powerful ETL tool as of now.

5^th, How does Dynamics AX handle Cubes

In Dynamics AX, initially getting a cube up is almost the equivalent of flipping a switch. It is fast and deceptively easy. This works great for companies with no BI staff, who need to get some cubes up quite quickly, or are just starting up with BI. It fails for larger companies who truly need to see an integrated view across their Enterprise every time.

One of the worst talks that I have to discuss with a client is when I tell them that the Dynamics Cubes won’t meet their long-term needs. I hate when that happens. In the SAP and JDEdwards worlds, companies understand that out of the box cubes have their limitations. Unfortunately, BI is still so new to many in the Dynamics AX world. It can be quite traumatizing to a company to hear that they need to invest 3 months into a BI project (and that is with someone who really knows what they are doing handling it). Also, understand that Dynamics AX is unaware of the tabular model (despite its powerful advantages).

Inevitably, you will end up using at least some of the out-of-the-box cubes that Dynamics AX uses. Important, you cannot be a good Dynamics AX cube developer without having a deep understanding of the functional side and configuration. Too much of the cube functionality hinges upon functional settings of the various components of Dynamics AX. Since you have no control over the ETL, these must be set correctly. Do create categories for accounts and transactions, reason codes, dimensions, dimension sets, date intervals, budget models, ect. All of that becomes very important. Unfortunately, the implementers are often unaware of how significant configurations are when it comes to good cube setup.

If done right, you can really see some benefits.

6^th, Management Reporter is the only true Self-Service BI tool at present

So, I just finished developing a ton of Management Reporter reports. Overall, I finished over 30 reports in less than 5 days development. There is nothing else like it. I recently trained one of my clients on it and they were in awe. Why this wonderful tool is not always used when it comes to financial data, bewilders me. A person needs to know no code, but can produce amazing solutions very rapidly.

Stay tuned on Management Reporter (Brandon has written something that he plans on posting in the future). You have to see how great this tool is. But Management Reporter cannot usher in Self-Service BI by itself. More is needed.

*******The GOOD******

7^th, There are workarounds for all of the BI things

At the end of the day, Dynamics AX runs through SQL Server. That means that if a BI Expert has enough knowledge, they can get around all of the limitations and impress their clients. I’ve created executive dashboards in PerformancePoint, quick dashboards in SSRS, and used Excel to my heart was content. I then integrated that stuff in modules and it looked like a regular part of the interface to my users. It can be done! One time, a user even requested that I rewrite all of the core built-in reports as pure SSRS reports to get around the formatting issues. Don’t be scared to use the regular BI tools with the exception of Integration Services.

Unfortunately, be very careful on doing anything that results in a DML operation or long read operation (with locking) on Dynamics AX. It can seriously mess stuff up.

8^th, Ushering in Self-Service BI

I dream of 100 blog posts that I would write if I just had the time. One of them that won’t leave my head is showing just how easy it is to set up a PowerPivot model that will make your users go crazy in Dynamics AX with joy. Oh well, one day I will write it. My point here is that there are ways to give your users Self-Service BI and they will love you for it. Dynamics AX has a very strong footprint with Excel (fortunately), so it is a good place to start if you are trying to think of ways to shake things up and make your users happy with true BI power.

9^th, Changing times but road map is defined

Dynamics AX is a good product, not a perfect one, but a good one. Dynamics AX 2012 was really the first version of Dynamics AX to go Enterprise. As a result, some of the changes are in their first version on infancy state. Everyone knows how first versions of software usually perform. I, personally, can’t wait to see some of the new enhancements as the Microsoft Dynamics AX BI toolset becomes more integrated with the existing SQL Server BI toolset. It will be a powerful combination. However, until then, there are workarounds to leverage true Microsoft BI functionality.

10^th, A strong integration with functional knowledge

One thing that I do like about Dynamics AX BI is that it is so intertwined with the techno-functional practitioner. Having a good functional command will really help on a lot of fronts when it comes to summarizing information, cubes, and reporting. I like that part because it makes it very easy to tie business functionality to BI.