Employee id (Not nullable)
Department city (Nullable)
Department county (Nullable)
And, Dimension table:
Dep Zip id (unique id of table)
I have a request to create hierarchy (able to create hierarchy) and join the Dimension to Fact.
As, the Department city and Department county are nullable in fact .... and i need to join my dim table.
So, i need to add across two columns.
Dim.Department county = Fact.Department county
Dim. Department city = Fact. Department City
But, In business layer i need to give left join on Fact table : such, that it shows all records of fact even if there is no city or county, or both not provided.
Please, provide me some inputs.. on this one.
How to make sure that if i have a 'Or' condition in physical layer and if i need left join or right outer joins in business how to do that one....
Since you are using 10g I would suggest these:
1) Use any join condition in Physical layer
2) In BMM layer on fact create a new logical collumn IfNull(DepartmentCity,'Unspecified')--> Named as City
and do the same for other IfNull(DepartmentCounty,'Unspecified')--->Named as County
3) Create Logical foreign key join between Fact and dim
with join conditions
Dim.Department county = Fact.County
Dim. Department city = Fact.City
BMM join will be used in the query and join in physical layer will be ignored.
Edited by: Srini VEERAVALLI on Jan 25, 2013 10:35 AM
Have done something else? What was the solution?
Edited by: Srini VEERAVALLI on Jan 25, 2013 2:41 PM