7 Replies Latest reply on Jan 25, 2013 8:41 PM by SriniVEERAVALLI

    Report Help

      Obiee 10g

      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)
      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
      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
          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

            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
              you can have a 'OR' condition in the physical layer expression bulider
              • 4. Re: Report Help
                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
                  Populate Dep Zip id (unique id of table) in Fact table.
                  • 6. Re: Report Help
                    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
                      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
                      1 person found this helpful