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

          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)
          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.
          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.
          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.

            People

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

              Dates

              • Created:
                Updated:
                Resolved: