How can I avoid default join of 2 facts with same field Names and sharing common dimensions?

[#1]

Hi All,

I have 2 facts tables and both of them are having same fields name except the measure field.

Both I have to use both facts in same application and should share same dimensions. How can I do this?

Fact Touch On

EMPKEY	CARDKEY	TOUCH_TYPE	TOUCH_ON_COUNT	DATE_KEY	TIME_KEY	TOUCHMACHINEKEY
1	111	Touch On	1	20150512	1203	10
2	112	Touch On	1	20150512	1205	10

Fact Touch Off

EMPKEY	CARDKEY	TOUCH_TYPE	TOUCH_OFF_COUNT	DATE_KEY	TIME_KEY	TOUCHMACHINEKEY
1	111	Touch Off	1	20150512	1629	11
2	112	Touch Off	1	20150512	1740	12

Message was edited by: Shivendoo Kumar

[Anonymous]

Hi Shivendoo,

You should be able to just use "Concatenate" before your second load statement to concatenate the two tables (even though the field structure is not a 1-1 match).

Cheers,

Johannes

[#3]

Hello Johannes,

I am trying to avoid concatenate as I have 700 millions records in each of these 2 tables. I have done with Concatenate and not getting better performance so trying for 2 facts with common dimensions.

Do you have any other approach for my scenario?

[#4]

Hi All,

Is attached model not possible to achieve in Qlikview as it is? Please see the screeshot.

In both the fact tables, column names are same and should share common dimensions

[sujeetsingh]

It can not is there is a cyclic loop

[#6]

Then what would be the best solution..

I concatenated both the facts and still see performance issue as we have total 1500 millions records in both the facts.

[Anonymous]

Hi Shivendoo,

You could use Qualify, to avoid that Qlikview concatenates both tables and create synthetic keys. You can also share dimensions between two fact tables through link tables. Maybe this post could give you a better insight about link tables:

Concatenate vs Link Table

Finally, I'm just curious about the structure of source system. You're tracking two different type of events and the times that these events occurs per employee. Wouldn't a snapshot fact table be help you to organize this type of information?, for instance you could have the following structure:

EMPKEY,

CARDKEY,

TOUCH_ON_DATE_KEY,

TOUCH_ON_TIME_KEY,

TOUCH_ON_MACHINE_KEY,

TOUCH_OFF_DATE_KEY,

TOUCH_OFF_TIME_KEY,

TOUCH_OFF_MACHINE_KEY

Regards,

Karla

[#8]

I have posted another question which gives you clear picture of data which I have and application design but I am having performance issue while handling 1500 million records.

So trying all the possible approaches.

Handling a model having 1500 Millions records for 2 years. What would be best design of App?

[kavita25]

Hi,

can u please tell us what is your expected result??

Regards,

Kavita

[#10]

Hi Kavita,

Expected result is in terms of gaining performance. I want to check which approach would be better in handling larger dataset like 1500 million.

In my application (One fact table (1500 million records) and 9 dimension tables) , and have date hierarchy and when I expend it, it is taking long time. same thing with other dimensions fields.