Problem with Keys among 4 tables

[rubinsky]

I have 4 tables:

Budgets

Amounts

Stats

AccountComponents

Budgets, Amounts, and Stats each have the same fields AccountID, MonthDT, YearDT, and AccountIDKey

AccountIDKey  is a concatenation of AccountID, MonthDT, and YearDT. 

AccountComponets has AccountID as it's field.  How can I keep Budgets, Amounts, and Stats "linked" together via AccountIDKey and link AccountCompoents to those three tables by AccountID?

-jdr

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Think of something like this

Facts:
LOAD *,
     'Budget' AS Fact_Type
FROM BudgetsTable;
CONCATENATE (Facts) LOAD *,
     'Stats' AS Fact_Type
FROM StatsTable;
CONCATENATE (Facts) LOAD *,
     'Amounts' AS Fact_Type
FROM AmountsTable;
Components:
LOAD *
FROM AccountComponentsTable;

So all facts (things that happen between dates, in this case: Budgets, Amounts, Stats) are in the same table, a fact table, and the AccountComponents is linked as a dimension table.

Hope that helps you on the development.

Miguel