Introduction

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:

• How to deploy SQL Server Analysis Services, which enables access to the built-in BI cubes
• How to process cubes in SQL
• Using the Role Center for the Warehouse Manager
• Using Excel 2013 to access cubes in a PivotTable
• Using the PivotTable to see the percentage of on-time shipments.

Prerequisites

The following must be installed:

• Microsoft Dynamics AX 2012 R3
• Microsoft SQL Server Management Studio
• Microsoft Excel 2013

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.

Deploy a cube in Dynamics AX 2012 R3

1. Click File > Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard.

Figure 1. SQL Server Analysis Services

1. Click Next.
2. Select Deploy, and click Next.
3. Select Dynamics AX in the drop-down menu
   
4. Click Next to start the deployment.
   

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.

Process a cube In SQL Server Management Studio

1. Start SQL Server Management Studio. It will ask you to connect to your Database Engine.
2. In the Object Explorer, click Databases > Dynamics AX initial > Cubes. From here you can either process all cubes by right-clicking Cubes and selecting Process, or you can select and process a specific cube in the list. The latter is more convenient when you only need to update a specific cube which has had transactional changes. Processing the cube is necessary at the interval the user wants to update the data in the cube. It could be daily, weekly, and monthly, depending on the measurements needed. In our case we are using the Sales cube.

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.

View a Role Center in Dynamics AX 2012 R3

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.

Create your own KPI’s in Excel 2013

The data that is displayed in a role center can also be exported and viewed in Excel 2013.

1. Click Data > Get External Data > From Other Sources, and select From Analysis Services in the drop-down menu.
2. Enter your server name and click Next.
   
3. Select the database that you want to access, which in this case is Dynamics AX initial. You can then either connect to the complete database or connect to a specific cube or table. In this case, we want to connect to the Sales cube.
   
4. Click Finish, and then specify how to import the data. Select PivotTable Report, and click OK. You’ve now linked a PivotTable to the Sales cube, and can slice and dice the data in the cube however you choose.

On-time shipments

To provide an example, let’s look at one way to manipulate the information in the PivotTable to analyze measures for on-time shipments.

1. In the PivotTable Field List drag the following fields to the corresponding area:
2. Drag Sales order lines > Order type to Report Filter.
3. Drag Requested ship date on 1^st packing slip > Requested ship date on 1^st packing slip. Year – Month – Date to Column Labels.
4. Drag Sales order lines > Precision of shipping based on requested date on 1^st packing slip to Row Labels.
5. Drag ∑ Sales order lines > Sales order lines Count to Values. Your PivotTable Field List should be as shown in Figure 8.

Figure 8. PivotTable Field List

1. In the PivotTable as seen in Figure 9, select Order type to be Sales order.

Figure 9. Select order type

1. Right-click on the values in the table and select Show Values as > % of Column Total.

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.

Conclusion

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.

Other interesting warehouse KPI’s

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:

1. On-time shipments
2. Internal order cycle time
3. Total order cycle time
4. Dock-to-stock cycle time
5. Order picking accuracy (Percent by order)
6. Lines picked and shipped per hour
7. Percent of supplier orders received damage free
8. Average warehouse capacity used
9. Peak warehouse capacity used
10. Lines received and put away per hour
11. Backorders as a percent of total orders
12. Percent of supplier orders received with correct documentation

Call for feedback!

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.