A client recently asked why their measure for Zero Inventory Counts (stock out counts) was running long, and you’d think getting a list of times you didn’t have inventory would be easy. Except, it depends on how you are figuring that out. For them it is very slow because they are not using a balances table.
The truest way to get an inventory balance is to add up all the transactions of a product for a location for a day (or month). And that is how they are doing this. A cube can be very fast at that. But if you want to count days where the balance added up to 0, well, now you are asking the cube to calculate a balance per product per location (warehouse) per day, assess whether it is 0 or not, and if 0, then add one. That’s pretty time consuming when you want a count by all products by all locations by all days (or at least a year). That’s thousands or millions of combinations much less aggregations.
A different way to do this is to create a balances table in the data warehouse. This table should hold the aggregated balance by product by location by day ahead of time. Will that be a lot of rows? Yes, and potentially you can narrow this down by doing it for a time frame, getting your counts and storing the counts (say at a week level). But now you will just be asking the cube to add up the 1s (or numerics) for each product / location / day. Much much faster.
