This post is going to be in english because its primary intent is to serve the image Martijn Evers, DW and DV practioner, BI blogger and generous pal on LinkedIn DV forum.
On that forum I have placed a post about a classic hierarchy situation:
(…)People asked me if my department is a business key.
It wotks this way: When I have signed up with my employer I’ve got a number – “matrícula”. I am also refered to by my CPF (kind of social security code, only it is for taxes.) Then I was asigned to a team, inside a department, which in turn is part of a superintendence that seats under a director (like development director, services director, financial director etc. – pretty much like a VP.)
So, my employee record in the source operational system (HR) reads more or less like:
Name (Fabio) – Matricula (00.000) – CPF (00.000) – Director (Development) – Superintendence (Tech Support) – Department (Tools) – Team (Databases)
Business keys are “components used by business users, business processes, or operacional code to access, identify, and associate information within a business operational life-cycle” (Super Charge book.)
All columns I have described fit the business key definition, so are all of them business keys (the question sounded better on my head.) I mean, where it a dimensional model, the natural key would be my CPF, and all else would be part of a hierarchy (departament hierarchy), down to my Name (CPF would not be shown on reports). Matricula would not be shown although it should be captured.
So is this it? Do employees, departments, divisions, vice-presidencies and so on give hub tables? Each one of them? Or should I have a, say, CPF hub, whith name, matricula, department etc. as a satellite? People wants to group reports by superintendence, departments, teams etc.
Martijn has answered it detailed and has post a SQL script that gave the relationship on the hubs and links involved in lieu of the image. I have entered the script into a PostgreSQL and with the help of Power*Architect (I am on Ubuntu) I have rendered this figure:
So this is the answer to my question. Now, onto to studying it! Thanks again, Martijn!