3 Replies Latest reply on Sep 25, 2015 11:07 AM by user 1208988

    Thousands of Members in Filter

    user 1208988

      Hi,

       

      I have an Essbase cube that integrates to OBIEE. The Essbase has one of the dimensions called Product. Product dimension has around 7 thousands products.

       

      I need to create a report to categorize these products into different six groups, which are sitting in Essbase Measure dimension as dynamic calc members.

       

      So, for example, Cat 1 is the products whose name starts with A, B, D, M. Cat 2 product names start with Q, T, Z and so on...

       

      As the analysis is considering all the products, as far as I understand I need to apply filter on Product level. So, the filter has all 7 thousands products.

       

      When I run the report, its throwing an error saying "State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 96002] Essbase Error: MDX Internal Error: Input MDX query expression is too large to process, consider rewriting the query (HY000)".

       

      Anyone been through same situation? Any idea how to solve this?

       

       

      Thanks

        • 1. Re: Thousands of Members in Filter
          Jerry S.

          You could define a category field in the RPD, then use the category field as a filter in your analysis.

          • 2. Re: Thousands of Members in Filter
            Gianni Ceresa

            Hi,

            Sorry but I don't really get why you have this issue: you must categorize these products and you didn't do that in your cube? By using shares members, UDAs or just dynamic calcs on members or something?

            I didn't really get why you are doing that job in OBIEE instead of your cube (your cube being the DB in your case you are supposed to push down as much job as possible to it as it performs better than OBIEE for these things).

            • 3. Re: Thousands of Members in Filter
              user 1208988

              Hi Gianni,

               

              Thanks for the response. Sorry, I could not get back in time as I was away and did not have Internet access.

               

              I have categories in Essbase.

               

              Let me give you an example of the cube I have-

               

              Essbase

              =======

              Two major dimensions - Measures and Product (and Scenario, Year etc)

               

              Product dimension has four categories. Cat1 product names start with A. Cat2 product names start B. Cat3 with C and Cat4 with D.

               

              Measure dimension has the following - Receipt Date, Inventory Amount, Period Days, Provision 000, Provision 111, Provision 333, Provision 444, Provision 555.

               

              Period Days = Current Date - Receipt Date (using @DATEDIFF() fucntion)

               

              Each of the Provisions dynamic calc with formula that says if a product's Period Days is less than 90, Provision 111 = Inventory Amount. If a product's Period Days is more than 200, Provision 555 = Inventory Amount/2.

               

              This way, each provision has formula that defines each provision is what Inventory Amount.

               

              Data loaded into two measures, Receipt Date and Inventory Amount for each product.

               

              Each Product category has roughly 5 thousands products each.

               

              OBIEE

              ======

              So, I have a report, were I want to see total Provision 000, Provision 111, Provision 333, Provision 444, Provision 555 only Cat1 and Cat3. Categories in rows, Provisions in column. So, I added a filter for Product dimension that selects Cat1, and Cat3.

              If I run this report it throws an error-

               

              State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 96002] Essbase Error: Error executing formula for [Period Days]: status code [37] in function [@_DATEDIFF] (HY000)


              But when I add a filter to filter on all products of Cat1 and Cat3, it runs for long time but produces result.

               

              And then when I add all other products from Cat2 and Cat4, then it throws an error -

               

              "State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 96002] Essbase Error: MDX Internal Error: Input MDX query expression is too large to process, consider rewriting the query (HY000)"


               

              Please let me know if I am understood correctly.....

               

               

              Thanks