9 Replies Latest reply: Jan 24, 2013 10:39 AM by 616046 RSS

    Complex Essbase MDX Issue - Need Guidance


      I have a complex Essbase issue in ASO version Currently I have a MDX formula with a Measure member named '10th Percentile'. It calculates the 10th Percentile perfectly. So new requirement is to create a new Measures member and instead of calculating the '10 Percentile' value, it needs to display the Customer Name of the value that is the 10th Percentile from the Customer dimension. So if I do a retrieval and the '10th Percentile' is 3.23, then it needs to display the Customer Name of the 3.23.

      So I altered the formula to do what I think needs to be done and it verifies. However if I retrieve on that new measure in the Excel Add In, I get and error: An error [1200315] Occured in Spreadsheet Extractor. However if I navigate without data I don't get the error, but I also don't get any data, which I obviously need. So my question is, if MDX support reporting on Metadata not just Data, what/how can one report on it? Ideally I need to have this work in the Excel Add In as the client is using a custom vba modified template for their end users.

      Any ideas and help?
        • 1. Re: Complex Essbase MDX Issue - Need Guidance
          Here's the formula. I bolded the part that is new.....

          IIF ( [Lbs Per Yard].CurrentMember IS [Lbs Per Yard].[No_Lbs/Yd] ,

          IIF( [Count_Price] = Missing, Missing, IIF( [Count_Price] < 2 , Missing,

          { Order (
          Filter ( CROSSJOIN ( Leaves ( [Service].CurrentMember)
          , Filter ( CROSSJOIN ( Leaves ( [Segment].CurrentMember)
          , Filter ( CROSSJOIN ( Leaves ( [Customer Type].CurrentMember)
          , Filter ( CROSSJOIN ( Leaves ( [Zip Code].CurrentMember)
          , Filter ( CROSSJOIN ( Leaves ( [Quantities].CurrentMember)
          , Filter ( CROSSJOIN ( Leaves ( [Frequencies].CurrentMember)
          , Filter ( CROSSJOIN ( Leaves ( [Yardages].CurrentMember)
          , Filter ( Leaves ( [Contract Year].CurrentMember)

          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing ))
          , [$/Yd] <> Missing )

          , [$/Yd] /*this is the measure we're using for sort */

          , BASC /* sort in $/Yd in ascending order */

          ) AS OrderedSetOfItems} /* here we define an alias for the set in order to be able to use it later */

          .Item ( Round ( Count ( OrderedSetOfItems) *

          10 / 100 /*where we specify which percentile is being calculated */

          + 0.5 , 0 ) -1 ) *.Item (3-1).[MEMBER_NAME]*

          /* this takes Nth item from the ordered set (0-based index, hence -1) */

          /* .Name takes its name */


          , Missing )
          • 2. Re: Complex Essbase MDX Issue - Need Guidance
            When I change 'MEMBER_NAME' to 'GEN_NUMBER' or 'LEVEL_NUMBER' it returns the proper values...... So I'm guessing Excel can't handle a text result???
            • 3. Re: Complex Essbase MDX Issue - Need Guidance
              I would be very surprised if the Add-In / Spreadsheet extractor could handle arbitrary text - the only situation I know of in which a data cell will return text are text measures (which your measure isn't). Can you get a result querying the member in MDX from either EAS or MaxL?

              The MdxFormat command documentation in the Tech Ref gives an example of returning text based on logic, but the text there is generated in the query itself rather than 'at source' (i.e. it's 'client-side' rather than 'cube-side' for want of a better term).
              • 4. Re: Complex Essbase MDX Issue - Need Guidance
                I haven't tried the MDX in the MDX editor and then running that yet. Currently users are using an Excel spreadsheet to do their analysis. Giving them access to a MDX report is not possible. I will try a classic MDX report and run via the editor and see what happens. Even though I know it will work :) Atleast I'm 99.9% sure it will :)
                • 5. Re: Complex Essbase MDX Issue - Need Guidance
                  I am 99.999% sure that the classic add-in won't handle a Text Measure. Smart View will, however.

                  That would make sense -- you return a number, the add-in (again, I think you mean the Classic/Legacy/been-with-us-since-year-dot) handles numeric results, but not text. Return text and the add-in goes KABOOM.

                  Or are you doing this in Smart View? Are you using a Text Measure? Or a Text Measure linked to a "normal" member?


                  Cameron Lackpour

                  P.S. Here's a treatise on Text Measures (first one I google-fu'd): http://www.unitask.com/oracledaily/2012/09/04/essbase-datetext-measures-in-detail/
                  • 6. Re: Complex Essbase MDX Issue - Need Guidance
                    I'm glad you gave yourself that 0.001% wiggle room - text and date typed measures do work in the Classic Add-In too. Varying attributes don't, sadly.

                    A text measure can only return values from a predefined list, so there's no way to use them in conjunction with the approach described, but the OP might be able to keep a text list up-to-date with every member from the customer dimension and work out how to return the right 'key' (i.e. numeric value) to associate with that member. Fiddly though, and only works if you've got less than 1024 customers (maximum text list size, I believe).

                    I say this every time someone asks a question related to them, but 'text measure' implies a lot more than the functionality really delivers.
                    • 7. Re: Complex Essbase MDX Issue - Need Guidance
                      CL. Ok. So I performed those tasks of creating a Text_List_Manager. On the left side "Name" I put my member name of my leaf level Customer Name of 'ABC Company'. Then on the right side "ID", I put a 100. I then go back to my newly created Text Measure named '10th_Test'. I ensure it's Type is 'Text' and the Select Text List is my 'Sample Text List'. After I save, I do a retrieval in SmartView and Excel Add In. They both throw an error. So is it possible I'm not doing this correctly? The concept seems simple. It's kinda like a lookup table. This happens when my formula is referencing 'MEMBER_NAME' too. When I change my formula to 'GEN_NUMBER' and retrieve it gives me 'Out of Range' in the retrieval for both SmartView and Excel Add In and no error. But GEN_NUMBER returns a value of 7 which is not text. Doesn't make sense. What's funny is I trick it and put a 7 on the right and on the left I put a You Got It and then I get the You Got It in my retrieval.

                      Can't figure out why it can't handle the MEMBER_NAME.......

                      • 8. Re: Complex Essbase MDX Issue - Need Guidance
                        The text list maps a numeric value (in the ID column on the right in the Text List dialog) to a string (in the Name column on the left). What you describe sounds like the designed behaviour. In your example, to get 'ABC_Company' back from your text measure you will have to create a formula that returns 100 when you want 'ABC_Company', not the member name.

                        In other words using a text measure still doesn't let you use an MDX formula that returns a string. The formula has to return a number. All the text measure lets you do is map that number to a string via the pre-defined Text List.
                        • 9. Re: Complex Essbase MDX Issue - Need Guidance
                          Maybe what I can do is assign an attribute value to every Customer so 1 to 600K and then return the attribute value in the results. Then just have a lookup table of the attribute and customer name... Sometimes you just can't fulfill a requirement :)