Dynamics NAVAX

Recently there was a release of the entity store for AX 2012 R3. This is a great tool that makes data warehousing simple and close to real time. Traditionally with a data warehouse, you would have to do full updates. This is fine for overnight process but when it comes to regular updates, it is a bit much to ask. Here are the links to Microsoft blogs of the release announcement: https://blogs.msdn.microsoft.com/dynamicsaxbi/2016/05/02/introducing-entity-store-for-dynamics-ax-2012-r3/ https://blogs.msdn.microsoft.com/intel/archives/185 The concept uses DIXF to do incremental updates to the secondary entity store database. This is done using change tracking which is enabled at the database level. One thing to note is, the entity store database should ideally be on a different server. Allowing you to have SQL 2014 or latest SQL 2016 installed. The entity store uses column store indexing which is super fast. Have a read of the whitepaper in the above links. Below is a diagram of what ...

In my last blog I did a demonstration of using an SQL query to build a PowerBI report for the general ledger transactions. I hard coded the RecId for the financial dimensions. It might be cumbersome to build the query manual. So here I wrote a job you could run to get the query. It will spit out an infolog which you should be able to copy and paste. static void navax_generateGLQuery(Args _args) {     DimensionAttribute  dimensionAttribute;     str                 sqlStatement, dimNameStr, dimMaxStr, name;     sqlStatement =     @'         SELECT         GeneralJournalCube.*         %1         FROM GeneralJournalCube INNER JOIN         (     ...

With the recent release of Power BI desktop it has been an exciting week to play with new toys. I decided to do a quick general ledger cube using Power BI desktop. I put together some SQL scripts to resolve the dimensions. 1. Open up PowerView Desktop and select Get data and select SQL as the source. 2. Paste the following query I mashed up to get the GL transactions with the 3 financial dimensions. The RecIds I have there are based on Contoso database. In my next blog I will try to explain how to get the RecIds. SELECT     GeneralJournalCube.*,     OLAPBUSINESSUNIT,     OLAPCOSTCENTER,     OLAPDEPARTMENT FROM GeneralJournalCube INNER JOIN (     SELECT         DAVC.RecId as CombinationId ,         MAX(CASE WHEN DA.RECID = 22565421195 THEN DAV.ENTITYINSTANCE ELSE NULL END) AS OLAPBUSINESSUNIT ,         ...

On 24th of July Microsoft announced the release of PowerBI . With that comes a lot of new features and I have to say it is a lot simpler to use. In this post I will walk you through what I did to get my first report done in 10 minutes. Download PowerBI Desktop and install it. (No need to show installation steps as it was super simple to follow the wizard). https://powerbi.microsoft.com/desktop Once that is done, I signed up on the website with my work email (we are already on Office365). First thing to do is give it a data source. In my case I chose OData to get AX data. You will get the list of data sets available in AX. I selected one of them and manipulated some of the columns. Usual thing you would do is rename the columns, maybe do filtering or grouping. You may add additional data to link to. Then next thing you do is click on the Load (Close & Load button). Once it is loaded, you can start creating the report. I did a basic pie chart. You then can click on th...

AX 2012 BI is so much easier and enjoyable to use. Seeing how easy it is, I don’t see a reason for not deploying it all clients. To add a financial dimension Click Tools > Business Intelligence (BI) tools > SQL ServerAnalysis Services project wizard. Click Next . Select Update and then click Next . Select an Analysis Services project to add the financial dimension to, and then click Next . After the project builds, click Next again. On the Select perspectives screen, click Next . On the Select Microsoft Dynamics AX dimensions screen, select financial dimensions you want to include in your Analysis Services project. Complete the wizard. Select the perspectives Select the dimensions Select the date diemension/s Select the label translations Then wait for it to finish. This will take some time. Once done. Continue with the wizard.   By the selections we made in the wizard. The “Visual Studio Projects” > “Analysis Services Project” > “Dyna...