Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.