pentahoolapmondriansaikurolap

Empty cells/offset in the report: how to define a dimension and hierarchy in the cube?


I want to analyze some dynamics of the some process. For that I use Saiku analytics plugin CE for Pentaho Business Intelligence Server CE 5.0.1.

There is a table of facts and a table of dimensions that using to perform some aggregations. Dimensions represent the hierarchy "Year - Month - Day".

I built some report in two cuts - by year and months. Report looks as follows:

enter image description here

The data it shows is correct:

enter image description here

If I define an independent dimension "Month", the report is looks right:

enter image description here

However, the data already is not right:

enter image description here

I tried to add the inverse dimension "Month - Year", but did not see any data.

Is there a way to define a dimension, where the report will not include empty cells?


Solution

  • I found the solution - the problem was in the wrong dimension of date.

    See detailed answer here: Create a date range in mysql

    New Mondrian schema:

    <Schema name="MondrianSchema">
      <Dimension type="TimeDimension" visible="true" highCardinality="false" name="X dimension">
        <Hierarchy name="X_hierarchy" visible="true" hasAll="true" primaryKey="date_key">
          <Table name="tbl_declaration_date_dim" schema="dbo">
          </Table>
          <Level name="Year" visible="true" table="tbl_declaration_date_dim" column="Year" nameColumn="Year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
          </Level>
          <Level name="Month" visible="true" table="tbl_declaration_date_dim" column="Month" nameColumn="Month" ordinalColumn="Month" type="Numeric" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
          </Level>
          <Level name="Day" visible="true" table="tbl_declaration_date_dim" column="Day" nameColumn="Day" ordinalColumn="Day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
          </Level>
        </Hierarchy>
      </Dimension>
      <Dimension type="TimeDimension" visible="true" name="Y dimension">
        <Hierarchy name="Y_Hierarchy" visible="true" hasAll="true" primaryKey="date_key">
          <Table name="tbl_declaration_date_dim" schema="dbo" alias="">
          </Table>
          <Level name="Year" visible="true" table="tbl_declaration_date_dim" column="Year" nameColumn="Year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
          </Level>
          <Level name="Month" visible="true" table="tbl_declaration_date_dim" column="Month" nameColumn="Month" ordinalColumn="Month" type="Numeric" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
          </Level>
          <Level name="Day" visible="true" table="tbl_declaration_date_dim" column="Day" nameColumn="Day" ordinalColumn="Day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
          </Level>
        </Hierarchy>
      </Dimension>
      <Cube name="tbl_application_cube" caption="..." visible="true" description="..." cache="true" enabled="true">
        <Table name="tbl_appl_olap_fact" schema="dbo">
        </Table>
        <DimensionUsage source="X dimension" name="X axis" visible="true" foreignKey="date_dim" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="Y dimension" name="Y axis" visible="true" foreignKey="date_dim">
        </DimensionUsage>
        <Measure name="DeclarationCount" column="declaration_id" aggregator="count" visible="true">
        </Measure>
      </Cube>
    </Schema>