Help on MDX query!!

Not related to a specific OLAP tool. (Includes forum policies and rules).
Post Reply
nko83
Posts: 5
Joined: Mon May 16, 2011 7:31 am
OLAP Product: mondrian
Version: 1.2
Excel Version: 2010

Help on MDX query!!

Post by nko83 »

Hi everybody,
I must define a MDX query based on the schema depicted below:

<Schema name="HDGL_SCHEMA">
<Dimension name="GROUP">
<Hierarchy hasAll="true" allMemberName="All Coils" primaryKey="key_coils">
<Table name="dim_coils"/>
<Level name="Campaign" column="labelCampaign" uniqueMembers="true" type="String"/>
<Level name="Coil" column="coilid_coil" uniqueMembers="false" type="Integer"/>
<Level name="Sector" column="idSector" uniqueMembers="false" type="Integer"/>
</Hierarchy>
</Dimension>

<Cube name="HDGL">
<Table name="fact_table"/
<DimensionUsage name="COIL" source="COIL" foreignKey="key_coils"/>
<Measure name="KPI1" column="KPI1" aggregator="sum" formatString="###0.00000"/>
</Cube>
</Schema>

So, I have a measure KPI1 and a hierarchy named GROUP (Sector->Coil->Campaign) and the aggregation functions for the KPI1, according to each level, are:
KPI for sector = KPI1
KPI for the coil = sum(KPI1)
KPI for the campaign = avg(sum(KPI1))

I would to create a query that select the the calculated KPI for the hierarchy level "Campaign" which should be based on the expression:
sum(KPI)/Count( number of coil related to each Campaign).

How I can make this? I have tried with the following query... but without results...

with member [Measures].[KPI1 - CAMPAGNA] as '(Sum({[Measures].[KPI1]}) / Count([Coil].[All Coils]))'
select {[Measures].[KPI1 - CAMPAGNA]} ON COLUMNS, NON EMPTY
Hierarchize({[COIL].[All Coils].Children}) ON ROWS
from [HDGL]

best regards
nico
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: Help on MDX query!!

Post by Marcus Scherer »

Hi Nico,
check this example below with the food mart db in mondrian.

You would adjust it to your schema.

best regards,

Code: Select all

with member Measures.camp_avg as 'AVG(
 Descendants([store].[Store state].currentmember, [store].[store name]),
 [Measures].[Unit Sales]
)'

select {[Measures].[Unit Sales], Measures.camp_avg} ON COLUMNS,
{[Store].[store state].members} ON ROWS
from [Sales]
where [Time].[1997]
nko83
Posts: 5
Joined: Mon May 16, 2011 7:31 am
OLAP Product: mondrian
Version: 1.2
Excel Version: 2010

Re: Help on MDX query!!

Post by nko83 »

Thanks Marcus!
I'll try your suggestion (also for my other thread) next week.

Thanks again!
regards

nico
Post Reply