none
Multiple Fact Table Design RRS feed

  • Question

  • Hi,

    I am trying to create a cube (maybe 2) using trade data.

    Basically a trade will have many attributes such as trade date, fund, trader, who the trade was done with. It then will have measures such as amount of shares purchased and amount of cash paid for share

    I am happy with this and have managed to make a cube that can be sliced and diced with these attributes and aggregates the measures just fine.

    The complexity I have is that each of these trades can have multiple charges associated with it. For example 1 trade may have a commission and a stamp duty charge.

    So in a normalised database you have a charge table that has a foreign key to the trade table.

    My requirements are that users want to be able report on Trades as described above but they also want to report on the charges.

    They will want to run reports on the charges in relation to the trades

    For example

    -what is the Total charge by type / Total amount of shares brought for a particular fund in a particular month.

    -what commision has been paid for a particular fund in a particular month.

    My question is what would be the best design to incororate the charges into the trade data so that the above analysis can be done

    As this is a new project I can do work in either the ETL or cube design stage

    Many thanks for your input

    Thursday, April 12, 2012 1:04 PM

Answers

  • I think you will need a separate fact table for Trades and Charges, as you have stated that you can have many charges per trade.  These two tables will have similar, but not exactly identical grains.  Based on your scenario above I would imagine the following fact table design;

    Trade Fact

    Dimensional Keys: Date, Fund, Trader, Client

    Facts: NumSharesPurchased, AmountPerShare

    TradeChargeFact

    Dimensional Keys: Date, Fund, Trader, Client, ChargeType

    Facts: ChargeAmount

    This would of course require the creation of a type 1 dimension (well, I assume you would not need a true SCD) for Charge Type.  This design will satisfy all of the requirements you posted above.  Simply create an SSAS Measure group on top of each.

    In the relational database you could store the PK for Trade in the TradeChargeFact as a degenerate dimension if you wish, but unless specifically requested by your users pushing up operational identifiers to the cube is not something I try to do.

    -Chris

    • Proposed as answer by Michael Riemer Thursday, April 12, 2012 2:01 PM
    • Marked as answer by KiwiInLondon Thursday, April 12, 2012 5:24 PM
    Thursday, April 12, 2012 1:58 PM

All replies

  • I think you will need a separate fact table for Trades and Charges, as you have stated that you can have many charges per trade.  These two tables will have similar, but not exactly identical grains.  Based on your scenario above I would imagine the following fact table design;

    Trade Fact

    Dimensional Keys: Date, Fund, Trader, Client

    Facts: NumSharesPurchased, AmountPerShare

    TradeChargeFact

    Dimensional Keys: Date, Fund, Trader, Client, ChargeType

    Facts: ChargeAmount

    This would of course require the creation of a type 1 dimension (well, I assume you would not need a true SCD) for Charge Type.  This design will satisfy all of the requirements you posted above.  Simply create an SSAS Measure group on top of each.

    In the relational database you could store the PK for Trade in the TradeChargeFact as a degenerate dimension if you wish, but unless specifically requested by your users pushing up operational identifiers to the cube is not something I try to do.

    -Chris

    • Proposed as answer by Michael Riemer Thursday, April 12, 2012 2:01 PM
    • Marked as answer by KiwiInLondon Thursday, April 12, 2012 5:24 PM
    Thursday, April 12, 2012 1:58 PM
  • In addition to the above, you could also have one measure group, with ChargeType including the two facts from Trade Fact if you wanted to, although this would mean a much bigger measure group, since you should always have those two measures.
    Thursday, April 12, 2012 2:02 PM
  • Thanks - Thats pretty much where I had got to.

    I presume there is not a way to specify all the dimensions of the tradesfact table on the tradechargesfact table given that they are essentially child facts?

    Thursday, April 12, 2012 5:24 PM