PDA

View Full Version : mdx - virtual cube not showing all results



bluesony
02-12-2015, 03:57 AM
I have 2 cubes (SalesCube and StockCube) with 2 shared dimensions: Shop name, Article Id. Each cube has it's own specific dimension: SalesCube has SalesDim and StockCube has StockDim. Also, each cube has it's own measure: SalesCube has "Sales value" and StockCube has "Stocks value".I created a virtual cube which contains all dimensions (2 shared and 2 specific) and both measures. The problem is that if I use a specific dimension the result will contain data only ofr the measure from the cube that has that specific dimension (the second measure is empty). If I use only shared dimensions, the report will bring results for both measures.
Practically, virtual values are blank for measures under dimensions that do not come from the same parent cube.
What am I missing ?

I found something similar but with no answers: http://forums.pentaho.com/showthread.php?130932-Virtual-Cube-Measure-Behaviour-Question

<Schema name="Shop_Sales_and_Stock">
<Dimension name="SalesDim">
<Hierarchy hasAll="true">
<Table name="Sales" primaryKey="Article_Id"/>
<Level name="SalesDim" uniqueMembers="false" column="SalesDim" type="String">
</Level>
</Hierarchy>
</Dimension>
<Dimension name="StockDim">
<Hierarchy hasAll="true">
<Table name="Stocks" primaryKey="Article_Id"/>
<Level name="StockDim" uniqueMembers="false" column="StockDim" type="String">
</Level>
</Hierarchy>
</Dimension>

<Cube name="SalesCube">
<Table name="Sales"/>
<Dimension name="Shop_name">
<Hierarchy visible="true" hasAll="true">
<Table name = "Sales"/>
<Level name="Shop name" uniqueMembers="false" column="Shop_name" type="String">
</Level>
</Hierarchy>
</Dimension>
<Dimension name="Article Id">
<Hierarchy visible="true" hasAll="true">
<Table name = "Sales"/>
<Level name="Article Id" uniqueMembers="false" column="Article_Id" type="String">
</Level>
</Hierarchy>
</Dimension>
<DimensionUsage name="SalesDim" source="SalesDim" foreignKey="Article_Id"/>
<Measure name="Sales value" column="Sales value" aggregator="sum" formatString="&#x23;"/>
</Cube>

<Cube name="StockCube">
<Table name="Stocks"/>
<Dimension name="Shop_name">
<Hierarchy visible="true" hasAll="true">
<Table name = "Stocks"/>
<Level name="Shop name" uniqueMembers="false" column="Shop_name" type="String">
</Level>
</Hierarchy>
</Dimension>
<Dimension name="Article Id">
<Hierarchy visible="true" hasAll="true">
<Table name = "Stocks"/>
<Level name="Article Id" uniqueMembers="false" column="Article_Id" type="String">
</Level>
</Hierarchy>
</Dimension>
<DimensionUsage name="StockDim" source="StockDim" foreignKey="Article_Id"/>
<Measure name="Stocks value" column="Stocks value" aggregator="sum" formatString="&#x23;"/>
</Cube>

<VirtualCube name="Shop_Sales_and_Stock">
<CubeUsages>
<CubeUsage cubeName="SalesCube"/>
<CubeUsage cubeName="StockCube"/>
</CubeUsages>
<VirtualCubeDimension name="Shop name"/>
<VirtualCubeDimension name="Article Id"/>
<VirtualCubeMeasure cubeName="SalesCube" name="[Measures].[Sales value]"/>
<VirtualCubeMeasure cubeName="StockCube" name="[Measures].[Stocks value]"/>
</VirtualCube>
</Schema>