Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

1 Reply
Not applicable
Author

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.