topic Re: Database logical structure in QlikView

Database logical structure

Tue, 06 Jan 2015 20:11:00 GMT

Hi all,

I'm facing a frustrating problem at the moment, I'm developing a skill management tool but I cant seem to get the database structure correct (works fine in Access). The problem is this: An employee has an employeeID, and a JobID. Linked to a certain job there are some skills, so each skill has a Job ID as well, the problem causer is the fact that every employee has their individual skills graded which causes a loop:

I thought that creating a duplicate table of jobs did the trick but now I face the problem that the jobs that are mentioned in the employee table aren't logically related to the skills anymore:

Anyone has any suggestions to solve this problem?

Thanks in advance!

Re: Database logical structure

NickHoff — Tue, 06 Jan 2015 20:17:01 GMT

Use a mapping load for Jobs/Jobs2, and apply map to your fact table which looks like it's IndividualS. I would also left join your organization table into employees.

Re: Database logical structure

Wed, 07 Jan 2015 09:32:27 GMT

How can I apply a Jobs mapping to my IndividualScores table? It doesn't contain JobID only SkillID

Re: Database logical structure

alexandros17 — Wed, 07 Jan 2015 09:36:12 GMT

Left join Skills to indivisual skills, so when you load:

load * resident IndivisdualsSkills;

left join

load * resident Skills

Let me know

Re: Database logical structure

NickHoff — Wed, 07 Jan 2015 14:41:16 GMT

You are correct. Instead of IndividualS apply map to Skills. Typically when you have a table in your data model that has ID, Description/Value, the general rule is to apply map to the table it relates to. Instead of a snow flake schema you'll see more performance out of a star schema. Your final data model should only be three tables, IndvidualS, Skills, and Employees

Another rule is when you have two tables with the same primary key, they should be joined.