Azure Data Lake(Gen2) integration with Dynamics 365 for Finance and Operations

Azure Data Lake(Gen2) integration with Dynamics 365 for Finance and Operations

Business Intelligence and Reporting has evolved in Dynamics 365 Finance and Operations since it’s launch. Entity store is the default relational database for dynamics 365 for finance and operations containing several aggregate measures and customers rely on it for near real time dashboarding on data from the Production database of D365 F&O. Entity store DB is included in the D365 F&O subscription and Microsoft ships quite a few embedded Power BI content packs that does direct query into the entity store to produce stunning dashboards and reports across various modules.

It is also possible to make the entity store available in Azure Data Lake (Gen2). This gives flexibility for customers to have the aggregate measures of entity store directly in their Azure data lake and allows them to do reporting and dashboarding by mashing up data from external sources also.

Another popular option used by many customers is BYOD. Customers can export out of the standard data entities and custom data entities and export them in a predefined schedule to their own Azure SQL DB and then use Power BI or other tools to create reports and dashboards and mash-up the data with external systems data as well if needed. While BYOD offers a good solution for reporting and dashboarding by mashing up data from systems other than just D365, F&O, it comes with it’s own limitations and often requires you to manage and maintain the recurring data exports. The Azure SQL storage is also comes at a cost, depending on what volume of data you want to export and store.

With the version 10.0.12 launch, Microsoft is now making it possible to replicate you D365 F&O production data into Azure Data Lake (Gen2) storage. This new feature and framework allows you chose the data tables and entities you want to export and will keep the F&O data up to date in Azure Data Lake in almost real time.

After you setup your Azure data lake storage account, application ID and authorization for D365 F&O to access the storage account you will first need to install the add-in within Lifecycle services(LCS). Then you can enable the new feature within the feature management area of D365 F&O and perform the remainder of the setup.

Why you should plan the transition to Azure Data Lake: In my view, customers who are invested in BYOD or the analytical workspaces of D365 F&O entity store today should look at taking advantage of Azure Data Lake and should plan the transition for the following reasons.

  1. BYOD requires continuous monitoring and troubleshooting. ADL maintains the data automatically and is always up to date with no intervention needed.
  2. You don’t need to create data entities to expose data in any table that you want. You can literally expose all data tables and fields with ADL integration.
  3. Azure SQL data storage is comparatively expensive than ADL.
  4. ADL cloud storage is more efficient, better for analytics and provides additional capabilities such as AI, and additional programming to transform large volume of data.
  5. Data lakes in Azure are designed for big data and analytics and are capable of handeling big amount of data with less cost. It takes advantage of Azure blob storage behind the scenes.
  6. Data lakes not only allows you to do analytics on the data using Power BI, but also it allows you do additional things like apply machine learning, AI on the data to learn and take meaning and action out of your big data.

That’s it for today’s post.

Additional “Settlement” type transactions on Customer and Vendor transactions page in D365 Finance – Why do you see these and how to fix it?

Additional “Settlement” type transactions on Customer and Vendor transactions page in D365 Finance – Why do you see these and how to fix it?

Here is a quick tip for you all if you ever see additional transaction entries on the Customer or Vendor transaction page with Transaction type = Settlement offsetting each other, when you settle and post a customer payment for an invoice. These additional entries looks something like below.

Let us understand why the system creates these additional transactions.

I have seen in some cases, customers tend to set “Fixed value” for one or more of their financial dimensions, for the Trade AR or AP ledger accounts. They do this to zero-fill a dimension that is not required or in some cases they set it to a fixed value intentionally, for example if Legal entity is a financial dimension, they tend to set this to have a fixed value. Screenshot below shows an example.

With this configuration, when you post the customer invoice transaction, the Legal entity financial dimension uses the Fixed value “USSI” on the AR line of the invoice voucher. See screenshot below.

When you go to settle and post the customer payment for this invoice, here is what the system will do. Since you are doing the cash application(Payment recording) and invoice settlement at the same time, the payment’s accounting entry is not created until the Settlement process is complete behind the scenes. Since the settlement happens first, it basically reconstructs the accounting entry of the payment. It finds the AR main account from the customer posting profile and dimensions are derived from the customer transaction (Payment journal). Since Legal entity is setup as Fixed value dimension and there is no source of this dimension when recording the payment, the system basically determines to create adjusting entries (Debit and credit the same account), since the invoice was posted with fixed legal entity dimension, but the reconstructed payment entry did not have any dimension due to fixed dimension setup(This part is a little technical :)) The settlement process creates this additional entry with the fixed dimension and does not roll back the accounting. This is a standard D365 F behavior.

So, how do you setup the system so that it does not create those 2 additional settlement type entries? There 3 options.

  1. You can setup the dimension which is setup for Fixed value on the Customer record. In many cases this may not be well suited.
  2. You can setup the financial dimension on the payment journal configuration itself. This is the option I prefer.

As a 3rd option, you can chose to enter the financial dimension manually on the payment journal line. Not very ideal option!

With the option 2 setup above, if I post a customer payment with settlement of an invoice now, system will NOT create those additional entries on the customer transaction. See screenshot below.

That’s it about this quick tip. Till next time!!!!