0
votes

Project DimensionI am working on creating dimensional model for real estate project and purchase order data which undergoes multiple approvals, there are multiple comments and notes also for each of the projects. In my design, I have considered grain of the fact table as purchase order line item. I am considering Project as a dimension and had to create snowflake tables for approval, comments, and notes. Since the approval, comments and notes are not directly associated to a purchase order. I am not sure if there is a way to avoid snowflake tables here. Please share your suggestions. Many thanks.

1
we can help you better if you post your data model. - RADO
I have posted the diagram now. - Siddharth
Does a project have one or many approvals, comments and notes? If many, 'things about projects' would be tracked as their own facts. If the purchase order identifies one and only one approval, comment or note they can be dimensions from the purchase order fact. - Rich
There are many approval, notes, and comments about projects and these are not related to Purchase Order. Should I keep it the way it is now. - Siddharth

1 Answers

1
votes

I don't know your fact table/s, but to handle this scenario I'd create a fact like Activity, with dimensions of Project, Date, ActivityType (ie, Approval, Note, Comment) and the comment value as the "measure".

This type of table, with non-numeric measures, is often referred to as a "factless fact".