In my previous Power BI blogs, we have discussed in detail the basics which included creating a simple report using an online webpage as the data source as well as exploring the sharing capabilities included in Power BI, but most of the topics were general and were related to the Power BI’s cloud app, so we decided to use the desktop version of Power BI this time in addition to using the most powerful ERP solution i.e. Microsoft Dynamics AX as the data source. For demo purpose, we decided to use the most popular scenario/problem in AX i.e. real-time visualisations/analyzation of OnHand items and its related fields with a touch of Business Intelligence to it.
Power BI Desktop is a desktop version of Power BI used for on-premises creation of Datasets and Reports mostly using data files hosted locally like in our current case, once the reports are created you can easily publish them to the Power BI cloud app and use the app’s features i.e. sharing, dashboards etc. Power BI desktop is available online and can be easily downloaded from there. Let’s now go straight to the point and start making a report for OnHand items in AX.
After you have successfully installed the desktop version of Power BI, open the application and click Get Data.
It will open a dialog with option to select various data sources, we’ll be choosing Microsoft SQL Server as our AX’s database is hosted there.
Once you have selected SQL server as your data source, it will ask for the Database Server and the Database Name from where the data is to be fetched.
It will ask for the credentials to be used in accessing the Database Server, make sure you have the appropriate rights for it.
After selecting the Database Server, Database Name and entering the appropriate credentials, it will show all the tables that are available in Dynamics AX’s Database, in our scenario we’ll only be needing tables related to inventory and to be exact we are going to fetch three tables from AX which are inventDim, inventTable and inventSum.
As we have a multiple data tables scenario, we’ll need to define the relationships between our selected tables, to do that go to the relationship tab and select “Manage Relationships” from the toolbar.
It will open up a dialog where we need to define the columns in our tables that are the criteria for relations among them, in our case the inventSum and inventDim tables were joined on InventDimId whereas inventSum and inventTable were joined on ItemId field so it was pretty straightforward to create the relationships, if your tables have obvious columns for the relations you can also try the auto detect button at the bottom which will detect them automatically.
Once the relations are created, it will preview the ERD for you in the same relations tab. It can easily be seen that inventDim and inventTable tables both have one-to-many relationship with the inventSum table.
After describing and setting proper relations for our tables, lets now format our data according to our needs here, for my scenario I just defined the data types of the unidentified columns so that I can play with them in my report with their appropriate usage, another thing that I did in the formatting phase was removing all the irrelevant columns so that I have a clean set of data that only contains useful information as needed for my scenario.
After the data is formatted and ready to be used let’s move forward and create the Item OnHand report for Microsoft Dynamics AX. First of all, I am using a pie chart visualization to show the physical availability of all the items that are available in my warehouses.
Moving further forward, I am adding some more useful visualisations using the AX’s Onhand information that will help the user analyse the real time status and information for the available items in a very interactive way, I added a stack area chart with Ordered, Received and Posted Quantities as well another visual with comparison between Reserved Physical and Available Physical quantities. We can create a lot more visualisations according to our needs/requirement.
Being a very large ERP solution with multiple Legal Entities, Sites and Locations for an Organisation, a filter will be very helpful in analysing the data by each of the available options, therefore I am adding three filters to my report that will be Location, Legal Entity and Site filters. Also, as Power BI is already that much intelligent/interactive that we don’t have to define any logic behind the filters, you’ll just have to pick your selection and it will automatically filter all the visualisations according to it.
That is pretty much all I have today for the topic “Using MS Dynamics as Power BI data source”, nevertheless, we can create various reports using AX’s data and visualise the live status of the Organisation as well as predicting its future with the ability to define strategies using the historical data to make Organisation more profitable and up to date. There will be more coming your way on Power BI soon, so stay tuned. Also, if you have any queries or want us to focus on a particular feature in our next blogs, please feel free to contact me.