Measure groups – Dynamics AX BI

Adding new date attributes to the AX 2012 cubes

In the sales and marketing cube I would like a date field related to the quotations created, currently a date attribute exists for the quotation lines which is not what I need. Here you will need to update the View to add a date field and then expose this in the cube. Below are the settings and steps you need to take, it was not as straight forward as I first thought. I’m using the example on quotations but you can apply this to any cube.

The View is the SmmQuotationView here you need to add a new date to the field list, I have used the CreatedDateTime of the SalesQuotationTable, no steps for this just do it!

Once added you can configure the date attribute in the perspective

Step 1 IMPORTANT, you need to change the analysis type of the View to Transaction.

Step 2 configure the date to be an attribute as follows

Step 3 Update the cube structure using the SQL Server Analysis Services project wizard.

What you can expect to see is a separate date attribute for the Quotation created date that is linked to the date heirarchies

Customer invoice line amount measure multiple records

The current release of AX 2012 R3 has a scenario where the Sales cube measure ‘ Customer invoice line amount – account currency’ displays the incorrect amount. Compare this to the Sales cube measure ‘Customer invoice amount – accounting currency’ and where the balance should be the same they are different.

The issue is in the relationships between the CustInvoiceTrans and CustInvoiceJour, it is possible that there is no unique relationship between these tables if the same invoice number is used when posting journals.

Take this scenario

1. User posts a customer payment with the incorrect amount using invoice number ’02’

2. User reverses payment and uses the same invoice number ’02’

3. User enters the correct amount and uses the same invoice number ’02’

When opening the AOT View CustInvoiceTransExpanded in AX you can see the multiple records.

This View is used in the SalesCube and therefore the incorrect balance is displayed.

I have not been able to solve this as there is no unique join that can be used but as a work around when creating your own reports is to use a different measure in the Sales cube ‘Customer invoice amount – accounting currency’ this gives the correct balance.

I’ll update this post if Microsoft offer a solution.

Connect an existing Financial Dimension to a measure group

Working with the AX 2012 OLAP is a much easier task then previous releases, there are a lot of moving parts but it can be quite simple. I will be demonstrating how to configure the cubes by adding an existing financial dimension to an existing measure group. In the Sales Cube there is a new measure group for AX 2012 R3 – Customer Invoice Lines – this measure group has no connection to the Financial Dimensions. So lets hook it up.

If you have trouble deploying and processing the standard cubes to get the dimensions in then follow my previous post on this topic.

First lets see what happens when we try to browse and this is typical when a dimension is not hooked up to the measure group.

If we import the OLAP database into Visual Studio you will see that this measure group has no financial dimension and any attempt to add it will fail.

All the work is done in an AX development environment and moved via the model store into Live as per any other change, once in Live you just need to deploy it. Now find which table or view is creating this measure group, in my example it is the SalesCube and the CustInvoiceTransExpanded View.

Once found open the AOT Perspectives, I have picked the LineAmountMST to show you the reference to the measure I used previously, Customer invoice line amount – accounting currency, from the properties you can see this is a measure.

You will see from the fields selected that the DefaultDimension field is not included, without this field no Financial dimension analysis is available. Go to the View and add the DefaultDimension field from the query, you do not need to modify the query as this field is already there. Take the defaultDimension field from the transaction lines table and not the header table. The customer invoices measure group already has the dimensions hooked up, we are looking at the invoice lines for our analysis.

Restore the SalesCube perspective and the DefaultDimension appears in the field list. Now open the Analysis services wizard and select the update option, just click next all the way through, pause at the Confirm changes option so you can see the magic.

Process the OLAP in Visual Studio or SQL and then browse the Sales cube to see the result!