Dynamics AX BI – Everything related to Dynamics AX BI

Saving OLAP configuration changes from BIDS back to AX 2012

What’s the development story here? You add measures and attributes, new tables and views to the AOT perspectives and run the wizard to update OLAP, move your AOT project (xpo) to Test and deploy to update OLAP. This is great and works well until you need to do more… Then you will need to make changes in BIDS and save your changes back to AX.

During the normal updating of OLAP using the wizard you might receive a message “The system encountered problems when updating the project. If you changed default options in the Confirm changes page, the system might not be able to successfully build and deploy the Analysis Services project”

Or you may want to update KPI’s and add/change calculations, these need to be done in BIDS.

How do you make this change and then save the definitions back to AX? This does not happen automatically and it not the same as working with SRS reports. The process you follow is dependant on the environment topology.

First the unlikely topology but this does allow a certain automation to the update – AOS instance, SAS instance, AX Client and BIDS on a single machine. Right click the SAS project from the AOT and select edit. After making a change to OLAP you can select build and the project definitions are saved back to AX. This is ready to deploy via the AX wizard or move to your Test environment.

Select Build from the menu bar > Build Dynamics AX

Save DAX project to AX

Now the real world where the SAS instance, AOS instance, and AX client are on separate servers, here you need to adopt a more manual approach but its simple when you know how.

When you select edit on the SAS project in the AOT have you ever taken notice of the infolog message? Here is an example and this is exactly what you need to do to move your changes back into AX. Notice the location of the SAS project in your environment.

To import changes from BIDS:

1. Open AOT > Visual Studio projects > Analysis services project > Right click and select Import

Select the project file in the location that was indicated in the infolog: “\Users\*******\AppData\Local\Temp\16\Dynamics AX”.

Once imported run the SAS deployment wizard and select deploy. The AOT project has everything you need so via an XPO you can import the project from DEV into TEST and just select deploy.

Please note: The configuration and update steps must be run at least once before doing anything to OLAP. Always Deploy, Configure and Update the standard cubes before making any changes.

Good luck!!

Parameterise the AX 2012 Financial dimensions in an SRS report

Have you tried to build an SRS report on the transactional database, then queried the financial dimensions and displayed them in the report – yes, OK what about offering the report consumer the option of selecting a financial dimension as a multi value parameter?

Displaying the dimensions in the report is not easy but there are many resources that give lots of code examples. If the report is easy than a standard AOT query will give you all you need with Dimension display methods.

The hard part is accepting a parameter value from the report consumer and passing that into your query. Here I will show you how. Remember LedgerTrans and how easy it was to work with dimensions well read on.

I will not create the actual report here but I will create the data source that you can use in a query or data provider. First what is the problem. If you said Microsoft referential integrity gone mad, I hear you, but no. The problem is that you just can’t create a query joining the dimension metadata and then add a range to that structure. Or can you?

We are going to create a data source that displays the individual values for Main account category, Accounting Date, Business Unit, Cost center, Department and AmountMST. From this data source you can use all the technics for queries, ranges, multi-value parameters etc. This also offers excellent performance as the results are aggregated and queried on SQL before we get them.

Here is an example of the data in the structure we will create.

The AOT View data structure looks like this

(I have added optional ranges for AccountingDate = (YearRange(0,0)) and AccountCategoryRef = 33)

So this is the end result, closer examination shows the use of 3 further views

AdGeneralJournalViewDept
AdGeneralJournalViewCost
AdGeneralJournalViewBusUnit

The AdGeneralJournalViewDept is an AOT View with 2 fields, Department and LedgerDimension. The other 2 Views have CostCenter and LedgerDimension and BusinessUnit and LedgerDimension.

I have created 3 queries one for each View, lets look closely at the Department query.

BackingEntityType Range has a value of 11765 for the department values only.

This data structure is replicated for cost center and business unit queries. The BackingEntityType range value is the only object that changes, for Business Unit 11763 and for cost center 11764. You get these unique values from the DimensionAttribute table.

Once the 3 queries are created add them to 3 new AOT Views. We only need the DisplayValue and LedgerDimension fields from the Query. Change the View field name of the DisplayValue field in each View, ie Department, CostCenter, BusinessUnit. This is the View for the department values.

Now you are ready to build the main AOT View. Here are the relationships you need to set from your new dimension Views back to the GeneralJournalAccountEntry table. Now all joins in a View are InnerJoins only the queries can handle the outerjoins so set this type of join in the queries if needed.

Add the fields you need, add the aggregations and ranges in the View. I have aggregated the Accounting Currency Amount and added 2 ranges on Accounting Date and Main account category for added performance.

Now you have a data source that can easily be used to present and query financial dimensions in AX 2012. Welcome back LedgerTrans!

There are many reasons why you might want to do this but there are also many tools that already work well with the dimensions. After updating the standard AX Cubes you have all dimensions easily available, Management reporter handles dimensions and Atlas as well. Make sure you use the right tool.

oData permissions in Dynamics AX 2012

oData is a term we have come to use a lot with D365FO but it has made me look backwards at AX 2012 recently to help a customer with PowerBI. There are many posts about consuming oData in AX 2012 but not 1 that I could find about the user permissions required to access the oData url.

The oData references are stored in a table called DocuDataSource and if you do not have high level permissions such as the Sys admin role or IT manager role then you are unable to consume these oData feeds in PowerBI or Excel.

So we need to add the right privilege to a role to open user access to the oData url, you can choose which Role to update I have choosen System user.

Navigate to the Role you want to update and select add, you now need to find the Maintain document handling privilege.

