Many-to-Many Design Question

# Many-to-Many Design Question

• Monday, March 18, 2013 10:09 AM

I have the following model:

Charge <== ChargeCostCentre ==> CostCentre

A charge is a single charge to the business, as it stands this would be my fact table.  If the charge is a call charge then the call duration is presented.

Chage: idCharge, Quantity, Duration, Amount

A charge is normally allocated to a single cost centre but there are cases where there can be more than one cost centre.

CostCentre: idCostCentre, CostCentreName

Therefore a bridge table is used to allocate charges to multiple cost centres.  The bridge table holds a percentage split which determines the cost apportionment to a given cost centre.

ChargeCostCentre: idCharge, idCostCentre, Split(%)

There is likely to be 12 million charges per month and growing so the Charge and the ChargeCostCentre table can grow very large.

I am having difficulty modelling this in both Tabular and Multidimensional, any advice would be much appreciated.

### All Replies

• Monday, March 18, 2013 11:20 AM

Be sure and test performance in Tabular before getting too far along.  Many 2 Many is not supported natively in Tabular and depending on the complexity of the calculations required, performance may become an issue.  Best to catch that early.

with regards to Multidimensional, is there something specific you are struggling with?

also, check this out: msdn <-- keep in mind that you will only see 3 tables in the M2M scenario based on your model.  It will look something like this:

• Edited by Monday, March 18, 2013 11:20 AM
•
• Tuesday, March 19, 2013 9:23 AM

Thanks for the response Bill.

I suppose my questions is how should I model this.  Should I stick with the following:

Charge <== ChargeCostCentre ==> CostCentre

Charge: idCharge, Quantity, Duration, Amount

Or should I build the charge table with the splits already in in?  Such as

ChargeCC ==> CostCentre

ChargeCC: idCharge, idCostCentre, Split, Quantity, Duration, Amount

Also I am not sure how to deal with quantity and duration.  Should these also be split based on the allocation like the amount column?

Quantity = (Quantity * Split)

Duration = (Duration * Split)

Amount = (Amount * Split)

The problem is it doesnt make much sense to have a quantity of 0.54 for example.