0

I have a fact table called ProjectTasks.

It's a left join of project and tasks table. A project can have 0 or more tasks. Thus for projects that don't have any task, the task columns are nulls (blanks).

It's similar to Order Header Detail modelling technique, except that a header can have 0 or more detail rows (rather than the usual 1 or more detail rows).

One of the Task column is RoomName. Each task has a valid (non null/blank) value in this RoomName column.

For projects that don't have a task, I cannot make the nulls (blanks) as some keyword like 'Not known' because there isn't a task yet. So logically it is a genuine null/blank.

In this scenario, I'm trying to think if it would be a good idea to leave the RoomName column in the fact table or create a dimension for it? If I extract the room name into a dimension table called Room, then it will link to Fact table on the Room name, however nulls (blanks) in fact table will not have any dimension link.

The dimension tables in the models I made in the past always had all the possible values that exist in the fact table. Whereas as explained above, here if I extract the Room into a dimension, then the dim table won't have the links to the nulls (blanks). Is this acceptable for data modelling?

Improve this question
5
  • Can you help me understand why you would like to use the Order Header Detail technique instead of Star Schema? Commented Aug 15, 2024 at 13:44
  • What I have explained is a star schema. Infact the order header detail modelling technique is a way to make a star schema Commented Aug 15, 2024 at 18:05
  • You need a “does not exist” record in your task dimension and link to it from your fact table. You would normally have these default records in every dimension table Commented Aug 17, 2024 at 17:09
  • Your formulation is a bit confusing. You have two fact tables Project and ProjectTask with a relation of cardinality 0..N; ProjectTask has a dimension Room. If a Project has no task there will be no record in the table ProjectTask, so where is your NULL problem? Commented Aug 22, 2024 at 10:41
  • If Project has no Task, even then there is a row with the Project details like Project name, Project Cost. The Task columns are nulls. So it's like a header detail scenario but complicated due to project having 0 or more tasks. I need some guidance. Commented Aug 22, 2024 at 15:27
Related questions
0
Using a source surrogate key in a Dimension Table
0
Clean up a dimension table
3
Fact table updates with slowly changing dimension
Load 4 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.