Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Sub Query Not working in CUBE

  1. #1

    Default Sub Query Not working in CUBE

    Hello,

    I am using pentaho 5.0 and hadoop 2.0 for my current scenario, I have created a cube with some fact and dimensions when i add a dimension with a view in dimension it's not working and throwing error when I use a dialect.

    coluld you please have a look in the below scenario :

    Choose Dialect : hive:
    Error : MondrianException: Mondrian Error:Internal error: View has no 'generic' variant

    Choose Dialect : generic: with no schema name in query
    SQLException: org.apache.spark.sql.AnalysisException: no such table dim_fin_sx_sub_account; line 10 pos 5

    Choose Dialect: with schema name
    SQLException: org.apache.spark.sql.AnalysisException: cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in subquery source; line 10 pos 51

    But when I check the query in log it is like this is like below :

    select count(*) from (select distinct kemu . sub_account_code1 as c0 from (select
    sub_account_code1,
    sub_account_desc1,
    sub_account_code2,
    sub_account_desc2,
    sub_account_code3,
    sub_account_desc3,
    sub_account_code4,
    sub_account_desc4
    from jt_mas_safe.dim_fin_sx_sub_account) as kemu )

    this sql query is also throwing error




    but when I add the alias to this query is working fine like below

    select count(*) from (select distinct kemu . sub_account_code1 as c0 from (select
    sub_account_code1,
    sub_account_desc1,
    sub_account_code2,
    sub_account_desc2,
    sub_account_code3,
    sub_account_desc3,
    sub_account_code4,
    sub_account_desc4
    from jt_mas_safe.dim_fin_sx_sub_account) as kemu )aa

    Here with I am attaching the cube code snippet :

    <Dimension type="StandardDimension" visible="true" foreignKey="sub_account_code" highCardinality="false" name="科目" caption="科目">
    <Hierarchy name="科目" visible="true" hasAll="true" primaryKey="sub_account_code4" caption="科目">
    <View alias="kemu">
    <SQL dialect="generic">
    <![CDATA[select
    sub_account_code1,
    sub_account_desc1,
    sub_account_code2,
    sub_account_desc2,
    sub_account_code3,
    sub_account_desc3,
    sub_account_code4,
    sub_account_desc4
    from jt_mas_safe.dim_fin_sx_sub_account]]>
    </SQL>
    </View>
    <Level name="科目1" visible="true" column="sub_account_code1" nameColumn="sub_account_desc1" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="科目1">
    </Level>
    <Level name="科目2" visible="true" column="sub_account_code2" nameColumn="sub_account_desc2" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="科目2">
    </Level>
    <Level name="科目3" visible="true" column="sub_account_code3" nameColumn="sub_account_desc3" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="科目3">
    </Level>
    <Level name="科目4" visible="true" column="sub_account_code4" nameColumn="sub_account_desc4" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption="科目4">
    </Level>
    </Hierarchy>
    </Dimension>


    Thanks in advance.

    Regards

    Nitesh

  2. #2

    Default

    Hello,

    I have solved this by editing mondrian.jar and added spark dialect.

    Community Rocks !!

    Regards

    Nitesh

Tags for this Thread

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.