Working with EBT

[FootyRef]

I work for a government program in Ohio that is going to start issuing EBT cards to participants.  The card will be loaded with specific items that participant is allowed to purchase in a month as well as the quantity of each of those items.  Those items will be classified by category (Example: Milk - whole) and subcategory (Example: Cow's milk - generic).  Participants are issued the card, they can go to a vendor to redeem the items on the card and the vendor submits a claim to the agency for the items purchased with the card.  There can be multiple participants on the same card, such as when there are two or more in the household receiving benefits rather than each getting their own card.

I see those three events as being separate processes thus getting their own fact - Participant Benefits Issuance Fact, Participant Benefits Redemption Fact and Vendor (Store) Benefits Claim Fact.  Each fact would be at the item grain with the benefits issuance fact being down to the participant and food item grain so it can be reported what each participant was issued.  When items are redeemed on a card, it is not know for which participant they were redeemed.  For example, there are two participants on the card and one of them was issued two gallons of milk for the month and the other participant gets issued three.  When the shopping is done, two gallons of milk are debited from the card but it is not possible to determine from which participants those two gallons of milk were debited as they are all rolled up on the card to a total of five gallons of milk.  However, the redemption of the gallons of milk and the claim the vendor makes to get paid for them can be tied together because a UPC was purchased and the vendor wants to get paid based on that UPC.  I guess it is possible to bring together the participant redemption and vendor claim facts, but I was thinking of keeping them separate since they are processes involving two different entities.

Does anyone have any experience or suggestions how this might be modeled?  At a simplistic level, each fact joins to a conformed food item table and date table (with different views for Issue Date, Redemption Date, Vendor Claim Date, Vendor Payment Date, etc.).  The issuance fact is joined to a clinic dimension table which gives the characteristics of the clinic that issued the card and also joined to what I call a participant bridge which has all of the surrogate keys that join to dimension tables that define the characteristics of the participant when they were issued the benefits (dimension tables: name, address, demographics, dates such as certified, birth, last pregnancy, etc.).  The other two facts are joined to what I call a vendor bridge table which has all of the surrogate keys that join to dimension tables that define the characteristics of the store when the participant redeemed there as well as when the vendor was paid (dimension tables: store address, primary entity contact, date store was contracted to be a vendor, etc.).

I know this is long and I could go offline with someone who would like to discuss their ideas, but hopefully some folks could benefit from this discussion and we all could learn from some who have been successful at it.  There are more questions about the process, but I wanted to start here.  I am not sure I really need an aggregate table which rolls up all of the items and quantities to a card because if the dimensional model is done correctly, and the tables are adequately indexed, the queries should perform very well.

[ngalemmo]

I have no experience with EBT processing, but from your description, the 3 fact model makes sense.

One thing you may want to consider is to include the issuing clinic and vendor dimension foreign keys in the redemption fact, this could simplify querying combinations of facts.

[FootyRef]

Thanks for taking the time to respond. I have not included the clinic dimension in the redemption fact because no information comes in from the stores that ties back to the clinic that issued the benefits. The only information that could tie back to the participant is the card number that was loaded with benefits, but that is a degenerate dimension in each of the fact tables.

I do include the vendor dimension key in the Redemption fact as I do receive information in that data about where the participant shopped. As stated previously "The other two facts are joined to what I call a vendor bridge table which has all of the surrogate keys that join to dimension tables that define the characteristics of the store."

[Sponsored content]