Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Import Mondrian Schema from Pentaho 5.1.0.0.752 to Pentaho 7.1.0.0.12

  1. #1

    Default Import Mondrian Schema from Pentaho 5.1.0.0.752 to Pentaho 7.1.0.0.12

    Hi all,


    I was importing im my company everything from Pentaho Pentaho 5.1.0.0.752 to Pentaho 7.1.0.0.12, including:


    Pentaho BI
    Pentaho PDI
    Saiku Plugins
    Create a clone of the DDBB
    etc etc


    Everything is fine but some MDX queries are not working fine.


    In pentaho 7, in the manage date source section, I imported the schema "Controlling_CM.mondrian.xml", and dashboard were fine at 90% of contents.


    I localize the problem, and is that this MDX query works perfectly in pentaho 5 (Done at 1.45s) and is continuosly thinking in pentaho 7




    This is the MDX query:


    Code:
    WITHMEMBER Measures.[O1 Turnover] AS '(Measures.[SP16 01 Turnover]) * 100'
    MEMBER Measures.[   Sales] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Sales Final] * 100, Measures.[Sales Final] / 1000 )'
    MEMBER Measures.[   Sales Portfolio] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Invoice portfolio Final] * 100, Measures.[Invoice portfolio Final] / 1000)'
    MEMBER Measures.[O2 Profitability] AS '(Measures.[SP16 02 Profitability]) * 100'
    MEMBER Measures.[   RBT] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Net Margin (%) Final] * 100, Measures.[Net Margin (%) Final]* 100)' 
    MEMBER Measures.[   (DD+AS)/Sales] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[(DD+AS) Sales Final] * 100, Measures.[(DD+AS) Sales Final] * 100)'
    MEMBER Measures.[O3 Customer Diversification] AS 'Measures.[SP16 03 Customer Diversification] * 100'
    MEMBER Measures.[   Customer Concentration] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Customer Concentration Final] * 100, Measures.[Customer Concentration Final])'
    MEMBER Measures.[   Main Cust. Dependency] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Main Customer Dependency Final] * 100, Measures.[Main Customer Dependency Final] * 100)'
    MEMBER Measures.[   Cust. w/ Sales > 500K EUR] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[# Customers Sales > 500K EUR Final] * 100, Measures.[# Customers Sales > 500K EUR Final])'
    MEMBER Measures.[O4 Business Diversification] AS 'Measures.[SP16 04 Business Diversification] * 100'
    MEMBER Measures.[   Sales Non-traditional Business] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Sales Non-traditional Business Final] * 100, Measures.[Sales Non-traditional Business Final] / 1000)'
    MEMBER Measures.[   Offers Non-traditional Business] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Offers Non-traditional Business Final] * 100, Measures.[Offers Non-traditional Business Final] / 1000)'
    MEMBER Measures.[O5 Geographical Diversification] AS '(Measures.[SP16 05 Geografical Diversification]) * 100'
    MEMBER Measures.[   Subsidiaries Concentration] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Subsidiaries Concentration Final] * 100, Measures.[Subsidiaries Concentration Final])'
    MEMBER Measures.[O6 LEP Development] AS '(Measures.[SP16 06 Posicionar como integrador de Soluciones de explotacion y optimizacion]) * 100'
    MEMBER Measures.[   LEP Sales / Sales] AS '(Measures.[LEP Sales (%)] * 100)'
    MEMBER Measures.[O7 Increase Operational Marketing] AS '(Measures.[SP16 07 Increase Operational Marketing]) * 100'
    MEMBER Measures.[   Amount Proposal] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Amount Proposal Final] * 100,  Measures.[Amount Proposal Final] / 1000)'
    MEMBER Measures.[O8 Improve Technical Knowledge] AS '(Measures.[SP16 08 Improve Technical Knowledge]) * 100'
    MEMBER Measures.[   Training Hours per Employee] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Personal training hours] * 100, Measures.[Personal training hours])'
    MEMBER Measures.[O9 Guarantee QH&S] AS '(Measures.[SP16 09 Guarantee QH&S]) * 100'
    MEMBER Measures.[   MSI / SSI] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[MSI Final] * 100, Measures.[MSI Final])'
    MEMBER Measures.[   Incidence Rate] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Incidence Rate Final] * 100,  Measures.[Incidence Rate Final])'
    MEMBER Measures.[   MQI / SQI] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[MQI Final] * 100,  Measures.[MQI Final])'
    MEMBER Measures.[   Cust. Complaints / 20K Hrs Wkd] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[RC NC_20kht Final] * 100,  Measures.[RC NC_20kht Final])'
    MEMBER Measures.[O10 Improve Strategical Marketing] AS '(Measures.[SP16 10 Improvement Strategical Marketing]) * 100'
    MEMBER Measures.[   Success Ratio] AS '(Measures.[Success Ratio]) * 100'
    MEMBER Measures.[O11 HR Policies] AS '(Measures.[SP16 11 HR Policies]) * 100'
    MEMBER Measures.[   Absenteeism Rt] AS '(Measures.[Absenteeism (%) Final]) * 100'
    MEMBER Measures.[O12 Cost Optimization] AS '(Measures.[SP16 12 Cost Optimization]) * 100'
    MEMBER Measures.[   % Gross Mg Cum.] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Gross Margin (%) Final] * 100, Measures.[Gross Margin (%) Final]* 100)' 
    MEMBER Measures.[   Non Production Overhead] AS 'Iif([Comparative scenario].Currentmember IS [Comparative scenario].[A / SP], Measures.[Non Production Overhead Final] * 100, Measures.[Non Production Overhead Final] / 1000)'
    
    SET [All_Cons] AS {
    Measures.[O1 Turnover],
    Measures.[O2 Profitability],
    //Measures.[O3 Customer Diversification],
    Measures.[O4 Business Diversification],
    Measures.[O5 Geographical Diversification],
    Measures.[O6 LEP Development],
    Measures.[O7 Increase Operational Marketing],
    Measures.[O8 Improve Technical Knowledge],
    Measures.[O9 Guarantee QH&S],
    Measures.[O10 Improve Strategical Marketing],
    Measures.[O11 HR Policies],
    Measures.[O12 Cost Optimization]
    } 
    
    
    SET [All_NonTrad] AS {
    Measures.[O1 Turnover],
    Measures.[O2 Profitability],
    Measures.[O3 Customer Diversification],
    Measures.[O4 Business Diversification],
    Measures.[O5 Geographical Diversification],
    Measures.[O6 LEP Development],
    Measures.[O7 Increase Operational Marketing],
    Measures.[O8 Improve Technical Knowledge],
    Measures.[O9 Guarantee QH&S],
    Measures.[O10 Improve Strategical Marketing],
    Measures.[O11 HR Policies],
    Measures.[O12 Cost Optimization]
    } 
    
    
    SET [All] AS {
    Measures.[O1 Turnover],
    Measures.[O2 Profitability],
    //Measures.[O3 Customer Diversification],
    Measures.[O4 Business Diversification],
    //Measures.[O5 Geographical Diversification],
    Measures.[O6 LEP Development],
    Measures.[O7 Increase Operational Marketing],
    Measures.[O8 Improve Technical Knowledge],
    Measures.[O9 Guarantee QH&S],
    Measures.[O10 Improve Strategical Marketing],
    Measures.[O11 HR Policies],
    Measures.[O12 Cost Optimization]
    } 
    
    
    SET [O1 Turnover] AS {
    Measures.[O1 Turnover],
    Measures.[   Sales], 
    Measures.[   Sales Portfolio]
    }
    
    
    SET [O2 Profitability] AS {
    Measures.[O2 Profitability],
    Measures.[   RBT],
    Measures.[   (DD+AS)/Sales]
    }
    
    
    SET [O3 Customer Diversification] AS {
    Measures.[O3 Customer Diversification],
    Measures.[   Customer Concentration],
    Measures.[   Main Cust. Dependency],
    Measures.[   Cust. w/ Sales > 500K EUR]
    }
    
    
    SET [O4 Business Diversification] AS {
    Measures.[O4 Business Diversification],
    Measures.[   Sales Non-traditional Business],
    Measures.[   Offers Non-traditional Business]}
    
    
    SET [O5 Geographical Diversification] AS {
    Measures.[O5 Geographical Diversification],
    Measures.[   Subsidiaries Concentration]
    }
    
    
    SET [O6 LEP Development] AS {
    Measures.[O6 LEP Development],
    Measures.[   LEP Sales / Sales]
    }
    
    
    SET [O7 Increase Operational Marketing] AS {
    Measures.[O7 Increase Operational Marketing],
    Measures.[   Amount Proposal]}
    
    
    SET [O8 Improve Technical Knowledge] AS {
    Measures.[O8 Improve Technical Knowledge],
    Measures.[   Training Hours per Employee]}
    
    
    SET [O9 Guarantee QH&S] AS {
    Measures.[O9 Guarantee QH&S],
    Measures.[   MSI / SSI],
    Measures.[   Incidence Rate],
    Measures.[   MQI / SQI],
    Measures.[   Cust. Complaints / 20K Hrs Wkd]}
    
    
    SET [O10 Improve Strategical Marketing] AS {
    Measures.[O10 Improve Strategical Marketing],
    Measures.[   Success Ratio]}
    
    
    SET [O11 HR Policies] AS {
    Measures.[O11 HR Policies],
    Measures.[   Absenteeism Rt]}
    
    
    SET [O12 Cost Optimization] AS {
    Measures.[O12 Cost Optimization],
    Measures.[   % Gross Mg Cum.],
    Measures.[   Non Production Overhead]
    } 
    
    
    SELECT [All_Cons] ON ROWS,
    {[Comparative scenario].[Strategic Plan], [Comparative scenario].[Actual], [Comparative scenario].[A / SP], [Comparative scenario].[A / SP]} ON COLUMNS
    
    
    FROM [Aggr - CMI]
    WHERE ([Previous year].[Current Year])

  2. #2

    Default

    In fact, I have checked this query in Saiku and it works fine in Pentaho 5, but never give results in Pentaho 7.

    PD: Thanks to the moderator for authorize my post.

  3. #3

    Default

    I have checked with the last version of Schema workbench that the XML file about mondrian schema, and that showt an error about: "Invalid.Mondrian Error: Named set in cube............"

    https://ibb.co/nQ1AaQ


    I was reading about to change the property to fix that error "#mondrian.rolap.ignoreInvalidMembers=true" in mondrian.properties file.


    I did it and the import process and publishing in pentaho trough Schema workbench was fine.


    But the problem with the MDX query still happening.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    If you read the error more carefully, it says that one of the named sets has a bad formula (ie cannot be calculated) which is why it won't let you load it.
    Turning on "Ignore Invalid Members" just moved the symptom.

    I can't tell you what the solution is, but it seems to be that you've defined something incorrectly.

  5. #5

    Default

    I have found the problem:


    When I export the XML mondrian schema, there aer a lot of symbols like :


    & instance of &
    > instance of >
    " instance of "


    and that´s the reason formulas are bads.


    I´m trying to convert to the correcto codification (UTF-8 I think) but I do not know to get it.


    Pentaho server is inside of a Windows Server Standard SP2 64 Bits, And I tried to import to a Pentaho Server 7.1 inside of a Ubuntu server and a windows 10 , but It does not works fine.


    I must convert that XML file or try to get that file correctly doing something in the Original Pentaho server ?


    Thanks a million.

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Repeating...
    Quote Originally Posted by gutlez View Post
    I can't tell you what the solution is, but it seems to be that you've defined something incorrectly.

  7. #7

    Default

    Quote Originally Posted by gutlez View Post
    Repeating...
    Thanks again, but yoy say I've defined something incorrectly. But ¿ in Pentaho 7 (Import xml) or Pentaho 5 (Export XML) ?

    Thanks in advance.

  8. #8

    Default

    I have tried all I saw in forums and Jira:


    1.- http://jira.pentaho.com/browse/MONDRIAN-1751


    I cant find where to change this code:


    Schema in UTF-8 encoding could not read, i had to change the code:
    public static String readVirtualFileAsString(
    String catalogUrl)
    throws IOException
    {
    InputStream in = readVirtualFile(catalogUrl);
    try
    { String s = IOUtils.toString(in, "UTF-8"); System.out.println(s); return s; }
    finally
    { IOUtils.closeQuietly(in); }
    }


    And I dont know if is correct for mondrian 3.14.


    2.-https://github.com/pentaho/mondrian/pull/156/files#diff-73bb34058f8edceecdc360fb6a251e0d




    According to that, I must change this code:
    InputStream in = readVirtualFile(catalogUrl);
    try {
    final byte[] bytes = Util.readFully(in, 1024);
    - final char[] chars = new char[bytes.length];
    - for (int i = 0; i < chars.length; i++) {
    - chars[i] = (char) bytes[i];
    - }
    - return new String(chars);
    + return new String(bytes, "UTF-8");
    } finally {
    if (in != null) {
    in.close();




    But I cant find this path in my Pentaho instalation:


    mondrian/src/main/mondrian/olap/Util.java


    3.- http://jira.pentaho.com/browse/MONDRIAN-1351


    I cant find the code:


    In class mondrian.olap.Util, line 3363:




    4.- http://forums.pentaho.com/showthread...acter-encoding


    I tried with ANSI and UTF-8


    And headers according to those encodings:
    <?xml version="1.0" encoding="utf-8"?>
    <?xml version="1.0" encoding="ANSI"?>


    5.- With NotePad++ I have tried to change the encoding to iso 8859-1
    and the header
    <?xml version="1.0" encoding="iso-8859-1"?>






    I must convert that XML file or try to get that file correctly doing something in the Original Pentaho server ?




    5.- http://ingmmurillo-dwh-bi.blogspot.com.es/2015/02/


    I have tried to change this code:


    Folder BI Server \biserver-ce-5.x


    start-pentaho file and set this:


    CATALINA_OPTS=-Dsun.jnu.encoding=UTF-8 -Dfile.encoding=UTF-8 -Xms


    I need some advices about that, I know I must chage something, but I don´t know how to carry on trying .............


    Really Thanks a million.

  9. #9
    Join Date
    Aug 2006
    Posts
    287

    Default

    I have seen a similar issue with the less than sign I do not think is matter setting a encoding; I think you have to change the offending character to a proper xml attribute

  10. #10

    Default

    I was checking, and the formula about sintaxis in the Schema workbench (3.14 ):

    Name:  Schema Workbench.jpg
Views: 307
Size:  18.1 KB

    This is the XML code:

    formula="iif( [Comparative Scenario].[Comparative scenario].CurrentMember.name="A / SP", iif((iif(IsEmpty([Measures].[Sales Final]), 0, 1) + iif(IsEmpty([Measures].[Invoice portfolio Final]), 0, 1)) > 1, ([Measures].[Sales Final] + [Measures].[Invoice portfolio Final])/ (iif(IsEmpty([Measures].[Sales Final]), 0, 1) + iif(IsEmpty([Measures].[Invoice portfolio Final]), 0, 1)), null) , null)"

    and this is the Schema Workbench code:

    iif( [Comparative Scenario].[Comparative scenario].CurrentMember.name="A / SP", iif((iif(IsEmpty([Measures].[Sales Final]), 0, 1) + iif(IsEmpty([Measures].[Invoice portfolio Final]), 0, 1)) > 1, ([Measures].[Sales Final] + [Measures].[Invoice portfolio Final])/ (iif(IsEmpty([Measures].[Sales Final]), 0, 1) + iif(IsEmpty([Measures].[Invoice portfolio Final]), 0, 1)), null) , null)

    But console still talking that in that:

    .xml is invalid.Mondrian Error:Named set in cube 'Aggr - CMI' has bad formulamondrian.olap.MondrianException: Mondrian Error:Named set in cube 'Aggr - CMI' has bad formula.

    I have deleted all formulas except which I´m talking about, in the cube 'Aggr - CMI' and still reporting " Mondrian Error:Named set in cube 'Aggr - CMI' has bad formula."


    Some suggestions???

    Thans in advance.
    Attached Images Attached Images  
    Last edited by Moterosk; 08-04-2017 at 07:30 AM.

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.