Flattening effects the selection of PK field(s). An unflattened IDT created from a 1:M relationship normally has a compound primary key constructed from a key field from the 1 table concatenated with a key field from the M table to ensure a unique PK. For example,

Assume the following rows were created by denormalization:

Emp1 Addr1

Emp1 Addr2

Emp1 Addr3

If these rows are flattened into a single row, Emp could serve as a unique primary key:

Emp1 Addr1 Addr2 Addr3

If the number of address field repeats in the flattened table was set to [2], the flattening would produce two rows (due to the overflow), rendering Emp unsuitable to be a unique PK.

Emp1 Addr1 Addr2

Emp1 Addr3

Unless you can guarantee that the number of repeating fields are adequate to hold all occurrences of the repeating data, you should define the PK with a Sync Level of

Allow_Duplicate_PK

. Flattening will improve synchronization performance (even when duplicates are allowed) by reducing the number of rows processed for a particular key value.

Matching on multiple repeating fields can be problematic if there are overflow records. For example, suppose we have one Identity (ID= I1). This identity has three alias names (N1, N2 and N3) and three addresses (A1, A2 and A3).

Full denormalization (without flattening) will produce 9 rows:

If you define the IDT layout as

Name [3]

and

Addr [3]

, you will get one flattened row:

I1 N1 N2 N3 A1 A2 A3

If you define the IDT as

Name [2]

and

Addr [2]

you will get two rows due to the overflow:

I1 N1 N2 A1 A2
I1 N3 A3

The above layout is a problem if you search on

N3

and match on

N3 + A1

because those values are in different rows. The solution is an IDT layout with

ID

,

Name

,

Addr [3]

which provides:

I1 N1 A1 A2 A3
I1 N2 A1 A2 A3
I1 N3 A1 A2 A3

This is still an improvement over 9 rows. When only one matching field repeats, it is also valid to use overflows:

ID

,

Name

,

Addr [2]

which provides:

So the bottom line is that you have to design your IDT carefully. Do not attempt to match two repeating fields unless you can guarantee that all occurrences will be in one flattened row.