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

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.

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.

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.

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

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.

Sure.