2012年4月12日 下午 01:04
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
-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
2012年4月12日 下午 01:58
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;
Dimensional Keys: Date, Fund, Trader, Client
Facts: NumSharesPurchased, AmountPerShare
Dimensional Keys: Date, Fund, Trader, Client, ChargeType
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.
2012年4月12日 下午 02:02In 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.
2012年4月12日 下午 05:24
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?