I can't figure out how to model this M:M relationship despite reading a dozen articles.

View previous topic View next topic Go down

I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  Solomon Levy on Mon Oct 17, 2011 10:17 am

I have a basic star schema representing stock trading activity on an exchange.

Each row in the Trade fact table represents a single trade. It's dimensions include Date, Time, Customer, Symbol, Market, Order Type, Market Index. The measures are things such as share size, fee paid, notional value. So far so good.

What I'm having trouble with is the Market Index dimension. The problem is, a symbol may belong to multiple indices, and a market index has multiple symbols. I read about bridge tables, factless fact tables, boolean columns, et al, but I can't figure out which path to take.

I'm tempted to also make Market Index a separate fact table, but I need the capability to drill down on it. For example, if I see $1000 was traded on Market Index ABC. My next question would be, "okay, drill down and show me the symbols making up the index that day and how much of each was traded".

This is my first crack at dimensional modelling so I hope I'm being clear. Thank you for reading.

Solomon Levy

Posts : 2
Join date : 2011-10-17

View user profile

Back to top Go down

Re: I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  Jeff Smith on Mon Oct 17, 2011 2:39 pm

I think it's a seperate fact table or maybe an aggregate table.

Each Trade has a Symbol and Index. You want to see the sum of all trades for a particular symbol or index - that's an aggregate table.

Instead of srilling down - you are really drilling up.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  ngalemmo on Mon Oct 17, 2011 3:40 pm

I would use a bridge, it gives you more flexibility.

I am assuming you want to use it with the trade facts. It would allow you to identify all trades relating to an index. It also allows you to do some interesting things, such as looking for a symbol that exists in a group of indexes. Also, if you decide to aggregate the trade data by CUSIP or symbol per day, you could apply the bridge to the aggregate table as well.


Last edited by ngalemmo on Mon Oct 17, 2011 5:26 pm; edited 1 time in total
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  SV on Mon Oct 17, 2011 5:08 pm

Agree. Bridge table is one of the right ways to go. Alternatively, if the number of indices is less, I would explore boolean way of using indicators and create a index bundle per symbol, and use this bundle key at fact - SV

SV

Posts : 2
Join date : 2011-10-17

View user profile

Back to top Go down

Re: I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  Solomon Levy on Tue Oct 18, 2011 8:12 am

Okay, after reading the feedback here and especially this post, I think I get it.
http://forum.kimballgroup.com/t1356-weighting-factor-in-bridge-table

I have an idea on how to implement the bridge table, but I don't think weighting should be included. Consider this scenario:
1) There is a stock with symbol JNJ.
2) It is part of two indices: ABC and XYZ.
3) A single trade was made today on JNJ for $1.

In this case, unlike the typical medical diagnosis example, it's not that one part of the $1 belongs to ABC and another part to XYZ. $1 was indeed traded on both ABC and XYZ indices. Granted, totaling the indices would give a false impression, but adding index trading values together makes no business sense anyway.

Can I still use a bridge table without weightings?

Thank you.

Solomon Levy

Posts : 2
Join date : 2011-10-17

View user profile

Back to top Go down

Re: I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  ngalemmo on Tue Oct 18, 2011 10:14 am

Sure.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I can't figure out how to model this M:M relationship despite reading a dozen articles.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum