Create a logical schema+ MDX query

[dprobst]

Hello,

 

I have a problem when I run the MDX query from my logical schema.

There is the following error message:

Mondrian Error:Error while parsing MDX statement 'select {[Measures].OccupationFS} on columns, {[Annee].[All Annee]} on rows
from [CubeTest]
'

0
 

I think my schema is not correct, whil the data source is very simple: I put all the data in one Table but there are many rows:

48 millions.

 This is the table, named KPSTOCKAGE

• ANNEE - VARCHAR(4)
• FS - VARCHAR(25)
• GROUPFS - VARCHAR(25)
• MOIS - VARCHAR(2)
• OccMoyenne(Mo) - DECIMAL(22, 0)
• TailleFS(Mo) - DECIMAL(22, 0)
• UH - CHAR

Thank you.

Code:

the xml file and mdx query are joined

[jeromepineau]

 Je pense qu'il faut utiliser .Members avec OccupationFS pour avoir le résultat.

I could be wrong. Not an MDX expert but I've hit that problem before.

[swood]

Try:

 

select {[Measures].[OccupationFS]} on columns, {[Annee].[All Annee]} on rows
from [CubeTest]

 

Note the additional [] around OccupationFS.

 

I suggest you try the Workbench. There functions for validation and testing of schemas, including automatic generation of MDX to exercise the schema.

 

 

Sherman

Jaspersoft

[dprobst]

Hello,

 

I haven't mentioned that I already had Jasper Workbench Analysis, and that one of my problem was that

I could not generate an MDX query automatically.

I solved my problem adding some attributes to the <hierarchy> of the dimension and to the <level>

The most important thing is to have in mind the following structure to define a dimension:

<dimension><hierarchie><level><Table>.

Some attributes are necessary.

-the first dimension doesn't work(  no MDX query generate)

<Dimension type="TimeDimension" foreignKey="ANNEE" name="Annee" caption="ANNEE">
<Hierarchy name="Default" hasAll="true" allMemberName="All Annee" primaryKey="ANNEE">
<Table name="KPSTOCKAGE" schema="BGSADMIN" alias="">
</Table>
<Level name="ANNEE" column="ANNEE" type="Time" uniqueMembers="false" levelType="TimeYears">

 

this way of definig a dimension work

<Dimension type="TimeDimension" foreignKey="ANNEE" name="Annee">
<Hierarchy hasAll="true" allMemberName="All Annee" primaryKey="ANNEE">
<Table name="KPSTOCKAGE" schema="BGSADMIN">
</Table>
<Level name="Annee" column="ANNEE" ordinalColumn="ANNEE" type="Time" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
</Level>
</Hierarchy>
</Dimension>

-->Solution:

some attributes are necessary, but I can't say which one precisely.( I tried to delete some but it is still working).

Thank you for your help.

 

David Probst

 

[swood]

What problem did you have with generating the MDX?

 

In the Hierarchy that had problems, I see:

• No need for the caption element
• Hierarchy name="Default" is wrong. No need for the name element if you only have 1 hierarchy in a dimension. If you enter a name, you have to refer to the Hierarchy in your MDX explicitly, like [Time.Default]

 

Sherman

Jaspersoft