It’s that time of month again, time for Adam Machanic’s (Twitter | Blog) T-SQL Tuesday blog party. This month the party is hosted by Jes Borland (blog | @grrl_geek) and the topic is Aggregation.
And I’m late.
I planned on doing this yesterday, I really did, but I got busy and had to let it slide. A quick story, just to participate in the party.
The Ordering Report
Years ago I worked for a company that imported products from overseas. We imported wood products, and it was an interesting business. Mostly because the products we imported were not uniform. A sheet of 4’ x 8’ plywood, made of an Oak that is imported from South America, might not be the same as one imported from Africa. Even if it’s the same species, we had many customers that would only want one or the other.
We even had customers that would want wood from one mill, but not another. Add to that the fact that your inventory is often sold in advance, while it’s being milled or shipped (literally on ships) from overseas. That meant that there were times when our any supply chain issues would cause our inventory to vary dramatically, with “available” dates sliding all over the place.
Needless to say, it was a complicated process, but my boss wanted an ordering report to help the supply department decide how much of each product to order. It was a somewhat important process since the trees sometimes were not cut until a purchase order was sent from the US.
I built the report, which used a rolling average of usage from the previous 3 months, along with factors from the previous few years to account for the seasonal nature of our customers and aggregated SUMs and AVGs across our product line. The report was deployed, and sure enough, I had my boss and the lead supply manager in my office complaining the next day.
Apparently my report was “broken” since they both had run the same report, same parameters, and had gotten different numbers. I spent days checking the aggregations, sure that somewhere I had a calculation error that would come up with certain parameters. Eventually I realized that the constant inventory shifts were actually affecting the reporting. A simple explanation, but not a good reason for my boss.
My solution? I decided that the daily inventory shifts, which were sometimes reversed by salesman just as quickly as they were made, weren’t helping matters. Instead I built a job that ran every night and pre-populated a table with all the aggregations needed for all products. Essentially simulating a simple cube, with limited measures for aggregation. The report ran against this table, and was the same for each day, all throughout the day, for all people that used it.
After a few months of using the report, and comparing it’s recommendations to the previous guesses made by the supply department, they decided that it was good enough for them and the daily aggregation was close enough for them to use.
Two added benefits to my overnight aggregation? First, the report would run quicker since all the aggregations were done overnight and not on the fly. Second? We could allow the people in the department to add an “adjustment” to the recommended order, raising or lowering the amount, and then store that value. Essentially making an interactive report that everyone in the department could see.
