11 Replies Latest reply on Dec 4, 2012 9:15 PM by Matt Sevin-Oracle

    Calculating least cost of a product across stores using OPA


      I have been working on POC which needs to return set of store names and least cost for a list of products available in different stores. I m having the following challenges in development.

      1. How to return multiple store names in OPA since an attribute holds a single value.
      2. Calculating least cost according to the availability in all the stores. How to declaratively do this?
      3. How to design the entity model in OPA.

      An example of the problem is, an order is placed for products A, B and C with the quantities 1,2,2 of each unit. Assume there are P, Q, R, S and T stores which offer the products A, B and C. Each store maintains its own inventory/stock of each product. The challenge is to fulfill the order requests according to the least cost offerred by a store with the available units of the product's inventory.

      It would be very helpful if any reply is posted early.
        • 1. Re: Calculating least cost of a product across stores using OPA
          Ben Rogers

          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)

          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.

          1 person found this helpful
          • 2. Re: Calculating least cost of a product across stores using OPA
            Matt Sevin-Oracle
            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.
            1 person found this helpful
            • 3. Re: Calculating least cost of a product across stores using OPA
              Thanks a lot Ben for the post. I will definitely get back to you.
              • 4. Re: Calculating least cost of a product across stores using OPA
                Hi, I'm have some issues figuring out inferred relationships.

                In the messages above, when you say:

                Create an inferred relationship between the prices and the product (linked by product type) - "the product's prices"

                How do I do that? Any specific rules or code here would be greatly appreciated!
                • 5. Re: Calculating least cost of a product across stores using OPA
                  Matt Sevin-Oracle
                  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.
                  1 person found this helpful
                  • 6. Re: Calculating least cost of a product across stores using OPA
                    That makes better sense. Thanks for the quick response Matt! Following this, I'm trying this:

                    the store is a member of the line item's stores if
                    ExistsScope(all instances of the inventory record for the store)
                    the inventory record’s product name = the line item’s product name

                    I'm using a slightly different and simpler model right now ("the line item" and "the store" at global, the store contain's "the inventory record" entities). the line item's stores is a Many to Many relationship on the line item to the store. I'm probably doing something wrong here but when I try to compile this rule, I get this error:

                    Ambiguous entity for 'the store is a member of the line item's stores' (the store, the line item) (OPA-E00344).
                    • 7. Re: Calculating least cost of a product across stores using OPA
                      Matt Sevin-Oracle
                      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.
                      1 person found this helpful
                      • 8. Re: Calculating least cost of a product across stores using OPA
                        Thanks Matt! It seems like formatting/indenting was my problem although everything looked like it should be correct. I was randomly messing with some of the styles and suddenly it compiled without any changes so I'll keep an eye on that one.

                        One additional problem I ran into was with the example above, where the rules try to identify the store with the cheapest price (using InstanceValueIf). When only one store has the cheapest price, this works fine but when there are multiple stores with the cheapest price, it returns Uncertain as that is the behavior defined by InstanceValueIf. I can't seem to find a way to just pick any one of these stores. Any advice on that problem?
                        • 9. Re: Calculating least cost of a product across stores using OPA
                          Matt Sevin-Oracle
                          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.
                          • 10. Re: Calculating least cost of a product across stores using OPA
                            I finally got it working with the numeric ID attribute. Thanks so much for all your help Matt!
                            • 11. Re: Calculating least cost of a product across stores using OPA
                              Matt Sevin-Oracle
                              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