Hierarchical Relationship building in the Dimensional Model

View previous topic View next topic Go down

Hierarchical Relationship building in the Dimensional Model

Post  sudip.bandyopadhyay on Tue Mar 15, 2011 8:29 am

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.

sudip.bandyopadhyay

Posts : 3
Join date : 2011-03-15

View user profile

Back to top Go down

Re: Hierarchical Relationship building in the Dimensional Model

Post  ngalemmo on Tue Mar 15, 2011 9:36 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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