Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Non-existing Dimension Attribute

2 posters

Go down

Non-existing Dimension Attribute Empty Non-existing Dimension Attribute

Post  ozisamur Mon Jan 05, 2015 4:35 am

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:




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?


Posts : 30
Join date : 2014-10-27

Back to top Go down

Non-existing Dimension Attribute Empty Re: Non-existing Dimension Attribute

Post  nick_white Mon Jan 05, 2015 8:54 am

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


Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum