DEV Community: Seth Meldon

Browse Healthcare.gov Marketplace Plans with a Corticon.js Dynamic Form

Seth Meldon — Mon, 04 Nov 2024 14:48:38 +0000

Just in time for open enrollment, let’s take a look at how Corticon.js can provide all of the logic involved in building a frontend dynamic form for selecting the optimal healthcare plan using healthcare.gov. As with all Corticon.js dynamic forms, the renderer is entirely independent of the business case at hand—Corticon.js JavaScript decision services tell the renderer what to do and when, the renderer captures the users’ inputs and passes along the inputs back to Corticon.js.
Healthcare.gov is one of the main pathways through which Americans can sign up for a healthcare plan. Like shopping on an ecommerce site and filtering down a search query result, selecting a plan entails narrowing down a list of options from a massive initial set of plan data. The variables considered by this dynamic form are as follows:
• Geography – based upon a household’s address, an applicant may be routed to a different healthcare marketplace—not all US states make use of healthcare.gov, opting instead to manage their own portal or share a portal with other non-healthcare.gov states. Additionally, each state has any number of Rating Areas which impact the rates for each plan.
• Household – who lives in the household? It’s common for plan options to differentiate between characteristics like married/unmarried couples, number of dependents, income sources, whether or not they are tobacco users, if anyone is pregnant, and their residency statuses.

• Cost Savings Programs – Depending on the household’s income and other variables described above, plans may provide cost waivers or tax credits to members. In this form, we’ll determine eligibility for the advance premium tax credit.
• Coverage options – Filtering down through the options, plans have different coverages and benefits like specialty drug coverages, homecare services, or chiropractic care. Copays, coinsurance rates, premiums, and limit for these services likewise vary, sometimes depending on criteria specific to a given household member.
• Quality – Enrollees of plans are annually surveyed about their experiences with their respective plan, providing a 1-5 rating for many different measures like customer service experience and availability of health services in a reasonable amount of time. This measure is indexed, along with patient outcomes data (e.g. readmissions, appropriate frequency and kind of screenings, accurate diagnostic determinations from reported clinical notes).
These factors are evaluated based upon data provided by the end user filling out the form, APIs exposed by the healthcare.gov marketplace API, with rules specifying what data should be obtained from each. Rules concurrently are ruling out plans based upon eligibility criteria and end user preferences for the healthcare plan’s coverages and costs. Finally, all of the matching plans are presented back to the user with links to the plan overview.
Data gathered from the end user filling out the form:
• Household zipcode
• Household members’ ages, marital statuses, tax filing status, income, citizenship status, employment status, and whether they use tobacco or are pregnant,
• Desired ‘metal’ level of plan’s coverage (Bronze, Silver, Gold)
• Desired minimum quality level of plan
Data gathered from REST APIs over the course of the form:
• Retrieve household FIPS code and State Code based upon provided zip code sent to the healthcare.gov /counties/by/zip/{zipcode} endpoint
• Retrieve the rating area for the household based upon where in the state the household is located from the healthcare.gov /rate-areas endpoint
• Retrieve the poverty level for the state where the household is with a household of the size provided by the end user from the healthcare.gov /states/{abbrev}/poverty-guidelines endpoint
• Query the healthcare.gov /api/1/datastore/sql endpoint for the Plan ID of all plans for the household state, rating area, and the age of enrollee(s)
• Retrieve rating, metal-level, and a link to the plan brochure by querying the healthcare.gov /plans/{plan_id} endpoint with the respective Plan ID numbers
Finally, after removing any plans beneath the quality or coverage thresholds specified, the form presents the user with the matching plans:

Halloween Candy Trading Rules with Corticon

Seth Meldon — Thu, 31 Oct 2024 13:34:02 +0000

On October 31, the U.S. celebrates the Halloween holiday, where children dress in costumes and collect candy during an evening ritual called “Trick or Treat.”

A conscientious parent wants to establish some rules to govern her children’s candy consumption in the aftermath of Halloween. These “screening” rules include:

All unpackaged or unwrapped items must be discarded. This includes fruit, baked goods or candy with damaged wrapping. If fruit, then the parent will replace the item with a new, fresh (and known safe) item of the same kind.
Because little Jimmy has a nut allergy, all items containing nuts must be given to his big sister, Sarah.
For every item containing nuts given to Sarah by Jimmy, Sarah will repay him with an item not containing nuts.
Finally, the parent would like all of this nonsense to be over with as quickly as possible. How many items, on average, must she distribute to each child per day so that all candy is gone within 30 days? Alternatively, if decides the maximum daily allotment per child is 3 pieces, how long will each child’s supply last? The preferred distribution strategy will be the option which gets the whole thing over with as soon as possible.

Like many decisions modeled using Progress Corticon Studio, this one can be approached as a series of steps. We’ll model these steps as individual rulesheets to help us organize our thinking, making it easier to change the rules later.

Here are the basics steps we’ll model:

Discard & Replace: Applying the first rule, we’ll inspect every item and mark or “tag” those which need to replaced or discarded.
Trade: Applying the second rule, Sarah gets all of Jimmy’s items containing nuts.
Repay: Applying the third rule, Sarah “pays back” Jimmy with an appropriate item (not containing nuts) for each item he gave her.
Allocate: Applying the last rule, we’ll calculate which distribution strategy the parent should choose.

Of course, there are many ways to approach and solve this problem. We prefer those that are clear and organized.

A sample Vocabulary containing the terms needed to model the rules is shown below. Child and Candy entities are related by an association with one-to-many cardinality, enabling any Child to have a collection of Candy, large or small. A piece of Candy has attributes which indicate its characteristics; for example, does it contain nuts? Is it properly packaged? Etc.

An important assumption is that someone (likely the child’s guardian) will classify each piece of candy using several of these attribute values so that the rules we build have data to work with. Boolean attributes, such as discard and replace, will have values derived by the rules themselves.

The attribute candyOwed functions as a “holder” or “counter” value, enabling us to keep track of how many pieces of candy change hands. Because its value is only used internally in the rules, we’ve made it a transient attribute.

The first rulesheet, named “discard_and_replace” examines every piece of candy (notice the root-level Candy, which means all candy) and identifies those which must be discarded or replaced. The rulesheet is complete and unambiguous, meaning every piece of candy will receive one, and only one, value for discard and replace.

The second rulesheet, named “trade,” acts on those pieces of Jimmy’s candy that were identified (by the parent) as having nuts. When candy containing nuts is found in Jimmy’s collection, the Actions add the item to Sarah’s collection of candy, remove the item from Jimmy’s collection, and increment Jimmy’s candyOwed tally by 1.

Next it’s payback time. For each item Jimmy gave to Sarah because it had nuts, Sarah needs to give one back. But not just any item will do. Sarah can’t repay Jimmy with items that are due to be discarded or replaced—that wouldn’t be fair. Nor can she repay with items containing nuts—that would defeat the whole purpose of the trade.

In the third rulesheet, named “repay,” one rule takes all of these requirements into account. When an item meeting all Conditions is found in Sarah’s candy collection, the Actions add it to Jimmy’s collection, remove it from Sarah’s and decrement Jimmy’s candyOwed tally.

This is very similar to the trade rulesheet, with one important exception. Let’s assume that Sarah has 5 items in her candy collection which satisfy Condition rows a, b and c. But let’s also assume that Sarah only owes Jimmy 2 items as repayment. How can we stop the evaluation of Sarah’s candy (and its reassignment) after 2 pieces have been repaid to Jimmy? This is tricky, as the default way Corticon evaluates elements in a collection is to evaluate each element satisfying the rule’s scope independently.

In other words, the examination of row b (SarahsCandy.discard) by the Conditions is unaffected by the examination of row c (SarahsCandy.replace), or any other item. That means the mere presence of Condition row d (Jimmy.candyOwed > 0) is no guarantee that rule execution will stop once Jimmy has been repaid in full. We need something else to accomplish this goal.

A rule that is not action-only (not in column zero) can be set to use conditions as processing thresholds. This configuration, enabled by selecting it from the rulesheet with the applicable rule column selected, instructs Corticon to reevaluate all Conditions each time a piece of candy is examined. As a result, the strict independence between rows b and c disappears.

Once selected, the column header number becomes bold, as shown in the screenshot below.

When the candyOwed tally is down to zero (meaning Jimmy has been fully repaid), the rule execution stops.

Once all candy has been checked, discarded, replaced and traded, we need to choose the right strategy so that all this candy disappears as quickly as possible.

The screenshot below shows two nonconditional rules which calculate two key attribute values needed to select a strategy. Each equation counts the pieces of candy in each child’s collection using the –> size operator. Then a Condition/Action rule compares the two options and recommends the final allocation strategy.

With all of the steps completed, the parent can now work with candy collections of any size. In the downloadable rule project, you’ll see three test sheets pre-configured. Here we have the results of the middle-sized candy haul:

Running the rule test with the trace data view, we can see how Corticon has incremented the traded candy values. In case Jimmy and Sarah say the trades were unfair, the trace data log provides us with the precise order and nature of the changes made throughout the four rule sheets in the ruleflow:

Want to lean on Corticon for Halloween candy redistribution or another use case? This project is freely available for download from our GitHub, and can be imported into Corticon Studio, which you can try free for 90 days. Better yet, if you already know how you’d like to try to automate complex decisions with Corticon, give us a shout and we’ll build a custom demo for you to see how we’d approach it.

Evaluate a Site for Solar Energy Potential with Corticon

Seth Meldon — Thu, 03 Oct 2024 19:07:22 +0000

Integrate data from external REST APIs to enrich your decision data

Table of Contents

First Launch of Corticon Studio
Rule Projects
Rule Vocabulary
- Entities
- Attributes
- Associations
- Complete the Vocabulary
Create your first rulesheet
- Logical Integrity Checks
Testing Rules
Data Integration
- Incorporate the REST Datasource
- Update the rules
- Test the Updated Rules

First Launch of Corticon Studio

Workspaces, projects, and folders provide a way to organize rule-modeling components. A workspace is a 'root' directory that acts as a container for rule projects created in Corticon Studio. When you first launch Corticon Studio, you'll be prompted for a workspace—this is different than the folders that were assigned during the Corticon installation wizard. A workspace is where all of the things created by you, the end user, are stored. The work directory—the one from the installation wizard—is where all of the things that Corticon itself needs to work as an application will live. If you choose to later uninstall Corticon, make sure to do so using the uninstall wizard in your start menu, not just deleted these folders.

You can feel free to leave the default workspace folder (it will be created automatically), or point to a different folder if you prefer.

Rule Projects

A Corticon rule project is a ‘container’ for rule-modeling files. A Corticon rule project can further contain folders to segregate rule-modeling components. A ‘template’ rule project can be downloaded from here—download the file, but don’t unzip it.

To import that template:

Click ‘File’ on the main toolbar, then click ‘Import…’

Choose the ‘Select archive file:’ option, then browse to the downloaded zip file

Click Finish

Rule Vocabulary

A Corticon Vocabulary is a rule-modeling component that enables you to define all the business terms that you require in your rules. For example, let’s take a look at how we might formulate a rule vocabulary from some common solar site evaluation rules.

An installation Site will have a size, annual radiation, and aspect. The Site will be where we install some number of Panels, each of which will have various values like length, width, and efficiency.
Let’s define this is as a rule vocabulary, with Site and Panel being entities, each having their respective attributes, and associated to one another as one to many—one site to any number of panels.

Entities

Follow these steps to add an entity in the Vocabulary editor:

Double click the vocabulary file in the project explorer—it is in the Vocabulary folder and has the extension ‘.ecore’:

In the Vocabulary editor, right-click and select Add Entity, and type in Site. If you don’t enter a name, it will default to ‘Entity_1’, but it can be changed by double clicking on the name of the entity.

Add one more entity, and give it the name Panel:

Attributes

After you create an entity, you can add attributes to it. An attribute is like an adjective, whose values are populated at runtime.

Right-click the entity name and then select Add Attribute.

You’re given a choice of the attributes’ data type—Boolean (True or False), Decimal, DateTime, Date, Integer, String, Time. For this example, select decimal. Enter a name, squareMetersfor the attribute, or edit the name in the same way you did with the entity. After you add an attribute, the property editor panel of the Vocabulary editor displays all the properties for the attribute with default values.

Associations

An association describes the relationship between two entities. In a Corticon Vocabulary, you can define the association under either entity. By default, the association that you define appears under both entities in the Vocabulary tree.

You add an association to an entity just as you add an attribute.

Right-click Site and select Add Association.
The default selections should be what we want—one site to many associations. Click OK.

Your vocabulary will now look like this:

Complete the Vocabulary

Now, add the rest of the vocabulary elements we’ll need.
Add these attributes to ‘Site’:

length(decimal)
width(decimal)
maxPanels(integer)
annualRadiation_kWh(decimal)
aspect(string)
isSuitable(boolean)
slopeDegrees(decimal)

Add these attributes to Panel:

length(decimal)
width(decimal)
squareMeters(decimal)

Your vocabulary should now look like:

As you likely deduced from the rule, “Aspect should not be north,” the word aspectis used here to mean compass direction (north, south, east, west). Each of the four compass directions are ‘string’ (alphanumeric) datatypes, but there is a specific list of potential values for those strings—we want to make sure we don’t deviate from those specific values when modeling rules. For this situation, we’re going to define a ‘Custom Data Type’:

Click the name of the vocabulary at the top of the vocabulary tree, as shown:

In the righthand side of the vocabulary editor, you’ll see a pane come into focus called ‘Custom Data Types’. In the table, type ‘aspect’ on the first row under Data Type Name, then select ‘String’ from Base Data Type. The default selection under ‘Enumeration’ will be ‘Yes’, don’t change this. Lastly, on the right hand side, under ‘Value’, type the four compass directions on the first four rows. Your custom data types table will look like this once complete:

Lastly, click the ‘aspect’ attribute underneath the Site entity that you created previously. On the righthand side, a panel labeled ‘Basic Properties’ should appear. Next to the row called ‘Data Type’, which will currently say ‘String’, select the dropdown button and select the newly created data type ‘aspect:

You’ll see why we defined this custom data type in the next step.

Create your first rulesheet

Let’s start with a rule that will solve for a site’s area in square meters. First, right click on the ‘Rulesheets’ folder and select New > Rulesheet:

Give it the name ‘Site Area’, and then click Finish.
You define your rule logic in a Corticon Rulesheet. A rule is like an ‘if-then’ statement. Each rule consists of one or more conditions (if) that are associated with one or more actions (then). Here is an example of a Rulesheet with five rules:

The Rulesheet editor has the following parts:
Conditions rows—for defining the specific value(s) of a given vocabulary attribute which should trigger this rule should fire. For example, car.price> 45000. The condition value could be a single value (45000), a set of values (45000, 60000), or a range of values (20000 .. 45000).
Actions—where you assign the value(s) to assign to vocabulary attribute(s) when the conditions are met. For example, car.potentialTheftRating= 'High'.
But what about unconditional rules? For example, let’s say our Solar site is always a rectangle, so square meters can always be solved for by ‘area=length * width’ with no conditions involved. This is called an ‘action-only’ rule—it will always fire. We define these in column 0. Let’s try it out.
Open your newly created ‘Site Area.ers’ rulesheet. When defining rules, it is easiest to arrange the different panels of the screen in the way shown here:

From vocabulary panel on the left, select the attribute squareMetersfrom underneath the Siteentity, and drag it onto the first action row:
In the 0 column, we’re going to define the expression which will be used to assign the value for the squareMetersattribute. Drag and drop (or just type in) the two other attributes that will be multiplied-- Site.length*Site.width:

Now, lets define a rule statement for this rule. Rule statements are ‘explainers’ of a sort for each rule that fires during the execution of the decision service, to help document the rules which contributed to the final output. In the Rule Statement section, copy the text below into the Text column in the first row: Site area is Site.squareMeters meters
Add the other values shown in the picture below to your rule statement:

Let’s now define the conditions and actions for our initial set of rules. We will set the boolean attribute isSuitablebased upon whether the Site meets these conditions:

The slope must be 45° or less.
Annual solar radiation should be at least 800 kWh.
Aspect should not be north.
Areas with a slope of 10° or less are determined as suitable areas regardless of the aspect.
20 m² and more area is needed.

Copy and paste the five rows above into your Rule Statements underneath the one we’ve just defined:

