From Microsoft's side, we are happy for the release of Microsoft Dynamics AX 2012 R3 and the new features it delivers in the Warehouse management (WHS) and Transportation management (TMS) modules. We hope you're as excited as we are about this release, and that you enjoy working with some our features such as the Mobile device interface and Outbound processes.
The purpose of this blog post is to show some features that you might not know about. It will specifically show how to view some key performance indicators (KPI) for warehouse management in the role center or in Excel 2013 to analyze data from Microsoft Dynamics AX 2012 R3. There are no cubes or analytics that handle this functionality out-of-the-box, so this should be seen as a light replacement in that topic. We would greatly appreciate feedback on this so please leave a comment.
This blog covers the following:
The following must be installed:
A packing slip must be posted on ship confirm so that the necessary data is available in the cube. This can be done either manually in the Load planning workbench or through scheduled batch jobs, where it could be set up to post packing slips during the night for all orders done throughout the day.
Figure 1. SQL Server Analysis Services
The cube is now deploying. Depending on the amount of data in the system, deployment may take some time to finish creating the link between SQL and AX. The next step is to process the cube in SQL Server Management Studio. This will enable a direct link with the transactional data in AX.
Figure 2. Object Explorer
Figure 3. Processing cube
When the cubes are processed, the data can be accessed either through your role center in AX or you can choose to access it through Excel. If you want more detailed information about the Sales cube, please visit TechNet.
If your organization uses role centers, your role center is displayed when you open Home in the rich client of Microsoft Dynamics AX or from the Enterprise Portal which is a role-specific web page. If you navigate away from your role center in the client, you can return to it at any time by clicking Home on the top link bar. Role Centers provide an overview of information that pertains to a user's job function in the business or organization. This information includes transactional data, alerts, links, and common tasks that are associated with the user's role in the company.
If you want more information about role centers and how to set them up, visit TechNet.
An alternative way to see the role center, if it is not displayed by clicking Home or if you are using a test environment, is to go to System Administration > Common > Users > User profiles. You can select the profile of the role center that you want to view, and click View role center. This will take you to that roles Enterprise Portal. In our case it would be the role center for the Warehouse Manager.
Figure 4. Example report of pie chart in the role center
Figure 5. Example report of Shipped in full and on time (SIFOT) table in the Role Center
In the role center, you can view reports or overviews of different metrics. These can be customized.
Figure 6. Example report of SIFOT in the role center
If you click Manage Indicators the following options are available:
Figure 7. Edit indicator form
There are also other metrics that were shipped as standard with Microsoft Dynamics AX 2012 R2, but have not yet been fully verified in WHS. For example, the R2 release included reports for warehouse space utilization, workload capacity, and purchase order cycle times.
So far, tests have been made in Microsoft Dynamics AX 2012 R3 to see whether on-time shipments (SIFOT) work with the current functionality. We are currently investigating which other metrics could be included and tested, and again we'd like to ask for your feedback about what to investigate.
The data that is displayed in a role center can also be exported and viewed in Excel 2013.
To provide an example, let's look at one way to manipulate the information in the PivotTable to analyze measures for on-time shipments.
Figure 8. PivotTable Field List
Figure 9. Select order type
Figure 10. Show value as percentage of column total
You now have a percentage view of shipments that were sent on, before, or after the requested shipping date. You can change the time dimensions to only look at one month, specific days, and so on.
If you want to preview your results in a graph, one way to do this is to copy the dates and values from Shipped on date and create a line based on that data.
Figure 11. Example of graph
This can be configured in several ways, and Excel 2013 offers a wide array of customizable fields. If you're interested in learning more about Office 2013, visit TechNet.
This post has taken you through some of the features that you can use to analyze performance in a warehouse by using of the existing cubes and the role center. This post also described how you can shape your KPI's yourself in Excel 2013, and provided an example of how to see on-time shipments in Excel.
If you want more general information about analytics in Microsoft Dynamics AX 2012, please visit TechNet. There are opportunities to customize default cubes and create new ones, and modify and create dimension usage, measures, and KPI's in the cubes. This blog post provides a work-around for doing that, yet the option is there for those who wish to pursue it.
WERC has defined several important warehouse KPI's, and we have in this post showed the number 1 on the ranking, on-time shipments. The following is the complete list:
Please feel free to comment on what else you think is important for warehouse management systems. Any feedback is greatly appreciated. Thank you for reading this!
Special thanks to Mirza Abdic' and Hans Kierulff in co-authoring this.
A clear post and one I read through sitting with a very encouraged Warehouse Manager on my current project - another great tool in our armoury!
Glad that you liked it David! Please give feedback if you have any trouble following these steps when trying it out! Thanks
Thanks for all these clues and practice sharing. best.
thank you Markus, this is useful to know
I am receiving a error on opening the excel- this excel document access the pivota data
Error says "Initialization of the data source failed. check the database server---"
when clicked OK then ApivotTable,cube function or filter control using the connection 'Demand forecast database' did not refresh"
Please suggest me on this