Dynamics NAVAX

One advantage with parquet files is that it keeps history. Run the below script and it will give you the history.  %% sql DESCRIBE HISTORY dirpartytable; Below is an example I have in my environment. Use "VERSION AS OF"  Now that you have the history, you can use the VERSION AS OF statement. This will give you the previous values for the record.  %% sql SELECT partynumber, primarycontactphone, primarycontactemail FROM dirpartytable WHERE partynumber = '000001702' ; SELECT partynumber, primarycontactphone, primarycontactemail FROM dirpartytable VERSION AS OF 2 WHERE partynumber = '000001702' ; The below example is when I deleted the primary contact phone number.

This will be a fairly straightforward blog post covering different ways of copying data from a shortcut delta table into a delta table created automatically via a notebook. Select statement with a join %% sql SELECT     party.recid AS PartyId     ,party.name AS Name     ,COALESCE(party.namealias, '' ) AS ShortName     ,COALESCE(postal.countryregionid, '' ) AS Country     ,COALESCE(postal.state, '' ) AS State     ,COALESCE(postal.city, '' ) AS City     ,COALESCE(postal.street, '' ) AS Street     ,COALESCE(postal.zipcode, '' ) AS PostCode     ,COALESCE(phone.locator, '' ) AS PhoneNumber     ,COALESCE(email.locator, '' ) AS Email FROM dirpartytable party LEFT OUTER JOIN logisticspostaladdress postal ON postal. location = party.primaryaddresslocation AND postal.validto > current_date () -- filters only valid(effective) addresses LEFT OUTER JOIN logisticselectronicadd...

I’ve recently been exploring various analytical options within Dynamics 365 Finance and Operations, and one that I’ve delved deeply into is Link to Fabric. There is a walkthrough guide available on the Microsoft Fasttrack Github repo . See  Hands-on Lab: Link to Fabric from Dynamics 365 finance and operations apps This guide is an excellent starting point and should be one of the first things you try out. However, it’s important to understand that there are limitations to this approach that may not be suitable for all real-world scenarios. Lets discuss these items and what I have been exploring ... Background I want to join multiple tables to create my denormalised views that I can report on. My goal is to use  Direct Lake  mode in the semantic model. Specifically, I wanted to avoid the need to reimport data into Power BI for reporting.  Key Limitations The first limitation you’ll encounter is: By design, only tables in the semantic model derived from tables in a Lak...

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