Synthetic Keys and Circular References

[#1]

I'm making myself crazy with circular references & synthetic keys during data load. I have several tables that share fields in common. What is the best way to maintain relationships while avoiding synthetic keys?

FiscalCalendar

PeriodStartDate

PeriodEndDate

Week

Quarter

Month

Year

Partner

PartnerName

PartnerChannel

PartnerRegion

PartnerCountry

…

Product

PartNumber

PartDescription

…

SalesIn

PartnerName

PartNumber

SalesDate

PeriodEndDate

SalesQty

SalesAmount

…

SalesOut

PartnerName

PartNumber

POSDate

PeriodEndDate

POSQty

POSAmt

…

Inventory

PartnerName

PartNumber

InventoryReportDate

PeriodEndDate

InvQty

InvAmt

…

[Anonymous]

Try to concatenate your transactions tables SalesIn, SalesOut, and Inventory, into one Facts table.

[#3]

I suppose that would do it, although that would lend another set of complexity on my dashboards vis-a-vis set analysis. Thanks anyway 🙂

[#4]

Create one fact table made of one key using autonuberhash128 feature and the fields.

Then in your related tables, just keep the autonumberhash128 key.

Rgds,

Sébastien.

[Anonymous]

QV application design with facts tables was in use long before set analysis. And even with the set analysis - it's not the end of the world...

[agsearle · Accepted solution]

Perhaps a bit simplistic, but if you renamed some of the fields to prevent the synthetic keys like below, and amended the load order slightly?

FiscalCalendar
PeriodStartDate
PeriodEndDate
Week
Quarter
Month
Year
...

SalesIn
PartnerName
PartNumber
SalesDate
PeriodEndDate
SalesQty
SalesAmount
…

Partner
PartnerName as PartnerNamePartner
PartnerChannel
PartnerRegion
PartnerCountry
…

Product
PartNumber
PartDescription
…

SalesOut
PartnerName
PartNumber as PartNumberSalesOut
POSDate
PeriodEndDate as PeriodEndDateSalesOut
POSQty
POSAmt
…

Inventory
PartnerName
PartNumber as PartNumberInventory
InventoryReportDate
PeriodEndDate as PeriodEndDateInventory
InvQty
InvAmt
…

[#7]

Here's my own fix using a link table. First I started out giving all fields a unique name (omitted this step in my example).

Next, I create the complex key (shown below). Finally, I create my link table (shown below). Thanks to all for their suggestions and support!

// Add keys to fact tables
JOIN (SalesIn) LOAD
SIPartnerName,
SIPartNumber,
SIPeriodEndDate,
SIPartnerName & '|' & SIPartNumber & '|' & SIPeriodEndDate as Key
RESIDENT
SalesIn;

JOIN (POS) LOAD
POSPartnerName,
POSPartNumber,
POSPeriodEndDate,
POSPartnerName & '|' & POSPartNumber & '|' & POSPeriodEndDate as Key
RESIDENT
POS;

JOIN (Inventory) LOAD
InvPartnerName,
InvPartNumber,
InvPeriodEndDate,
InvPartnerName & '|' & InvPartNumber & '|' & InvPeriodEndDate as Key
RESIDENT
Inventory;

// =========================================================================

// Create Link table
Link:
LOAD DISTINCT
Key,
SIPartnerName & '|' & SIPartNumber & '|' & SIPeriodYr & '|' & SIPeriodMonth as MonthKey,
SIPartnerName & '|' & SIPartNumber & '|' & SIPeriodQtr as QtrKey,
SIPartnerName & '|' & SIPeriodEndDate as TrackerKey,
SIPartnerName as PartnerName,
SIPartNumber as PartNumber,
SIPeriodEndDate as PeriodEndDate
RESIDENT
SalesIn;

CONCATENATE (Link) LOAD DISTINCT
Key,
POSPartnerName & '|' & POSPartNumber & '|' & POSPeriodYr & '|' & POSPeriodMonth as MonthKey,
POSPartnerName & '|' & POSPartNumber & '|' & POSPeriodQtr as QtrKey,
POSPartnerName & '|' & POSPeriodEndDate as TrackerKey,
POSPartnerName as PartnerName,
POSPartNumber as PartNumber,
POSPeriodEndDate as PeriodEndDate
RESIDENT
POS;

CONCATENATE (Link) LOAD DISTINCT
Key,
InvPartnerName & '|' & InvPartNumber & '|' & InvPeriodYr & '|' & InvPeriodMonth as MonthKey,
InvPartnerName & '|' & InvPartNumber & '|' & InvPeriodQtr as QtrKey,
InvPartnerName & '|' & InvPeriodEndDate as TrackerKey,
InvPartnerName as PartnerName,
InvPartNumber as PartNumber,
InvPeriodEndDate as PeriodEndDate
RESIDENT
Inventory;


CONCATENATE (Link) LOAD DISTINCT
Null() & '|' & PartNumber & '|' & Null() as Key,
Null() & '|' & PartNumber & '|' & Null() as MonthKey,
Null() & '|' & PartNumber & '|' & Null() as QtrKey,
Null() & '|' & Null() as TrackerKey,
Null() as PartnerName,
PartNumber,
Null() as PeriodEndDate
RESIDENT
Product;

CONCATENATE (Link) LOAD DISTINCT
PartnerName & '|' & Null() & '|' & Null() as Key,
PartnerName & '|' & Null() & '|' & Null() as MonthKey,
PartnerName & '|' & Null() & '|' & Null() as QtrKey,
PartnerName & '|' & Null() as TrackerKey,
PartnerName,
Null() as PartNumber,
Null() as PeriodEndDate
RESIDENT
Partner;

CONCATENATE (Link) LOAD DISTINCT
Null() & '|' & Null() & '|' & PeriodEndDate as Key,
Null() & '|' & Null() & '|' & PeriodMonth as MonthKey,
Null() & '|' & Null() & '|' & PeriodQtr as QtrKey,
Null() & '|' & Null() as TrackerKey,
Null() as PartnerName,
Null() as PartNumber,
PeriodEndDate
RESIDENT
FiscalCalendar;