Drag Site.slopeDegrees onto the first Condition row. In the first row of the ‘1’ column, type '< 45'
Drag Site.isSuitable to the second Action row, then ‘T’ from the dropdown.
Drag Site.annualRadiation_kWhto the second Condition row. In the second row of the ‘2’ column, type >=800. Set the corresponding action to also be Site.isSuitable = T

Drag Site.aspect to the third condition row. In column 3, select the third condition row. A dropdown button will be available on the right side of the cell—select North from it:

Set the corresponding action to set isSuitableto F.
Implement the next two rules and then update the Rule Statements as shown below.

Logical Integrity Checks

Before we test these rules with test data, Corticon can analyze them for logical issues we may have introduced. Look for these three buttons

at the top of the rulesheet editor screen (if they’re greyed out, then click anywhere in the rulesheet to bring it into focus).

Click them from left to right, start with the Logical Loop Checker. As the name implies, if we were introducing any circular logic, Corticon would point us to it automatically with this check. We shouldn’t have any loops.
Next is the Completeness Checker—are there more scenarios implicitly possible based upon the rules we’ve authored so far? Corticon will condense all possibilities into a new column. This can be helpful, but some of the new conditions should be evaluated independently, and we’re not going to worry about null checks. You can select this column and click the delete button.
Instead, let’s implement the missing rules in a few additional columns such that your rulesheet looks like this:

Since we have new rules, it is a good idea to add our additional rule statements for the rules.

You can copy and paste the italicized text below directly into your rule statements pane to save yourself some time.

| | | | |
|---- |----------- |------ |---------------------------------------------------------------------------------------------- |
| A0 | Info | Site | Site area is Site.squareMeters} meters |
| 1 | Info | Site | The slope must be 45° or less. |
| 2 | Info | Site | Annual solar radiation should be at least 800 kWh. |
| 3 | Violation | Site | Aspect should not be north. |
| 4 | Info | Site | Areas with a slope of 10° or less are determined as suitable areas regardless of the aspect. |
| 5 | Info | Site | 20 m² and more area is needed. |
| 6 | Violation | Site | The slope is not 45° or less. |
| 7 | Violation | Site | Annual solar radiation is not at least 800 kWh. |
| 8 | Info | Site | Aspect is not north. |
| 9 | Violation | Site | Site is not at least 20 m² |

Finally, let’s run the Conflict Checker. Here, we can really see the value of business users that are well-acquainted with the rules being the implementers of the logic versus developers, because they will know best how to address logical mistakes in the rules:

Corticon has identified 15 conflicts—situations where the conditions we’ve defined could presumably overlap. Currently, any of the cells with a ‘ – ‘ in them will evaluate that rule without considering the vocabulary attribute on the left hand side. For example, the first conflict which Corticon shows us is a conflict between 1 and 3, because Corticon has inferred there may be scenarios ‘North’ could be the value in cell c2, or a value < 45 could be in the cell a3—if both conditions are met, which matters more?

We can resolve this in a few ways—explicitly define every combination of values in every rule, or simply override one with another in cases of conflicts. We’re going to evaluate these rules such that if any aspect of the Site makes is unsuitable for solar, then that it will take precedent over any condition which resolves to isSuitable=T. However, recall that the condition for rule #4—when the slope is less than 10 degrees—will resolve to isSuitable=T even when the Site.aspect= ‘North’.

We’ll define overrides to tell Corticon which action to defer to in cases of conflict. Other than rule #4 overriding number #3 for the reason mentioned above, all other rules that lead to a determination of isSuitable=F should override those which set isSuitable=T. At the bottom of each rule column, you’ll see an override row where you can specify which rule(s) that particular rule will override in cases of conflict. You can type them in or select them from the dropdown (hold down control to select multiple). Try this on your own, then check your work based on the screenshot below.

Testing Rules

Next, let’s set up a rule test to run test scenarios against this rulesheet. A Ruletest simulates a business scenario where the rules are applied to input data. If the data satisfies all the conditions in a rule, the rule fires and some output containing the results of the rule execution is produced. You can define different sets of input data to test how the rules behave in different scenarios. You can also use a Ruletest to compare the output of a rule execution with expected results. A Ruletest stores this information in a Ruletest file, enabling you to save use-cases that are of interest, change rules, and run the test again to see how the modified rules behave when applied to the same use-cases.

Right click on the Ruletests folder > New > Ruletest.

Give it a name like ‘Site Check’, and select the RuleTests folder if it isn’t already.

Hit next, and you should see the ‘Site Area’ rulesheet already selected. This is the ‘test subject’, i.e. the specific rule asset file we’re testing against. Click Finish.

The Ruletest editor has four parts:
Test Subject—specifies which Rulesheet or Ruleflow is being tested
Input—where you define input data to be processed by the rules in the Rulesheet.
Output—where Corticon Studio displays the result of a Ruletest execution.
Expected—where you can optionally define the result that you expect.
In the real world, a Corticon Decision Service may receive input in different formats such as XML, JSON, Java, or .NET objects. However, in a Ruletest, you just specify input data using the same Rule Vocabulary we’ve used for the rules.

You can specify different entities as well as multiple instances of the same entity. Let’s try it—drag in two instances of the Siteentity, into the input pane. This adds all the attributes in the entity to the Input pane. However, you can delete attributes that you do not need.
Double click the attributes to specify values. The syntax for specifying values is similar to specifying values for attributes in Rulesheets, with one difference—you must not enter any values in quotes, even if the data type of the attribute is String, DateTime, Date or Time. If you enter quotes, the Ruletest treats it as part of the value. Copy the values as shown below for your input:

Above the ruletest, click the Run Test button:

The rules will be compiled into a Decision Service and executed against a test instance of the Corticon Server. In other words, the ruletest enables us to replicate the precise behavior we would expect from the rules if they were deployed as is. You should see the output populated in the Output pane, and Rule Messages sent back for the rules that fired.

Data Integration

So far, we’ve built a rulesheet and a test case in which we define the test data. Corticon is not limited to only evaluating data provided on the initial request to the Decision Service (like the data we defined by double clicking the attributes in the ruletest). Let’s incorporate some external data retrieved from a REST API. We’ll use the API provided by the US National Renewable Energy Lab to retrieve data about the solar radiation for a specific address.
We’ll be using an API key that will expire after today’s workshop, but they are free to generate at NREL.gov.

Reopen your rule vocabulary
Add two attributes to Site—address(string), and avgDailyRadiation_kWh(decimal)
At the top of the screen, click the Vocabulary menu > Add Datasource > REST Datasource
Click the name of the rule vocabulary to bring the datasource configuration pane into focus:

Rename the Datasource Name from the default, “REST Service”, to “NREL”.
In the REST URL field, paste the URL: https://developer.nrel.gov/api/solar/solar_resource/v1.json?address=Tremont%20St,%20Boston,%20MA%2002111
On the Authentication dropdown, select URL Parameter Token
Under field name, enter ‘api_key’
Under token, paste your API key

Click ‘Test Connection’. If everything is in the right place, you should get an alert that the connection was successful.
Click ‘Discover’. This will import the various fields at that datasource endpoint, which we will then ‘map’ to our entities/attributes.
Click your Siteentity. On the right hand side, select AUTOREST.REST_DATA from the dropdown next to Table Name, then select ‘address’ from the entity identity dropdown.

Next, click the address attribute. This time on the righty hand side, select ADDRESS from the column name dropdown.
Finally, do the same for the new avgDailyRadiation_kWhattribute, selecting OUTPUTS_AVG_DNI_ANNUALfrom the column name dropdown.

)

Incorporate the REST Datasource

The one file we haven’t worked with yet is the Ruleflow. A Ruleflow enables you to connect two or more Rulesheets in a sequence. When the Ruleflow is processed at runtime, the Rulesheets are executed one by one in that sequence. The output of one Rulesheet becomes the input of the next Rulesheet. Note that a Rulesheet can be used in multiple Ruleflows. This enables you to use Rulesheets as reusable modules of rule logic. Any change in the rule logic only has to be made once in the Rulesheet and it is propagated across all Ruleflows that refer to it.
Besides defining a sequence of rulesheet executions, we can specify behavior like branching to different rulesheets at specific points in the decision execution based upon attribute values. We also specify the REST callout within the ruleflow.

Right click the Ruleflows folder > New > Ruleflow
Name it Solar Flow and click finish
When we were defining rules in a rulesheets, we dragged and dropped from the Rule Vocabulary. For the ruleflow, we’ll be defining the order to rule execution by dragging and dropping our rulesheets onto the Ruleflow canvas, and then connecting them using the Connection tool on the righthand side of the canvas. Drag the Site Area rulesheet onto the ruleflow

