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!!

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.VSReportStart

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…

Open 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.

OLAP report

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.MDX

On the dataset of the report paste the MDX

SRS MDXNow 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.

Metadata

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.

AXMenu

MDX report

Now you are ready for something a little more complicated!