I have a fact table - Employee Sales
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....
Edited by: GRK on Jan 25, 2013 7:15 AM
I was aware of presence of right outer or left outer joins in business layer.
I was unable to do right outer and left outer joins in business layer - if i have 'OR' condition in physical layer.
So, how could i resolve this issue.
I had 'OR' in physical layer.
And .. in business layer when i applied left or right outer joins it is not working... it is throwing error saying you cannot do left outer or right outer joins with 'OR' conditions.
is there any method of joining without populating... zip..
I'm thinking to create alias dim1, alias dim2 ... which gets joined to fact... on 2 different conditions.
one as dim1.county = fact1.county
and another as dim2.city = fact2.city
And, merge the dim1 and dim2 as dim in business layer... with (different sources) what do you say.. any ideas..??
And, then apply left or right joins as i like..
Edited by: GRK on Jan 25, 2013 8:25 AM
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