Table design

[qlik_venu]

Hi,

I am building the base table design in the Qlikview.

My database has 2 FACT table which are connected through couple of Dimensional tables....and I have read that, its best to concatenate 2 FACT tables, so as to get the best performance.

For this I was able to preserve common keys used in the FACT tables to connect to the Dimensional Tables.

But there is a problem with one dimension table.

It has column named "source column" by which it connect to one Fact table.
And the dimension table has column named "extension id" by which it connects to the other fact table.

I am using the Concatenate in the script for the FACT tables.

Now, How do I connect the dimsnion table. Should i use seprate column for both the FACTS?

Thanks,
Venu

[vgutkovsky]

Well, you could combine the source column and extension id into 1 unique key, which you would either generate from scratch after the table concatenation or create by concatenating the 2 columns. What was the point of having 2 keys in the first place? If it was just because your tables were separate (and the fields had separate names), then this is no longer an issue and you should be able to replace the 2 keys with 1.

Regards,

[qlik_venu]

Hi,

If i concatenate, the 2 columns, how would it join to the 2 Fact tables. I am not able to understand at this point. Can you pls clarify this.

The 2 Fact tables have different key columns to the same dimension tables. Source_colimn field and the extension_id are the 2 fields.

If we have concat(source_colum, extension_id), then how would this link to Fact tables 1 and 2.

Fact tables have only source_column and the other fact has only the extension.

So how would a concatenated field join to such a FACT table.

Can you pleae explain this. I have urgency to understand this concept to go ahead with my model

Thanks,

Venu

[vgutkovsky]

I think we both misunderstood. I thought that your 2 fact tables have identical columns and that's why you are concatenating them. You thought that by concatenating the fields I meant to use the concat() function, where that actually is quite different from the ampersand & concatenation I had in mind. So let's start over...

I'm not sure why would want to concatenate your fact tables in the first place. I doubt you would see much of a performance improvement if the tables have entirely unrelated fields. So I would think that the best solution would be to leave the 2 fact tables separate.

However, if you want to concatenate the tables, why can't you just leave the keys alone? The result would be 1 fact table with 2 dimension tables linked to it (each with a different key). The key will be null for the rows that only exist for the other fact table, but that shouldn't be a problem. But like I said, I'm not sure it makes sense to concatenate at all in this situation...

Regards,