Best practice/ideas for complex what-if analysis

[mhassinger]

OK, here's the scenario that I have been challenged with.

Business users in a purchasing department need to see how inventory, broken down through bill of material, is allocatted to customer orders. Essentially you have limited resources comprised of a combination of on-hand inventory and items that are on outstanding purchase or work orders (total available inventory). Based on customer order priority (driven primarily by promise date but potentially other factors as well), you can see how far down the list of orders you can fulfill without getting more inventory, which drives smart purchasing decisions for the department.

Right now there is a nightly job that performs the allocation (called "pegging"). QlikView will either utilize this or duplicate the logic in the load script, depending on which makes more sense after some analysis of the job.

So far, nothing too bad.

Now, what I've been asked is whether we could allow the users to re-arrange order priority and see how that would then affect things. For example, I see the #1 order is missing several components that will take a week to get, so maybe I should move it down the list and tell the customer it will be late, freeing up the other allocated components to fulfill other orders that may need them and making other customers happy (at this point the application is not just for purchasing, but for everyone in sales and operation planning).

I haven't done much of anything yet with QlikView writing data, and I know that this is neither QlikView's strong suit nor primary purpose in life. However, from what I understand you can embed other applications, like say a custom .NET app that could allow for this re-ordering. But then it would require a re-load of the data and other potential issues. I can't think of a way for this to work with typical what-if analysis using variables in QlikView.

So what I'm asking is: has anyone ecountered a similar request and how was it handled? At a high level, does anyone have any ideas as to the best direction to take this in?

[johnw]

We have a very similar process, but I suppose that is to be expected in any manufacturing environment.  In our case, we "commit" material (raw inventory or purchased) in bulk to our orders, and "tag" material (raw inventory or shipped from supplier) by ID to our orders.  Once the order actually starts manufacturing, we "apply" the material (in process inventory) to the order, and that's as firm as it gets.

For products that are closer to "made to order", I believe this is handled manually, as moving material from one order to another can only be done if the product happens to be the same, or more commonly is close enough that we can fix it during the remainder of the manufacturing process. 

For products that are closer to "order from stock", a nightly process in the business system reallocates all of the commits, tags and applied material.  This is, as in your case, primarily done by promise date, but I believe there are other factors involved as well.  We also have real time programs that, for a given order, will pretend they are running the nightly process, and tell you what the results for that specific order would be.  However, the only way to see something like "what if I moved the promise date out by a week" is to actually move the promise date out by a week.  That's the extent of our what-if capability, and it's in the business system itself, not in QlikView.

I've not started work on it yet as I'm still working/waiting on some changes in the underlying business system, but our users have been patiently waiting for a very long time to get this information in QlikView.  Well, one aspect of it anyway, which is promise performance - how well are we meeting our promises? 

As currently-envisioned, that will only report on our past promise performance.  But how much better a system would it be if you COULD do what-if analysis to predict FUTURE promise performance based on proposed changes?

So I'm with you, and I want to see what people have to say, but I don't have the answers myself.  All I can do is try to think it through a bit.

Even if QlikView could easily let you enter the new order priorities (and I expect even this part will be problematic), I'd hate to have to replicate the algorithm from the underlying business system.  We already have two copies of it - one in the nightly process, and one in the online process.  These are eventually intended to be merged, with the nightly process replaced by reallocating all of the commits, tags and applications in real time.  That would get us back to one copy of the logic.  But I would hate to be stuck with three copies for now and two copies forever, one in the business system, one in QlikView.  I don't think there's really any way for QlikView to, in real time, send the business system itself a what-if scenario and then report the results.  I suppose technically there is, in terms of using macros to write directly to business system tables, trigger stored procedures that process the what-if, and then dynamically write back to QlikView.  But I've not done any of that before, and can only imagine what a headache it would be.  Duplicating the logic might ultimately be a simpler solution.

Again, I've not done what-if, so I could be way off base here.  But I figure you'd have input fields for the promise date and other important factors.  Then you'd have some very complicated chart logic duplicating the nightly reallocation.  Ugh.  I'm just not seeing how to do it in a practical way.

So I'm eagerly awaiting responses!