[Cube/MDX] Conditional Formulas

[jogrey]

 hi,

 

i am begining practicing mdx/xmla, and as the take off is easy and capacities large, i have some difficulties :

 

I got a cube, pivoting on 5 dimensions, into, i have 2 measures, a sum of "ok" qty, and a sum of "total" qty.

 

I have a CalculatedMember dividing both to get a %, everything is ok, i put colors on cells, that's computed fast, and the results are accurate, atomic bomb I like it.

 

But, i just learnt a new rule must exist in my formula : from a specified hierarcical level i do not have to divide sums of the current population, but multiply the %, and then, i am getting blocked. I saw i can test the actual level of the computation, there is my cube with a hierarchy level test :

 

 

 

  <Cube name="EvolutionFPY" caption="EvolutionFPY" cache="true" enabled="true">

    <Table name="fait_fpy">

    </Table>

    <DimensionUsage source="Temps" name="Temps" caption="Temps" foreignKey="ffy_tps_id">

    </DimensionUsage>

    <DimensionUsage source="Produit" name="Produit" caption="Produit" foreignKey="ffy_pdt_id">

    </DimensionUsage>

    <DimensionUsage source="Etablissement" name="Etablissement" caption="Etablissement" foreignKey="ffy_etb_id">

    </DimensionUsage>

    <DimensionUsage source="Client" name="Client" caption="Client" foreignKey="ffy_dct_id">

    </DimensionUsage>

    <DimensionUsage source="Utilisateur" name="Utilisateur" caption="Utilisateur" foreignKey="ffy_utl_id">

    </DimensionUsage>

    <Measure name="SOMME_QTE_OK" column="ffy_qte_ok" datatype="Numeric" formatString="### ###" aggregator="sum" visible="true">

    </Measure>

    <Measure name="SOMME_QTE_TOT" column="ffy_qte_tot" datatype="Numeric" formatString="### ###" aggregator="sum" visible="true">

    </Measure>

    <CalculatedMember name="FPY_TOT" caption="FPY" dimension="Measures" visible="true">

      <Formula>

        <![CDATA[CASE

        WHEN

         [Etablissement].CurrentMember.Level.Name = "Poste de saisie"

        THEN

          4

        ELSE

          [Measures].[sOMME_QTE_OK]/[Measures].[sOMME_QTE_TOT]

        END]]>

      </Formula>

      <CalculatedMemberProperty name="FORMAT_STRING" expression="Iif([Measures].[FPY_TOT] < 1, Iif([Measures].[FPY_TOT] < 0.9, '|0.00|style=red', '|0.00|style=yellow'), '|0.00|style=green')">

      </CalculatedMemberProperty>

    </CalculatedMember>

  </Cube>

 

 

and then the dimension and is hierarchy, under "poste de saisie" i want to divide sums.

 

 

 

  <Dimension type="StandardDimension" name="Etablissement" caption="Etablissement">

    <Hierarchy hasAll="true" allMemberName="Tous les etablissements" primaryKey="etb_id" caption="Etablissement">

      <Table name="dim_etablissement">

      </Table>

      <Level name="Poste de charge" column="etb_pdc" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Etablissement">

      </Level>

      <Level name="Poste de saisie" column="etb_pds" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Etablissement">

      </Level>

      <Level name="Equipement" column="etb_eqp" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Etablissement">

      </Level>

      <Level name="Code Etablissement" column="etb_code" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="Etablissement">

      </Level>

    </Hierarchy>

  </Dimension>

 

 

I don't know how to retrieve results from inferior levels to multiplicate them, nor if it's only possible.

 

I am getting blocked, if you have any clue where i should look for, i will send you greetings :)

 

Post Edited by jogrey at 11/23/2009 15:53

[swood]

Your need is exactly what MDX is for.

 

Sum(Descendants(Etablissement.CurrentMember), [sOMME_QTE_OK])

 

There are a lot of MDX references available. Microsoft Analysis Services examples work fine with JasperAnalysis.

 

Sherman

Jaspersoft

[jogrey]

 Exactly what i need thank you !

But i am now blocked by aggregations, as sum is only an addition aggregation and not products aggregs., i remember math classes and i solved the probleme with logarithms, as the following :

 WHEN

         [Etablissement].CurrentMember.Level.Name = "Poste de charge"

        THEN

         Power( 10, Sum( Descendants( Etablissement.CurrentMember, Etablissement.[Poste de saisie] ), log10( [Measures].[sOMME_QTE_OK] / [Measures].[sOMME_QTE_TOT] )  ) )

        ELSE

          [Measures].[sOMME_QTE_OK]/[Measures].[sOMME_QTE_TOT]

 

Now everything works fine !

 

Thank you again.

Post Edited by jogrey at 12/02/2009 13:01

[jogrey]

 I come back to you with another question :)

I have a cube, with several dimensions, in one dimension, 3 levels (lets take A,B,C for instance), values of the leaf can be shared by father's node ie :

A1->B1->C1

A1->B1->C2

A1->B2->C1

A1->B2->C3

 

I'd like to group by "C", i mean when i am on A level, i want to do some product computation with a sql group by on only C levels.

A1 = sum(C1) * sum(C2) * sum(C3)

 

in sql this is quite simple :

select power (10, sum( log10( C )  )

from ...my tables....

Where ...my filters...

Group by C

 

But in xml/a mdx I don't know how to proceed ? Should i creates a measure like :

 

<Measure name="C" datatype="Numeric" formatString="### ###" aggregator="sum" visible="true">

      <MeasureExpression>

        <SQL dialect="mysql">

          aQty GROUP BY myC

        </SQL>    

      </MeasureExpression>

    </Measure>    

 

And then use it in a calculatedMember like this :

Power( 10, Sum( [Etablissement].CurrentMember, log10( C ) ) )

 

 

Thank you in advance :)

 

[jogrey]

 Maybe this should be done in the mdx statement only ? I found some aggregates functions, but i don't know if it fits my requierments.

Thx.

[jogrey]

I tried to add a dimension, with one level and unique name, on the same data than i want to "group by", it seems to work, is the good way to do ?

I have this kind of mdx query :

WITH MEMBER
[Measures].[groupby]
AS
Power( 10, Sum( [DimPds].[pds].members, log10( [Measures].[FPY_TOT] ) ) )
SELECT
[temps].members ON COLUMNS,
[Etablissement].members ON ROWS
FROM
[EvolutionFPY]
WHERE
[Measures].[groupby]

Post Edited by jogrey at 02/23/2010 13:08

[swood]

I was going to say that it looked to me like the C dimension in your example was not really part of the A and B dimension. It looks like you sorted that out and have a solution.

 

Sherman

Jaspersoft

[jogrey]

 After deeper checks, the solution is really axed on a simple new dimension and not on complicated functions with attractives and seducing  names :)

 

Thank you again Mr S.