Remove $Syn or Keep?

[chris1987]

Hi,

I've always been advised to remove the $syn tables when I create apps as they are created by bad structure, which I agree with. But I've also read they don't actually affect performance. I multiple tables (sales, orders, quotes) that are all linked by the customer, I've linked then and the app works 100% as expected but I have 6 $syn tables, here's how they are created

and here's the structure:

As you can see the $Syn 6 table links everything, I was wondering if I should get rid of these? and if so How would I do that so I kept the same functionality?

Thanks

Chris

[bramkn]

You could create a Fact table storing all transaction data. This is considered best practice when working with Qlik.

if you don't want to do this renaming fields is what needs to happen. You will have to check all fields that are stated in syn table and make sure only 1 field can be used to connect 2 tables.

[marcus_sommer]

In my opinion you should resolve the synthetic tables because the statement that a synthetic key behaved identically or at least similar to a combined key is only true if you have a single synthetic key. As far as you have multiple synthetic keys and even synthetic keys from other synthetic keys it will have an impact on the performance and you could further never be sure that your calculated data are really correct - at least I wouldn't even if they contain those fields which I would have used for a combined key. And if there is just one single field which not really belonged to the key the data couldn't be right.

Like Bram suggested merging all fact-tables into a single-table through joining/mapping and/or concatenating the tables would be the preferable first approach. If this isn't possible you could also try to transform a fact-table into a dimension-table and/or creating a link-table between your facts.

- Marcus

[jmvilaplanap]

Hi,

Following the best practices, you must to remove the syn keys. In the help appear this tips to remove it:

• Check that only fields that logically link two tables are used as keys.
  • Fields like “Comment”, “Remark” and “Description” may exist in several tables without being related, and should therefore not be used as keys.
  • Fields like “Date”, “Company” and “Name” may exist in several tables and have identical values, but still have different roles (Order Date/Shipping Date, Customer Company/Supplier Company). In such cases they should not be used as keys.
• Make sure that redundant fields aren’t used – that only the necessary fields connect. If for example a date is used as a key, make sure not to load year, month or day_of_month of the same date from more than one internal table.
• If necessary, form your own non-composite keys, typically using string concatenation inside an AutoNumber script function.

[chris1987]

Thanks for all the replies. I'm going to work on removing the $Syn tables now.

Using the Customer table from the above pic. All of the Invoice, Order and quotes are linked to this via "Cust-no" would it be better practice to have "INV_Cust-no" and then the same field in the Customer table, "ORD_Cust-no" and the same field in Customer table and finally "QUO_Cust-no" and the same in the Customer table?

If I did this would I still be able to click on a "Customer Name" and the filter be applied to all three tables?

Thanks

Chris

[bramkn]

Creating a fact Table with one field Cust-No Would be the better way to go. But yes this could work.

[chris1987]

Thanks, I'll read up on fact tables as I've not come across them before

Chris