Dimension attribute with one-to-many relationship

Question

I have a request for an attribute in a dimension that has a one-to-many relationship with the lower level of the dimension.

Here is the case:

• Dimension: Employee
• Attribute: Immatriculation

I have 3 source tables : Employee, Language and LanguageLevel.

One employee can have multiple immatriculation codes. Each immatriculation has a number, start_date, expiration_date.

How can I model this case ?

Answer 1

You need to create Immatriculation-Dimension and use a foreign key on the many side (Immatriculation) of the relationship that linkes back to the one side(Employee). This gives Primary Key- Foreign Key relationship.

Here's a similar case. One book can have multiple authors (in order for this to be a one-to-many relationship one author can only be related to one book. Otherwise it would be a many-to-many relationship..):

CREATE TABLE dbo.Book
(
Pk_Book_Id INT PRIMARY KEY,
Name VARCHAR(255),
ISBN VARCHAR(255)
);

CREATE TABLE dbo.Author
(
Pk_Author_Id INT PRIMARY KEY,
FullName     VARCHAR(255),
MobileNo     CHAR(10),
Fk_Book_Id   INT FOREIGN KEY REFERENCES Book(Pk_Book_Id)
);

INSERT INTO Book VALUES (1, 'Let is Snow', 'ISBN3030303');
INSERT INTO Book VALUES (2, 'Three Cups of Tea','ISBN638242');
GO

INSERT INTO dbo.Author VALUES(100,'John Green','30303',1);
INSERT INTO dbo.Author VALUES(101,'Maureen Johnson','4343',1);
INSERT INTO dbo.Author VALUES(102,'Lauren Myracle','76665',1);
INSERT INTO dbo.Author VALUES(103,'Greg Mortenson','6434',2);
INSERT INTO dbo.Author VALUES(104,'David Oliver Relin','72322',2);
GO

SELECT * FROM dbo.Book;
SELECT * FROM dbo.Author;