Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Mondrian not generating the correct sql

  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Default Mondrian not generating the correct sql

    I have the following schema defintion:

    <?xml version="1.0"?>
    <Schema name="Zinc">

    <Dimension name="Submission">
    <Hierarchy hasAll="true" pimaryKey="SUBMISSION_UNIT_ID">
    <Table name="LATEST_VIEW_ID"/>
    <Level name="COB Date" column="COB_DATE" uniqueMembers="true"/>
    <Level name="SOURCE" column="SOURCE" uniqueMembers="true"/>
    <Level name="NAME" column="NAME" uniqueMembers="true"/>
    </Hierarchy>
    </Dimension>

    <Cube name="ZINC" defaultMeasure="ZINC">
    <Table name="ZINC"/>
    <DimensionUsage name="Submission" source="Submission" foreignKey="SUBMISSION_UNIT_ID"/>
    <Dimension name="Measure Type">
    <Hierarchy hasAll="true">
    <Level name="Measure Type" column="MEASURE_NAME" uniqueMembers="true"/>
    </Hierarchy>
    </Dimension>

    <Measure name="measure" column="MEASURE" aggregator="sum"
    formatString="Standard"/>
    </Cube>

    </Schema>


    Mondrian is generating the following sql:
    select "LATEST_VIEW_ID"."SOURCE" as "c0", sum("ZINC"."MEASURE") as "m0" from "LATEST_VIEW_ID" as "LATEST_VIEW_ID", "ZINC" as "ZINC" where "ZINC"."SUBMISSION_UNIT_ID" = "LATEST_VIEW_ID"."NAME" group by "LATEST_VIEW_ID"."SOURCE"

    from MDX query :
    with member [Submission].[XL_QZX] as 'Aggregate({[Submission].[2011-09-07].[RV2], [Submission].[2011-09-07].[RAM], [Submission].[2011-09-07].[Endur], [Submission].[2011-09-07].[CRE]})'
    select from [ZINC]
    where ([Measures].[measure], [Submission].[XL_QZX]

    Why is mondrain not generating "ZINC"."SUBMISSION_UNIT_ID" = "LATEST_VIEW_ID"."SUBMSSION_UNIT_ID" in the where clause?

  2. #2

    Default

    Can you also post the Schema DDL - for the simple tables

  3. #3
    Join Date
    Sep 2011
    Posts
    4

    Default

    Added The following row in the 'Submission' dimension, the hack resolve the error:
    <Level name="ID" column="SUBMISSION_UNIT_ID" uniqueMembers="true"/>

    Here is the latest_view_id view definition:
    CREATE OR REPLACE VIEW LATEST_VIEW_ID AS
    SELECT SU.SUBMISSION_UNIT_ID, SU.SOURCE, SU.COB_DATE, SU."NAME", SU.PUBLISH_DATE
    FROM SUBMISSION_UNITS SU, LATEST_VIEW LV
    WHERE ((((LV.PUB_DATE = SU.PUBLISH_DATE) AND
    (SU.COB_DATE = LV.COB_DATE)) AND (SU.SOURCE = LV.SOURCE))
    AND (SU."NAME" = LV."NAME"));


    CREATE TABLE ZINC
    (
    COB_DATE DATE NOT NULL,
    PUBLISH_DATE TIMESTAMP NOT NULL,
    SOURCE CHARACTER VARYING(128) NOT NULL,
    NAME CHARACTER VARYING(128) NOT NULL,
    POSITION_ID BIGINT NOT NULL,
    SUBMISSION_UNIT_ID BIGINT NOT NULL,
    TRADER CHARACTER VARYING(32) NOT NULL,
    LEGAL_ENTITY CHARACTER VARYING(100) NOT NULL,
    SOURCE_BOOK_NAME CHARACTER VARYING(100) NOT NULL,
    SOURCE_COUNTERPARTY_NAME CHARACTER VARYING(255) NOT NULL,
    RISK_WEIGHT DOUBLE PRECISION NOT NULL,
    MEASURE DOUBLE PRECISION NOT NULL,
    CLASS CHARACTER VARYING(32) NOT NULL,
    FACTOR CHARACTER VARYING(64) NOT NULL,
    SIZE DOUBLE PRECISION NOT NULL,
    SHOCK_UNIT CHARACTER VARYING(16) NOT NULL,
    TENOR CHARACTER VARYING(16) NOT NULL,
    MEASURE_UNIT CHARACTER VARYING(16) NOT NULL,
    SCENARIO_TYPE CHARACTER VARYING(32) NOT NULL,
    MEASURE_NAME CHARACTER VARYING(32) NOT NULL,
    SOURCE_POSITION_ID CHARACTER VARYING(128) NOT NULL,
    BOOKMAP_ID INTEGER NOT NULL,
    COPER_ID INTEGER NOT NULL,
    CPT_NM CHARACTER VARYING(255) NOT NULL,
    ANALYST CHARACTER VARYING(255) NOT NULL,
    INSTRUMENT_ID BIGINT NOT NULL,
    SOURCE_ORIGINATING_SYSTEM CHARACTER VARYING(32) NOT NULL DEFAULT '[UNKNOWN]'::"NVARCHAR",
    SOURCE_DEAL_ID CHARACTER VARYING(128) NOT NULL DEFAULT '[DEFAULT]'::"NVARCHAR",
    SOURCE_INSTRUMENT_TYPE CHARACTER VARYING(64) NOT NULL DEFAULT '[UNKNOWN]'::"NVARCHAR",
    COMPONENT_LEVEL BYTEINT NOT NULL DEFAULT '0',
    DOMAIN CHARACTER VARYING(32) NOT NULL DEFAULT 'Universal'::"NVARCHAR"
    )
    DISTRIBUTE ON (INSTRUMENT_ID)
    ORGANIZE ON (COB_DATE, SUBMISSION_UNIT_ID);

  4. #4

    Default

    so the problem is resolved for you and you are all set ?

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.