jogrey Posted November 23, 2009 Share Posted November 23, 2009 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 Link to comment Share on other sites More sharing options...
swood Posted December 1, 2009 Share Posted December 1, 2009 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. ShermanJaspersoft Link to comment Share on other sites More sharing options...
jogrey Posted December 2, 2009 Author Share Posted December 2, 2009 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 Link to comment Share on other sites More sharing options...
jogrey Posted January 22, 2010 Author Share Posted January 22, 2010 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->C1A1->B1->C2A1->B2->C1A1->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 :) Link to comment Share on other sites More sharing options...
jogrey Posted January 29, 2010 Author Share Posted January 29, 2010 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. Link to comment Share on other sites More sharing options...
jogrey Posted February 23, 2010 Author Share Posted February 23, 2010 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 ROWSFROM [EvolutionFPY]WHERE[Measures].[groupby]Post Edited by jogrey at 02/23/2010 13:08 Link to comment Share on other sites More sharing options...
swood Posted March 1, 2010 Share Posted March 1, 2010 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. ShermanJaspersoft Link to comment Share on other sites More sharing options...
jogrey Posted March 2, 2010 Author Share Posted March 2, 2010 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now