Non-existing Dimension Attribute

Hi guys,

In dimensional modelling we know that we should generate flatten dimension which has many attributes. Think about Employee dimension. It has Department attribute and in the employee dimension just Department name exist. Consider this example:

DIM_EMPLOYEE

EMPLOYEE_SK | EMPLOYEE_ID | EMPLOYEE_NAME | BIRTH_CITY | BIRTH_DATE | DEPARTMENT_NAME

...

As you see DEPARMENT_NAME attribute in the dimension table.

But in dimensional model I could not find the department which has no employee. In reality this would not happen but I wonder how can we solve the non-existing dimension attribute? User should look atthe source system?

My suggestion would be to create a fact table that would answer this question.
Presumably there are lots of attributes of, and relationships to, a department that you could report on:
- Location
- Head of Department
- School the department is a sub-division of
- etc.

Create a fact table that links to relevant Dimensions, including a Department dimension, and make one of the measures the Employee count (which would have a zero value for a department with no employees).
There is nothing to stop you having both a Department Dim and department_name as an attribute of Employee if there is a valid case for wanting to report on employee/department name but you don't want to associate the department dim with the fact being used - a dimensional model is not normalised.

Alternatively, if this is not really an analytical reporting requirement but the users just want to make occasional data quality checks that no department records have been created that have no associated employees then I would probably just query the source system. Just because a Dimensional model can answer a question doesn't always mean that it is the best solution - particularly from a cost/benefit perspective e.g. creating a dimensional model to answer this, building the ETL to populate it, etc. would be a few day's work whereas I could build and test a DB view that would provide the answer in about 15 mins