Click ‘Service Callout’ on the panel to the right, then click above the Site Area rulesheet to place the callout node. Connect the callout to the rulesheet by click Connection in the righthand pallet and dragging a connection from the callout to Site Area.

Click on the service callout, and bring the ‘Properties’ pane into view. You should see a dropdown for Service Name—select ‘Retrieve Data’.
Next, click ‘Runtime Properties’ on the tab to the left, underneath the ‘Service Call-out’ tab. Here, you’ll set options for 6 different cells by choosing from each cell’s dropdown. Mirror the content shown below:

Update the rules

The last step we need to perform is to annualize the solar radiation data because we need the sum total of kWh. We’ll solve for this by multiplying the daily kWh value which will be retrieved from the data source by 365. We can do this by just adding another action only rule to the Site Area rulesheet. Reopen that rulesheet, and define the new rule to annualize the data:

Test the Updated Rules

Let’s give this thing a whirl. Open your ruletest back up. Change the test subject to the ruleflow from the rulesheet by double clicking the folder path above the input pane, highlighted below. Change the inputs to match the input shown in the same photo:

When you execute this test, Corticon will retrieve the data value for avgDailyRadiation_kWhfrom the REST endpoint, incorporate it into the data used for the rule processing, and evaluate the site’s suitability based upon the existing rules and the annualized kWh value.

➾ Download project from Github

Internal Rate of Return Solver with Corticon

Seth Meldon — Thu, 03 Oct 2024 18:07:48 +0000

Understanding the Internal Rate of Return Formula
The Rules

Internal Rate of Return

Using Corticon's iterative execution capabilities, we can solve calculations that require solving for the best possible answer.

Internal Rate of Return (IRR) is a financial measure used, among other things, to evaluate the profitability and opportunity cost of an investment. A typical use case would be to evaluate whether to pursue an investment wherein:

Initial expense (investment cost) of $5000
Year 1 - $0 return
Year 2 - $2000 return
Year 3 - $0 return
Year 4 - $4000 return
Year 5 - $0 return
Year 6 - $9000 return

The internal rate of return is solved for by using the formula:

Plugging in our numbers, we have:

~0 = (0/(1+IRR)^1 + 2000/(1+IRR)^2 + 0/(1+IRR)^3 + 4000/(1+IRR)^4 + 0/(1+IRR)^5+ 9000/(1+IRR)^6) - 5000

We are seeking the IRR at which the Net Present Value (NPV) is zero (or as close as we can get within X number of decimal points). We thus need to recurringly try different values for IRR to get as close to zero as possible.

The Rules

First, our rule vocabulary.

We have 3 entities, Candidate, Cashflow, and the root entity, Investment. The investment has any number of cashflows which we'll be evaluating. It also has any number of candidates that will be created during the decision execution, representing various rates that will be plugged in.

The inputs will be simply the parent entity, Investment, with all corresponding cashflows and an installment number marking their sequence. The first cashflow is always the cash outflow, so its amount is thus always negative. It will use the value 0 for its installment number.

Next our rules.

We start by initializing a value for IRR, which will be incremented up or down depending upon the resulting NPV. We likewise will slot the initial cashflow's value into the 'principal' attribute of the Investment entity, and then remove that cashflow to more easily operate upon only the future flows.

We'll drag this first rulesheet onto a new ruleflow which will be generated into the runtime decision service later on. A ruleflow can contain any number of rulesheets and any number of 'embedded' ruleflows. We're going to create an embedded ruleflow containing two more rulesheets, and loop through this embedded ruleflow as we try candidate IRR rates by applying the 'Iterative' option to it from the ruleflow pallete.

When an object on a ruleflow is set to iterate, it will repeatedly re-execute until the values derived by the object’s rules cease to change. Once values in the object cease changing, the iteration stops and execution continues to the next object (as determined by the Connectors).
Within the inner ruleflow, we have two rulesheets.

The first will calculate each individual cashflow's portion it contributes to the final calculation-- for example, the 'portion' attribute of cash flow 3 would be the result from 0/(1+IRR)^3.

The second rulesheet in the embedded ruleflow will:
- Set Investment.npv to the sum of each cashflow's portion less the initial investment
- If that npv is greater than zero, increment the irr up by .01, or if less than zero, down by .01.
- However, we need to know if we've already tried a given rate or not so we don't end up in an endless loop. This is where the Candidates come in. We create a new candidate for each rate that we try, until we run into a rate that has already been tried, at which point no action is triggered an we return the calculated value.

Testing against the top level ruleflow, we set the input based upon the use case listed above for the $5000 investment. We see that Corticon has settled on an IRR of .27 (27%).

When we run the ruletest with ruletrace, we can see how Corticon has looped through the rules with each tweaked rate until it got as close to zero for the investment's NPV as it could.

The rule trace data can be exported to a CSV as well.

➾ Download project from Github

➾ Learn more about Corticon

Offline Dynamic Forms with Corticon.js - Video Training

Seth Meldon — Thu, 03 Oct 2024 15:34:25 +0000

Dynamic Forms with Corticon.js - YouTube

youtube.com

File Governance and Versioning in Corticon BRMS

Seth Meldon — Thu, 03 Oct 2024 15:26:59 +0000

Learn how to integrate Corticon business rule assets into your version control systems.

Progress Corticon Studio is a robust application, enabling all aspects of business rule development and testing without coding. Because Progress Corticon operates upon data while storing no data, all the components developed within Corticon Studio are straightforward to manage as standard file assets—you have to really go out of your way to introduce external dependencies to Corticon business rules projects!

Everything authored in Corticon Studio’s respective file types—the vocabulary, rule sheets, rule tests and rule flows—are stored under the covers as XML files, making integration with version control systems like Git a cinch.

Table of Contents

Basic Version Control Workflow
- Repositories
- Branches
- Git Operations
- Integrate a Repository into Corticon Studio’s Project Explorer

Basic Version Control Workflow
- Repositories
- Branches
- Git Operations
Integrate a Repository into Corticon Studio’s Project Explorer

Basic Version Control Workflow

Using version control systems (VCS) is a standard practice for most development teams, but here’s a brief overview of the primary VCS, Git, if it’s newer to you. GitHub is one of the most widely employed Git-based VCS and will be the tool used here.

Using a version control system provides numerous benefits for organizations, namely:

Restoring older versions in case you accidentally break something
Collaborating with team members without overwriting each other’s changes
Documenting who makes a change, when they made it and what they changed
Backing up your work in case files are lost

Repositories

The central hub where everything is stored and tracked is called a Repository. A common practice with Corticon is to create a Repository for each rule project. That way, when you open up Corticon Studio, the repository can be represented as a folder within the Project Explorer.

Every change made to the rule assets and any new files will be tracked as you go, and when you’ve completed the needed changes, you “stage” the changes to specify which files’ changes you want to save into the repository, and finally commit the rules back to the central repository. A commit is like a save point in a video game but documented with a user-defined commit message which will serve as an explainer of the changes made that can be referred to later on.

Branches

Branching is what enables multiple development team members to make changes to a shared repository. When you create a new repository, a “main” branch is created along with it. If one rule developer needs to tweak a few rules, she’ll create a new branch off the main branch to isolate her instance of the rule repository from the master repository, and “merge” her branch’s changes into the main branch once the changes are ready to go live. You can read more about how branching works in GitHub at this link.

Git Operations

Typically, a developer will execute these steps with the command line. For example:

$ git init     # Initialize a new git database

$ git clone    # Copy an existing database

$ git status   # Check the status of the local project

$ git diff     # Review the changes done to the project

$ git commit   # Save the current state of the project to database

Using terminal commands isn’t necessary for basic adoption of Git with Corticon Studio files, though. There are various tools that will allow us to bypass the command line when defining rules, including the built-in Eclipse plugin for Git version control. If you’ll be storing your assets on GitHub, though, an even easier solution is GitHub Desktop, a free desktop software that GitHub offers. It can be used in conjunction with Corticon Studio, handling most git commands for you. For full instructions on getting set up with GitHub desktop, follow the guides they provide here.

