topic Re: Link Table with no composite keys in App Development

Link Table with no composite keys

DanaL — Wed, 09 Jun 2021 14:38:07 GMT

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!

Re: Link Table with no composite keys

Or — Sat, 05 Jun 2021 18:29:47 GMT

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

Re: Link Table with no composite keys

DanaL — Sat, 05 Jun 2021 18:37:39 GMT

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.

Re: Link Table with no composite keys

Vikash — Sun, 06 Jun 2021 03:49:46 GMT

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.

Re: Link Table with no composite keys

DanaL — Sun, 06 Jun 2021 05:33:38 GMT

Thanks, Vikash.

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

Re: Link Table with no composite keys

Vikash — Sun, 06 Jun 2021 05:49:51 GMT

Hi Danal,

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

Re: Link Table with no composite keys

Or — Sun, 06 Jun 2021 09:13:18 GMT

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.

Re: Link Table with no composite keys

DanaL — Sun, 06 Jun 2021 17:46:35 GMT

Hi Or,

I guess my question is still not clear...

Probably my fault...

Thanks for your tips, anyway.. 🙂

Re: Link Table with no composite keys

DanaL — Sun, 06 Jun 2021 17:48:36 GMT

Hi,

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

Thanks!

Re: Link Table with no composite keys

marcus_sommer — Wed, 09 Jun 2021 14:17:28 GMT

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

Re: Link Table with no composite keys

DanaL — Wed, 09 Jun 2021 14:42:53 GMT

Hi Marcus,

I just realized that I referred to one table (invoices), but there are actually 2 separate tables: Invoices and orders.

I'm not sure I understand what is your recommendation in such a case when both are sharing the 3 dimensions.

Thanks!

Re: Link Table with no composite keys

marcus_sommer — Wed, 09 Jun 2021 15:00:55 GMT

The suggestion ist to concatenate both tables into one fact-table and adding there all missing or relevant information, for example the order-id to the records from the invoices and also an extra field Source to be able to select or reference later to a certain area of the dataset.

- Marcus

Re: Link Table with no composite keys

DanaL — Wed, 09 Jun 2021 15:52:05 GMT

Thanks! 🙂