Should I concatenate very different Fact tables?

[Anonymous]

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

[nizamsha]

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

[Anonymous]

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?

[Anonymous]

Hi,

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

HTH

Ravi N.

[#5]

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.