Model Customer Transactions with variable tax counts and amounts

[pcs]

Hello,

I am trying to create a dimensional model for the customer sales transaction business process for a regional grocery chain. The business requirements specify we must be able to recreate the accurate transaction level (aggregate) totals as well as slice and dice on detail level data.

Best practice tells me to allocate the transaction header level measures down to the atomic transaction detail level; however I am having trouble with taxes.

All tax amounts are stored in the transaction header record and each transaction may get a variable number and amount of taxes applied.

Transaction header:
itemCount = 3
priceTotal = 30.00
taxTotalA = $2.00
taxTotalB = $1.00
GrandTotal = $33.00
…

TransactionLineItems
Item1Price $10.00 taxTypeA…
Item2Price $10.00 taxTypeB…
Item3Price $10.00 taxTypeA&B…

Taxes are configured at each store –
Store #1 might set taxTypeA = 10% state sales tax and taxTypeB = 5% county sales tax
Store #2 could set taxTypeA = 2.5% city sales tax and taxTypeB = 6.5% state sales tax. We do not have a source for this store level detail however; all we see is the Transaction Header Total Tax Amounts for each tax type.

I could create a fact table with the following setup:
Item1 Price $10.00 AllocTaxAmt $1.00
Item2 Price $10.00 AllocTaxAmt $0.50
Item3 Price $10.00 AllocTaxAmt $1.50
But it does not afford insight into the tax breakout

I could create the facts like this:
Item1 Price $10.00 AllocTaxAAmt $1.00 AllocTaxBAmt $0.00
Item2 Price $10.00 AllocTaxAAmt $0.00 AllocTaxBAmt $0.50
Item3 Price $10.00 AllocTaxAAmt $1.00 AllocTaxBAmt $0.50
But this offers no real descriptors for the Tax Types, and if we encounter a new tax type we have to change the fact table specification

Another thought is to create facts like this:
Item1 Price $10.00 TaxAmt $0.00 TaxType -1
Item2 Price $10.00 TaxAmt $0.00 TaxType -1
Item3 Price $10.00 TaxAmt $0.00 TaxType -1
-1 Price $0.00 TaxAmt $2.00 TaxTypeA
-1 Price $0.00 TaxAmt $1.00 TaxTypeB
Seems to meet the need and is flexible but this is mixing grains in the fact table and wastes space (item facts don’t have tax amounts, tax amounts don’t have item amounts)

Maybe even enhance the first proposed model with a tax group bridge table
Item1 Price $10.00 AllocTaxAmt $1.00 taxGroupKey 1
Item2 Price $10.00 AllocTaxAmt $0.50 taxGroupKey 2
Item3 Price $10.00 AllocTaxAmt $1.50 taxGroupKey 3

taxGroupBridge
taxGroupKey 1 TaxKey 1 Weight 100%
taxGroupKey 2 TaxKey 2 Weight 100%
taxGroupKey 3 TaxKey 1 Weight 67%
taxGroupKey 3 TaxKey 2 Weight 33%

TaxDim
TaxKey1 TaxTypeA
TaxKey2 TaxTypeB

I’m looking for the best way to resolve this and would appreciate guidance.

Thank you,

[Jeff Smith]

How about a fact table that had a measure dimension? Say an item had a cost, a city tax and a sales tax. This "transaction" would have 3 records, 1 for each amount. The Measure dimension would indicate that the record was for the price of the good and for which tax. You could have different levels to group the vaiours taxes into a single "Tax". This design would also enable you to list coupons or discounts. Say an item's price was $10.00 and the customer had a 10% discount coupon or was able to get 10% because of a frequent shopper card, plus the state and local taxes. This one transaction would have 4 records.

[ngalemmo]

Assuming there may be multiple combinations of rates applied given a mix of items, and that the register log indicates which rate applies to each line, then you can capture tax at the line using a multivalued dimension for the tax authorities.

Every unique combination of authority (state, county. parish, township, municipality and any other government entity out to grab cash) and rate would be a tax authority group. You carry the group key on the line. There is then a bridge table containing group key, tax authority key and rate. This would allow you to report sales back to any authority with the approriate tax split. As rates change, you would create new tax groups so you retain historical perspective as to what was collected for who.

The only issue is allocating the header level tax amounts back to the lines. There are a couple of ways to go, neither is particularly difficult.

[pcs]

Thank you both Jeff and Nick - for the response...
I considered both alternatives - I think Jeff's suggestion works and is easier to implement, but am concerned about increasing the fact row count - already about 6-10m per day. (we have a lot of line item and txn level discounts/coupons/promotions to apply as well )
Nick's suggestion might work here and does not multiply the number of rows, but adds to query and etl complexity a bit. Going this route I could conceivably include other multivalued dimensions as well...
I appreciate both suggestions and will discuss them with my team –
thank you again

[Sponsored content]