4 Replies Latest reply on Jan 22, 2013 1:11 PM by 986352

    Filter Grouping not working for OBIEE 11g


      I need some help on the below:
      I have built a simple report in OBIEE 11g with FILTER grouping on two dimensions as below:
                Operative Hierachy is equal to Europe, IMEA, APAC, China Focus Country, Americas, US and Canada
           and      Accounts is equal to Net Sales (i), Gross Margin (i), GM% (i)
                Accounts is equal to Gross Margin, Net Sales, Gross Margin %
           and      Operative Hierachy is equal to / is in Organizations Total
      The report was working as expected; but now suddenly it's showing no results with the error "+The specified criteria didn't result in any data. This is often caused by applying filters and/or selections that are too restrictive or that contain incorrect values.+" - even though data is present ! - I have tested this by splitting it into two reports (with only the AND conditions)- which is then woking as expected.

      Points to note:
      --There had been no change on the Report, when it stopped showing results.
      --I have tested this filter grouping with other dimensions- & they are working fine.
      --The OBIEE reports are fetching data from ESSBASE cube.
      --Only thing which I can remember when it stopped working is that, there had been a data load on the ESSBASE cube. But other reports using these dimensions are working.

      Has anyone faced the same issue before. Please help.

      Edited by: Anu on Jan 22, 2013 12:31 AM
        • 1. Re: Filter Grouping not working for OBIEE 11g
          Reddy G
          try to execute the back end SQl in Source and check for which Condition is missing data..need to do some debugging to know what happened afterr load..
          • 2. Re: Filter Grouping not working for OBIEE 11g

            Please check how those conditions were used in SQL queries fired to DB and also see if you get any data for those conditions from DB.

            • 3. Re: Filter Grouping not working for OBIEE 11g
              Vineeth. K.M
              Hi Anu,

              As you guessed, the data load on the Essbase might have caused the problem. The measures must be missing in the essbase cube for this combination of the dimensions.
              To debug, remove one of the dimensions from the report and test it. Thus, you would be able to figure out the problem causing dimension.

              Hope this helps !

              • 4. Re: Filter Grouping not working for OBIEE 11g
                Hi User, Vijay,

                Yes I have checked the generated SQL queries and the MDX queries as well. Both queries are generated as expected, but returns no result.

                But now I have found out how these two different dimensions are different from my other dimensions. Only these two dimensions have a SORT order logical column defined in the BMM layer- which I think is causing the problem, BI Server not able decide which column to sort. So, the sql gives result when I cimment out one of the SORTKEY

                0 s_0,
                "PSS Essbase"."Accounts"."Accounts" s_1,
                "PSS Essbase"."Operative Hierarchy"."Operative Hierachy" s_2,
                -- SORTKEY("PSS Essbase"."Accounts"."Accounts") s_3,
                SORTKEY("PSS Essbase"."Operative Hierarchy"."Operative Hierachy") s_4,
                "PSS Essbase"."Facts"."Measure" s_5
                FROM "PSS Essbase"
                ((("Operative Hierarchy"."Operative Hierachy" = 'Europe') AND ("Accounts"."Accounts" = 'Gross Margin'))
                OR (("Operative Hierarchy"."Operative Hierachy" = 'Russia Focus Country') AND ("Accounts"."Accounts" = 'Sales ASP')))
                ORDER BY 1, 5 ASC NULLS LAST, 4 ASC NULLS LAST
                FETCH FIRST 65001 ROWS ONLY

                Any views on what happens when we use two dimensions in the same report which have sort columns defined in the repository...