5 Replies Latest reply: Jun 1, 2012 2:56 PM by Luko RSS

    query not returning all data

    user622098
      All,

      I have 2 dimension tables and 1 logical fact table
      a01_bi_agency_interest_dim
      a01_bi_dsk_central_file_dim
      tier2_facts


      1 Complex Join in Physical Layer
      A01_BI_AGENCY_INTEREST_DIM.MASTER_AI_ID = A01_BI_DSK_CENTRAL_FILE_DIM.MASTER_AI_ID AND A01_BI_AGENCY_INTEREST_DIM.INT_DOC_ID = A01_BI_DSK_CENTRAL_FILE_DIM.INT_DOC_ID


      2 Logical Joins in BMM
      A01_BI_AGENCY_INTEREST_DIM to tier2_facts (inner, 0 or 1 to many)
      A01_BI_DSK_CENTRAL_FILE_DIM to tier2_facts (inner, 0 or 1 to many)


      Query only returns 1 row. Should return many rows, I have checked the data through sql queries in SqlPlus.

      Query from advanced tab.

      SELECT A01_BI_AGENCY_INTEREST_DIM.MASTER_AI_NAME saw_0,
      A01_BI_DSK_CENTRAL_FILE_DIM.INT_DOC_ID saw_1
      FROM TIER2 ORDER BY saw_0, saw_1

      Any help would be appreciated. I am missing something, but doesn't make sense.
      Thanks,
      Kathy
        • 1. Re: query not returning all data
          Luko
          Hello,

          Why do you have one join in physical layer and only between dimension instead two join with fact table?

          Regards,
          Luko
          • 2. Re: query not returning all data
            user622098
            I don't have any physical fact tables.

            I have created logical fact tables.

            Edited by: user622098 on Jun 1, 2012 5:02 AM
            • 3. Re: query not returning all data
              Luko
              Hello,

              I can no imagine your BMM. Maybe check real sql in obiee (http://gerardnico.com/wiki/dat/obiee/manage_session_log).

              Regards,
              Luko
              • 4. Re: query not returning all data
                user622098
                It doesn't do the join and I get 1 record returned with zero's for both values. Why doesn't it pick up my join?

                #############################################
                -------------------- SQL Request:
                SET VARIABLE QUERY_SRC_CD='Report';SELECT A01_BI_AGENCY_INTEREST_DIM.MASTER_AI_ID saw_0, A01_BI_DSK_CENTRAL_FILE_DIM.INT_DOC_ID saw_1 FROM TIER2 ORDER BY saw_0, saw_1


                +++300000:300004:----2012/06/01 12:44:51

                -------------------- General Query Info:
                Repository: Star, Subject Area: TIER2, Presentation: TIER2


                +++300000:300004:----2012/06/01 12:44:51

                -------------------- Cache Hit on query:
                Matching Query:     SET VARIABLE QUERY_SRC_CD='Report';SELECT A01_BI_AGENCY_INTEREST_DIM.MASTER_AI_ID saw_0,
                A01_BI_DSK_CENTRAL_FILE_DIM.INT_DOC_ID saw_1
                FROM TIER2 ORDER BY saw_0, saw_1
                Created by:     Administrator


                +++300000:300004:----2012/06/01 12:44:51

                -------------------- Query Status: Successful Completion


                +++300000:300004:----2012/06/01 12:44:51

                -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds)


                +++300000:300004:----2012/06/01 12:44:51

                -------------------- Rows returned to Client 1


                +++300000:300004:----2012/06/01 12:44:51

                -------------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds)
                • 5. Re: query not returning all data
                  Luko
                  Hello,

                  Complex join you should use in BM. Look at http://gerardnico.com/wiki/dat/obiee/complex_join. Correct this joins but I think it is not your issue. In physical layer I always have star/snowflake schema or relationship schema.
                  You have two dimension with measures for fact table this strange for me but you can see you obiee do not pick your joins correct it

                  Regards,
                  Luko