Hierarchical Relationship building in the Dimensional Model
2 posters
Page 1 of 1
Hierarchical Relationship building in the Dimensional Model
We are building a BI Solution which will identify the cost of rework on Defects for a Project/Application.
We are having a hierarchy of Application, Project and Defects.
One Application will have multiple Projects and One Project should have Multiple Defects. But for some defects there may be situation that it is having relation with the Application but not related to a Project.
My requirement is to build a solution which will generate measures based on the Defects(the most granular)level based on the parameters of Application and/or Project and on Time.
So I made one DEFECT Fact table having the DEFECT_ID, APPLICATION_DIM_ID, PROJECT_DIM_ID and TIME_DIM_ID related with all the necessary Dimensions.
Now I am having one more requirement to show all the PROJECT details (how many projects?, name, status etc etc.) related to one APPLICATION. But there may be situation that we are having Project under an Application which is not having any DEFECT. In that case if I want to have a mapping relationship between APPLICATION and PROJECT via DEFECT Fact then it will not have any; as there is no DEFECT_ID for the Project.
Could you please throw some light on how can I overcome this situation being able to get both:
1. Having measures out of the Defect Fact.
2. Getting the Application – Project relationship where there is no related Defect under a specific Project.
We are having a hierarchy of Application, Project and Defects.
One Application will have multiple Projects and One Project should have Multiple Defects. But for some defects there may be situation that it is having relation with the Application but not related to a Project.
My requirement is to build a solution which will generate measures based on the Defects(the most granular)level based on the parameters of Application and/or Project and on Time.
So I made one DEFECT Fact table having the DEFECT_ID, APPLICATION_DIM_ID, PROJECT_DIM_ID and TIME_DIM_ID related with all the necessary Dimensions.
Now I am having one more requirement to show all the PROJECT details (how many projects?, name, status etc etc.) related to one APPLICATION. But there may be situation that we are having Project under an Application which is not having any DEFECT. In that case if I want to have a mapping relationship between APPLICATION and PROJECT via DEFECT Fact then it will not have any; as there is no DEFECT_ID for the Project.
Could you please throw some light on how can I overcome this situation being able to get both:
1. Having measures out of the Defect Fact.
2. Getting the Application – Project relationship where there is no related Defect under a specific Project.
sudip.bandyopadhyay- Posts : 3
Join date : 2011-03-15
Re: Hierarchical Relationship building in the Dimensional Model
Have a separate fact table to record project/application relationships.
Similar topics
» Dimensional Model from a Hierarchical Data Source
» Many to many relationship in a dimensional model
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Meta-model of Kimball dimensional model
» Many to many relationship in a dimensional model
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Meta-model of Kimball dimensional model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum