Link Table with no composite keys

[DanaL]

Hi,

I have  seen some projects in which a link table is created without composite keys. It's not clear to me why.
In the  example of the script below, each fact table is connected to the link table by InvoiceID and OrderID.

KeyTable:
LOAD InvoiceID,
Agent,
Customer,
Product
Resident Invoices;

Concatenate (KeyTable)
LOAD OrderID,
Agent,
Customer,
Product
Resident Orders;

I wonder what is the impact on performance, and whether I should change it and add composite keys, or whether there are situations where composite keys are not necessary.

Thanks in advance!

[Or]

Different people have different takes on this. My own take is that if you know why the synthetic key is there, there's not much reason to go out of your way to replace it with a manually-created composite key, unless your dataset is huge.

Have a look at this post, as well as the post linked within: https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

[DanaL]

Hi Or,

Thanks for your reply.

There aren't any synthetic keys in the data model described above since the key table contains the common dimensions fields.

The question is whether the link table should be linked to the fact tables with composite keys instead of the current fields.

[Vikash]

Hi, 

You can use Autonumber() function to generate a numeric key in any table having more than 1 common fields. Like- Autonumber(ProductID&'-'& OrderID) 

It will generate a random numeric values. By this little change in the script we can remove synthetic and Composite key.

VK

[DanaL]

Thanks, Vikash.

But the current script doesn't have synthetic key issue. 

[Vikash]

 Hi Danal,

Can you please provide the qvw file(if possible)?

[Or]

This is a question that is specific to your data model, not something generic, so there's no single answer. If memory serves, concatenating will result in a larger app but potentially faster performance (usually) relative to separate tables with keys, but I would guess that in either case unless the data volume is huge (hundreds of millions of rows) it won't matter much either way.

For app size, you can test this by running the script as-is and then running it with a synthetic key. For performance, you could try and do the same, or possibly use Document Analyzer to try and get a read on how long your display objects take using each method.

[DanaL]

Hi Or,

I guess my question is still not clear...

Probably my fault...

Thanks for your tips, anyway.. 🙂 

[DanaL]

Hi,

I will try to create a sample app to clarify..

Thanks!

[marcus_sommer]

It depends on the data and the required views how a link-table might be connected to the fact-tables and this is mainly not performance-related else if the linked fields provides the proper associations or not.

Beside this a link-table approach is often the most unsuitable way to create a datamodel - especially in regard to the UI performance but also to script run-times, file-size, RAM consumption and the efforts to create and to maintain an application. I know it's very common here and much loved but the official recommendation from Qlik is to create a datamodel in the direction of a star-scheme and I do agree with it.

In your case it seems that you have already orders and invoices in one table - therefore there is no need to to extract them with the shown both loads + the apparently extra loads to fill with them the link-table.

- Marcus