Once installed, users can link their account to the installation, then connect to existing repositories or create a new one. The repository will then be stored on GitHub and cloned to your local desktop. You can make changes to the files with branches (recommended), or make changes directly on the main branch. As you make changes to the Corticon rules, the files will be monitored and considered by GitHub as being in one of four states: untracked, modified, staged or committed. Once you're satisfied with your work, you can create a pull request to merge your changes in the current branch into another branch.

Integrate a Repository into Corticon Studio’s Project Explorer

Whether you’re using Corticon Studio for server-based or JavaScript deployments, the steps for version control integration are the same. Here, we’ll use the Corticon rule project samples repository for a demo.

1. Install GitHub Desktop and link to your GitHub account, as described above.

2. Navigate to the repository’s URL on GitHub (e.g. https://github.com/corticon/Importable-Rule-Projects).

3. Click the green “Code” button and select “Open with GitHub Desktop.”

4. Select either the default or a custom directory which will serve as your local copy of the repository that you’ll work off of in Corticon Studio.

5. Open Corticon Studio or Corticon.js Studio.

6. Right click inside of the Project Explorer pane.

7. Select “Import.”

8. Select “Projects from Folder or Archive” then “Next.”

9. Select “Directory.”

10. Navigate to the directory path of the GitHub repository on your local machine.

11. Choose the parent folder of all rule project files you want to import.

12. Open up the project, proceed with rule modeling.

13. Once you wrap up, save all files, then open up GitHub Desktop if not already open, make sure “Current repository” and “Correct branch” are correct, then enter in a commit summary and optionally, a commit description.

14. Hit “Commit to main” (or the name of the current branch) and then “Push Origin.”

Your rules have been versioned! Whenever you or another rule developer next goes to make edits, you just need to open up GitHub Desktop and “Fetch Origin” so that your local copy of the repository is up to date. Then simply open up Corticon Studio, and the project explorer will automatically contain any of the new/changed files from the repository.

As mentioned, this is just one, basic way to integrate rules into your VCS workflow.

Wrangling Data across a Logistics Enterprise with Business Rules + SQL

Seth Meldon — Mon, 09 Sep 2024 00:29:17 +0000

Taming SQL Logical Complexities with a Business Rules Engine

For enterprises and government agencies whose operations depend on efficient logistical management of geographically diverse personnel and assets, capturing and acting on relevant data about these moving parts is critical. While the amount of data available and the sources from which this data is logged and exposed are more expansive than ever, the mounting complexity attributable to the continual accumulation of data from new and existing data sources necessitates thoroughly considered the extent to which the data can be acted upon, not accumulated just for the sake of it.

Business processes and decision-making for logistical functions like fleet management require data from enormous datasets, often spread across multiple databases and made available through different protocols, and maintained in distinct formats. Individual decisions that seem straightforward, answering simple questions with simple answers-- may rely upon a labyrinth of intermediate business logic and data operations executed along the way.

With the proliferation of connected technologies and the resulting growth in the scale and variability of data, ensuring that applications are able to access specifically relevant data—no more, no less— for a given business process is a critical performance and security requirement for organizations' enterprise architects.

Effective use of SQL helps minimize the processing time required to get to desired subsets of data, but this approach can quickly result in organizations unwittingly hoisting an anvil of maintainability issues over their operations. As regulations change, data sources expand, storage and compute costs grow, personnel turn over, and novel security threats appear, the practice of intertwining logic and data within the database using SQL tends to make it increasingly difficult to isolate bottlenecks.

Table of Contents

Design Approaches for Data Driven Decisions
Implementation Guide - Corticon Fleet Management
Create Fleet Manager Database
Querying for Driving Compliance Violations with SQL
- SQL View of Trip Origins and Destinations
- SQL Table Valued Function of Trip Origins, Destinations, and Driver Info
- SQL Stored Procedure of Driving Shifts
Querying for Driving Compliance Violations with Corticon + SQL
- How Corticon modularizes queries, data, and rules
- How the rules and queries are organized
- Generate the Rule Vocabulary from the Fleet Management Database Schema
- Connect to the Query Database
The Business Rules
- Initialize Values - Rulesheet
- 60 70 Hour Limit - Ruleflow
- Time Driving per Shift Rules - Ruleflow
- remove temporary fields - Rulesheet
Testing
Better Together

Design Approaches for Data Driven Decisions

In this tutorial, I'll try to solve for some of the fleet management challenges which can present maintainability challenges, first by implementing a SQL only approach to common queries. Then, I'll show how those queries can be made more maintainable, traceable, and understandable by externalizing rules in a rules engine yet operating on the same SQL data source.

Fleet management encompasses the logistical tasks related to vehicle maintenance, driver dispatch and scheduling, cargo tracking and related services in managing moving people and parts. For example, a cable company's fleet of vehicles driven by field service technicians, or a delivery company providing a network of courier and package delivery services.

Organizations that have fleets to manage must compete by maximizing efficiencies across numerous different areas—vehicle reliability, driving behavior and safety, route optimization, and trip completion timelines—representing distinct yet interdependent data points to weigh and balance. Moreover, safety regulations define maximum shifts in which a driver can be driving without a break, minimum break periods, and related measures to mitigate fatigue related dangers. For example, in the United States, the Department of Transportation defines 'Hours of Service Rules' which stipulate the limits such as:

14-hour shift limit - When a driver comes off any ten hour period in which they were off duty, they can only drive in the time window between when they come on duty and 14 hours later (not that they would be driving for the entire 14 hours). After this 14 hour period, they may not drive again until after another 10 hour period spent off duty.
11-hour driving limit - Within this 14 hour window, a driver cannot be be driving for more than 11 total hours.
60/70-hour limit - If a driver works for a carrier that operates vehicles fewer than 7 days per week, no driver may drive for more than 60 hours within a period of 7 consecutive days. If the carrier does operate vehicles every day of the week, then their drivers may not drive for more than 70 hours in any 8 consecutive day period
30-Minute Driving Break - Drivers must take a 30-minute break when they have driven for a period of 8 cumulative hours without at least a 30-minute interruption.

Let's step through how these data points can be catalogued in a SQL database, and then how to assess these sorts of compliance queries directly within the database versus externalizing the query rule logic with business rules.

If you'd like to follow along, the instructions, scripts, and project files are all freely available in this repository.

Implementation Guide - Corticon Fleet Management

To follow along you'll need:

Progress Corticon Studio - can be downloaded for free by following the 'download a trial' link here. The evaluation version of Corticon Studio support in full all functionalities described in this tutorial.
Microsoft SQL Server Management Studio (SSMS) 2019 Express (other versions of SSMS are likely to work), which can be downloaded for free here. To ensure compatibility with Corticon's data integration drivers, please follow the installation configuration documented here.
Download the template files from here to create our database(s) and to import the pre-made rule project files.

Create Fleet Manager Database

Let's start by creating the database and defining its schema.

Across various interrelated tables, we'll define some sample data about the entities involved with the fleet like Driver, Vehicle, Trip, and Destination. This can be automatically done by copying/downloading this script, opening it in Microsoft SQL Server Management Studio and clicking execute.

Trip table: dateOfArrival, dateOfDeparture, driverId, destinationID, originDepotID
Depot table: depotID, streetAddress, city, state
Destination table: destinationID, streetAddress, city, state
Driver table: driverId, first, last
DriverStatusChange: record, statusStart, statusEnd, dutyStatus, driverId

You should see a message that the query was completed popup in the bottom of the interface. If you don't see the newly created database "CorticonFleetManager" in the Object Explorer on the left hand side of your SQL Studio window, right click the root element in the project explorer (likely has your computer's assigned name followed by "\SQLEXPRESS..."), and click refresh.

Expand out the database by clicking the '+' icon next to its name, and then expand the Tables folder to show the data tables.

If you right click one of the table names and click 'Select Top XYZ Rows', you'll see the initial set of dummy data we're going to use. This data is primarily generated using Mockaroo, but was also supplemented by using Corticon itself to generate data!

Querying for Driving Compliance Violations with SQL

Let's create some queries which will enable us to answer questions about the data.

SQL View of Trip Origins and Destinations

To start, take a look at the data in the Trip table of the database. You'll see that we have information about the trip's origin depot and the destination, but not the location information in full--only identifiers for destinationID and originDepotID.

To view the location information for the trip's start and end points, we could create a view that presents each trip in a row that combines the Origin and Destination location data for each Trip, using the identifiers denoted in the Trip table. To create this view, execute this SQL in a new query window.

