Pentaho Reporting and Pentaho Report Designer
  1. Pentaho Reporting and Pentaho Report Designer
  2. PRD-4003

PRD should allow all Mondrian connection properties to go through to Mondrian, particularly JDBCConnectionUUID, which would allow reporting and analysis to share the same SQL cache.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • QA Validation Status:
      Validated by QA

      Description

      When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
      With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

      Requirements:
      Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
      Add the value pair table UI with add and remove. see attached screenshot of table.

      Test Case: JDBCConnectionUUID

      1. Enable sql logging in the log4j.xml file on the BA Server.
         <!-- ========================================================= -->
         <!-- Special Log File specifically for Mondrian MDX Statements -->
         <!-- ========================================================= -->

         
         <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
           <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
           <param name="Append" value="false"/>
           <param name="MaxFileSize" value="500KB"/>
           <param name="MaxBackupIndex" value="1"/>
           <layout class="org.apache.log4j.PatternLayout">
             <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
           </layout>
         </appender>

         <category name="mondrian.mdx">
            <priority value="DEBUG"/>
            <appender-ref ref="MDXLOG"/>
         </category>
        

         <!-- ========================================================= -->
         <!-- Special Log File specifically for Mondrian SQL Statements -->
         <!-- ========================================================= -->

         
         <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
           <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
           <param name="Append" value="false"/>
           <param name="MaxFileSize" value="500KB"/>
           <param name="MaxBackupIndex" value="1"/>
           <layout class="org.apache.log4j.PatternLayout">
             <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
           </layout>
         </appender>

         <category name="mondrian.sql">
            <priority value="DEBUG"/>
            <appender-ref ref="SQLLOG"/>
         </category>
         


      2. Add a JDBC Connection UUID on the server.

      <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
        <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
      </Catalog>
       
      3. Run the attached Analyzer report (.xanalyzer) which contains the query:

      With
      Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], Not IsEmpty ([Measures].[Quantity]))'
      Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC)'
      Set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
      Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
      Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Markets].currentMember)})'
      Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
      Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = '#,###', SOLVE_ORDER=400
      Select
      [*BASE_MEMBERS_Measures] on columns,
      [*SORTED_ROW_AXIS] on rows
      From [SteelWheelsSales]

      4. Run the attached .prpt which contains the following connection properties defined:
      UseContentChecksum=true
      JdbcConnectionUuid=SampleDataUUID

      and the same query as the Analyzer report in step 3.

      RESULTS: PASSED.

      I see one SQL entry in the log file ...

      2012-10-15 11:09:10,720 DEBUG [mondrian.sql] 0: HighCardSqlTupleReader.readTuples [[Markets].[Territory]]: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY" having NOT((sum("ORDERFACT"."QUANTITYORDERED") is null)) order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC]
      2012-10-15 11:09:10,851 DEBUG [mondrian.sql] 0: , exec 123 ms
      2012-10-15 11:09:10,870 DEBUG [mondrian.sql] 0: , exec+fetch 148 ms, 4 rows
      2012-10-15 11:09:10,962 DEBUG [mondrian.sql] 1: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct "TERRITORY") from "CUSTOMER_W_TER"]
      2012-10-15 11:09:10,981 DEBUG [mondrian.sql] 1: , exec 12 ms
      2012-10-15 11:09:10,997 DEBUG [mondrian.sql] 1: , exec+fetch 30 ms, 1 rows
      2012-10-15 11:09:11,116 DEBUG [mondrian.sql] 2: Segment.load: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY"]
      2012-10-15 11:09:11,297 DEBUG [mondrian.sql] 2: , exec 169 ms
      2012-10-15 11:09:11,335 DEBUG [mondrian.sql] 2: , exec+fetch 208 ms, 4 rows

      Two MDX logged for each report ( Analyzer and Reporting (.prpt))

      2012-10-15 11:09:10,536 DEBUG [mondrian.mdx] 44: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
        set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
        set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
        set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
        set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
        set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
        member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
      select [*BASE_MEMBERS_Measures] ON COLUMNS,
        [*SORTED_ROW_AXIS] ON ROWS
      from [SteelWheelsSales]

      2012-10-15 11:09:11,340 DEBUG [mondrian.mdx] 44: exec: 801 ms
      2012-10-15 11:10:40,667 DEBUG [mondrian.mdx] 102: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
        set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
        set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
        set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
        set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
        set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
        member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
      select [*BASE_MEMBERS_Measures] ON COLUMNS,
        [*SORTED_ROW_AXIS] ON ROWS
      from [SteelWheelsSales]

      2012-10-15 11:10:40,675 DEBUG [mondrian.mdx] 102: exec: 8 ms
       

      1. mondrian_mdx.log
        2 kB
        Kurtis Cruzada
      2. mondrian_sql.log
        1 kB
        Kurtis Cruzada
      3. mondrian-ds.xml
        2 kB
        Sulaiman Karmali
      4. pentaho.log
        8 kB
        Kurtis Cruzada
      5. Test Connection.xanalyzer
        2 kB
        Kurtis Cruzada
      1. mockup.jpg
        68 kB
      2. screenshot-1.jpg
        29 kB

        Issue Links

          Activity

          Pedro Vale created issue -
          Hide
          Tiago Gomes Ferreira added a comment -
          added a way to set default connection properties (will be overridden by the properties it already exposes) in AbstractMDXDataFactory (extensions-mondrian in reporting engine)
            trunk: revision 15997
            3.9: revision 15998
          Show
          Tiago Gomes Ferreira added a comment - added a way to set default connection properties (will be overridden by the properties it already exposes) in AbstractMDXDataFactory (extensions-mondrian in reporting engine)   trunk: revision 15997   3.9: revision 15998
          Will Gorman made changes -
          Field Original Value New Value
          Status Open [ 1 ] Open [ 1 ]
          Assignee Triage [ project admin ] Pedro Vale [ pamval ]
          Fix Version/s 3.9.1 GA (4.8.0 GA Suite Release) [ 11455 ]
          Pedro Vale made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Pedro Vale made changes -
          Status In Progress [ 3 ] Open [ 1 ]
          Pedro Vale made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Hide
          Brandon Bruce added a comment -
          Pedro, are you validating this?
          Show
          Brandon Bruce added a comment - Pedro, are you validating this?
          Hide
          Brandon Bruce added a comment -
          *Use the latest trunk version for mondrian
          *Edit the datasources.xml from:
                      <DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo>
          to
                      <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
          *Create a report using a mdx query from SampleData and run it inside the ba-server.
          *Check the mondrian.log file for references to sql execution.
          You'll need to check the mondrian.log file for references to sql execution.

          If you now run the same query in JPivot or Saiku you should not see any sql query info appearing in the mondrian log files.


          Show
          Brandon Bruce added a comment - *Use the latest trunk version for mondrian *Edit the datasources.xml from:             <DataSourceInfo>Provider=mondrian;DataSource=SampleData</DataSourceInfo> to             <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo> *Create a report using a mdx query from SampleData and run it inside the ba-server. *Check the mondrian.log file for references to sql execution. You'll need to check the mondrian.log file for references to sql execution. If you now run the same query in JPivot or Saiku you should not see any sql query info appearing in the mondrian log files.
          Brandon Bruce made changes -
          Assignee Pedro Vale [ pamval ] Brandon Bruce [ bbruce ]
          Will Gorman made changes -
          Assignee Brandon Bruce [ bbruce ] Will Gorman [ wgorman ]
          Will Gorman made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Will Gorman made changes -
          Assignee Will Gorman [ wgorman ] Pedro Vale [ pamval ]
          Hide
          Will Gorman added a comment -
          This is still not functional in PRD.
          Show
          Will Gorman added a comment - This is still not functional in PRD.
          Will Gorman made changes -
          Assignee Pedro Vale [ pamval ] Will Gorman [ wgorman ]
          Hide
          Will Gorman added a comment -
          Hi Kurtis, please work on describing how dev should implement arbitrary Mondrian properties in the UI.

          Thanks!

          Will
          Show
          Will Gorman added a comment - Hi Kurtis, please work on describing how dev should implement arbitrary Mondrian properties in the UI. Thanks! Will
          Will Gorman made changes -
          Assignee Will Gorman [ wgorman ] Kurtis Cruzada [ kcruzada ]
          Kurtis Cruzada made changes -
          Attachment screenshot-1.jpg [ 36016 ]
          Kurtis Cruzada made changes -
          Attachment screenshot-2.jpg [ 36024 ]
          Kurtis Cruzada made changes -
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.
          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.
          Kurtis Cruzada made changes -
          Status Reopened [ 4 ] Open [ 1 ]
          Assignee Kurtis Cruzada [ kcruzada ] Sulaiman Karmali [ skarmali ]
          Kurtis Cruzada made changes -
          Attachment mockup.jpg [ 36025 ]
          Kurtis Cruzada made changes -
          Attachment screenshot-2.jpg [ 36024 ]
          Sulaiman Karmali made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Show
          Sulaiman Karmali added a comment - http://mondrian.pentaho.com/documentation/configuration.php#Property_list
          Hide
          Sulaiman Karmali added a comment -
          Committed to 3.9 feature branch (needs to be migrated to trunk). PRD user can now enter additional Mondrian parameters to a Mondrian data-source.
          Show
          Sulaiman Karmali added a comment - Committed to 3.9 feature branch (needs to be migrated to trunk). PRD user can now enter additional Mondrian parameters to a Mondrian data-source.
          Sulaiman Karmali made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Hide
          Sulaiman Karmali added a comment -
          Please verify functionality
          Show
          Sulaiman Karmali added a comment - Please verify functionality
          Sulaiman Karmali made changes -
          Assignee Sulaiman Karmali [ skarmali ] Kurtis Cruzada [ kcruzada ]
          Hide
          Kurtis Cruzada added a comment -
          Show
          Kurtis Cruzada added a comment - This is the connection properties list. http://mondrian.pentaho.com/documentation/configuration.php#Connect_string_properties
          Kurtis Cruzada made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Kurtis Cruzada made changes -
          Status Reopened [ 4 ] Open [ 1 ]
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.
          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          To test whether the connection JDBCConnectionUUID is working:

          BA Server Requirements:

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          Create a report using a mdx query (JPivot) from Steel Wheels and run it inside the ba-server.

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          3. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          4. You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.




          Kurtis Cruzada made changes -
          Status Open [ 1 ] Open [ 1 ]
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          To test whether the connection JDBCConnectionUUID is working:

          BA Server Requirements:

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          Create a report using a mdx query (JPivot) from Steel Wheels and run it inside the ba-server.

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          3. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          4. You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.




          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          To test whether the connection JDBCConnectionUUID is working:

          Test Case:

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          Create a report using a mdx query (JPivot) from Steel Wheels and run it inside the ba-server.

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          3. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          4. You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.




          Kurtis Cruzada made changes -
          Attachment Test Connection Properties.analysisview.xaction [ 36228 ]
          Attachment Test Connection Properties.prpt [ 36229 ]
          Kurtis Cruzada made changes -
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          To test whether the connection JDBCConnectionUUID is working:

          Test Case:

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          Create a report using a mdx query (JPivot) from Steel Wheels and run it inside the ba-server.

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          3. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          4. You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.




          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          Create a report using a mdx query (JPivot) from Steel Wheels and run it inside the ba-server.

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          3. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          4. You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.



          Hide
          Kurtis Cruzada added a comment -
          Ok. Despite the fact that I can't add a new Analysis connection. I can edit an existing in a report. Adding connection properties seems to work fine. However, if you see Brandon's test case in the comments. Using a JDBCConnectionUUID should only generate the sql query once in mondrian, however, this is NOT the case. It appears that it is working per content type and NOT working across content types.
          Show
          Kurtis Cruzada added a comment - Ok. Despite the fact that I can't add a new Analysis connection. I can edit an existing in a report. Adding connection properties seems to work fine. However, if you see Brandon's test case in the comments. Using a JDBCConnectionUUID should only generate the sql query once in mondrian, however, this is NOT the case. It appears that it is working per content type and NOT working across content types.
          Kurtis Cruzada made changes -
          Status Open [ 1 ] Open [ 1 ]
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          Create a report using a mdx query (JPivot) from Steel Wheels and run it inside the ba-server.

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          3. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          4. You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.



          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          3. Run the attached JPivot report (.xaction) which contains the query:

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          4. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          and the same query as the JPivot report in step 3.

          RESULTS:

          You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.



          Kurtis Cruzada made changes -
          Status Open [ 1 ] Open [ 1 ]
          Assignee Kurtis Cruzada [ kcruzada ] Unassigned User [ unassigned ]
          Sulaiman Karmali made changes -
          Assignee Unassigned User [ unassigned ] Sulaiman Karmali [ skarmali ]
          Hide
          Sulaiman Karmali added a comment -
          The issue that you can't add a new analysis connection has been fixed. After buildguy builds the next release you should be able to add it. So if I add a new property in mondrian connection property dialog with a key of JDBCConnectionUUID and a value of SampleDataUUID, and they deploy the report to BA Server and execute I should only see on sql query (instead of two) right?

          The property JDBCConnectionUUID does not appear in the URL you provided. Is this documented anywhere?
          Show
          Sulaiman Karmali added a comment - The issue that you can't add a new analysis connection has been fixed. After buildguy builds the next release you should be able to add it. So if I add a new property in mondrian connection property dialog with a key of JDBCConnectionUUID and a value of SampleDataUUID, and they deploy the report to BA Server and execute I should only see on sql query (instead of two) right? The property JDBCConnectionUUID does not appear in the URL you provided. Is this documented anywhere?
          Sulaiman Karmali made changes -
          Link This issue relates to MONDRIAN-1231 [ MONDRIAN-1231 ]
          Sulaiman Karmali made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Hide
          Sulaiman Karmali added a comment -
          I am able to reproduce the problem. Am seeing two sql statement entries in the logs. The first one coming from the published PRPT file that was attached to this case. The second being an xaction that was also attached to this case.

          Will investigate further. Currently we are passing in properties as a separate xml element (mondrian-properties). I have a feeling that Mondrian is expecting them in the connection string. Can anyone validate that?

          In the mondrian-ds.xml file, we are passing in parameters in the mondrian-properties tag:
              <data:mondrian-properties>
                  <data:property name="UseContentChecksum">true</data:property>
                  <data:property name="JdbcConnectionUuid">SampleDataUUID</data:property>
              </data:mondrian-properties>

          The connection parameters:
              <data:connection design-time-name="SampleData">
                  <data:jndi>
                      <data:path>SampleData</data:path>
                  </data:jndi>
                  <data:cube-file>
                      <data:cube-filename>../pentaho/biserver-ee/4.6-204/biserver-ee/pentaho-solutions/steel-wheels/analysis/steelwheels.mondrian.xml</data:cube-filename>
                  </data:cube-file>
              </data:connection>


          Show
          Sulaiman Karmali added a comment - I am able to reproduce the problem. Am seeing two sql statement entries in the logs. The first one coming from the published PRPT file that was attached to this case. The second being an xaction that was also attached to this case. Will investigate further. Currently we are passing in properties as a separate xml element (mondrian-properties). I have a feeling that Mondrian is expecting them in the connection string. Can anyone validate that? In the mondrian-ds.xml file, we are passing in parameters in the mondrian-properties tag:     <data:mondrian-properties>         <data:property name="UseContentChecksum">true</data:property>         <data:property name="JdbcConnectionUuid">SampleDataUUID</data:property>     </data:mondrian-properties> The connection parameters:     <data:connection design-time-name="SampleData">         <data:jndi>             <data:path>SampleData</data:path>         </data:jndi>         <data:cube-file>             <data:cube-filename>../pentaho/biserver-ee/4.6-204/biserver-ee/pentaho-solutions/steel-wheels/analysis/steelwheels.mondrian.xml</data:cube-filename>         </data:cube-file>     </data:connection>
          Hide
          Sulaiman Karmali added a comment -
          Attached is the mondrian-ds.xml with the mondrian-properties element (was just added to support passing parameters to Mondrian)
          Show
          Sulaiman Karmali added a comment - Attached is the mondrian-ds.xml with the mondrian-properties element (was just added to support passing parameters to Mondrian)
          Sulaiman Karmali made changes -
          Attachment mondrian-ds.xml [ 36238 ]
          Hide
          Tiago Gomes Ferreira added a comment -
          Yes, the properties will have to end up in the connection string.
          If they are passed to AbstractMDXDataFactory#setBaseConnectionProperties(Properties) they will be included in the mondrian connection string by DefaultMondrianConnectionProvider.
          Show
          Tiago Gomes Ferreira added a comment - Yes, the properties will have to end up in the connection string. If they are passed to AbstractMDXDataFactory#setBaseConnectionProperties(Properties) they will be included in the mondrian connection string by DefaultMondrianConnectionProvider.
          Sulaiman Karmali made changes -
          Assignee Sulaiman Karmali [ skarmali ] Kurtis Cruzada [ kcruzada ]
          Kurtis Cruzada made changes -
          Attachment Test Connection Properties.analysisview.xaction [ 36228 ]
          Kurtis Cruzada made changes -
          Attachment Test Connection Properties.prpt [ 36229 ]
          Kurtis Cruzada made changes -
          Attachment Test Connection Properties.prpt [ 36248 ]
          Attachment Test Connection.xanalyzer [ 36249 ]
          Attachment mondrian_mdx.log [ 36250 ]
          Attachment mondrian_sql.log [ 36251 ]
          Attachment pentaho.log [ 36252 ]
          Kurtis Cruzada made changes -
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          3. Run the attached JPivot report (.xaction) which contains the query:

          select NON EMPTY {[Measures].[Quantity]} ON COLUMNS,
            NON EMPTY {([Markets].[All Markets], [Customers].[All Customers], [Product].[All Products], [Time].[All Years], [Order Status].[All Status Types])} ON ROWS
          from [SteelWheelsSales]
           

          4. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          and the same query as the JPivot report in step 3.

          RESULTS:

          You should only see one SQL entry in the log file according to the Brandon in comments. Something like this...

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows

          However, I saw two entries after I launched jPivot and the .prpt.

          2012-10-12 12:44:13,129 DEBUG [mondrian.sql] 0: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:13,193 DEBUG [mondrian.sql] 0: , exec 58 ms
          2012-10-12 12:44:13,212 DEBUG [mondrian.sql] 0: , exec+fetch 82 ms, 1 rows
          2012-10-12 12:44:37,085 DEBUG [mondrian.sql] 1: Segment.load: executing sql [select sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "ORDERFACT" as "ORDERFACT"]
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec 9 ms
          2012-10-12 12:44:37,095 DEBUG [mondrian.sql] 1: , exec+fetch 10 ms, 1 rows
           
          Additional notes:
          It appears that it is per content type. If I launch JPivot multiple times, I don't get any additional queries in the sql log and likewise for the .prpt.



          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          3. Run the attached Analyzer report (.xanalyzer) which contains the query:

          With
          Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], Not IsEmpty ([Measures].[Quantity]))'
          Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC)'
          Set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
          Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
          Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Markets].currentMember)})'
          Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
          Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = '#,###', SOLVE_ORDER=400
          Select
          [*BASE_MEMBERS_Measures] on columns,
          [*SORTED_ROW_AXIS] on rows
          From [SteelWheelsSales]

          4. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          and the same query as the Analyzer report in step 3.

          RESULTS:

          I see one SQL entry in the log file ...

          2012-10-15 11:09:10,720 DEBUG [mondrian.sql] 0: HighCardSqlTupleReader.readTuples [[Markets].[Territory]]: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY" having NOT((sum("ORDERFACT"."QUANTITYORDERED") is null)) order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC]
          2012-10-15 11:09:10,851 DEBUG [mondrian.sql] 0: , exec 123 ms
          2012-10-15 11:09:10,870 DEBUG [mondrian.sql] 0: , exec+fetch 148 ms, 4 rows
          2012-10-15 11:09:10,962 DEBUG [mondrian.sql] 1: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct "TERRITORY") from "CUSTOMER_W_TER"]
          2012-10-15 11:09:10,981 DEBUG [mondrian.sql] 1: , exec 12 ms
          2012-10-15 11:09:10,997 DEBUG [mondrian.sql] 1: , exec+fetch 30 ms, 1 rows
          2012-10-15 11:09:11,116 DEBUG [mondrian.sql] 2: Segment.load: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY"]
          2012-10-15 11:09:11,297 DEBUG [mondrian.sql] 2: , exec 169 ms
          2012-10-15 11:09:11,335 DEBUG [mondrian.sql] 2: , exec+fetch 208 ms, 4 rows

          Two MDX logged for each report ( Analyzer and Reporting (.prpt))

          2012-10-15 11:09:10,536 DEBUG [mondrian.mdx] 44: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
            set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
            set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
            set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
            set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
            set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
            member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
          select [*BASE_MEMBERS_Measures] ON COLUMNS,
            [*SORTED_ROW_AXIS] ON ROWS
          from [SteelWheelsSales]

          2012-10-15 11:09:11,340 DEBUG [mondrian.mdx] 44: exec: 801 ms
          2012-10-15 11:10:40,667 DEBUG [mondrian.mdx] 102: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
            set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
            set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
            set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
            set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
            set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
            member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
          select [*BASE_MEMBERS_Measures] ON COLUMNS,
            [*SORTED_ROW_AXIS] ON ROWS
          from [SteelWheelsSales]

          2012-10-15 11:10:40,675 DEBUG [mondrian.mdx] 102: exec: 8 ms
           

          Kurtis Cruzada made changes -
          Summary PRD should allow all Mondrian connection properties to go through to Mondrian PRD should allow all Mondrian connection properties to go through to Mondrian, particularly JDBCConnectionUUID, which would allow reporting and analysis to share the same SQL cache.
          Kurtis Cruzada made changes -
          Description When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          3. Run the attached Analyzer report (.xanalyzer) which contains the query:

          With
          Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], Not IsEmpty ([Measures].[Quantity]))'
          Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC)'
          Set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
          Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
          Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Markets].currentMember)})'
          Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
          Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = '#,###', SOLVE_ORDER=400
          Select
          [*BASE_MEMBERS_Measures] on columns,
          [*SORTED_ROW_AXIS] on rows
          From [SteelWheelsSales]

          4. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          and the same query as the Analyzer report in step 3.

          RESULTS:

          I see one SQL entry in the log file ...

          2012-10-15 11:09:10,720 DEBUG [mondrian.sql] 0: HighCardSqlTupleReader.readTuples [[Markets].[Territory]]: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY" having NOT((sum("ORDERFACT"."QUANTITYORDERED") is null)) order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC]
          2012-10-15 11:09:10,851 DEBUG [mondrian.sql] 0: , exec 123 ms
          2012-10-15 11:09:10,870 DEBUG [mondrian.sql] 0: , exec+fetch 148 ms, 4 rows
          2012-10-15 11:09:10,962 DEBUG [mondrian.sql] 1: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct "TERRITORY") from "CUSTOMER_W_TER"]
          2012-10-15 11:09:10,981 DEBUG [mondrian.sql] 1: , exec 12 ms
          2012-10-15 11:09:10,997 DEBUG [mondrian.sql] 1: , exec+fetch 30 ms, 1 rows
          2012-10-15 11:09:11,116 DEBUG [mondrian.sql] 2: Segment.load: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY"]
          2012-10-15 11:09:11,297 DEBUG [mondrian.sql] 2: , exec 169 ms
          2012-10-15 11:09:11,335 DEBUG [mondrian.sql] 2: , exec+fetch 208 ms, 4 rows

          Two MDX logged for each report ( Analyzer and Reporting (.prpt))

          2012-10-15 11:09:10,536 DEBUG [mondrian.mdx] 44: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
            set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
            set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
            set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
            set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
            set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
            member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
          select [*BASE_MEMBERS_Measures] ON COLUMNS,
            [*SORTED_ROW_AXIS] ON ROWS
          from [SteelWheelsSales]

          2012-10-15 11:09:11,340 DEBUG [mondrian.mdx] 44: exec: 801 ms
          2012-10-15 11:10:40,667 DEBUG [mondrian.mdx] 102: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
            set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
            set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
            set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
            set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
            set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
            member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
          select [*BASE_MEMBERS_Measures] ON COLUMNS,
            [*SORTED_ROW_AXIS] ON ROWS
          from [SteelWheelsSales]

          2012-10-15 11:10:40,675 DEBUG [mondrian.mdx] 102: exec: 8 ms
           

          When creating a Mondrian connection, PRD only uses some of the properties in the catalog string.
          With the addition of the jdbcConnectionUID feature, PRD should allow all of the properties to go through to mondrian.

          Requirements:
          Re-purpose the existing "Security" button. Rename to "Properties". see attached screenshot of button to re-purpose.
          Add the value pair table UI with add and remove. see attached screenshot of table.

          Test Case: JDBCConnectionUUID

          1. Enable sql logging in the log4j.xml file on the BA Server.
             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian MDX Statements -->
             <!-- ========================================================= -->

             
             <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_mdx.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.mdx">
                <priority value="DEBUG"/>
                <appender-ref ref="MDXLOG"/>
             </category>
            

             <!-- ========================================================= -->
             <!-- Special Log File specifically for Mondrian SQL Statements -->
             <!-- ========================================================= -->

             
             <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
               <param name="File" value="/Applications/pentaho48/server/biserver-ee/logs/mondrian_sql.log"/>
               <param name="Append" value="false"/>
               <param name="MaxFileSize" value="500KB"/>
               <param name="MaxBackupIndex" value="1"/>
               <layout class="org.apache.log4j.PatternLayout">
                 <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
               </layout>
             </appender>

             <category name="mondrian.sql">
                <priority value="DEBUG"/>
                <appender-ref ref="SQLLOG"/>
             </category>
             


          2. Add a JDBC Connection UUID on the server.

          <Catalog name="SteelWheels" <DataSourceInfo>Provider=mondrian;DataSource=SampleData;UseContentChecksum=true;JdbcConnectionUuid=SampleDataUUID</DataSourceInfo>
            <Definition>solution:steel-wheels/analysis/steelwheels.mondrian.xml</Definition>
          </Catalog>
           
          3. Run the attached Analyzer report (.xanalyzer) which contains the query:

          With
          Set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], Not IsEmpty ([Measures].[Quantity]))'
          Set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS],[Markets].CurrentMember.OrderKey,BASC)'
          Set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
          Set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
          Set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {([Markets].currentMember)})'
          Set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
          Member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = '#,###', SOLVE_ORDER=400
          Select
          [*BASE_MEMBERS_Measures] on columns,
          [*SORTED_ROW_AXIS] on rows
          From [SteelWheelsSales]

          4. Run the attached .prpt which contains the following connection properties defined:
          UseContentChecksum=true
          JdbcConnectionUuid=SampleDataUUID

          and the same query as the Analyzer report in step 3.

          RESULTS: PASSED.

          I see one SQL entry in the log file ...

          2012-10-15 11:09:10,720 DEBUG [mondrian.sql] 0: HighCardSqlTupleReader.readTuples [[Markets].[Territory]]: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY" having NOT((sum("ORDERFACT"."QUANTITYORDERED") is null)) order by CASE WHEN "CUSTOMER_W_TER"."TERRITORY" IS NULL THEN 1 ELSE 0 END, "CUSTOMER_W_TER"."TERRITORY" ASC]
          2012-10-15 11:09:10,851 DEBUG [mondrian.sql] 0: , exec 123 ms
          2012-10-15 11:09:10,870 DEBUG [mondrian.sql] 0: , exec+fetch 148 ms, 4 rows
          2012-10-15 11:09:10,962 DEBUG [mondrian.sql] 1: SqlStatisticsProvider.getColumnCardinality: executing sql [select count(distinct "TERRITORY") from "CUSTOMER_W_TER"]
          2012-10-15 11:09:10,981 DEBUG [mondrian.sql] 1: , exec 12 ms
          2012-10-15 11:09:10,997 DEBUG [mondrian.sql] 1: , exec+fetch 30 ms, 1 rows
          2012-10-15 11:09:11,116 DEBUG [mondrian.sql] 2: Segment.load: executing sql [select "CUSTOMER_W_TER"."TERRITORY" as "c0", sum("ORDERFACT"."QUANTITYORDERED") as "m0" from "CUSTOMER_W_TER" as "CUSTOMER_W_TER", "ORDERFACT" as "ORDERFACT" where "ORDERFACT"."CUSTOMERNUMBER" = "CUSTOMER_W_TER"."CUSTOMERNUMBER" group by "CUSTOMER_W_TER"."TERRITORY"]
          2012-10-15 11:09:11,297 DEBUG [mondrian.sql] 2: , exec 169 ms
          2012-10-15 11:09:11,335 DEBUG [mondrian.sql] 2: , exec+fetch 208 ms, 4 rows

          Two MDX logged for each report ( Analyzer and Reporting (.prpt))

          2012-10-15 11:09:10,536 DEBUG [mondrian.mdx] 44: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
            set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
            set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
            set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
            set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
            set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
            member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
          select [*BASE_MEMBERS_Measures] ON COLUMNS,
            [*SORTED_ROW_AXIS] ON ROWS
          from [SteelWheelsSales]

          2012-10-15 11:09:11,340 DEBUG [mondrian.mdx] 44: exec: 801 ms
          2012-10-15 11:10:40,667 DEBUG [mondrian.mdx] 102: with set [*NATIVE_CJ_SET] as 'Filter([*BASE_MEMBERS_Markets], (NOT IsEmpty([Measures].[Quantity])))'
            set [*SORTED_ROW_AXIS] as 'Order([*CJ_ROW_AXIS], [Markets].CurrentMember.OrderKey, BASC)'
            set [*BASE_MEMBERS_Markets] as '[Markets].[Territory].Members'
            set [*BASE_MEMBERS_Measures] as '{[Measures].[*FORMATTED_MEASURE_0]}'
            set [*CJ_ROW_AXIS] as 'Generate([*NATIVE_CJ_SET], {[Markets].CurrentMember})'
            set [*CJ_COL_AXIS] as '[*NATIVE_CJ_SET]'
            member [Measures].[*FORMATTED_MEASURE_0] as '[Measures].[Quantity]', FORMAT_STRING = "#,###", SOLVE_ORDER = 400
          select [*BASE_MEMBERS_Measures] ON COLUMNS,
            [*SORTED_ROW_AXIS] ON ROWS
          from [SteelWheelsSales]

          2012-10-15 11:10:40,675 DEBUG [mondrian.mdx] 102: exec: 8 ms
           

          Kurtis Cruzada made changes -
          Status In Progress [ 3 ] Resolved [ 5 ]
          Hide
          Kurtis Cruzada added a comment - - edited
          Per Will, the SQL cache will not work between JPivot and Reporting. I updated the Test Case. It works fine with Analyzer and Reporting. I wasn't able to test the MDX component in an .xaction because there is no documentation on how to set the connection properties within an .xaction.
          Show
          Kurtis Cruzada added a comment - - edited Per Will, the SQL cache will not work between JPivot and Reporting. I updated the Test Case. It works fine with Analyzer and Reporting. I wasn't able to test the MDX component in an .xaction because there is no documentation on how to set the connection properties within an .xaction.
          Kurtis Cruzada made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          QA Validation Status Not Yet Validated Validated by QA
          Resolution Fixed [ 1 ]
          Hide
          Pedro Vale added a comment -
          If you use an xaction that uses the component MDXLookupRule, you do not have to configure anything else - as long as the datasources.xml is using the JdbcConnectionUUID, cache should be shared.

          I noticed that the PivotComponent has a different behavior, since it *seems* to reuse jpivot under the covers and has the same issues as JPivot by itself.
          Show
          Pedro Vale added a comment - If you use an xaction that uses the component MDXLookupRule, you do not have to configure anything else - as long as the datasources.xml is using the JdbcConnectionUUID, cache should be shared. I noticed that the PivotComponent has a different behavior, since it *seems* to reuse jpivot under the covers and has the same issues as JPivot by itself.
          Hide
          Pedro Vale added a comment -
          PivotComponent is PivotViewComponent. sorry.
          Show
          Pedro Vale added a comment - PivotComponent is PivotViewComponent. sorry.
          Kurtis Cruzada made changes -
          Link This issue relates to BISERVER-7641 [ BISERVER-7641 ]
          Joe Grajewski made changes -
          Workflow Pentaho Engineering 9.0 Workflow [ 606103 ] Pentaho Bug 1.0 Workflow [ 641861 ]
          Joe Grajewski made changes -
          Workflow Pentaho Bug 1.0 Workflow [ 641861 ] Pentaho Engineering 9.0 Workflow [ 665194 ]
          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          In Progress In Progress Open Open
          4h 39m 1 Pedro Vale 27/Sep/12 9:19 AM
          Open Open Resolved Resolved
          9s 1 Pedro Vale 27/Sep/12 9:19 AM
          Resolved Resolved Reopened Reopened
          11d 17h 49m 2 Kurtis Cruzada 12/Oct/12 12:36 PM
          Reopened Reopened Open Open
          3h 15m 2 Kurtis Cruzada 12/Oct/12 12:49 PM
          Open Open Open Open
          15h 27m 4 Kurtis Cruzada 12/Oct/12 1:00 PM
          Open Open In Progress In Progress
          9h 35m 3 Sulaiman Karmali 12/Oct/12 8:21 PM
          In Progress In Progress Resolved Resolved
          5d 22h 37m 2 Kurtis Cruzada 15/Oct/12 1:10 PM
          Resolved Resolved Closed Closed
          2d 2h 20m 1 Kurtis Cruzada 17/Oct/12 3:30 PM

            People

            • Assignee:
              Kurtis Cruzada
              Reporter:
              Pedro Vale
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: