This case study describes the implementation of an innovative methodology for the calculation of inventory value, integrated with warehouse analysis made with Analysis Services.

 

The customer, a steel manufacturing company, has a Dynamics NAV (formerly known as Navision) customized solution and implemented a complete data analysis system based on Analysis Services to analyze Sales, Production and Purchases. A new requirement was the analysis of the inventory over time, both in terms of quantity and value. An important part of the issue was that steel price changes daily in the commodities market, and this has strong implications over certain calculations of the inventory value. End users of the analysis system were used to query cubes using Excel, thus another requirement was to create an Inventory cube in Analysis Services.
Usually, this type of analysis is made using inventory data generated by the ERP system. However, in this case there were two issues which prevented such approach:

• Navision did not store historical data of inventory values at the level of detail the user needed.Â
• Navision did not consider all of the elements that constitute the cost of the steel products made by the company. This second point was particularly important because, at the time, the production cost of products was manually estimated using complex Excel spreadsheets and by using groups of similar products, just to ease the calculation.

We satisfied user requirements with a powerful system that:

• Calculates the production cost of each single product at each stage of production, giving a precise cost for each transaction of that product (raw material purchase, internal and external manufacturing, general costs, and so on).
• Evaluates and stores the raw material cost using several algorithms (FIFO, LIFO, last purchase price, average and others) so that the end user can change the algorithm in any query with a simple selection of a dimension member.
• Creates snapshots of the inventory (which has hundreds of thousands of registered products, but only a few thousands that are in the warehouse in a single day), with a granularity of weeks and months.
• Provides margin calculation specific to each sale transaction, without using monthly averages like the previous system did.

The main innovation is that we created a model where all the calculation are made at the transaction level, without using weekly or monthly aggregated data that would have required a daily revision of the data calculated in the last few days or a delay in providing margin measurement. Every day the end user has all the inventory and margin data updated until the day before. This single factor has been critical in the 2007–2008 years, because steel price had high volatility that required making critical decisions with updated data. Concurrent companies without a similar tool were not able to understand the impact of price changes over their inventory in real–time, and this has been a critical factor for the success of out client company.
The implementation challenge was how to make the calculation of inventory as part of the nightly ETL process. To support certain algorithms of calculation and simulation, it was needed to reprocess at least the whole current year warehouse movements each night. There were almost a million of transactions each year and for each one there are at least 12 different evaluations generated by the calculation. We considered using stored procedures, SSIS, T–SQL and a combination of them. However, the number of different calculations and checks required the development of a custom application written in C# which uses bulk operations to write results into SQL Server: millions of rows are calculated and written in a few minutes.
The result of the calculation is used to both Sales and Warehouse Inventory cubes. To support the Warehouse Inventory cube with the Standard Edition of SQL Server, we created an efficient solution to provide aggregated data (to Week/Month/Quarter/Year level) by using custom MDX Script leveraging on a dedicated cube model, without the need of the LastNonEmpty aggregation feature that is available only in the Enterprise edition.
Let us conclude with what we can learn from this case study:

• There are scenarios where common practices have to be challenged. Designing and implementing a cost analysis working at the transaction level, instead than at a monthly granularity like most of concurrent companies does, was hard but rewarding when market was shrinking.
• Implementing calculations algorithms in the ETL phase could be done using C# when the complexity of calculation increases, even if the number of rows that have to be read and to be written is in the order of millions.
• Using SQL Server Standard Edition, a custom MDX Script and a well designed model can offer a similar level of performance than using the LastNonEmpty aggregation measure, which is available only on SQL Server Enterprise Edition.