If successful, you should see the text in the 'Messages' pane that "Commands completed successfully."

Now, we can get to the data we're after by using a query like

SELECT [tripID]
    ,[originStreetAddress]
    ,[originCity]
    ,[originState]
    ,[destinationStreetAddress]   
    ,[destinationCity]
    ,[destinationState]
  FROM [CorticonFleetManager].[dbo].[TripDetails]
  WHERE tripID=1999323

Which would output:

tripID	originStreetAddress	originCity	originState	destinationStreetAddress	destinationCity	destinationState
1999323	04 Dexter Terrace	Worcester	Massachusetts	85 Hagan Plaza	Manchester	New Hampshire

Not too painful, but this hasn't solved many problems for our fleet company. More realistically, the company compliance officer might request a report that will help them track and document driving shifts to demonstrate compliance with the HOS rules.

SQL Table Valued Function of Trip Origins, Destinations, and Driver Info

A first step could be to pull in data about the driver for each of these trips. This could be created with a table-valued function, an alternative approach for selecting and displaying related data from various tables joined by a defined set of key criteria. To create this function, you can run this script in a new query window.
Once created, we run queries like the following in order to see the driver details for the respective trips:

SELECT * FROM [dbo].[GetTripDetailsWithDriverInfo] ()
GO

The top few results we get back should look something like this:

tripID	originCity	originState	originStreetAddress	destinationCity	destinationState	destinationStreetAddress	driverFirstName	driverLastName	driverEmail	driverPhone
1999323	Worcester	Massachusetts	04 Dexter Terrace	Manchester	New Hampshire	85 Hagan Plaza	Vivyanne	Denizet	vdenizet7@csmonitor.com	598-283-8892
2105883	Lynn	Massachusetts	7556 Fremont Hill	Boston	Massachusetts	9562 Roth Circle	Elvina	Paddison	epaddison8@wordpress.org	371-456-5406
2142826	Springfield	Massachusetts	914 Heffernan Plaza	Waltham	Massachusetts	567 Carberry Center	Salomi	Sammonds	ssammondst@fastcompany.com	805-578-1666
2148980	Lynn	Massachusetts	7556 Fremont Hill	Waltham	Massachusetts	84398 Vermont Circle	Carol-jean	Collick	ccollickg@ocn.ne.jp	173-318-5651
2380224	Manchester	New Hampshire	5657 Nova Park	Boston	Massachusetts	1284 Basil Road	Lucais	Rickardsson	lrickardssonb@etsy.com	435-394-7326
4214048	Waltham	Massachusetts	37767 Bartelt Place	Boston	Massachusetts	81 Caliangt Pass	Reagen	Roglieri	rroglierii@sbwire.com	752-814-7885
4324638	Manchester	New Hampshire	5657 Nova Park	Worcester	Massachusetts	56 New Castle Lane	Elvina	Paddison	epaddison8@wordpress.org	371-456-5406
4787172	Waltham	Massachusetts	37767 Bartelt Place	Boston	Massachusetts	997 Schlimgen Park	Wendell	Clatter	Wendellc@gmail.com	752-814-8723
6738436	Lynn	Massachusetts	7556 Fremont Hill	Portland	Maine	9 Prairie Rose Trail	Jae	Liddon	jliddonq@photobucket.com	936-348-8823
7029025	Manchester	New Hampshire	5657 Nova Park	Montpelier	Vermont	017 Waubesa Place	Desirae	Bawdon	dbawdon9@live.com	430-153-9555
7240845	Manchester	New Hampshire	21087 Haas Lane	Brockton	Massachusetts	0 Emmet Trail	Stan	Foran	sforanj@ucoz.com	538-784-6972
7969721	Manchester	New Hampshire	21087 Haas Lane	Portsmouth	New Hampshire	1 Bayside Center	Adela	O'Hederscoll	aohederscolll@redcross.org	738-350-1113
8648078	Manchester	New Hampshire	5657 Nova Park	Portsmouth	New Hampshire	68506 Moland Center	Carol-jean	Collick	ccollickg@ocn.ne.jp	173-318-5651
8789396	Waltham	Massachusetts	37767 Bartelt Place	Springfield	Massachusetts	014 Orin Road	Cassie	Docker	cdocker4@blogspot.com	189-509-8433

We're one step closer to documenting the drivers' hours of service regulatory information, but now we'll need to really get creative to get information about the various legs of each trip, in order to verify whether the driving shifts exceeded any of the defined thresholds, such as driving 11 hours in a 14 hour period.

SQL Stored Procedure of Driving Shifts

We're presented with a few challenges to accomplish this, as data is spread across a quite distributed database schema. Drivers' vehicles in an organization's fleet generally will have an Electronic Logging Device (ELD) onboard to log and report on the vehicle's statuses. From these ELD devices, fleet managers must maintain drivers' hours of duty records for compliance purposes, capturing data required by regulators in the format required by regulators.

In the database we're working with, the ELD records are stored in the DriverStatusChange table. Each driver change in shift records the new status as one of: "Drive", "On Duty", "Sleeper Berth", and "Off-duty". For the rules related to driving time between off duty periods, we must:

Segment out the records chronologically into groupings separated by any row where, in the DriverStateChange table, the row dutyStatus='Off Duty' and the duration is greater than 10 hours based upon the statusStartand statusEndrows
From within each of these groupings, sum together the duration of all rows where dutyStatus='Driving'. If this value is greater than 11, then this represents a violation of rule 1: drivers may not drive more than 11 hours after 10 consecutive hours off duty.
From within each of these groupings between 10 hours off duty, identify any case where the driver had a status of 'Driving' beyond the 14th hour after they had come back on duty. These will represent violations of rule 2: drivers may not drive beyond the 14th consecutive hour after coming on duty, following 10 consecutive hours off duty

Rule 1: 11 hours after 10 consecutive hours off duty

May drive a maximum of 11 hours after 10 consecutive hours off duty.

Rule 1 is fairly straightforward to create a stored procedure for. For a given driver identified by their driverID in the DriverStatusChange table, flag any instance of a row where the DriverStatusChange dutyStatus field = 'Driving' and the durationMins > 660 (11 hours).

CREATE PROCEDURE [dbo].[FlagDrivingOver11Hours]
    @DriverId VARCHAR(255)
AS
BEGIN
    SELECT record, driverId, dutyStatus, durationMins, statusStart, statusEnd
    FROM DriverStatusChange
    WHERE driverId = @DriverId
      AND dutyStatus = 'Driving'
      AND durationMins > 660; 
END;
GO

When we execute the procedure for any of the Drivers' driverId values such as

DECLARE @return_value int
EXEC    @return_value = [dbo].[FlagDrivingOver11Hours]
DriverId = N'271177229'

record	driverId	dutyStatus	durationMins	statusStart	statusEnd
1126	271177229	Driving	668	2024-01-15 17:10:38.000	2024-01-16 04:18:38.000
1366	271177229	Driving	679	2024-01-19 09:58:46.000	2024-01-19 21:17:46.000
2846	271177229	Driving	698	2024-02-09 16:45:36.000	2024-02-10 04:23:36.000

Rule 2: Driving beyond 14th hour

May not drive beyond the 14th consecutive hour after coming on duty, following 10 consecutive hours off duty. Off-duty time does not extend the 14-hour period.

Now we're getting into more tricky territory. For this procedure, an approach could be to:

Create a temporary table which will hold the statuses flagged as violating the rule.

CREATE PROCEDURE [dbo].[FlagDrivingViolations]
    @driverId VARCHAR(255)
AS
BEGIN
    CREATE TABLE #Violations (
        driverId VARCHAR(255),
        record INT,
        statusStart DATETIME,
        statusEnd DATETIME,
        violationType VARCHAR(255)
    );

Use a common table expression to find all Off Duty statuses with a duration over 10 hours for the Driver, along with all Driving statuses with their status start/stop times.

