Create datamodel for tracking brokers order book/transactions

[Anonymous]

Hi, 

I am in the process of building a power bi report to analyze the various transactions / order book of a member. The member can carry out several transactions on behalf of one or more customers over a period. For a transaction there are necessarily two orders: a buy and a sale knowing that the member can be a buyer and a seller.

I'm trying to find the best way to design it. The following analysis need to be performed:

• Volume analysis per Member
• Volume Analysis per Member/Customer and ORDERTYPE
• PRICE evolution, etc..

[amitchandak]

@Anonymous , order and transaction should have 1-M relationship (if possible). Create a date table and join with transaction date

[Anonymous]

@amitchandak thank you for your feedback but I already tried this solution and the way of filtering data doesn't work perfectly. I think I might need a factless table. A transaction means two orders (Ask and bid) and for an orderID we can have M TransactionID. T join them we not only need the transactionDate but also an ExecutionID, IdSecurity and the OrderID that should correspond to Transaction.OrderID_ASK or Transaction.OrderID_BID.