analyticspentahosaiku

Saiku query shows no data


So, I installed pentaho bi server on my debian8 server. Then managed to install saiku on it. Now, I have used workbench to make a cube and published it into the server.

Here's the problem: when I access a saiku query, I DO can load my published cube, but when I try to navigate it, that is, establishing the measure and some dimension field in both columns and rows, NO DATA SHOWS!

And I know the database is not empty, thank you very much. Here is the fact_table, if it is helpful (the dimensions have the shown id's plus their own data, of course):

CREATE TABLE sc_fact_avi.fact_avi
(
  id_fact bigserial NOT NULL,
  id_empresa smallint,
  id_cliente integer,
  id_segmento bigint,
  id_usuario bigint,
  id_campana bigint,
  id_resultado smallint,
  id_telefono bigint,
  id_fecha_llamada integer,
  id_canal integer,
  id_geografia integer,
  id_hora_llamada integer,
  id_hora_contestacion integer,
  id_hora_inicio_asr integer,
  id_hora_fin_asr integer,
  id_hora_fin_llamada integer,
  id_respuesta_p1 smallint,
  id_respuesta_p2 smallint,
  id_respuesta_p3 smallint,
  id_respuesta_p4 smallint,
  id_respuesta_p5 smallint,
  id_respuesta_p6 smallint,
  id_respuesta_p7 smallint,
  id_respuesta_p8 smallint,
  id_respuesta_p9 smallint,
  id_respuesta_p10 smallint,
  id_respuesta_p11 smallint,
  id_respuesta_p12 smallint,
  id_respuesta_p13 smallint,
  id_respuesta_p14 smallint,
  id_respuesta_p15 smallint,
  link_grabacion character varying,
  intento character(1),
  cant_llamadas smallint DEFAULT 1,
  CONSTRAINT pk_id_fact PRIMARY KEY (id_fact),
  CONSTRAINT fk_id_campana FOREIGN KEY (id_campana)
      REFERENCES sc_dim_avi.dim_campana (id_campana) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_canal FOREIGN KEY (id_canal)
      REFERENCES sc_dim_avi.dim_canal (id_canal) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_cliente FOREIGN KEY (id_cliente)
      REFERENCES sc_dim_avi.dim_cliente (id_cliente) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_empresa FOREIGN KEY (id_empresa)
      REFERENCES sc_dim_avi.dim_empresa (id_empresa) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_fecha FOREIGN KEY (id_fecha_llamada)
      REFERENCES sc_dim_avi.dim_fecha (id_fecha) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_fin_asr FOREIGN KEY (id_hora_fin_asr)
      REFERENCES sc_dim_avi.dim_hora (id_hora) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_geografia FOREIGN KEY (id_geografia)
      REFERENCES sc_dim_avi.dim_geografia (id_geografia) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_hora_fin_llamada FOREIGN KEY (id_hora_fin_llamada)
      REFERENCES sc_dim_avi.dim_hora (id_hora) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_hora_llamada FOREIGN KEY (id_hora_llamada)
      REFERENCES sc_dim_avi.dim_hora (id_hora) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_llamada_contestacion FOREIGN KEY (id_hora_contestacion)
      REFERENCES sc_dim_avi.dim_hora (id_hora) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p1 FOREIGN KEY (id_respuesta_p1)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p10 FOREIGN KEY (id_respuesta_p10)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p11 FOREIGN KEY (id_respuesta_p11)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p12 FOREIGN KEY (id_respuesta_p12)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p13 FOREIGN KEY (id_respuesta_p13)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p14 FOREIGN KEY (id_respuesta_p14)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p15 FOREIGN KEY (id_respuesta_p15)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p2 FOREIGN KEY (id_respuesta_p2)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p3 FOREIGN KEY (id_respuesta_p3)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p4 FOREIGN KEY (id_respuesta_p4)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p5 FOREIGN KEY (id_respuesta_p5)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p6 FOREIGN KEY (id_respuesta_p6)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p7 FOREIGN KEY (id_respuesta_p7)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p8 FOREIGN KEY (id_respuesta_p8)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_respuesta_p9 FOREIGN KEY (id_respuesta_p9)
      REFERENCES sc_dim_avi.dim_respuesta (id_respuesta) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_resultado_funcional FOREIGN KEY (id_resultado)
      REFERENCES sc_dim_avi.dim_resultado (id_resultado) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_segmento FOREIGN KEY (id_segmento)
      REFERENCES sc_dim_avi.dim_segmento (id_segmento) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_telefono FOREIGN KEY (id_telefono)
      REFERENCES sc_dim_avi.dim_telefono (id_telefono) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_id_usuario FOREIGN KEY (id_usuario)
      REFERENCES sc_dim_avi.dim_usuario (id_usuario) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT if_id_hora_inicio_asr FOREIGN KEY (id_hora_inicio_asr)
      REFERENCES sc_dim_avi.dim_hora (id_hora) MATCH FULL
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
)
TABLESPACE ts_fact_avi;
ALTER TABLE sc_fact_avi.fact_avi
  OWNER TO desarrollo;

I've tried modifying the cube from workbench several times and nothing. Here it is, I know it is not complete, but if it doesn't work with only a couple of tables, how will it with all of them (or maybe I'm calling the wrong fields?):

    <Schema name="Schema_cubo">
<Dimension type="StandardDimension" visible="true" highCardinality="false" name="Campana">
    <Hierarchy name="jerarquiaCampana" visible="true" hasAll="true" primaryKey="id_campana">
        <Table name="dim_campana" schema="sc_dim_avi">
        </Table>
        <Level name="Campanas" visible="true" column="cd_campana" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
    </Hierarchy>
</Dimension>
<Dimension type="StandardDimension" visible="true" highCardinality="false" name="Cliente">
    <Hierarchy name="jerarquiaCliente" visible="true" hasAll="true" primaryKey="id_cliente">
        <Table name="dim_cliente" schema="sc_dim_avi">
        </Table>
        <Level name="Cliente" visible="true" column="cd_cliente" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
        </Level>
    </Hierarchy>
</Dimension>
<Cube name="Cubo_reporteadorprueba" visible="true" cache="true" enabled="true">
    <Table name="fact_avi" schema="sc_fact_avi">
    </Table>
    <DimensionUsage source="Campana" name="Campana" visible="true" foreignKey="id_campana" highCardinality="false">
    </DimensionUsage>
    <DimensionUsage source="Cliente" name="Cliente" visible="true" foreignKey="id_cliente" highCardinality="false">
    </DimensionUsage>
    <Measure name="Cantidad_llamadas" column="cant_llamadas" datatype="Integer" aggregator="sum" visible="true">
    </Measure>
</Cube>
</Schema>    

Uninstalled and reinstalled saiku, yet nothing. I'm not sure if maybe just maybe, the pentaho bi server may not have been installed properly.

Is it because of different tablespaces? different schemas? Help! Thank you for your replies and your time.

EDIT

I changed the cube (to the code now shown), and now it won't even show in the select cube option of saiku :/


Solution

  • It turned out that the most recent version of Pentaho BI server is not compatible with Saiku. I installed the previous stable version and now the saiku queries show the data.