topic Avoiding Loop - Link table suggestions? in QlikView

Avoiding Loop - Link table suggestions?

Thu, 29 Dec 2011 14:06:46 GMT

Hi Folks -

I've been reading the various posts on avoiding circular references in my QlikView design, and think I want to use a link table. I'm just not sure whether I want to be building it by joining or concatenating in the various keys. Here's my model and issues, roughly - I can't share the app itself since the data are confidential:

- Common dimensions are country, division, person ID and time, potentially

- I have tables for admissions (with term, home country, citizenship country, person ID, division); enrollment (same elements), alumni (same elements), research (agreement country, person ID), etc.

- I'd arlready gotten as far as (below) making a country table that does the linking to each of the act tables, loading the country in multiple columns to map to each type of country

- Now I'd really also be alble to link across the tables by term, division and potentially person ID, although the person ID may only be common in admissions/enrollment cases, but definitely still need the country in common as well

So, for my link table, I understand that I'd want the PK from the various dimensions and facts, but am I joining or concantenating those together?

Any pointers would be most welcome!

Thanks

Amy

Avoiding Loop - Link table suggestions?

Fri, 30 Dec 2011 16:39:16 GMT

Well, I've gotten a bit further, and think I have a good model using JOIN, but the performance when joining in the larger tables is really getting worse and worse. I've made QVDs to join in rather than resident loads, but that only helped a little bit. I was really trying to get QlikView to do the heavy lifting and make the most of it by letting it make the linking table - not sure of a way to help it along.

Any suggestions would be most welcome.

Thanks!

Avoiding Loop - Link table suggestions?

Fri, 30 Dec 2011 20:20:52 GMT

Hi ,

maybe this will help you:

http://qliktips.blogspot.com/2011/05/rules-for-creating-keylink-table-in.html

Avoiding Loop - Link table suggestions?

Sat, 31 Dec 2011 14:20:44 GMT

Thanks, Yigal - I had seen this page, which actually helped me get as far as I did - the join with the keys seems to be what I want, but the reload performance is really degraded as a result. I'm contemplating interim QVDs rather than doing all the joins at once. I wander if that will help...

Avoiding Loop - Link table suggestions?

Anonymous — Sat, 31 Dec 2011 15:40:17 GMT

Some of the issue might be in the length of your keys. If your PK's result in lengthy combinations, than you could create hash keys out of them first using autonumberhash128 for example. Then join those compressed keys from the QVD's into the link table. Although I am not sure if that would save more time in your reload than you would spend creating the hash keys. You would have to experiment with it. Keep in mind that the autonumberhash128 function will only create matching keys when you create all the data in the same script reload. Although I think when all is said and done, you will have to bite the bullet here. Creating a joined link table will definitely require more time than simply loading data.

Avoiding Loop - Link table suggestions?

Sat, 31 Dec 2011 15:43:25 GMT

Thanks, Aaron - I did autonumberhash256 actually. From what I can tell, the time really seems to be on the join end, as you say. I'll keep experimenting. Thanks for the comment (and happy new year)!

Avoiding Loop - Link table suggestions?

Sat, 31 Dec 2011 20:12:22 GMT

Hi milleraa,

Maybe this suggestion will increase the all Reload Time,

But in some cases, Depend youre needs, It Can Help,

The idea is to 'Divide And Conquer', Seperate the development to 3 Phases,

1. Generating QVD-files

2. Data Model- Optimize load, Make a Conaction within the table.

3. Application- Binary Load for and Layout Development.

This is a big advantage for Maintenance and clear workflow and reduce reload time while developing.

Hope it helps.