CODEWITHSUNDEEP
pentahomondriansaiku
BOENDAGGER
BOENDAGGER

Reputation: 433

Mondrian all cube measures are empty

I am using the Saiku plugin on Pentaho to analize a cube. Although all demension and measurement fields are visible, there are no facts being displayed, all cells are empty. (If the "Non Empty" option is selected in the toolbar, Saiku returns "No result", when the option is not selected, a table listing all the members correctly in the columns and rows are displayed - just no cell values). Because the member values in the columns and rows are correct, I know Saiku is reading the cube xml file and MySQL data source correctly. I think there might be some issue with the specified measurements, but I cannot find any error (No errors in console or logs that I can see). Note that I get this issue no matter what dimensions I select when cubing in Saiku. This is the Mondrian schema file i created with schema workbench.

<Schema name="Test Small4">
<Cube name="Policy Cube" caption="A small test" visible="true" description="Policy data description" cache="true" enabled="true">
<Table name="fact_policy">
</Table>
<Dimension type="StandardDimension" visible="true" foreignKey="deductable_id" highCardinality="false" name="Deductable" caption="Caption: deductable">
  <Hierarchy visible="true" hasAll="false">
    <Table name="deductable">
    </Table>
    <Level name="Deductable Type" visible="true" column="type" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Deductable Limit" visible="true" column="deductable_limit" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Deductable Cap" visible="true" column="cap" type="Numeric" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>
<Dimension type="StandardDimension" visible="true" foreignKey="endorsement_id" highCardinality="false" name="Endorsement" caption="Caption: Endorsement">
  <Hierarchy visible="true" hasAll="true">
    <Table name="endorsement">
    </Table>
    <Level name="Endorsement Type" visible="true" column="type" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Endorsement Number" visible="true" column="number" type="Numeric" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="Endorsement Effective Date Regular" visible="true" column="effective_date" type="Date" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
  </Hierarchy>
</Dimension>
<Measure name="Quantity Unique Rate" column="rate" datatype="Numeric" aggregator="distinct-count" visible="true">
</Measure>
<Measure name="Average Rate" column="rate" datatype="Numeric" aggregator="avg" visible="true">
</Measure>
<Measure name="Average Premium" column="premium" datatype="Numeric" formatString="#,###" aggregator="avg" visible="true">
</Measure>
<Measure name="Num Rates" column="rate" datatype="Integer" aggregator="distinct-count" visible="true">
</Measure>
<Measure name="String test" column="rate" datatype="String" aggregator="distinct count" visible="true">
</Measure>
<Measure name="Sum test" column="system_id" datatype="Integer" aggregator="sum" visible="true">
</Measure>
<Measure name="Test" column="system_id" datatype="Integer" formatString="Standard" aggregator="count" visible="true">
</Measure>
<CalculatedMember name="Premium Rate dif" formula="[Measures].[Average Rate]-[Measures].[Average Premium]" dimension="Measures" visible="true">
</CalculatedMember>

Any idea why this might be? How can I go about debugging this issue?

Upvotes: 0

Views: 1944

Answers (1)

nsousa
nsousa

Reputation: 4544

You're missing the primaryKey attribute on the hierarchy. The Dimension element has the foreignKey attribute correctly identifying the column from the fact table, but the Hierarchy element doesn't specify a primaryKey from the dimension table to perform the join.

As such, all joins will return 0 rows. (I had no idea Mondrian would even accept such a schema, as I would expect the join condition to be malformed while testing the schema)

Upvotes: 2

Related Questions