Using Python to plot D365FO data and visualize the trend, by using dataframes

 





I am sure you must have read my earlier post: https://subsd365.blogspot.com/2024/03/using-python-to-fetch-data-from-d365.html, which outlines a way of reading the data from D365FO, using Python. This post will help you just not fetching the data from F&O, but also visualise the data plot against x and y axes and then understanding the trend of your business (eg: you can see which Item is a high demand item, which customer has made the maximum transactions across a given time frame -- even when could a customer potentially could transact again: examples galore).

And uh, ok -- before you proceed you have read the above post first and make sure:

a. You have installed Jupyter notebook on your machine (it's free and absolutely easy to install and operate).

b. You have a valid Microsoft Entra Id configured and given API permission to D365F&O.

c. You have listed the client Id in your D365F&O side Entra Id registration.

Before we begin, let us quickly check: what is a Dataframe in Python:

A Pandas DataFrame is a 2 dimensional data structure, like a 2 dimensional array, or a table with rows and columns. Think of it like a flatfile or 2 X 2 data set structure. 

Ok, all set? Let's begin, shall we?

Step-1: Import necessary module packages:

import requests

import json 

import pandas as pd

import matplotlib.pyplot as plt


The package 'matplotlib.pyplot' is the one which we would be actually needing for plotting the data. The pandas package in Python is a well known package for providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real-world data analysis in Python.

Step-2: Define your variables --

CLIENT_SECRET = '##Your client secret here'

TENANT_ID = '##your Tenant Id here'

tokenendpoint =  "https://login.microsoftonline.com/" + TENANT_ID + "/oauth2/token"

CLIENT_ID = '##your client Id here'

GRANT_TYPE = 'client_credentials'

RESOURCE =  '##your base your URL here'

basUrl = '##your base URL here again, but this time ending witha '/'' 

Step-3: Getting the response from D365FO:

You need to generate the token and can obtain the response JSON payload from D365FO by the way outlined in our previous post: 

test_api_url = f"{basUrl}/data/CustTransactions"

api_call_response = requests.get(test_api_url, headers=api_call_headers)

Evidently we are calling the data-entity CustTransactions and would like to plot customer vs transaction amounts.

Step-4: Loading the data in a dataframe and plotting it

try:

    

    custTrans = api_call_response.json()

    mapTrans = {}

    #loop through it

    for x in custTrans['value']:

        mapTrans[x['OrderAccount']] = x['AmountCur']

    

    custAccounts = list(mapTrans.keys())

    amountCur = list(mapTrans.values())


    #tick_label does the some work as plt.xticks()

    plt.plot(custAccounts, amountCur)

    plt.ylabel('Transaction amounts')

    plt.show()

    

except KeyError:

    #handle any missing key errors

    print('Could not parse D365FO results')

Whoa whoa whoa: so many things have happened in the above code. First I am loading up the response JSON in a JSON object variable: custTrans. Then I am initializing a 'dictionary' element called 'mapTrans' which will work as key-value pair variable containing my customers and their corresponding transactions. This I am doing by simply looping through each transaction of the custTrans and identifying the keys and their corresponding values. 

Once done, I am splitting up the values between keys and values by ascribing them on an x-Axis values, called custAccounts, and a y-axis values called amountCur (which are actually list type variables).

Finally, its simple: I am plotting y-axis values against x-axis values and showing it: plt.show().


You can further check only y-axis values (for example; if you want to find out how are diffrent Items getting sold in the market), simply by:

plt.plot(itemIds)
plt.show()

Where ItemIds is a list that contains the list of the Items sold over differnt transactions. Just try 'InventTransCDSEntities' entity and then store the details in a list variable and plot it:



Comments

Post a Comment

Popular posts from this blog

X++ : mistakes which developers commit the most

Image
   The most important thing is to remember where and when to write the code. a. Developers have an inherent habit of writing erratic select statements: select * from custTable where custTable.AccountNum == <SomeValue> Where in essence we need to make use of optimized choice of fields in the select statement. b. Making use of 'continue' keywords: While select custTrans where <someConditions> { if (cusTTrans.CustGroup != mappingTable.CustGroup) { continue; } } 'Continue' statements are precisely a very unhygienic way of designing your code. The above statement could have been easily ornamented by using exists/not exists joins. Using Continue statements would eventually result in a high performance impact. Also select statements should be made punctuated with a 'firstonly' clause -- just not from best practise standpoint, but also from performance considerations. c. Writing nested loops: This is a common mistake that perha
Read more

Make your menu items visible on main menu, conditionally,, using this cool feature of D365FO

Image
  Wassup guys? I am sure you must have encountered this situation where you needed to display your menuItems, on main menu, based on some condition, haven't you? This was only possible by using config keys and flights. Things get a bit tricky,  when suppose you have a requirement like this: you need to make it dynamic, by introducing a toggle switch on a field on a paremeter form.  Suppose on Accounts Receivables Parameters >> You have a field called ' Show US customer '. You have a requirement that says when you turn on this toggle, then only the menuItem ' Input SSN details ', under AR module. This is now highly acheivable by leveraging subscription to 'SysMenuNavigationObjectFactory' class, simply by saying a. Create a new class USMenuItemsVisibilityManager b. Copy the following method, with proper documentation (don't change signature/anything else) [SubscribesTo(classstr(SysMenuNavigationObjectFactory), staticdelegatestr(SysMenuNavigationObjec
Read more

Speed up your execution performance by using SysGlobalCaches

Image
  Ever come across a situation where you needed to  a. Using a multi-threaded batch to import a journal header and lines, from a staging table. The table already contains the Journal Ids which you need to assign to the header and allocate the lines under that header. b. You need to continuously check after each line being imported, if the journal with the Id has been created. If not, it will create the header and create the lines under it;  If yes, it will directly create the lines for the header (Find or create). c. As you could rightly guess, this architecture has an inherent sluggishness associated with it -- each time you keep on checking if the header has been created or not, it does mean a lot of performance effects, as a consequence. Also as you can understand it's a multithreaded code, which means each thread has no direct relation with the other -- which does mean there is no way I can talk between the threads/pass on any information. SysGlobalCaches to the rescue: I perso
Read more