Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Help Syntax for <join> in Schema file

  1. #1
    Join Date
    Jan 2007
    Posts
    485

    Default Help Syntax for <join> in Schema file

    I am trying to include <join> for a number of snowflake dimensions, but cannot get the syntax right. My case is as follows:

    Table1: fact_table
    - CORTE_DATE datetime, (fk1)
    - OPERACION bigint, fk2)
    - ...more columns

    Table2: dim_operaciones
    - OPERACION bigint, (pk2)
    - DESEMB_FEC, datetime (fk3) -column for the TiemposDesembolso dimension)
    - ...more columns

    Table4: dim_time2 (to be used for Dimension=TiempoDesembolso)
    - DATE_ID datetime (pk3)
    - YEAR integer,
    - QUARTERNUM, integer
    - MONTHNUM, integer
    - ...more columns

    My cube must have 2 TimeDimensions: TiemposCorte (simple and straight forward.. no problem here) and TiemposDesembolso (which requieres a "join" clause):

    The join required is (literally speaking):
    fact_table joins dim_operaciones on (fact_table.OPERACION(fk2) = dim_operaciones.OPERACION(pk2)) and
    dim_operaciones joins dim_time2 on (dim_operaciones.DESEMB_FEC(fk3) = dim_time2.DATE_ID(pk3)

    Following FoodMart examples and Mondrian Tech Paper, the Dimension I am building with the Workbench for this join is as follows (omitting the "Time Dimension" for ease of reference until I get the general Dimension with the join correct):

    <Dimension type="StandardDimension" usagePrefix="" foreignKey="OPERACION" name="TiempoDesembolso">
    <Hierarchy name="PeriodosDesembolso" hasAll="true" allMemberName="PeriodosDesembolso Todos" allMemberCaption="" primaryKey="OPERACION" primaryKeyTable="dim_operaciones" caption="">
    <Join leftAlias="dim_operaciones" leftKey="DESEMB_FEC" rightAlias="dim_time2" rightKey="DATE_ID">
    <Table name="dim_operaciones" schema="" alias="">
    </Table>
    <Table name="dim_time2" schema="" alias="">
    </Table>
    </Join>
    <Level name="Anios" table="dim_time2" column="YEAR" type="Numeric" uniqueMembers="false" levelType="Regular">
    </Level>
    <Level name="Trimestres" table="dim_time2" column="QUARTERNUM" nameColumn="QUARTER_SNAME_ES" type="Numeric" uniqueMembers="false" levelType="Regular">
    </Level>
    <Level name="Meses" table="dim_time2" column="MONTHNUM" nameColumn="MONTH_SNAME_ES" type="Numeric" uniqueMembers="false" levelType="Regular">
    </Level>
    </Hierarchy>
    </Dimension>

    When executing the and MDX Query to test my schema, I am getting an error:
    Mondrian Error:MDX object '[TiempoDesembolso.PeriodosDesembolso].[PeriodosDesembolso Todos]' not found in cube 'CD2CuboCartera'

    Please advise the correct syntax to be applied to the above specified "TiempoDesembolso" dimension.
    Regards, DMurray3
    Last edited by DMurray3; 03-04-2008 at 11:47 AM. Reason: to correct typos...

  2. #2
    Join Date
    Jan 2007
    Posts
    485

    Default

    Thank you everybody.... I reoslved the issue myself... Regards, DMurray3

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.