Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Hierarchical Relationship building in the Dimensional Model

2 posters

Go down

Hierarchical Relationship building in the Dimensional Model Empty Hierarchical Relationship building in the Dimensional Model

Post  sudip.bandyopadhyay 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

Back to top Go down

Hierarchical Relationship building in the Dimensional Model Empty Re: Hierarchical Relationship building in the Dimensional Model

Post  ngalemmo Tue Mar 15, 2011 9:36 am

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

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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