topic Dilemma with Synthetic Key and Data Modeling in QlikView

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 16:21:01 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Miguel_Angel_Baeyens — Thu, 23 Sep 2010 16:27:27 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 16:28:23 GMT

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

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 16:38:37 GMT

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

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 16:42:50 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 16:54:44 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 17:00:22 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 17:05:45 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 17:13:58 GMT

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

Dilemma with Synthetic Key and Data Modeling

Miguel_Angel_Baeyens — Thu, 23 Sep 2010 17:17:57 GMT

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.

Dilemma with Synthetic Key and Data Modeling

Thu, 23 Sep 2010 17:21:23 GMT

I think the problem is related with the point of view for the task. You need to think about QV table structure more carefully and from stamp point of business needs .

Dilemma with Synthetic Key and Data Modeling

Sun, 26 Sep 2010 11:10:43 GMT

Hi Ivan,

Can we concatenate 3 fact table into one, but at the same time, 2 of the 3fact table having few field that are same name? How to solve this?

Do we rename the field?

Thank you

Dilemma with Synthetic Key and Data Modeling

Miguel_Angel_Baeyens — Sun, 26 Sep 2010 11:34:49 GMT

Hello,

If you are concatenating, you shouldn't worry about that, because you will have one table instead of three. In case some of the tables have the same field name will not create a synth key, you are already avoiding that concatenating. The issue may be somewhere else.

Regards.

Dilemma with Synthetic Key and Data Modeling

Sun, 26 Sep 2010 12:12:17 GMT

Hi Ivan,

Can we concatenate 3 fact table into one, but at the same time, 2 of the 3fact table having few field that are same name? How to solve this?

Do we rename the field?

Thank you

Dilemma with Synthetic Key and Data Modeling

Sun, 26 Sep 2010 12:31:15 GMT

Hi Miguel,

Thanks for the reply.

Maybe i use a example to express my doubt on concatenate.

let say i have 3 fact table name as factA, factB, factC.

factA and factB having the same name field as REV_IN_GC. but both REV_IN_GC having different values.

After i concatenate the table, there'll be only one field REV_IN_GC in the concatenate table, am i right?

if i want to pull the data from REV_IN_GC from factB instead of factA, how qlikview identify?

Thank you so much.

Best Regards

Re: Dilemma with Synthetic Key and Data Modeling

carlos13 — Mon, 14 Oct 2013 15:20:11 GMT

where I can find this tool? as this link does not work