Dilemma with Synthetic Key and Data Modeling

[#1]

I've been dilemma with Synthetic Keys and Data Modeling for my 10 reports and 3 dashboard for my current project.

Let say if i have 5 fact tables and 10 dimensions.

What are the fastest ways to solve the Synthetic keys and produce my reports & data correctly?

I really having time constraint as i'm just beginner but i've given a short time line to produce the reports and dashboard.[:'(]

Thank you.

[Miguel_Angel_Baeyens]

Hello,

Synthetic keys are not always a problem, actually they may save some time. But I'm guessing that in your case you have several tables with the same field name, so QlikView links those tables when you don't want to do it. (For example, say you have an Item table with an "ID" field and a Customer table with an "ID" field. QlikView will understand that both tables are related, and that's not the case).

If so, check that every table you load has a different name for each field, except for those you want to link, using "Preceding Load" from the script editor wizard will help you to rename those fields, and your script will look like

ItemTable:LOAD ID AS ITEMID, WAREHOUSE; SQL SELECT ID, WAREHOUSE FROM Items;

Hope that helps.

[#3]

If you really do understand what your synthetic keys are, you can keep them for short term.

Take a look here for more details: http://community.qlik.com/forums/t/31028.aspx?PageIndex=1

[#4]

I'd rather suggest you to concatenate your 5 fact tables and make a single transactions table. Do not forget to add and Id for every fact table, so you can filter by set analysis. This way you dimensions will only link to one transactions table.

Fact1:

load *, 'G/L' as FlagTable;

Fact2:

concatenate(Fact1)

load *, 'Marketing' as FlagTable;

Regards

[#5]

Hi Miguel A. Baeyens,

Thank you for your quick response.

I'd used the method that you shared to me previously. it works fine if we have 1 or 2 syn keys, especially when combining few Dimensions Table.

But when i start to add in my fact table, some data doesn't pull out correctly. it makes .. errr.. let say.. reportA show data correctly, but report B doesn't show data correctly.

Is it we must solve it the synthetic keys always? (i have more than 30 syn keys at the same time)

if yes, then under what circumstances?what reasons?

if no?

Thank you.

Sorry qlikers for disturb, i really don't have any experience in BI tools etc. Qlikview is my first experience with BI/DW.

Appreciate you response.

[#6]

Hi Nick Bor,

Thank you for your response and link.

I've gone through the post previously. But still, i'm having the dilemma and even have the "run out of virtual memory" error message.

should i hang myself? [:'(]

Thanks for the information.

[#7]

Hi,

there's a useful "share qlikview" that automatically removes every syn keys from your app, you can download it here:

http://community.qlik.com/media/p/76744.aspx

Regards.

[#8]

Hi ivan_cruz,

Thank you for the reply. I'd appreciate it.

Each of my fact table have like 10-15 same field name. If i use concatenate, will this produce a very large table?

Should i link the fact table first? or dimension table first? or how? can you advise. thank you.

i explored some posts in this forum here, some people saying that we should use Link Table if involved more than 2 fact table.

is this true? if yes, then we should link all the fact table first? or dimension table first? or mixed up?

Thank you.

Thank you qlikers, wish i get a solution when i wake up tomorrow.

God Bless.

[#9]

Depending on what you want to do with the information, the linktable approach might be convenient. Most of the times you can get away with the concatenation approach. If you concatenate all you fact tables, the result is going to be a big fat table, however performance will improve since all data is on the same table. If you want a fast solution you can use the share qlikview I mentioned in my last post.

Regards

[Miguel_Angel_Baeyens]

My suggestion, not going into it in depth, is that you have to take synthetic keys as a warning when something similar to what I described above is happening, that is, when two fields that store different information are named alike. One example is Date from Orders table and Date from Invoice Table. QlikView will then link Orders and Invoices through the Date field, which is used for different purposes and probably need a different calendar each. You can avoid this renaming the first to OrderDate and the second InvoiceDate

Say you have a primary key field in your tables, called "Key". Obviously, Key = 1 in Item table has not to be linked with Key = 1 in Customers, Vendors or Invoices tables. Renaming as ItemKey, CustomerKey, VendorKey or InvoiceKey will solve the issue.

But now you say you have several invoice line for each invoice, and you want to link InvoiceLine and InvoiceHeader tables with fields InvoiceNo, ItemNo and InvoiceLineNo, Should both tables have those three fields, a synthetic key will be created, saving you the time to create a composite key and will create a third table (called something like "$Syn_Table") that will store all possible combinations between those fields.

I strongly recommend you to read this post (among others) and check and understand the scenarios described there about synthetic keys, their meaning and their use.

Regards.