4 Replies Latest reply: May 22, 2014 10:14 AM by marcelo kupfer RSS

    Should I concatenate very different Fact tables?

    Darrin Pilkington

      I have a dashboard that has very different data that will be aggregated.  The question is, at what point do you go from Concatenate to Link Table?

      As you can see below I have a number of Sales, Counters and Durations to aggregate along with a number of different dimensions that only relate to the individual tables.

      There are also a number of information columns related to each table.

       

      Sample of the data structure is below.

       

      Orders:

      AgentID   

      Date   

      Sales($)    

      SaleCounter    

      OrderID    

      CustomerID    

      AddlInfo1    

      AddlInfo2    

      AddlInfo3

       

      AgentShift:

      AgentID   

      Date   

      ShiftDuration   

      BreaksDuration   

      ShiftCode

       

      CallStats:

      AgentID   

      Date   

      Group

      Queue

      CallCounter   

      CallDuration   

      ACDCount   

      NonACDCount   

      HoldTime

      HoldCount

      AddlCounter1

      AddlCounter2

      AddlCounter3

       

      Disconnects:

      AgentID

      Date

      DisconnectCounter

      DropReason

      DropComment

       

      Journals:

      AgentID

      Date

      JournalCounter

      IsSave

      JournalType

      JournalStatus

       

      Thank you

        • Re: Should I concatenate very different Fact tables?
          Nizam HM

          Create a key for  Agent and Date  Agent &'_' & Date as AgentDateKey for all Table

            • Re: Should I concatenate very different Fact tables?
              Darrin Pilkington

              Nizam,

              Thanks for the quick reply.  I completely understand how to create a link table and would have a Agents table along with a Calendar for those two fields.

              The question was about concatenating multiple fact tables that have many different values to Join.

               

               

               

              It would look something like

               

              Sales     SalesCounter     Calls     CallDuration     Disconnects     ShiftDuration     BreaksCounter     BreaksDuration

              $50          2

              $75          1

                                                      1          .05                  

                                                                                                2                   

                                                                                                                    .33                    3                         .12

               

               

              Would it make sense to do something like this.

               

              Date     AgentID   Type             Qty     Amount

              41804    01          Sales               1          $50

              41804    01          Sales               2          $75

              41804    01          Calls                1          .05

              41804    01          Disconnects     1          (null)

              41804    01          Shift                1          .33

              41804    01          Breaks            3          .12

               

              Or should I just do a Link Table to all the Fact Tables?

            • Re: Should I concatenate very different Fact tables?
              Ravichandra Nadiminti

              Hi,

               

              As Nijam said, u could join them all. Use AgentID and join the tables. That would make your work easier.

              HTH

              Ravi N.

              • Re: Should I concatenate very different Fact tables?

                I make many applications that concatenate differente fact table at the final application avoinding link tables and they run perfectly and also they are easier to understand for you and for your coleagues.