Linking Facts tables

I'm pretty new to Dimensional Modeling and working on trying to build a Data Mart, in my domain we have companies which have  measures like PE, market cap etc, they change all the time but for our purpose capturing end of day is good enough, companies are also tied to Industries, sectors, countries, regions, so as far as companies goes, I have:

CompanyFact (contains fields like PE, Market Cap, CompanyDimId, RegionDimId, SectorDimId, etc)
CompanyDim (contains stuff that just doesn't change much like Name, Symbol, etc)
RegionDim (contains region and country)
SectorDim (contains Sector, industry)

Also we have portfolios which has measures like position value and other items, so we have this modeled like:

PositionFact (which contains PositionValue and other items, PorfolioDimId)
PortfolioDim (which contains portfolio items)

With this in mind, we tend to have requests like give me the total value for all the positions in a portfolio broken down by market cap or sector or many other items in the CompanyFact table.

The main question is if I should have a PositionFact pointing to the CompanyFact directly or the CompanyDim, not really finding much in terms of linking facts tables is good or bad, anyone has any ideas or suggestions on this?

Any help would be appreciated!
Thanks

Company is a dimension, not a fact. I think that solves all your issues here. :-)

I'm a bit confused by the terminology.

I'm going to assume what you are really talking about is securities, not companies. The measures related to a security change constantly, this is typically represented by an end-of-day snapshot fact with date, price (open/high/low/close), PE at close, cap, shares traded, outstanding, etc…

But, such a structure is awkward to use if all you are trying to do is provide current state information. So what typically happens is the information is stored both as a snapshot fact (for historical use) and on the securities dimension (type 1). The market close load of the snapshot will also update similar values in the securities dimension to make them easily available for portfolio reporting of the current state.

Let me clarify the terminology, open, high, etc are a security level, Google has 2 common stocks for example, both have different open, high, etc, also companies can issue bonds and other financial instruments, so companies do have multiple securities they can isse.

Then there is data that are tracked at a company level like market cap, Earnings, Sales, Income, etc, they apply at the company level not the actual security so there is a one to many from company to security.

So in this model for example:


Let say I need to breakdown the PositionMarketValue form the PositionFact by marketCap, which means adding PositionMarketValue and figure the % that falls in the bucket defined below, but to do that I need to know the market value for the company that issued the position.

Market Cap
< .05 bill 30%
.05 bill to 10 bill 60%
> 10 bill 10%

So here are the options I can see:
1. store market cap in the company dim which bothers me as Market Cap smells like a fact to me more than a dimension (I'm new at this and my brain is probably thinking normalize!)
2. Store  Market Cap in the CompanyFacts, which causes this to snowflake a bit, having to do 2 joins to get to market cap
3.  Sotre Market Cap in Company Facts and Link PositionFact and CompanyFacts by storing CompanyFactsId in PositionFact (which is sort of the original question)  so now  I can get market cap it with one join.

Hope this helps understand the question a bit better.

Thanks a lot for your help!!

Throw away company fact. Put all those columns in Company Dim. Take Industry Dim and collapse into Company Dim. You'll have a nice hierarchy now with no snowflaking. That leaves you with two facts, your daily pricing snapshot as ngalemmo recommended and your position fact which I assume takes into account the number of shares owned by someone at the end of day (sounds like a daily snapshot as well).

Given market cap is changing daily depending on the stock price, you do want to have something like 'Companyfact' which tracks the overall metrics e.g. market cap and others, then you could bring only today's market cap into the company dimension as a type 1 attribute, and maybe put the classification as a type 2 attribute (as it will change more slowly). Make sure that you call the different versions of market cap different names.

Ok, I'm going to play around with some of your suggestions, thanks a lot.