Wrong Values in Analysis View

[englbrechtful]

Hey everbody,

Im having a very strange problem, which is pretty difficult to explain for me:

I built a Schema and a MDX query, very simple and apparently working. Now when i check the view ( all positions are collapsed ) the shown values are just fine and correct.

But now if i expand (for example all customers) i see wrong values for every position, i double checked with sql and i have absolutely no idea where JA is taking that values from.

I know this could be any kind of problem, but i dont know how i can give you more info on the issue as i cannot give you access to my databases nor make screenshots with data in it :)

 

Please help me!

best regards,

Stefan

[englbrechtful]

I investigated a bit further and figure out the problem is that all shown measures are multiplyed with 1024!

 

Here some data to understand what im talking about:

PostgreSQL DB table: umsatz_cube

Columns: name, artikelgruppe, artikel, jahr, quartal, monat, menge, nettogesamtbetrag

 

now if I do a sql query like this : "select sum(menge) from umsatz_cube where name = 'certainname' and jahr = 2009 and quartal = 4"

I get the correct value: 21849

JasperAnalys shows if i drill down to that name the value 22373376

Same with all other values of my Measures its always original value * 1024 which JA shows!?!? how can that be?

; 22373376/21849
        1024
; 43602944/42581
        1024
; 45289472/44228
        1024

 

Im running JasperServer 3.5.0 CE on debian lenny.

My MDX query:

SELECT
{ [Measures].Netto, [Measures].Menge } ON COLUMNS,
{ ([Kunde].[All Kundes], [Artikel].[All Artikels], [Time].[Year].&[2009]) } ON ROWS
FROM [umsatz]
 

Attached is my schema, and screenshots to underline the problem.

PLEASE HELP ME!!!!!!

[englbrechtful]

come on swood, mdahlman, someone help me, or tell me at least if thats a bug or its problem on my side. Im totaly lost here.

[swood]

It is your schema.

 

When you look at the All levels, the calcs are done based on queries that do not join with the dimensions, so they look correct.

 

All your dimensions are on the fact table. The duplication is happening because you set them up as full dimensions. See below for a corrected schema. Let me know whether it works.

 

 

Sherman

Jaspersoft

Code:

<Schema name="ICECUBE">  <Cube name="Umsatz" cache="true" enabled="true">    <Table name="umsatz_cube" schema="public">    </Table>    <Dimension type="TimeDimension" name="Time">      <Hierarchy hasAll="true">        <Level name="Year" column="jahr" type="Numeric" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never">        </Level>        <Level name="Quartal" column="quartal" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">        </Level>        <Level name="Month" column="monat" type="Numeric" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">        </Level>      </Hierarchy>    </Dimension>    <Dimension type="StandardDimension" name="Kunde">      <Hierarchy hasAll="true">        <Level name="Name" column="name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">        </Level>      </Hierarchy>    </Dimension>    <Dimension type="StandardDimension" name="Artikel">      <Hierarchy hasAll="true">        </Table>        <Level name="Artikelname" column="artikel" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">        </Level>      </Hierarchy>    </Dimension>    <Measure name="Netto" column="nettogesamtbetrag" datatype="Numeric" aggregator="sum" visible="true">    </Measure>    <Measure name="Menge" column="menge" datatype="Numeric" aggregator="sum" visible="true">    </Measure>  </Cube></Schema>

[englbrechtful]

Thanks a lot Sherman, its working!!!! :)

Theres only one question left: how does "artikelgruppe" fit in? where can i put it? as a level of Artikel?

( Artikelgruppe = articlegroup // the articles are divided into certain groups )

should be look like in the pic i attached

[swood]

If it makes sense to you that artikelgruppe contain articles and you want to be able to summarize the artikelnames by artikelgruppe, then add artikelgruppe as a level above artikelname.

 

Sherman

Jaspersoft

[englbrechtful]

Thank you very much Sherman, thats it, exactly.

 

:)