What makes a "Perfect Key"?

[mike_garcia]

Hello,

Going through one of the Demos, I stumbled upon a concept that I'm curious to know about: 'Perfect Keys'.

So far, I have seen three types of keys: Primary Key, Perfect Key and Key. What are the differences among those? Is there any other type? Does a Perfect Key make a model more efficient?

This is a screenshot showing the 'Perfect Key' Legend:

and this one is showing only 'Key':

Thanks in advance for sharing your knowledge about this.

Mike.

[johnw]

While I don't actually know the answers, with a brief glance at some sample tables, it looks like they're defined as follows:

Primary Perfect
Key Key Key
Y Y Y Links two or more tables together?
Y Y Corresponds to one and only one row of the table?
Y Every value of key field appears in the table?

[#3]

From the Book QlikView 11 for Developers:

"The name of the field. Optionally, if the field is a key field, a qualifier is shown enclosed in square brackets. This qualifier indicates the following levels of key quality:

[Perfect Key] indicates that every row contains a key value, and that all of these key values are unique. At the same time, the field's subset ratio is 100 percent. This qualifier should be seen in dimension tables, where every key should uniquely identify a single record.

[Primary Key] indicates that all key values are unique, but not every row contains a key value or the field's subset ratio is less  than 100 percent

[Key] indicates that the key is not unique. This qualifier is usually seen in fact tables, where the same dimension value may be associated with many different facts."

[Anonymous]

Perfect Key Vs Primary key:

.

The [Perfect Key] is a bit more: it is a [Primary Key] (unique) with no "orphans".

Let me clarify this with 2 examples:

Example 1)

InvoiceDetails:

LOAD *

INLINE [

InvoiceID, InvoiceLine, ProductID, Quantity, Amount

Inv001,1,PR01,10,50

Inv001,2,PR02,10,40

Inv002,1,PR01,30,150

Inv002,2,PR03,10,800

];

Invoices:

LOAD *

INLINE [

InvoiceID, ClientID

Inv001,CL0001

Inv002,CL0002

];

In this example, InvoiceID is [Key] in InvoiceDetails and [Perfect Key] in Invoices: it is unique and it has no orphans.

Example 2)

InvoiceDetails:

LOAD *

INLINE [

InvoiceID, InvoiceLine, ProductID, Quantity, Amount

Inv001,1,PR01,10,50

Inv001,2,PR02,10,40

Inv002,1,PR01,30,150

Inv002,2,PR03,10,800

Inv003,1,PR01,10,50

];

Invoices:

LOAD *

INLINE [

InvoiceID, ClientID

Inv001,CL0001

Inv002,CL0002

];

In this example, InvoiceID is [Key] in InvoiceDetails and [Primary Key] in Invoices. There is a value in InvoiceDetails, Inv003, that does not find a corresponding value in the Invoices table. This is called a "orphan". For this reason InvoiceID is not anymore perfect, but it is still primary, because it is unique.

If you copy-paste the example you can verify it

BR,

Chinna

[alexdataiq]

This is kind of funny, because Miguel [original poster] is the author of that book!

Cheers