Calculating least cost of a product across stores using OPA

1. Re: Calculating least cost of a product across stores using OPA

Ben Rogers Sep 27, 2012 5:49 AM (in response to 964796)

Currently Being Moderated

Hi,

It's tricky to think about a data model without knowing the full requirements, but if you are just designing a POC for now then I think you can demonstrate a good working model of this system.

There are many ways to do this I'm sure... but here is my two cents...it's difficult to explain it on here in plain text but I'll give it a shot.

data model:

the product (contained by global. to many.)
--the product type (base, static)
--the product's cheapest price (inferred, possibly temporal)
--the product's cheapest store name (inferred, possibly temporal)

the store (contained by global. to-many.)
--the store name (base, static)

the price (contained by the store, to-many.)
--the price's product (base, static)
--the price's amount (base, static)
--the price's store name (inferred, static)
if you want to make the prices temporal otherwise leave out:
--the price's start date (base, static)
--the price's end date (base, static)


the quantity (contained by the store. to-many.)
-- the quantity's product (base, static)
-- the quantity's amount (base, static)


Finally, it might be convenient to have the following:
the order (contained by global. to-many.)
--the order number (base, static) etc...

the ordered item (contained by the order. to-many.)
--the ordered item quantity (base, static)
--the ordered item product type (base, static)



This should be a good base model and should be flexible enough to demo pretty much everything. It is especially powerful because it allows for prices to be temporal. Store prices go up and down all the time, and this data model could easily handle that. It could give you the cheapest price on a given order date...(if orders were backdated, or scheduled for a future date)...but it may be best to keep the POC simple for now...

Some other ideas based on your requirements...
There are many ways of doing this, but here is one idea:
Create an inferred relationship between the prices and the product (linked by product type) - "the product's prices"

You can work out the product's cheapest price by doing something like:
the product's cheapest price = InstanceMin(the product's prices, the price's amount)

Then
the product's cheapest store name = InstanceValueIf(the product's prices, the price's store name, the price's amount = the product's cheapest price)



using "the order" and "the ordered item" allows you to handle things like quantities of products, and totalling up the entire order etc.
you can also add extra layers around availability... so the cheapest price is only selected IF that store also has the product available.

The main difficulty I can predict with this is around the availability and a potential loop (i.e. the order is only approved if there are products available, and products available = availability minus the approved orders.)...that would need some careful design and consideration and beyond the scope of a forum post!


So, some ideas there.
It's well worth investing a LOT of thought and time into this sort of data model, and think about every possible requirement up-front, because a poorly designed OPA data model can very quickly become restrictive and a real problem. But a good, flexible model can make seemingly complex calculations very simple with a small number of rules.

Hope it helps.
Feel free to contact me further should you need more assistance.

Thanks
Ben

2. Re: Calculating least cost of a product across stores using OPA

MattSevin Sep 27, 2012 10:16 AM (in response to Ben Rogers)

Currently Being Moderated

Going roughly with Ben's data model, I'd suggest starting to draft some of the conclusions you would like to reach. For example:

- the line item should be filled by the store if ...
(e.g. the store has the lowest price for the line item's product and the store's inventory of the product > the ordered amount, etc.) of course you also need to decide whether you want to partially fill a line item from a store and only if it has a lower price than another store or if two or more stores have the same lowest use the store with the highest inventory etc. etc. ... there is much to consider from a policy perspective, so it's best if you know or can obtain the actual fulfillment policies. Other considerations for robustness include whether you are enforcing assumptions with error conditions or not making assumptions within your policies (e.g. can two line items on the order be for the same product or is that considered an error that should be captured and rejected as an order?)

Other suggestions include
- Choose terminology that is explicit an obvious to readers (especially if no fulfillment policies exist and you are defining the terms). For example: the line item's product which is a relationship to a product and distinct from "the product" entity - in other words "the product" is an entity that can be related to a line item and also to "a inventoried item" as in a line item relates a product and a quantity to a specific order and "a inventoried item" relates the product, a quantity in inventory and a price to a specific store. As Ben mentions, there are other ways to model, but making well considered decisions and using clear, well defined terminology will make everything a bit easier.
- Break down the policies into intermediate conclusions as you write the policies rather than trying to use a single final conclusion (although starting with a final conclusion will help to begin drafting many of the conditions) For example: a line item can be filled by an inventoried item if the inventoried item's quantity > ... and the inventoried item has the lowest price and they both reference the same product, etc.; separately determine if the inventoried item "has the lowest price" (boolean attribute) by comparing it to other "inventoried items" for the same product.

Please note the above examples are not using specific rule syntax. I am assuming you are well practiced in using and familiar with the syntax for cross-entity reasoning, deducing inferred relationships, etc.) If not, my best advice is to engage the assistance of an experienced OPA consultant to greatly lower the learning curve and expedite the design and implementation of the rulebase.

5. Re: Calculating least cost of a product across stores using OPA

MattSevin Oct 16, 2012 1:03 PM (in response to 964796)

Currently Being Moderated

Sticking with Ben's model, I would assume the product and the prices of the product would be related through regular (not inferred) relationship. (e.g. the price's product would be a one to one relationship that arrives as incoming data, not inferred by rules).

However, if you are pursuing a rule that infers the relationship, the rule would follow the format

the product is a member of the price's product if
the product's SKU number = the price's SKU number

Note in the above that SKU number would be a numeric attribute of each of the price and product entities. That numeric attribute is used to define when a specific price instance should be related (inferred relationship) to a specific product instance.

7. Re: Calculating least cost of a product across stores using OPA

MattSevin Oct 17, 2012 8:40 AM (in response to 964796)

Currently Being Moderated

The following should work:

the store is a member of the line item's stores if
....for at least one of the store's inventory records
........the inventory record's product name = the line item's product name

The ambiguous entity error makes me think you have something else going on in your policy model or rule example. For example, try defining the phrase "the store's inventory records" rather using the default "a instances of ..." for your relationship. Double check the indent levels in your rule (i.e. each line is conclusion, level 1, then level 2 for the example rule).

If you still have an issue, please create a new project with just the three entities, one containment relationship, one inferred relationship and the single rule to verify the problem isn't caused by something else left over from earlier attempts at the model, phrases, or rules etc. If an issue still persists, please post all the details of the simplified project, version of OPA, and any other details that may be relevant. Thanks.

9. Re: Calculating least cost of a product across stores using OPA

MattSevin Oct 18, 2012 9:43 AM (in response to 964796)

Currently Being Moderated

If you are trying to ship from multiple stores, you will need to infer a strictly ordered list (i.e. one store is most preferred, each subsequent store is the "next more preferred", etc.). A strictly ordered list will require something unique to use as a "tie breaker" when two or more stores have the same price. Typically an ID or some other unique attribute is used.

To directly address your question of picking one store ... the easiest approach is to do something similar since policies/rules are supposed to be deterministic, "random" selection of one store would prove to be problematic in the long run (e.g. predictable test cases, etc.)

Instead of using InstanceValueIf to identify the cheapest price, use InstanceMinimum to get the lowest price regardless of how many stores have that price. To identify a single store, decide which attribute will be used as a tie breaker (e.g. a numeric ID attribute on the price entity would work in your case). You can then use conditions which identify the price to use as the one which has the minimum ID among those with the minimum price.

11. Re: Calculating least cost of a product across stores using OPA

MattSevin Dec 4, 2012 1:15 PM (in response to 964796)

Currently Being Moderated

There is now a tutorial available that covers representing and using preferences and the example uses a very similar scenario as discussed in this thread ... see the thread New Tutorial and example available on OTN - representing preferences for more info and the links