11 Replies Latest reply on Feb 1, 2010 6:31 PM by 180271

    Outer Joins with multiple columns

    180271
      I'm trying to understand how OBIEE is building it joins etc.
      If you look at structure below, I have a validation table (VALID_GRADE) which has foreign keys to both 'YEAR' and 'SCHOOL' tables. I have a empoyee table (EMP_GRADE)
      which stores the foreign key to the 'VALID_GRADE' table. In my physical table layer I defined my joins to tables,
      so 'VALID_GRADE' joins to both 'YEAR' and 'SCHOOL' (with Foreign keys). 'VALID_GRADE' also joins to 'EMP_GRADE'
      VALID_GRADE.PK_ID = EMP_GRADE.FK_VALID_GRADE.

      In my logical table source I am only choosing 'VALID_GRADE' and 'EMP_GRADE' with right outer join. When I look at my SQL source it is including the 'YEAR' and 'SCHOOL' table (even though not used in source) and making the joins. Why doe sit do that? And becuase it is, it is messing up my outer join condition because it only is issuing the outer join between VALID_GRADE.PK_ID = EMP_GRADE.FK_VALID_GRADE and leaving the others as Inner. So no rows return. Help!


      YEAR
      --------
      PK_ID
      YEAR

      SCHOOL
      -----------
      PK_ID
      SCHOOL

      VALID_GRADE
      --------------------
      PK_ID
      FK_YEAR
      FK_SCHOOL
      GRADE

      EMP_GRADE
      ----------------
      PK_ID
      FK_VALID_GRADE
        • 1. Re: Outer Joins with multiple columns
          gerardnico
          OBIEE is fact table centric software. The join path goes always through the fact table.

          Then what is your fact table ?

          Check here an how-to:
          http://gerardnico.com/wiki/dat/obiee/bi_server/design/fact_table/obiee_identifying_fact_table

          Cheers
          Nico
          • 2. Re: Outer Joins with multiple columns
            180271
            Yes, this is connecting to a fact table called 'ENROLLMENT_FACTS'. All my Logical tables are connecting to this fact table (similar to the help link you gave). The fact table is using 3 physical table sources ('YEAR','STU_SCHOOL','STU_YEAR').

            So are you telling me that every time I issue a query it will always use all the physical tables from all the logical tables that are connecting to my fact table?
            • 3. Re: Outer Joins with multiple columns
              gerardnico
              If your have define that the 3 physical tables are in the same hierarchies level and join together in only one logical table source: yes.

              You can have multiple LTS (logical table source) for several reasons:
              1) LTS1 and LTS2 have different columns, a.k.a. fact-based fragmentation
              2) LTS1 and LTS2 have different rows (content), a.k.a. value-based fragmentation
              3) LTS1 is an aggregate table from LTS2, a.k.a. level-based fragmentation
              4) Combination of the techniques above.

              Why the fact table use three different LTS ?
              • 4. Re: Outer Joins with multiple columns
                180271
                Ok, played with this some more. The tables/joins issued in SQL depend on what columns I choose in Answers.
                So my problem is still with the outer joins.

                If I include any columns from the 'YEAR' and 'SCHOOL' table, it includes the tables from my other logical tables and these joins:
                * this doesn't return anything becuase the outer join is missing on 1st 2 lines

                YEAR.PK_ID = VALID_GRADE.FK_YEAR
                SCHOOL.PK_ID = VALID_GRADE.FK_SCHOOL
                EMP_GRADE left outer join VALID_GRADE T8052 On EMP_GRADE.FK_VALID_GRADE = VALID_GRADE.PK_ID

                If I don't include any columns from the 'YEAR' and 'SCHOOL' table, it includes this 1 join:

                EMP_GRADE left outer join VALID_GRADE T8052 On EMP_GRADE.FK_VALID_GRADE = VALID_GRADE.PK_ID
                * this returns the appropriate rows

                So how can I get all the tables to outer join or not include the 1st 2 join conditions (which aren't really needed)?

                Help!



                YEAR

                --------------------------------------------------------------------------------
                PK_ID
                YEAR

                SCHOOL

                --------------------------------------------------------------------------------
                PK_ID
                SCHOOL

                VALID_GRADE

                --------------------------------------------------------------------------------
                PK_ID
                FK_YEAR
                FK_SCHOOL
                GRADE

                EMP_GRADE

                --------------------------------------------------------------------------------
                PK_ID
                FK_VALID_GRADE
                • 5. Re: Outer Joins with multiple columns
                  gerardnico
                  When I see your tables. I will create one fact table:VALID_GRADE (because it has all foreign key) and all others table as dimension. Then in the logical join between VALID_GRADE and EMP_GRADE, i will set it as an outer join.

                  I see with a lot of difficulty your data model and where is the previous ENROL... fact and why it has three different tables ...
                  • 6. Re: Outer Joins with multiple columns
                    180271
                    I'm probably not understanding how this works yet.
                    I only currently have 1 fact table. I changed my fact table to 1 source (STU-SCHOOL). All my logical tables are pointing to it in Business Diagram.

                    My logical table is currently compromised of 2 physical tables. In 'Map to these tables' I have:
                    'VALID_GRADE' and 'EMP_GRADE' with appropriate join (outer) defined.

                    Are you saying I should take the 'VALID_GRADE' table out of the logical table (leaving me with just 'EMP_GRADE') and
                    make 'VALID_GRADE' table a fact table?
                    • 7. Re: Outer Joins with multiple columns
                      gerardnico
                      What is difficult with you is that you don't give all informations on your data model.
                      You talk now about STU_SCHOOL, STU_YEAR and I don't see anywhere where is their data model (relationship and column)with the others tables)

                      Just to say that OBIEE is a fact centric table. Then you have to choose as fact table the table which has the more foreign key and it's the case of VALID_GRADE.

                      Cheers
                      Nico
                      • 8. Re: Outer Joins with multiple columns
                        180271
                        Let me take a step back. All of my tables have a primary key/foreign key relationship.

                        #1, Is it safe to say that in the Physical model, you join al of the tables together with foreign key joins and not complex joins?

                        #2, For my logical tables I combined many tables together (using 'Map to these tables') into 1 source. I did this because my main table is not storing the values of fields, it's storing the foreign key to other tables that have the value. The user needs to display the 'GRADE' and 'DESCRIPTION' from the "VALID_GRADE' table in answers.
                        Should I have only made 'EMP_GRADE' the source and created another (fact) table with 'VALID_GRADE' as the sources?


                        EMP_GRADE
                        ----------------
                        PK_ID
                        FK_VALID_GRADE
                        EMP_NAME
                        EMP_STATUS


                        VALID_GRADE
                        --------------------
                        PK_ID
                        FK_YEAR
                        FK_SCHOOL
                        GRADE
                        DESCRIPTION



                        I thought that was best practice.
                        • 9. Re: Outer Joins with multiple columns
                          gerardnico
                          I understand now.

                          In my first link, you can find this description of the fact table:
                          The fact table is at the many end of a all logical (0, 1:N) (zero, one-to-many relationship) joins.
                          Then in our case, the fact table is EMP_GRADE and you have one dimension VALID_GRADE.
                          You must then set in your logical join that you have an outer join.

                          Then in your dimension table, try to create only one logical table source with the joins property as here:
                          http://gerardnico.com/wiki/dat/obiee/bi_server/obiee_join_in_lts
                          to integrate the table school and year

                          Normally if you don't select a field from school of year, you may have no join.

                          Cheers
                          Nico
                          • 10. Re: Outer Joins with multiple columns
                            gerardnico
                            I say a nonsense.
                            Don't use the join property. It's for the many to many relationship.

                            Just try to drag and drop the column of the physical table school and year from the physical layer in the logical dimension VALID_GRADE.

                            Cheers
                            Nico
                            • 11. Re: Outer Joins with multiple columns
                              180271
                              Don't use the join property where?