Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: URGENT HELP Request: Agg Tables Not being Recognized

  1. #1
    Join Date
    Jan 2007
    Posts
    485

    Default URGENT HELP Request: Agg Tables Not being Recognized

    Hi all,

    I would very much appreciate someone´s help. I can´t get agg tables to be recognized in the PCI when calling up a JPivot on a large cube I have.

    I have followed eactly what the Mondrian 2.2 technical paper call for when using agg tables; I have further set "mondrian.rolap.aggregates.generateSql=true" in the mondrian.properties file, and have built my 3 initial agg tables following those suggested in the cmd.exe run.

    Furthermore, I have been carefull to comply with the "agg_pattern" as well as with the "agg table column naming convention" and all my attempts, Mondrian Recognizer shows warining messages not recognizing the agg field names I have, which is very strange since I created the agg following the recommended definitions.

    For the purpose of further review, I am enclosing:
    - my cube´s mondrian.xml file
    - my configutation "particulars" to include the PCI startup, my mondrian.properties settings, etc.
    - the cmd.exe warining messages displayed when using the agg tables created

    Please note that in my cube's mondrian.xml file I have commented the "agg" attributes I have tried to use (for the time-being and until I can correct any mistakes I might have).

    Please help.... getting the agg tables to work is "cornerstone" to my project's approval and rollout.

    Regards, DMurray3
    Last edited by DMurray3; 03-17-2008 at 04:02 PM.

  2. #2
    wgorman Guest

    Default

    Hi DMurray3,

    If you post your db schema along with the mondrian schema, that might help diagnose the problem. The warnings I see in the " Error agg table fields not recognized 17 Mar 08" are concerning. In the future, if you could post the compressed files as zips vs. rar's, that might also help. Many people don't have a rar extractor.

    Will

  3. #3
    Join Date
    Jan 2007
    Posts
    485

    Default More info: URGENT HELP Request: Agg Tables Not being Recognized

    Thanks Will..

    I have removed the previuos .rar file and enclose herein the .zip file to include the db schema (png file) along with my cube´s schema mondrian file.

    Again many thanks for your interest in helping me out.

    Regards, DMurray
    Last edited by DMurray3; 04-03-2008 at 11:28 AM.

  4. #4
    wgorman Guest

    Default

    I can't open the file "Agg tables recommended by cmd.exe run.txt" what character encoding are you using? Could you convert it to utf8? I took a look at your schema, nothing in there raised red flags. The aggregate tables aren't listed in the graphic view, those are really what I need access to.

    My only hunch right now is that there may be a non-ascii column name issue with agg tables possibly. If you could convert those columns over to regular ascii, that might fix your problem.

    Also, another experiment you can do is explicitly list a single agg table that you have defined in your cube, and see if that gets used. That will at least narrow down the issue to the rules portion of the agg table functionality.

    Good luck! As you are discovering Aggregate Tables are not the easiest technology to use.

    Will

  5. #5
    Join Date
    Jan 2007
    Posts
    485

    Default

    Will, following the lines of the "Agg tables recommended by cmd.exe run.txt" ... it was saved using "unicode".

    09:47:27,031 WARN [AggGen] not a measure: CALIFICACION
    09:47:27,031 INFO [STDOUT] createLost:
    CREATE TABLE agg_l_XXX_cartera_fact (
    N┌MERO_DEUDORES VARCHAR(14) NOT NULL,
    N┌MERO_OPERACIONES BIGINT NOT NULL,
    CUOTAS_PACTADAS INT,
    CUOTAS_PAGADAS INT,
    SALDO_MES FLOAT,
    SALDO_VIG FLOAT,
    SALDO_VCDO FLOAT,
    PROVISION_MV FLOAT,
    PROVISION_ACELERADA FLOAT,
    MONTO_ORIGINAL FLOAT,
    RENTA_LIQUIDA FLOAT,
    VALOR_CUOTA FLOAT,
    MXTN FLOAT,
    MXTE FLOAT,
    fact_count INTEGER NOT NULL
    );
    09:47:27,031 INFO [STDOUT] insertIntoLost:
    INSERT INTO agg_l_XXX_cartera_fact (
    N┌MERO_DEUDORES,
    N┌MERO_OPERACIONES,
    CUOTAS_PACTADAS,
    CUOTAS_PAGADAS,
    SALDO_MES,
    SALDO_VIG,
    SALDO_VCDO,
    PROVISION_MV,
    PROVISION_ACELERADA,
    MONTO_ORIGINAL,
    RENTA_LIQUIDA,
    VALOR_CUOTA,
    MXTN,
    MXTE,
    fact_count)
    SELECT
    COUNT(`CARTERA_FACT`.`CEDULA_IDENTIDAD`) AS `CEDULA_IDENTIDAD`,
    COUNT(`CARTERA_FACT`.`OPERACION`) AS `OPERACION`,
    SUM(`CARTERA_FACT`.`CUOTAS_PACTADAS`) AS `CUOTAS_PACTADAS`,
    SUM(`CARTERA_FACT`.`CUOTAS_PAGADAS`) AS `CUOTAS_PAGADAS`,
    SUM(`CARTERA_FACT`.`SALDO_MES`) AS `SALDO_MES`,
    SUM(`CARTERA_FACT`.`SALDO_VIG`) AS `SALDO_VIG`,
    SUM(`CARTERA_FACT`.`SALDO_VCDO`) AS `SALDO_VCDO`,
    SUM(`CARTERA_FACT`.`PROVISION_MV`) AS `PROVISION_MV`,
    SUM(`CARTERA_FACT`.`PROVISION_ACELERADA`) AS `PROVISION_ACELERADA`,
    SUM(`CARTERA_FACT`.`MONTO_ORIGINAL`) AS `MONTO_ORIGINAL`,
    SUM(`CARTERA_FACT`.`RENTA_LIQUIDA`) AS `RENTA_LIQUIDA`,
    SUM(`CARTERA_FACT`.`VALOR_CUOTA`) AS `VALOR_CUOTA`,
    SUM(`CARTERA_FACT`.`MXTN`) AS `MXTN`,
    SUM(`CARTERA_FACT`.`MXTE`) AS `MXTE`,
    COUNT(*) AS `fact_count`
    FROM
    `cartera_fact` `cartera_fact`
    GROUP BY
    09:47:27,031 INFO [STDOUT] createCollapsed:
    CREATE TABLE agg_c_XXX_cartera_fact (
    N┌MERO_DEUDORES VARCHAR(14) NOT NULL,
    N┌MERO_OPERACIONES BIGINT NOT NULL,
    CUOTAS_PACTADAS INT,
    CUOTAS_PAGADAS INT,
    SALDO_MES FLOAT,
    SALDO_VIG FLOAT,
    SALDO_VCDO FLOAT,
    PROVISION_MV FLOAT,
    PROVISION_ACELERADA FLOAT,
    MONTO_ORIGINAL FLOAT,
    RENTA_LIQUIDA FLOAT,
    VALOR_CUOTA FLOAT,
    MXTN FLOAT,
    MXTE FLOAT,
    fact_count INTEGER NOT NULL
    );
    09:47:27,031 INFO [STDOUT] insertIntoCollapsed:
    INSERT INTO agg_c_XXX_cartera_fact (
    N┌MERO_DEUDORES,
    N┌MERO_OPERACIONES,
    CUOTAS_PACTADAS,
    CUOTAS_PAGADAS,
    SALDO_MES,
    SALDO_VIG,
    SALDO_VCDO,
    PROVISION_MV,
    PROVISION_ACELERADA,
    MONTO_ORIGINAL,
    RENTA_LIQUIDA,
    VALOR_CUOTA,
    MXTN,
    MXTE,
    fact_count)
    SELECT
    COUNT(`CARTERA_FACT`.`CEDULA_IDENTIDAD`) AS `CEDULA_IDENTIDAD`,
    COUNT(`CARTERA_FACT`.`OPERACION`) AS `OPERACION`,
    SUM(`CARTERA_FACT`.`CUOTAS_PACTADAS`) AS `CUOTAS_PACTADAS`,
    SUM(`CARTERA_FACT`.`CUOTAS_PAGADAS`) AS `CUOTAS_PAGADAS`,
    SUM(`CARTERA_FACT`.`SALDO_MES`) AS `SALDO_MES`,
    SUM(`CARTERA_FACT`.`SALDO_VIG`) AS `SALDO_VIG`,
    SUM(`CARTERA_FACT`.`SALDO_VCDO`) AS `SALDO_VCDO`,
    SUM(`CARTERA_FACT`.`PROVISION_MV`) AS `PROVISION_MV`,
    SUM(`CARTERA_FACT`.`PROVISION_ACELERADA`) AS `PROVISION_ACELERADA`,
    SUM(`CARTERA_FACT`.`MONTO_ORIGINAL`) AS `MONTO_ORIGINAL`,
    SUM(`CARTERA_FACT`.`RENTA_LIQUIDA`) AS `RENTA_LIQUIDA`,
    SUM(`CARTERA_FACT`.`VALOR_CUOTA`) AS `VALOR_CUOTA`,
    SUM(`CARTERA_FACT`.`MXTN`) AS `MXTN`,
    SUM(`CARTERA_FACT`.`MXTE`) AS `MXTE`,
    COUNT(*) AS `fact_count`
    FROM
    `cartera_fact` `cartera_fact`,
    WHERE
    GROUP BY
    ;
    09:47:36,953 ERROR [STDERR] Warning: Running an XSLT 1.0 stylesheet with an XSLT
    2.0 processor
    09:47:37,109 ERROR [STDERR] Warning: Running an XSLT 1.0 stylesheet with an XSLT
    2.0 processor

    I think you have a point when saying "...be a non-ascii column name issue with agg tables possibly...".

    The tables in my MYSQL are all named in capital letters, ie.: no strange latin characters; how ever as can be noted in the schema´s mondrian.xml file, I DO use latin characters (ie. á, é,

  6. #6
    Join Date
    Jan 2007
    Posts
    485

    Default

    By the way, the agg tables´s created in my db are as follows:

    -- Table "agg_c_default_cartera_fact" DDL
    CREATE TABLE `agg_c_default_cartera_fact` (
    `NÚMERO_DEUDORES` bigint(20) NOT NULL,
    `NÚMERO_OPERACIONES` bigint(20) NOT NULL,
    `CUOTAS_PACTADAS` int(11) default NULL,
    `CUOTAS_PAGADAS` int(11) default NULL,
    `SALDO_MES` float default NULL,
    `SALDO_VIG` float default NULL,
    `SALDO_VCDO` float default NULL,
    `PROVISIÓN_MV` float default NULL,
    `PROVISIÓN_ACELERADA` float default NULL,
    `MONTO_ORIGINAL` float default NULL,
    `RENTA_LÍQUIDA` float default NULL,
    `VALOR_CUOTA` float default NULL,
    `MXTN` float default NULL,
    `MXTE` float default NULL,
    `fact_count` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    -- Table "agg_c_iriesgofeccorcos_cartera_fact" DDL
    CREATE TABLE `agg_c_iriesgofeccorcos_cartera_fact` (
    `COSECHAS2YEAR` int(11) default NULL,
    `COSECHAS2QUARTERNUM` int(11) default NULL,
    `COSECHAS2MONTHNUM` int(11) default NULL,
    `CORTESYEAR` int(11) default NULL,
    `CORTESQUARTERNUM` int(11) default NULL,
    `CORTESMONTHNUM` int(11) default NULL,
    `NÚMERO_DEUDORES` bigint(20) NOT NULL,
    `NÚMERO_OPERACIONES` bigint(20) NOT NULL,
    `CUOTAS_PACTADAS` int(11) default NULL,
    `CUOTAS_PAGADAS` int(11) default NULL,
    `SALDO_MES` float default NULL,
    `SALDO_VIG` float default NULL,
    `SALDO_VCDO` float default NULL,
    `PROVISIÓN_MV` float default NULL,
    `PROVISIÓN_ACELERADA` float default NULL,
    `MONTO_ORIGINAL` float default NULL,
    `RENTA_LÍQUIDA` float default NULL,
    `VALOR_CUOTA` float default NULL,
    `MXTN` float default NULL,
    `MXTE` float default NULL,
    `fact_count` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    -- Table "agg_l_default_cartera_fact" DDL
    CREATE TABLE `agg_l_default_cartera_fact` (
    `NÚMERO_DEUDORES` varchar(14) NOT NULL,
    `NÚMERO_OPERACIONES` bigint(20) NOT NULL,
    `CUOTAS_PACTADAS` int(11) default NULL,
    `CUOTAS_PAGADAS` int(11) default NULL,
    `SALDO_MES` float default NULL,
    `SALDO_VIG` float default NULL,
    `SALDO_VCDO` float default NULL,
    `PROVISIÓN_MV` float default NULL,
    `PROVISIÓN_ACELERADA` float default NULL,
    `MONTO_ORIGINAL` float default NULL,
    `RENTA_LÍQUIDA` float default NULL,
    `VALOR_CUOTA` float default NULL,
    `MXTN` float default NULL,
    `MXTE` float default NULL,
    `fact_count` int(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    I trust I have not ommitted anything... thanks, Daniel

  7. #7
    Join Date
    Jan 2007
    Posts
    485

    Default

    Will, More on the detail of the db schema and the agg tables that have been built enclosed in the .png.

    I again would appreciate continuing the assitance.

    Regards, DMurray3
    Last edited by DMurray3; 04-03-2008 at 11:28 AM.

  8. #8
    wgorman Guest

    Default

    From the Aggregate Tables doc:

    http://mondrian.pentaho.org/document...ate_tables.php

    "There are three sub rules for matching level columns. Each is a template which is parameterized with 1) the fact table's cube's dimension hierarchy's name, "hierarchy_name", 2) the fact table's cube's dimension hierarchy's level name, "level_name", 3) the dimension table's level column name, "level_column_name", and 4) a usage prefix, "usage_prefix", which in most cases is null":"

    I suggest using level_column_name vs. level_name. You will need to manually rename the suggested columns in your agg tables to match those rules. My hunch is that the special characters in your agg tables are messing things up.

    Will

  9. #9
    Join Date
    Jan 2007
    Posts
    485

    Default

    Hi Will and all,
    Well.. triumph at last!!! We have finally managed to get our agg tables recognized and used.
    This is what we did:

    - we removed all special latin charcaters;
    - eliminated the Hierarchy name="..." to allow the hierarchy_name to take-on the default name (ie. Dimension name)
    - we eliminated all unused attributes (these were inserted while using Workbench)
    - we changed the agg table column names and used the "level_column_name" as you suggested.
    Doing so, we were finallyable to get the agg tables recognized and used.

    We then back-tracked and reinserted the latin characters (test along the way..) and the agg tables continued to be recognized and used. This to proove that the special latin chars were not necessarily causing our problems (and moreover, because we HAD to have them...).

    We can´t tell for sure which of the changes made everything work, but at least I can say that apparently the following issues coincided :
    - Having too many unused attributes in he Schema's Dimensions;
    - Not letting Mondrian assume the defaulk Hierarchy_name;
    - Mondrian suggested the agg tables to create and how (setting "mondrian.rolap.aggregates.generateSql=true" in jboss\mondrian.properties). Therein, Mondrian suggested using "usagePrefixlevel_column_name". Following Will's suggestions we changed our agg table columns to use "level_column_name" (ie. ommitting the usagePrefix).
    - And -last but not least- I don´t doubt that ading to our problem were some typos which we corrected along the way.

    Thanks Will for the guidance... you made us proof read (again...) the Mondrian 2.2 Tech Manual and review in even closer detail our Schema and AGG table defintion...

    Should anyone like to see how the Schema / agg tables look now, let me know so that I may post it.
    Regards, DMurray3

  10. #10
    wgorman Guest

    Default

    Glad to hear that you were successful! Definitely post your schema here so others in the future who encounter similar issues can see how you resolved the problems you were having.

    Will

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.