Help designing a link table

[#1]

I have an odd structure and I am trying to figure out how to properly link up with multiple fields as the key in multiple tables.  any help/suggestions would be appreciated.

One could easily argue over what the fact and dimensions are as it changes depending on what question the users are asking, but below is my best explanation with their key fields.  Concatenating the key fields of each table will give a unique key for that table.  The Performance table may have more dates than either of the fact tables, the SecurityStatic table will have more distinct SecurityIDs than the fact tables, yet there are cases where I would want to treat these dimension tables as facts and have all of the data, not just those that have related data in either of the fact tables.

Fact Tables:

Holdings

     BusinessDate; SecurityID; SecSourceID; PortfolioNo

Transactions

     BusinessDate; SecurityID; SecSourceID; PortfolioNo

Dimensions:

Analytics

     BusinessDate; SecurityID; SecSourceID

SecurityStatic

     SecurityID; SecSourceID

PortfolioInfo

     SecSourceID; PortfolioNo

Performance

     BusinessDate; SecSourceID; PortfolioNo

Calendar

     BusinessDate

[#2]

Hello,

I think you don't need a link table, try to concatenate Holdings Table and Transactions Table and the association with Dimension Tables will be natural. The resultant script can be something like this:

FactTable:

//Holdings

Load

*,

'Holdings' as Source

from Holdings.qvd;

Concatenate(FactTable)

//Transactions

Load

*,

'Transaction' as Source

from Transaction.qvd;

[#3]

I may eventually join the two, but for the sake of argument, let's assume that doesn't work with the analyses the business needs to perform.  Even if we were to assume that, I have key fields in the dimensions, but I have dimensions that have keys that aren't avialable in the fact table.  My goal is to have 1 single date field that will work across the application to make the user experience more consistent; however, there are different amounts of dates, and If i link the calendar to the holding table, i can't report on the data in the performance table that doesn't have a date in the holding table.