Data Model Logic

[paul_vans267]

Hi all,

I am trying to build a QlikView application where I analyse Match information from a current eSports competition.  I am struggling to determine the logic to connect the three sources of data:

Table 1 (shows all the available heroes in the game):

Hero - there are 117 different heroes

Ability - strength/agility/intelligence

Table 2 (shows the picks and bans that were made to start the game):

MatchID - unique for that whole match.  Each match will have 20 fields

Phase - 1 to 20

Team - Either Radient or Dire

Pick/Ban - Either Pick or Ban

Hero - chosen Hero.  Each hero can only be picked once.  Matches a hero from Table 1

Table 3 (shows the detailed results from the game):

MatchID - Matches the MatchID in table 2

Team - Matches the Team in table 2

Hero - Matches one of the picked heroes in table 2 but also, of course, a hero from table 1

Result - Either Win or Lose

Duration - time

Player - the person playing that character

ProTeam - who the person plays for

Kills

Deaths

Assists

**there are other fields but they do not interfere with the model**

I am just not sure how to create a model my data so that I can keep all the fields but not create loops and synthetic keys.

Can someone provide me with some guidance?  Thank you

[Marcio_Campestrini · Accepted solution]

Paul

Don't link Table3.Hero with Table1.Hero. This may solve your problem.

[santiago_respane]

Hi Paul,

you can use a link table to avoid synthetic keys.

The model should look something like this:

Let me know if you need help building this up.

Hope this helps.

Kindest regards,

[johnw]

If it were my data, I'd probably start with what marcio.campestrini suggests. The presence of a single Hero on table 3 for a team and match indicates that that Hero has a special role, and that role should be identified in some way. Are they the MVP? Call it "MVP Hero" and your loop goes away... as does your ability to see the Ability for this Hero, and any other information from table 1. But you can always clone an "MVP Hero" table if it's critical. As for the synthetic key that remains between tables 2 and 3, I don't see it as a problem. If you want to remove it, though, you could create a composite key.

[johnw]

As a much more radical possibility, you could merge all three tables. We could think of the match itself as being just a final phase shared by both teams. So merge it with table 2 as phase 21. And if there are only a few fields on table 1, no real reason to keep it separate. Denormalize away. Apologies for any bugs in the below.

// Start with just all the data you care about from table 2
[Match]:
LOAD
MatchID
,Team
,Phase
,Pick/Ban
,Hero
,etc.
FROM Table2.qvd (QVD)
;
// Merge information from table 3 by Match ID and Team.
// The idea is that if we pick, say, a Result of Win,
// we may still want to see, say, what our picks were
// that may have resulted in that win. But you might
// not want this step. See what behavior you prefer.
LEFT JOIN ([Match])
LOAD
MatchID
,Team
// skip Hero
,Result
,etc.
FROM Table3.qvd(QVD)
;
// Concatenate table 3 as well so that we have
// the table 3 hero associated with phase 21.
// Or so that we have table 3 data at all if
// you decide not to join above.
CONCATENATE ([Match])
LOAD
MatchID
,Team
,Phase // add this to your QVD, dual('Match',21) as Phase
,Hero
,Result
,etc.
FROM Table3.qvd (QVD)
;
// Join the hero data from table 1
// because hey, why not?
LEFT JOIN ([Match])
LOAD
Hero
,Ability
,etc.
FROM Table1.qvd (QVD)
;

[paul_vans267]

Hi all,

Thanks for the suggestions.  Managed to get it working using  a combination of them.

Kind regards,

Paul