Now, in my continuing mission to ensure that my blog devalues my skills as an AX Technical Architect/Consultant, I’m going to give away more of my precious secrets. If you understand this post, you will have learned a very, very strong tactic for extracting data with SQL – and not just financial data. The other advantage, besides the incredible value to the business, is that we can now use set-based operations to really speed up our AX reports. These will blow away the default reports in Dynamics AX with speed, especially if you create indexes for them or replicate the tables to another database. Let’s continue here.

Step 4: Use your SQL and get the dimensions in a non-flattened out manner, insert that into the Temp Table

Financial data has a fancy word called dimensions. You can think of these as descriptive terms that group your data. Financial data is often described with multiple dimensions. We put rules how that data is described with our Account structure, which explains the order of dimensions and which dimensions appear. Go back above and you will see that main account appears before business unit which appears before Department which appears before Cost Center which appears before item group. We can use all 5 of these terms to describe a transaction or we can use just two of them (Green arrows mean optional). Classical interesting challenge for a report writer because you have the non-uniform string headache to deal with it. First what we have to do is get the values Ordered and sorted. AX provides a value called DimensionAttributeValueGroup which describes all values that go into one unique combination of dimensions. We also need to deduplicate the data and sort it for easier consumption. So, we will use a Row_Number() function here and a group by statement. If you highlight just the query, notice what it yields.

This actually makes a lot of sense. Ordinal defines the place where a value is listed. Display value is the actual value of a dimension. One combination of values used in the system is known as the DimensionattributeValueGroup. Every transaction in the General Ledger will link to one unique DimensionAttributeGroup which contains a set of dimension values and their orders. Isn’t this fun? It really starts to make sense.

Step 5: Use your SQL and get the dimensions in a non-flattened out manner, insert that into the Temp Table

When using SQL in AX, this is one of the most revered tricks that I can find. It concerns the art of flattening data, and you will have to use this in all sorts of places within Dynamics AX, other than just financial data if you want to use SQL. You have 2 challenges. You need to make the dimension data appear on the same rows, they need to also be separate columns for your report filtering, and you have to deal with the fact that some dimensions can be blank in some cases while filled in other cases, so you don’t have a uniform string.. Ouch challenging.. The secret to solving this is use an old-time trick where you ‘fool’ SQL by using the Group by clause but by using the MAX statement. That makes SQL Group each dimension value on the same row as it’s unique DimensionAttributeValueGroup. Thus, DimensionAttributeValueGroup will only appear one time.

Now, we are in Business. You’ve flattened out the data and prevented multiple duplicate rows. You’ve seen the tables that are necessary to join financial data. And you’ve learned a precious SQL method for flattening out data in Dynamics AX that can be used for many more reports. For example, you often have multiple invoices for a Sales Order or Purchase Order. The tables are different but the strategy is the same as you see here for flattening out the data. But we are missing one central component. Our users didn’t ask for a flattened table of the AX financial framework. They wanted financial general ledger data. We’ll give them that in Part 3 of our series.