How to Make Inner join with MDX

[Neveen Adel]

Hello,
I have the following dimensions:

<Dimension name="Years">
<Hierarchy hasAll="true" allMemberName="ALL Years"
primaryKey="fiscalYear">
<Table name="OLAP_Projects"/>
<Level name="Years" column="fiscalYear" uniqueMembers="true"/>
</Hierarchy>
</Dimension>

<Dimension name="LOCATIONS">
<Hierarchy hasAll="true" allMemberName="ALL LOCATIONS"
primaryKey="projectLocation_id">
<Table name="OLAP_Projects"/>
<Level name="location1" column="location1" />
<Level name="location2" column="location2"
hideMemberIf="IfBlankName"/>
<Level name="location3" column="location3"
hideMemberIf="IfBlankName"/>
</Hierarchy>
</Dimension>

<Dimension name="program">
<Hierarchy hasAll="true" allMemberName="programs"
primaryKey="programId">
<Table name="OLAP_Projects"/>
<Level name="program" column="program" />
<Level name="subProgram" column="subProgram"
uniqueMembers="false" hideMemberIf="IfBlankName"/>
</Hierarchy>
</Dimension>

<Dimension name="program Type">
<Hierarchy hasAll="true" allMemberName="programs Types"
primaryKey="programTypeId">
<Table name="OLAP_Projects"/>
<Level name="programType" column="programType"
uniqueMembers="false" />
</Hierarchy>
</Dimension>

<Dimension name="project Name">
<Hierarchy hasAll="true" allMemberName="project Name"
primaryKey="projectNameId">
<Table name="OLAP_Projects"/>
<Level name="projectName" column="projectName"
uniqueMembers="false"/>
</Hierarchy>
</Dimension>

and The following cube :
<Cube name="Project">
<Table name="OLAP_Projects"/>
<DimensionUsage name="Years" source="Years" foreignKey="Years"/>
<DimensionUsage name="LOCATIONS" source="LOCATIONS"
foreignKey="projectLocation_id"/>
<DimensionUsage name="program" source="program"
foreignKey="programId"/>
<DimensionUsage name="program Type" source="program Type"
foreignKey="programTypeId"/>
<DimensionUsage name="project Name" source="project Name"
foreignKey="projectNameId"/>
<Measure name="Governmental Budget used in project"
column="actualGovernmentalBudget" aggregator="sum" datatype="Numeric"
formatString="#,###"/>
<Measure name="Internal Budget used in projects"
column="actualInternalBudget" aggregator="sum" datatype="Numeric"
formatString="#,###"/>
<Measure name="Grant Budget used in projects" column="grantFund"
aggregator="sum" datatype="Numeric" formatString="#,###"/>
<Measure name="Loans Budget used in projects" column="loanFund"
aggregator="sum" datatype="Numeric" formatString="#,###"/>
<Measure name="# of Projects" column="projectId" aggregator="distinct-
count" datatype="Numeric" />
</Cube>


I want to display per location the program of the project , then
program type, and finally project Nam on a specific level of location.


I tried the following MDX query:
select NON EMPTY Crossjoin({[Years].[ALL Years].Children}, {[Measures].
[Governmental Budget used in project], [Measures].[Internal Budget
used in projects], [Measures].[Grant Budget used in projects],
[Measures].[Loans Budget used in projects],[Measures].[# of
Projects]}) ON COLUMNS,
NON EMPTY Crossjoin({[LOCATIONS].[ALL LOCATIONS].Children},
Crossjoin({[program].Children}, Crossjoin({[program Type].Children},
{[project Name].[project Name].Children}))) ON ROWS
from [Project]


But it display for a location at level one all projects that are in
level1 and also sub-levels, i think that because crossjoin(get all
combinations).

Could you please help me to do what i want?

Thanks in Advance

Neveen

[Raimonds Simanovskis]

It was really hard to understand what result exactly you would like to get - if you could provide picture with some "pivot table" example what you would like on rows, what on columns and what in cells for each row and cell then probably I could help.

One thing that I noticed is that if you have just one flat table with all measures and dimensions as well then you should use "degenerate dimensions" syntax, see http://mondrian.pentaho.com/documentation/schema.php#Degenerate_dimensions

You should define separately dimensions if you use separate dimension tables.

Raimonds