1 Reply Latest reply: Sep 1, 2011 7:19 AM by Benoît Parlier RSS

    undesired "all" string in field when loading data from cube OLAP

      Hi,

       

      I have a problem that I don't manage to solve. I'm loading data from a cube OLAP with a MDX request. In addition with the measures, one of the loaded dimensions is:

       

      [Energy Delivery].[Energy Type].ALLMEMBERS

      ON AXIS(3),

       

      It should contain 4 different possibilities:

      - Auxiliary energy stream

      - Energy from outside

      - Local renewable energy

      - Main energy stream

       

      So when I open a listbox, I only see these 4 options. But if I create a chart, then I have an additional one, called "-", corresponding to ALL, that I don't want.

       

      The problem is that if I replace in my script ALLMEMBERS by CHILDREN, then I lose navigability through other dimensions.

       

      Thanks for you help.

        • undesired "all" string in field when loading data from cube OLAP

          I finally found a solution.

           

          In fact, Qlikview seems not to support the cube format. With a MDX request, I was loading data as they were on my OLAP cube, that's to say with many dimensions. But if I want Qlikview, which play the role of the cube, to understand well the relations between the different sets of data, I need to load them as a set of columns. In order to do so, I use a SQL request to flatten the data and get every single measure and dimension as a columns table. Thus I write as many requests as the number of measures I have, as following:

           

          CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=Myserver;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error];

           

          SQL SELECT * FROM OPENQUERY(myopenserver,

          'SELECT NON EMPTY

          {

              [MyMeasures]

          }

          ON AXIS(0),

          NON EMPTY

          {

              [MyFirstDimension].MEMBERS

          }

          ON AXIS(1),

          NON EMPTY

          (

              [MySecondDimension].MEMBERS,

              [FirstAttribute].Children,

              [SecondAttribute].Children

          )

          ON AXIS(2)

          FROM [MyCube];');

           

          So create as many similar request as the number of measures you have. Also to access your Cube with an OPENQUERY, you need to have an administrator account. Then now that the data are flattened, the CHILDREN works fine and keep navigability.