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”

Wizard problems

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.

SAS Location

To import changes from BIDS:

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

Import sas project

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)

View data structure

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.