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.

DimensionDataSource

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.

DeptView

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

DeptQuery

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.

DeptViewMeta

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.

ViewRelationships

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.

DimensionFields

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

DimensionDataSource

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.

 

 

 

 

 

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.