2 Replies Latest reply: Dec 16, 2013 4:58 PM by Mark campbell RSS

    Help designing a link table

      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

        • Re: Help designing a link table

          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;

            • Re: Help designing a link table

              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.