Hierarchical Relationship building in the Dimensional Model

[sudip.bandyopadhyay]

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.

[ngalemmo]

Have a separate fact table to record project/application relationships.