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.

smmQuotationView

Step 2 configure the date to be an attribute as follows

Created date attribute

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

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

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.

CustInvLines

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.

MeasureGroup

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.

salescubeLineAmountMST

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.

Confirm changes

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

Corrected report

 

Successfully updating the default AX 2012 Cubes

In recent weeks I have been working with the AX 2012 cubes as part of a new implementation, deploying and processing that all works well but when updating to include the financial dimensions things start to go a little wrong.  Relationships between the table structures and enums  in the Database View are broken and the cubes wont process.  If you are having the same problem and the MSDN don’t help then follow this procedure to successfully process the updated cubes.

We need a starting point so lets start with the SYS layer SAS project

  • From the AOT  navigate to the DynamicsAX SAS project we want to have no customisations so sys layer only – Delete the project to remove any customized layers.

Using the SQL Server Analysis Services project wizard

SQL Server Analysis Services project wizard

  •  Select the Deploy option
  • Accept all the defaults
  • You do not need to select the process option.

Run the Project wizard again

  • Select the Configure option
  • Do not add any translations.  I played a lot with this and even though Dynamics AX translations ARE said to be supported on SQL Standard Edition I could not get it to work.
  • You do not need to process the cubes

Run the Project wizard again

  • Select the Update option – now we want to get the dimensions into the cubes, just add the ones you need
  • Do not add translations
  • Do not process
  • Accept all the defaults

I prefer to process the cubes directly on the SAS instance or in Visual Studio, so open SQL Server management studio and connect to the SAS instance and now process the cubes.  They should all process with no errors.  Test the financial dimensions by connecting from Excel or using the SQL Browser.

If you want to include more dimensions or you have changed the perspectives then you only need to select the update option from the wizard.  If there are AX configuration changes (typically through the Go-Live process) then you will need the Configuration option and then the Update option.