topic Re: How can I avoid default join of 2 facts with same field Names and sharing common dimensions? in QlikView

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

Tue, 12 May 2015 02:13:10 GMT

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

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

Anonymous — Tue, 12 May 2015 02:39:15 GMT

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

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

Tue, 12 May 2015 03:05:49 GMT

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?

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

Tue, 12 May 2015 03:26:23 GMT

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

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

sujeetsingh — Tue, 12 May 2015 03:31:15 GMT

It can not is there is a cyclic loop

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

Tue, 12 May 2015 03:34:54 GMT

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.

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

Anonymous — Tue, 12 May 2015 04:57:27 GMT

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

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

Tue, 12 May 2015 05:25:20 GMT

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?

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

kavita25 — Tue, 12 May 2015 05:46:09 GMT

Hi,

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

Regards,

Kavita

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

Tue, 12 May 2015 05:52:08 GMT

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.

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

kavita25 — Tue, 12 May 2015 06:26:06 GMT

Hi,

Try the below link approaches...

*** 6 Weeks in to QV Development, 30 Million Records QV Document and Help Needed!!! ****

Hope it helps u...