WITH OffDutyPeriods AS (
        SELECT 
            driverId,
            statusStart AS offDutyStart,
            statusEnd AS offDutyEnd,
            ROW_NUMBER() OVER (PARTITION BY driverId ORDER BY statusStart) AS rowNumber
        FROM DriverStatusChange
        WHERE driverId = @driverId  
        AND dutyStatus = 'Off Duty'
        AND DATEDIFF(MINUTE, statusStart, statusEnd) >= 600  
    DrivingRecords AS (
        SELECT 
            dsc.driverId,
            dsc.record,
            dsc.statusStart,
            dsc.statusEnd,
            dsc.dutyStatus,
            ROW_NUMBER() OVER (PARTITION BY dsc.driverId ORDER BY dsc.statusStart) AS rowNumber
        FROM DriverStatusChange dsc
        WHERE dsc.driverId = @driverId 
        AND dsc.dutyStatus = 'Driving'
    )

From these results, select any instances with a duty status of 'Driving' beyond 14 hours since 10 hours spent off duty.

SELECT
        dsc.driverId,
        dsc.record,
        dsc.statusStart,
        dsc.statusEnd,
        CASE
            WHEN DATEDIFF(HOUR, odp.offDutyEnd, dsc.statusStart) >= 14 THEN '14 Hour Driving Violation'
        END AS violationType
    INTO #TempViolations
    FROM DrivingRecords dsc
    CROSS APPLY (
        SELECT TOP 1 *
        FROM OffDutyPeriods odp
        WHERE odp.driverId = dsc.driverId
        AND odp.offDutyEnd < dsc.statusStart
        ORDER BY odp.offDutyEnd DESC
    ) odp
    WHERE DATEDIFF(HOUR, odp.offDutyEnd, dsc.statusStart) >= 14;

Insert these records into the temporary table and clean up the temporary tables.

    INSERT INTO #Violations (driverId, record, statusStart, statusEnd, violationType)
    SELECT
        driverId,
        record,
        statusStart,
        statusEnd,
        violationType
    FROM #TempViolations;  
    SELECT * FROM #Violations;  
    DROP TABLE #TempViolations;
    DROP TABLE #Violations;
END;

By calling the procedure with a driverID, we can see the non compliant records: EXEC FlagDrivingViolations @driverId = '202251400';

driverId	record	statusStart	statusEnd	violationType
202251400	594	2024-01-07T06:09:19.000Z	2024-01-07T11:24:19.000Z	14 Hour Driving Violation

Rule 3: 60/70-Hour Driving Limit

May not drive after 60/70 hours on duty in 7/8 consecutive days. A driver may restart a 7/8 consecutive day period after taking 34 or more consecutive hours off duty.

This is complex! There are a considerable number of 'ifs' to evaluate here. We will need to evaluate the 7 day periods and 8 day periods subsequent to every instance that a DriverStatusChange.dutyStatus, in order to sum together the hours not 'Off Duty' over that period. Over the 8 day stretch, the sum of the status durations not 'Off Duty' cannot exceed 70 hours, and for the 7 day stretch, 60 hours. But if there exists any instance in that window where the Driver has a DriverStatusChange record of dutyStatus='Off Duty' with a durationHours>=34, then skip over that calculation.

Here we might use a procedure that first creates a temporary table to hold all statuses that are not 'Off Duty'.

CREATE PROCEDURE [dbo].[FlagHoursWorkedOverLimits]
    @DriverId VARCHAR(255)
AS
CREATE TABLE #DriverStatus (
        record INT,
        driverId VARCHAR(250),
        dutyStatus VARCHAR(250),
        durationMins INT,
        statusStart DATETIME,
        statusEnd DATETIME
    );
    INSERT INTO #DriverStatus (record, driverId, dutyStatus, durationMins, statusStart, statusEnd)
    SELECT record, driverId, dutyStatus, durationMins, statusStart, statusEnd
    FROM DriverStatusChange
    WHERE driverId = @DriverId
    AND dutyStatus <> 'Off Duty';

And a temporary table to hold all flagged violations:

    CREATE TABLE #FlaggedPeriods (
        PeriodType VARCHAR(50),
        StartDate DATETIME,
        EndDate DATETIME,
        TotalHoursWorked DECIMAL(5, 2)
    );  
    INSERT INTO #FlaggedPeriods (PeriodType, StartDate, EndDate, TotalHoursWorked)
    SELECT '7 Day Period', MIN(statusStart), MAX(statusEnd),
           SUM(durationMins) / 60.0 AS TotalHoursWorked
    FROM #DriverStatus
    GROUP BY DATEDIFF(DAY, '1900-01-01', statusStart) / 7
    HAVING SUM(durationMins) > 60 * 60;
    INSERT INTO #FlaggedPeriods (PeriodType, StartDate, EndDate, TotalHoursWorked)
    SELECT '8 Day Period', MIN(statusStart), MAX(statusEnd),
           SUM(durationMins) / 60.0 AS TotalHoursWorked
    FROM #DriverStatus
    GROUP BY DATEDIFF(DAY, '1900-01-01', statusStart) / 8
    HAVING SUM(durationMins) > 70 * 60;
    SELECT * FROM #FlaggedPeriods;  
    DROP TABLE #DriverStatus;
    DROP TABLE #FlaggedPeriods;
END;

And finally call the procedure with a driverID:

DECLARE @return_value int
EXEC    @return_value = [dbo].[FlagHoursWorkedOverLimits]
        @DriverId = N'202251400'
GO

PeriodType	TotalHoursWorked	StartDate	EndDate
7 Day Period	75.08	2024-01-01 02:00:00.000	2024-01-07 22:30:21.000
7 Day Period	69.10	2024-01-08 06:43:23.000	2024-01-15 00:58:10.000
7 Day Period	76.23	2024-01-15 03:31:12.000	2024-01-21 19:55:58.000
7 Day Period	72.40	2024-01-22 03:00:00.000	2024-01-29 04:16:44.000
8 Day Period	77.77	2024-01-07 04:17:18.000	2024-01-15 00:58:10.000
8 Day Period	86.23	2024-01-15 03:31:12.000	2024-01-22 23:26:06.000
8 Day Period	73.65	2024-01-23 07:03:08.000	2024-01-30 20:05:54.000

That's a lot of logic to maintain directly as SQL queries! And we didn't even check for the 34 hour exclusion rule or consider overlapping periods that violate rules.

Let's shift to Corticon to see how we can solve the above and more by maintaining the rules as...rules.

Querying for Driving Compliance Violations with Corticon + SQL

How Corticon modularizes queries, data, and rules

In Corticon, the _rule vocabulary_ provides a singular dictionary of business terminologies that will serve as building blocks to build decisions from--elements in the vocabulary are abstractions of all data involved in the decision. For example, an application may pass some data about a driver to a Corticon decision service that conforms to the model of the rule vocabulary, and that decision service may retrieve/overwrite/delete elements of that vocabulary over the course of rule evaluation or by accessing external data sets.

Corticon rule vocabularies can be mapped to data in any of the leading relational databases as well as to REST APIs. For this Corticon project, we'll make use of Corticon's Advanced Data Connector (ADC), to trigger queries to be performed during the execution of a decision service.

This allows rule architects to optimize decision processing efficiency by retrieving data at specific points in the execution of a decision service, with queries populated by either data sent in the initial input to the decision service, or data produced in preceding rulesheets in a ruleflow. Planning how to use data queries as part of a decision service execution is an essential way to minimize the 'chattiness' symptomatic in data-intensive decisioning.

How the rules and queries are organized

Similar to the way stored procedures and views are kept separate from the data tables in a database, but preconfigured to be run against the actual data set interested in, we separate out the queries and the data when using ADC:

CorticonFleetManager Database - the same database as used in the exercises above, containing tables like Driver, Trip, Destination, and DriverStatusChange.
fleetQueries Database - Serves as the library of preconfigured, parameterized queries to be executed during a decision service.
- CORTICON_ADC_READ -Table containing rows of each read query's name and unique ID
- CORTICON_ADC_READ_DEFS -Table containing one or more rows per read query ID, the sequence in which they should be executed, and the Corticon rule vocabulary entity / entities that are mapped to the data being retrieved

In Corticon Studio, the vocabulary is mapped to the CorticonFleetManager database tables, and the fleetQueries are imported and then usable in a Ruleflow. This ruleflow, made up of rule logic and these queries, can then be generated into a decision service as illustrated below.

Generate the Rule Vocabulary from the Fleet Management Database Schema

For our fleet management scenario that starts from a database containing the fleet company's data, we'll start by generating a vocabulary from the schema of the CorticonFleetManager database. This way, the schema is automatically incorporated into the rule vocabulary's structure with the entities' interrelations.

Connect to the Query Database

In either the same database or an entirely separate database, we'll next define the queries to evaluate rules based upon this data. These queries are essentially parameterized SQL that substitutes variables specified 'upstream' in the rules.

