Concatenation Multiple fact tables different dimensions

[ivandrago]

Hi,

At the moment I have three fact tables where two of the three would have some kind of relationship i.e the same CustomerID & DateEntered, I would also have a field called [Record Type] which would differentiate what each fact table is as per below:

Fact:

Load

     'Sales' as [Record Type]

     ,OrderId as [PK]

     ,CustomerID

     ,DateEntered

     ,InvoiceDate

     ,Product

     ,Amount as Amount

From [..\Sales.qvd] (QVD);

CONCATENATE

Load

     'Quotes' as [Record Type]

     ,QuoteId as [PK]

     ,CustomerID

     ,DateEntered

     ,LongDescription

     ,QuoteAmount as Amount

From [..\Quotes.qvd] (QVD);

CONCATENATE

Load

     'Schedules' as [Record Type]

     ,SchedueId &'_'&SchedueVisitNo as [PK]

     ,PlaceId

     ,ScheduleDateEntered as DateEntered

     ,SchduleName as EngineerName

From [..\Schdeules.qvd] (QVD)

The Sales and Quotes QVD's come from the same ERP system, the Schedules QVD is coming from a different Application altogether and has no relationship, I know this is a case of a Data Island issue but the end user wants it to be all on one Document.

My question is, should I have the same amount of field names on each QVD and put a default value i.e 'UNKNOWN' if it is a Text Field and 0 if it value? Or does it matter if there are more fields on one qvd than the other qvd when CONCATENATE the qvd's together?

Thanks

[devarasu07]

Hi,

It's not required to keep same amount of field names in each qvd.

You may try like below,  is (SchedueId & '|' & SchedueVisitNo  = CustomerID ?)

Fact:

Load

     'Sales' as [Record Type],

     OrderId as [PK],

     CustomerID,

     DateEntered,

     InvoiceDate,

     Product,

     CustomerID & '|' & Date(DateEntered) & '|' & 'Sales' as CustomerID_DateEntered_Type,

    Amount as Amount

From [..\Sales.qvd] (QVD);

CONCATENATE (Fact)

Load

     'Quotes' as [Record Type],

     QuoteId as [PK],

     CustomerID,

     DateEntered,

     LongDescription,

     CustomerID & '|' & Date(DateEntered) & '|' & 'Quotes' as CustomerID_DateEntered_Type,

     QuoteAmount as Amount

From [..\Quotes.qvd] (QVD);

CONCATENATE (Fact)

Load Schedules' as [Record Type],

     SchedueId &'_'&SchedueVisitNo as [PK],

     PlaceId,

     ScheduleDateEntered as DateEntered,

    SchedueId & '|' & SchedueVisitNo & '|' & Date(Floor(ScheduleDateEntered)) & '|' & 'Schedules' as CustomerID_DateEntered_Type,

     SchduleName as EngineerName

From [..\Schdeules.qvd] (QVD)

Thanks,

Deva

[jonathandienst]

It is not necessary to have the same field names in all the LOAD statements. For each load, the missing field will be automatically loaded with null values. This normal for a fact table that contains more than one type of value or varying granularity levels (eg budgets values and actual values).