This discussion is archived
7 Replies Latest reply: Jan 25, 2013 12:41 PM by Srini VEERAVALLI RSS

Report Help

GRK Journeyer
Currently Being Moderated
Obiee 10g

I have a fact table - Employee Sales

Employee id (Not nullable)
Sales
Department city (Nullable)
Department county (Nullable)

And, Dimension table:

Dep Zip id (unique id of table)
Department state
Department county
Department city
Department zip


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
or
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
  • 1. Re: Report Help
    Copter Explorer
    Currently Being Moderated
    in the BMM layer when you do a complex join by default it will be inner join there in the drop down you should be able to change it right outer join or left outer join.
  • 2. Re: Report Help
    GRK Journeyer
    Currently Being Moderated
    Hi,

    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.
  • 3. Re: Report Help
    Copter Explorer
    Currently Being Moderated
    you can have a 'OR' condition in the physical layer expression bulider
  • 4. Re: Report Help
    GRK Journeyer
    Currently Being Moderated
    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.
  • 5. Re: Report Help
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Populate Dep Zip id (unique id of table) in Fact table.
  • 6. Re: Report Help
    GRK Journeyer
    Currently Being Moderated
    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
  • 7. Re: Report Help
    Srini VEERAVALLI Guru
    Currently Being Moderated
    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
    and
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points