Composite Key

[nihhalmca]

Hi All,

Which scenarios we go for 'composite key' except avoiding synthetic keys. can you give any example.

My question is both values (Year and Product) should be same in both key values to get data on selections.

For Example:

Table1:

LOAD

   Year&Product AS Key,

   *;

LOAD * INLINE [

    Year, Product, Sales

    2011, A, 10

    2012, B, 20

    2013, C, 30  

];

Table2:

LOAD

  Year&Product AS Key,

  *;

LOAD * INLINE [

    Year, Product, Rev

    2011, A, 1000

    2012, B, 2000

    2013, X ,3000

];

Thanks

Nihhal.

[nihhalmca]

Thanks for writing shiva.

There is no customer id just i given example.

I have confusion which scenarios we go for 'composite key' except avoiding synthetic keys. can you give any example.

Thank you.

Nihhal.

[buzzy996]

as u said,for avoiding synthetic keys u can use composite keys as technique.

for example ur have 3 tables,

table1 having 10 fields,out of 10 fields 5 are key fields

table2 having 15 fields,out of 15 fields 7 are key fields

table3 having 20 fields,out of 20 fields 3 are key fields

but,we have to map all these tables,here some of the key fields in table1 will match with tabl2 key fields and some of the key fields in table2 will match with table3 keys fields.

so,this is the case u have to create multiple key field by combining those fields from corresponding table.

note: pls mark whichever post helps to as helpful.

[Peter_Cammaert · Accepted solution]

No, you're assuming that keys can do partial matching which they cannot. If all customer IDs have values, the keys will never match. and the two tables will be in separate universes (that is if you rename at least one set of
Product & Year fields, otherwise you'll have a syntehtic key as well).

If you want a simple solution, LEFT JOIN the second table to the first. Rows that have a Sales amount but no Rev value will have Null in the Rev field. Rows that have a Rev value but no Sales amount will have Null in the Sales field. Rows that have both will show both when you select the corresponding Year (& Product).

If you want to forcibly link with a key, create identical keys in both tables and rename at least one set of Product/Year fields. You will now have a problem with Dimension filtering because the only Product field left will only show values from one table. That's why you should extract in a second step all Product/Year pairs from both tables into a third table, together with their key values. Then drop both Product and Year fields from the original tables, as they will become Dimensions in the third table.

Peter

[pratap6699]

Table1:

LOAD

   Year&Product AS Key,

   *;

LOAD * INLINE [

    Year, Product, Sales

    2011, A, 10

    2012, B, 20

    2013, C, 30 

];

Table2:

LOAD

  Year&Product AS Key,

  *;

LOAD * INLINE [

    Year, Product, Rev

    2011, A, 1000

    2012, B, 2000

    2013, X ,3000

];

Bridgetable:

LOAD Key,

Year,

Product

Resident of Table1;

LOAD Key,

Year,

Product

Resident of Table2;

Drop fields Year,Product from Table1;

Drop fields Year,Product from Table2;

[nihhalmca]

Thanks Peter.