6 Replies Latest reply: Jul 24, 2012 4:16 AM by 935013

# Cross entity reasoning in Excel

Hi All,

Is it possible to prove rules across entities in Excel?

E.g.
I have 2 set of entities aspired program and awarded qualification.

There are 2 instances for each entity. Now I need to check each combination if valid. So essentially, I'm looking at 4 possible combinations to test in Excel which will prove a boolean rule. I choose Excel to hold the rule table due to the large number of rows to configure.

How should I approach this scenario?

Thank you and kind regards,
Jerome
• ###### 1. Re: Cross entity reasoning in Excel
Hi Jerome,

It should be possible so long as there are only ever two instances for each of your entities. (I'm assuming there were other reasons why these were created as entities, since if there are only ever two of them, it may be simpler to move them to global or to consolidate them?)

I'll bat some ideas around here and let me know if this is useful!

I'm assuming that you want the "combination is valid" conclusion to be on the aspired program and I'm also assuming aspired program and awarded qualification is a many-to-many relationship.

The first thing to do would be to create some useful mapping attributes on the aspired program entity, such as:

the aspired program's first awarded qualification name = InstanceValueIf(the aspirated program's awarded qualification, the awarded qualification's name, the awarded qualification's id=1)

the aspired program's second awarded qualification name = InstanceValueIf(the aspirated program's awarded qualification, the awarded qualification's name, the awarded qualification's id=2)

then you can have an Excel table with headings:

the aspired program name | the aspired program's first awarded qualification name | the aspired program's second awarded qualification name | the aspired program level is valid (conclusion)

then you can just start a new row for each possible combination such as:

"Physics" | "Maths A Level" | "Physics A Level" | TRUE
"Physics" | "Food Technology" | "Biology" | FALSE
"MasterChef" | "Food Technology" | "Biology" | TRUE

If you wanted the conclusion on global level, looking at combinations of aspired programs and qualifications, then you would need to repeat but for one level higher, so you'd have "the first aspired program name" etc... but if that is the case, then it really makes sense to have a flat structure with everything just as global attributes (again, there may be other reasons why these entities have been created?)

Cross entity reasoning using multiple instance operators like ForAllScope and ExistsScope is not possible in Excel, so you need to find a way to pinpoint a specific instance and use mapping rules as I describe, if you want to use Excel in this way. This solution is handy when you have a large number of combinations to validate.

Hope this is a useful idea! It's worked well for a similar problem I had to solve with an insurance rulebase. Let me know some more info if my assumptions about your requirements are incorrect?

Cheers,
Ben
• ###### 2. Re: Cross entity reasoning in Excel
Hi Ben,

Thank you as always those we're great points I'll try to work out the mapping attributes.

Here's our scenario:

Before I only have *1 entity (the qualification)* holding both the aspired program and the awarded qualification information so it is easy to do an Excel rule table to validate different permutations. However, on the data structure of the source system, we have these two information sitting in two different tables. So what we're doing right now is we traverse the rows to do a union (there is no direct table relationship between the two) of these two tables and sends it across to ODS as different instances for the qualification entity (e.g. two rows each = 4 rows).

Now we're thinking of optimizing the message to ODS instead of doing a union (multiply) we intend to just send the information as different sets which translates to having different entity instances. We also need to test not only 1 instance but one or more so I think we cannot work around the Global entity here (please correct me here).

I already tried separating the entities and got stuck in the scoping problem with Excel since the headings are now coming from two entities. If I'm not mistaken we cannot use Entity functions in excel tables.

To give you the complete picture the qualification */* the aspired program and the awarded qualification are contained in an entity the applicant. The boolean conclusion being proven in the excel rule table out of the combinations of aspired and awarded programs are used to infer instances called the exempted courses which is also contained by the applicant.

Thank you and kind regards,
Jerome
• ###### 3. Re: Cross entity reasoning in Excel
Hi Jerome,

No problem! You're right, if you want to assess multiple qualifications as part of one rulebase session then entities is definitely the right approach (not global).

It does sound like quite a complicated problem. I'm still not 100% sure of why there will be 4 rows, is there duplicate / repeated data being sent?

So your data model is something like this?

the applicant
-the exempted course (one-to-many containment)
-the qualification (one-to-many containment)
-- the aspired program (one-to-many containment)
-- the awarded qualification (one-to-many containment)

Then do you have a reference / manual relationship between the aspired program and the awarded qualification? Also, what is the relationship between course and qualifications? I.e. if the applicant does not have the right qualifications, they are not able to apply for a course?

In my experience (from many late nights in front of OPM!!) it's worth spending a bit of time on data model optimisation and getting the inputs (from ODS) as simple as possible before writing the rules. This will make your rules and rulebase much easier to write, understand and maintain.

It sounds like you have some ideas to optimise it, and you now have one idea for your Excel table rules to try out. If you're still stuck let me know and I can try and work through the problems with you.

Good luck!
Ben
• ###### 4. Re: Cross entity reasoning in Excel
Hi Ben,

I really don't think I can use cross entity referencing in Excel. You are right it seems it will come down to my data model. I verified I can achieve half of my objective (separating the aspired program and awarded qualification into two entities) and it worked so long as one is contained by the other as you suggested.

Here's the revisions of the data model:

Model 1 - the qualification entity contains a union of aspired program and awarded qualification
the applicant
- the exempted course (one-to-many containment)
- the qualification (one-to-many containment)

Model 2 - broke-down the qualification entity into two same level entities contained by the applicant
the applicant
- the exempted course (one-to-many containment)
- the aspired program (one-to-many containment)
- the awarded qualification (one-to-many containment)

Model 3 - the awarded qualification contained by the aspired program
the applicant
- the exempted course (one-to-many containment)
- the aspired program (one-to-many containment)
-- the awarded qualification (one-to-many containment)

Model 3 essentially has the same number of rows being sent to OPA as in Model 1. However, it has a better structure. So in PeopleSoft we agreed that we will just repeat attaching the awarded qualification as a subset of aspired program and avoiding a union.

the exempted course entity meanwhile contains inferred entity instances achieved by validating the awarded qualification against the aspired programs.

Regards,
Jerome
• ###### 5. Re: Cross entity reasoning in Excel
Hi Jerome,

I think you are definitely on the right track here and the proposed models look much simpler. I also agree that Module 3 seems to give you the most flexibility, because you can still retain the awarded qualification entity.

Although, would this lead to duplicate instances of "the awarded qualification" if you had two aspired programs which shared the same qualification (is that possibe)? Due to containment you would need to create the same awarded qualification instance twice.

If the rulebase has to handle a large number of instances this could have a performance implication (we've found!) otherwise it should be fine.

For a quick pros and cons: Module 1 avoids duplicate instances, but will have duplicate attributes perhaps - but this is a negligible performance implication. Module 2 will have the "normalised" structure and avoid duplicates, but the rules will be harder to cross-entity reason and maintain. Module 3 may have the most flexibility but could create duplicate instances which may affect performance.

I hope I've understood the problem correctly! Let me know if you need a hand with anything else... good luck!
Cheers,
Ben
• ###### 6. Re: Cross entity reasoning in Excel
Hi Ben,

You are right in Model 3 that it will have redundant instances (which is essentially the root cause of my problem). Applying attribute mapping for Model 1 looks good to me but I have already send sample rule doc design for client review and they preferred to have the excel rows grow instead of adding more columns not to mention there are around 8 attrs I need to map.

As for model 2, if there is way to do cross referencing via Excel that I will prefer this approach but apparently looks like its now possible. So for I'll keep with the third model to make simple despite the redundancy.

Thank you Ben!

Regards,
Jerome