Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Many-to-Many Design Question

Unanswered Many-to-Many Design Question

  • Monday, March 18, 2013 10:09 AM
     
     
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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 BillAnton Monday, March 18, 2013 11:20 AM
    •  
     
  • Tuesday, March 19, 2013 9:23 AM
     
     
    Sign In to Vote
    0

    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.