Similar to the ADC datasource, we'll connect to the query database, but the data contained within the database is not data involved with the business decision--rather, this database will contain the queries that will be referenced by Corticon during rule processing. These queries then can be executed at specified points in a single decision service's execution, in order to fulfill workflows like the following:

Query 1: For the provided DriverID, retrieve the applicable record from the Driver table in the CorticonFleetManager database, and map the Driver data to the Driver entity in the Corticon vocabulary. Then pull in all DriverStatusChange records with this same DriverID, and associate these DriverStatusChange records in the Corticon vocabulary as child entities to the Driver.
Query 2: Based upon the driver type specified in the Driver table, retrieve regulatory thresholds from the Thresholds table. Here, we'll externalize the values for thresholds like maximum hours a driver can legally drive consecutively. These can then be referenced in subsequent rulesheets to flag non-compliant shifts.
Business Rules: Identify violations for a given driver of the 60/70 hours in 7/8 days rule, driving after the 14th hour on duty rule, and driving more than 11 hours without a break.

The Business Rules

Initialize Values - Rulesheet

After the two ADC READ operations are executed, the ruleflow subsequently executes the 'Initialize Values' rulesheet. This rulesheet:

Solves DriverStatusChange.durationHours based on DriverStatusChange.durationMinutes/60
Sets Driver.offDutyCount to be the size of the collection of Driver.driverStatusChange where dutyStatus = 'Off Duty'
Sets a placeholder value of '1' for the attribute Driver.temp. This will be used as a counter for looping during subsequent rulesheets.

60 70 Hour Limit - Ruleflow

Create Work Periods - Iterative Rulesheet

This rulesheet iterates until no more changes occur when evaluated. We create a new entity, Period associated as a child to Driver, which will contain the attributes day1, day7, and day8. We iterate over this rulesheet to create unique instances of Period for the 8 days subsequent to every DriverStatusChange. Each Period will contain the date of the DriverStatusChange in the attribute day1, add 6 to that value for the attribute day7, and add 7 to that value for the attribute day8 until the number of periods=the number of statuses.

8 Day Rules - Ruleflow

Associate statuses with 8 day period - Rulesheet

Add any instance of Driver.driverStatusChange to the collection Driver.period.driverStatusChange for any driverStatusChange where :

Driver.driverStatusChange attribute dutyStatus is 'Off Duty' as well as has a durationHours >= 34
The Driver.driverStatusChange statusStart and statusEnd are within a period's day1 and day8values.

Sum statuses 8 days - Rulesheet

Set the value of Driver.period.hoursNotOffDuty8 to be the sum of all DriverStatusChange.durationHours where DriverStatusChange is a child entity of Driver.period.

7 Day Rules - Ruleflow

Remove association - Rulesheet

Removes the previously created association between DriverStatusChange and Period created in the 8 day ruleflow.

Associate statuses with 7 day period - Rulesheet

Add any instance of Driver.driverStatusChange to the collection Driver.period.driverStatusChange for any DriverStatusChange where :

Driver.driverStatusChange attribute dutyStatus is 'Off Duty' as well as has a durationHours>= 34
The Driver.driverStatusChange statusStart and statusEnd are within a period's day1and day7values.

Sum statuses 7 days - Rulesheet

Set the value of Driver.period.hoursNotOffDuty7 to be the sum of all DriverStatusChange.durationHours where DriverStatusChangeis a child entity of Driver.period.

Overtime Flags - Rulesheet

For any instance of Driver.period where hoursNotOffDuty7 > 60, create a new instance of HOS_Flag as a child entity to Driver, with the attribute type having a value of '>60 hours in 7 days' and the attribute when having a value of [Period.day1] to [Period.day7], time not off duty was [Driver.period.hoursNotOffDuty7].

For any instance of Driver.period where hoursNotOffDuty8 > 70, create a new instance of HOS_Flag as a child entity to Driver, with the attribute type having a value of '>70 hours in 8 days' and the attribute when having a value of [Period.day1] to [Period.day8], time not off duty was [Driver.period.hoursNotOffDuty8].

Time Driving per Shift Rules - Ruleflow

Iterative Rulesheet: Create Windows Off Duty

This rulesheet iterates until no more changes occur when evaluated. We create a new entity, ShiftWindow associated as a child to Driver, which will contain the attribute statusEnd, statusStart, isOffDuty, and windowID. Corticon will iterate over this rulesheet to create a new ShiftWindow for every instance of DriverStatusChange where dutyStatus = 'Off Duty'.

The rulesheet will set the windowIDfor each ShiftWindow to be the value of Driver.temp, while Driver.temp will be incremented up by 1 each time the rulesheet executes, repeating until the value of the Driver.temp is equal to the number of off duty statuses.

Reset - Rulesheet

Set Driver.temp back to 1.

Create Windows Driving - Iterative Rulesheet

Here we're creating three aliases for Driver.drivingWindow- 'preceding', 'subsequent', and 'timeNotOff':

'preceding' - represents any case where Driver.drivingWindow.windowID= Driver.temp and isOffDuty = T
'subsequent' - represents any case where Driver.drivingWindow.windowID= Driver.temp+1 and isOffDuty = T
'timeNotOff' - this will represent the period of time in between the times off duty

This rulesheet will iteratively create new, unique ShiftWindow entities under the timeNotOff alias, until there are no further instances of the subsequent alias to evaluate.

Group Windows - Rulesheet

Similar to the design pattern with the 60/70 hour rules, here we're going to set DriverStatusChange to be a child entity to any instance of ShiftWindow within the same statusStart and statusEnd times, where the value of DriverStatusChange.dutyStatus='Driving'.

Duration Driving Between off Duty Windows - Rulesheet

Here we evaluate all instances of the newly associated DriverStatusChange entities that are a child of DrivingWindow for the driver. If the last instance of Driver.drivingWindow.driverStatusChange any each Driver.drivingWindow has a driving statusEnd greater than 14 hours after the drivingWindow, create a new HOS_flag with the value for the attribute when set to = 'Last off duty concluded at [Driver.shiftWindow.statusStart], driving status ended at [Driver.drivingWindow.driverStatusChange.statusEnd] and for the attribute type set to='Driving status ended more than 14 hours since off duty'.

11 in 14 flags - Rulesheet

Evaluate all instances of the newly associated DriverStatusChange entities that are a child of DrivingWindow for the driver. If any instance of Driver.drivingWindow has a durationHours spent with a duty status of 'Driving' greater than the 11 permissible hours they may drive in a 14 hour window, create a new HOS_flag with with the value of the whenattribute set to ='Driving shift of [DriverStatusChange.durationHours] beginning [DriverStatusChange.statusStart]' and the type attribute set to '>11 hour driving within 14 hour period on duty'.

remove temporary fields - Rulesheet

Remove the newly created entities (Period, ShiftWindow) as well as the retrieved and DriverStatusChange records from the final response payload of the decision services.

Testing

Before we deploy the rules to a Corticon Server, we can validate rule behavior with ruletests to simulate the logic evaluated for a given driverID.

Better Together

Our ruleflow can now be generated into a decision service on Corticon Server, turning our compliance assessment into a ready to use API endpoint for decisioning. These rules could also be built upon, or we could add in a write step, to persist the final determinations back to the database.

We could also run this decision as a regular batch job, evaluating many records concurrently across disparate data sources and persisting the outputs directly back into a database.

And, we can build in further functionalities that would be very difficult to maintain through other approaches, such as incorporating data from REST API endpoints like geocoding and weather condition APIs, in order to ensure all variables beyond just regulatory considerations are readily available and maintainable in a fleet manager's dispatch application.

With Corticon, existing application logic doesn't need to be eliminated, and new experts in complex languages don't need to be brought in. Instead Corticon enables enterprises to separate the 'know' from the 'flow' of their complex decision making logic, future proofing the systems from which major competitive differentiators can be derived.

By externalizing the logic which evaluates driving compliance from the underlying data that the logic is evaluating, business analysts at logistics-intensive companies maintain rules without needing to know all of the complexities of the ever-changing fleet data. Because we can incorporate any number of data sources, we can maintain the regulatory thresholds separate from the logic which pulls in the relevant records, keeping distinct areas of the decision in quickly adaptable modules.

➾ Download project from Github

➾ Learn more about Corticon