Use the process cycles path

Information technology cycle
Select Maintain system settings
Then from here select Maintain document handling.

This will allow the users access to the oData table only, individual table access still applies.

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.

Build an AX 2012 SRS report based on cube dimensions and measures

They are lots of really good resources dedicated to building SRS reports for AX 2012 using data providers and queries for transaction reporting. I searched and searched and was never able to find any resource that could demonstrate to me how to do the analytical reporting based on the OLAP database.

This is not for newbies even though what I’ll show you is easy, if you are new to Dynamics and reporting I would highly recommend the series of videos by Saveen Reddy – this is how I started 4 years ago.

The MDX language is an art all of its own and being able to write these queries was always a barrier to producing OLAP reports. What I didn’t know is that you do not need to know anything about MDX! But you do need to be able to work with the SQL SAS instance and Visual Studio. I will demonstrate all the steps to build your first SRS report using an MDX query.

Prerequisites

Fully processed and updated OLAP database, steps to do this are here.

So what report shall we build? Something very simple – Customer invoice amount per year. I’ll ignore the Company Id but you will need to parameterise it later, what we want is our first MDX report.

Open Visual Studio and create a project and report framework as you have done many times before.

Add a dataset and give it a good name.

Set the Data Source property to DynamicsAXOLAP. Now we need an MDX query. I will show you how to get this in Visual Studio but you can also do it on the OLAP database as well.

Open a new instance of Visual Studio and select File > Open > Analysis Services Database…

Browse to the SAS Instance and wait for it to open. Our report will be on the Sales cube so lets open that cube. On the browser tab drag the measure and dimension you want. This is the result for me.

We will use the MDX statement created here and embed that into our SRS report. Switch the designer to show the MDX code and copy it.

On the dataset of the report paste the MDX

Now we can create the report using what ever style you like. I will build a Bar Chart. Change the Default Layout on the Dataset from Table to BarChart and drag it onto the Designs node to make your AutoReport.

Set the Layout Template to “RoleCenterReportLayout” and change the AutoReport name to BarChart.

Set the Style template to BarChartStyleTemplate and change the chart sizing to suit.

Change the display labels as the MDX default is not a good look and you should have something like this.

Once you are happy with the labels formatting titles etc deploy the report. Create a menu item in the AOT and open it to run the report.

Now you are ready for something a little more complicated!

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!

Production Route Card SSRS report

In the Production model is a report to print the Route card, when selected from the Reports area there is no problem but when selected from the Production order it fails. It fails because the report controller is not looking for the Production order origin.

To replicate the bug select a production order and on the ribbon bar in the Process area select Release. This opens another dialog with 3 print options Print job card, Print route job and Print route card. Select the Route card option and click OK.

In the ProdRouteCardController class the setRange method is a missing block of code in the case statement.

Add the following

//Start Microsoft bug in AX 2012 R3 and below. Code to be removed when hotfix provided

case tablenum(ProdParmRelease):

prodParmRelease = this.parmArgs().record();

prodId = prodParmRelease.ProdId;

break;

//End

Label files and SRS static reports

Labels not showing on your AX documents? Or my label works in the AX form but not in the report.

When working with multiple AOS’s it is important that your team maintain a good structure with new labels across multiple languages. If someone forgets just 1 label this can have a knock on effect and the person correcting it will not be too happy.

Firstly always try to use the system labels!

In AX 2012 exporting and importing the ald files is still the easier method to maintain consistency across all AOS’s. You could also select to export ‘only labels’ in the xpo but this could still lead to inconsistencies. Also via your model but for a small customizations or a new report then moving ald’s is easy and quick. Making it easy means better adoption.

Export:

1. In the AOT find the labels node > then the modified label file and the modified language.

2. Select ‘Export to Label file’ which will create your ald file “axTSTen-au.ald”

Import:

1. In the AOT right click the Label Files node and select ‘Create from File’

2. Browse to the label file, you will be asked to overwrite.

Easy! But this is a BI post so what about those static SRS documents, Sales invoice, Sales confirmation etc. These are pre-rendered with translated labels.

You have successfully updated a label in AX 2012 but the change was not actioned in the SRS report. Restarting the AX client, AOS restart, made no difference and the report was definitely using this label, the translation was working perfectly on an AX form, so what is going on?

Basically the report holds onto the old label and restarting the AOS, report servers or client, clearing caches will not make any difference, the only way to get the correct label to be displayed is to redeploy the report, but this will not always work unless you follow the correct procedure!!

The following procedure should be followed after a label change whether you are updating an existing label OR adding a new translation language.

Make the change in the label editor as normal or import the language as above.
Close the AX client, this is because the new label is not ‘released’ until the client is closed.
Restart the AOS
Restart the client in development mode.

Find the SSRS report and deploy it. Job done.

Multi-company installations can lead to failures in the cube based reports

During a staged AX 2012 deployment on a multi-national, multi-company it is normal to create a new AX company. But did you know that some missing setup in that new company could mean that the Live companies loose their reports. The MDX queries within the reports require that certain Ledger parameters are set and if they are missing then the report shows “No data available”.

Any MDX query that uses the “Accounting Currency” setting in the Ledger, I think this is most reports, will fail if this setting is missing. I also mean the DAT company.

The new company must have the Chart of Accounts, the Accounting currency AND exchange rates setup for that currency. The MDX queries are looking directly at exchange rates and currencies in the reports and if they do not exist the query does not cope with it.

The lesson here is be sure you are ready to complete the setup in the Live environment.