Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-1177

Define multiple JDBC connections under a Mondrian schema

    XMLWordPrintable

    Details

    • Type: New Feature
    • Status: Open
    • Severity: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog - Mondrian 4.0
    • 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.

      Description

      Sometimes the tables needed to power a Mondrian schema live in different databases, and therefore have JDBC connections. This RFE describes how these JDBC connections can be defined and used within a Mondrian schema.

      The mechanism could be extended to allow connections to non-JDBC sources, but this feature request does not address this requirement.

      This RFE does not describe what Mondrian will do if two tables it needs (e.g. an aggregate table and a dimension table it would like to join to) are in different connections. That will be covered by another RFE.

      In Mondrian 3.4, connections are defined using connect string parameters: Jdbc, JdbcUser, JdbcPassword, DataSource. They are not defined in the schema XML file.

      In Mondrian 4, connections can be defined in the schema XML file, and can be overridden using connect string parameters. For example,

      <Schema name='FoodMart'>
        <Connections>
          <Connection name='default' default='true'>
            <Jdbc>jdbc:mysql://localhost/foodmart?characterEncoding=latin1</Jdbc>
            <JdbcUser>foodmart</JdbcUser>
            <JdbcPassword>foodmart</JdbcPassword>
          </Connection>
          <Connection name='aggs' default='false'>
            <Jdbc>jdbc:mysql://localhost/foodmartAggs?characterEncoding=latin1</Jdbc>
            <JdbcUser>foodmartAggs</JdbcUser>
            <Properties>
              <Property name='prop1'>value1</Property>
              <Property name='prop2'>value2</Property>
            </Properties>
          </Connection>
        </Connections>
      
      Connection connection =
        java.sql.DriverManager.createConnection(
          "jdbc:mondrian:Catalog=foodmart/FoodMart.xml;JdbcPassword=bardmart;JdbcPassword.aggs=bazdmart");
      

      The 'JdbcPassword' connect string argument overrides the <JdbcPassword> value in the 'default' connection. And the 'JdbcPassword.aggs' connect string argument overrides the <JdbcPassword> element in the

      The connection called 'default' is implicitly defined. If you do not define a connection called 'default', Mondrian defines one as if you had written

        <Connections>
          <Connection name='default'/>
        </Connections>
      

      When you use connect string properties without a qualifying schema name, '.default' is assumed. 'Jdbc=foo' is equivalent to 'Jdbc.default=foo'; similarly JdbcUser, JdbcPassword, DataSource.

      It is an error to define properties in the connect string for a connection xxx (e.g. JdbcUser.xxx) if there is no connection xxx defined in the schema. (JdbcUser.default is OK, because 'default' is implicitly defined, and is of course equivalent to JdbcUser.)

      The embedded Properties element provides properties to be passed in to the java.sql.DriverManager.getConnection(String, String, String, Properties) method. It is not equivalent to any connect string argument. It is ignored if DataSource is specified.

      At most one connection can be labeled 'default=true'. If there are no connections with default=true, Mondrian uses the one called 'default'. You can flag another connection with 'default=true', at possible risk of confusing yourself.

      Mondrian uses the default connection for any Table element in PhysicalSchema that does not specify a connection attribute.

      For example, in the following, Mondrian will use connection 'default' for table1 and table3, and will use 'agg' connection for table2.

      <PhysicalSchema>
        <Table name='table1' connection='default'/>
        <Table name='table2' connection='agg'/>
        <Table name='table3'/>
      </PhysicalSchema>
      

      This feature does not affect the 'schema' attribute of the Table element. The schema, if specified, needs to be a valid prefix for table within that JDBC connection.

      It is an error if you define a connection in the schema but do not provide enough information to connect, either in the Connection element or using connect-string parameters.

      Mondrian may or may not test JDBC connections that are not used. If you provide invalid connection information for an unused connection, behavior is undefined.

      Connection names are case sensitive.

      For integration with Pentaho server (and other containers) we recommend using "jndi:..." connections. Then you can define them elsewhere.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            jhyde Julian Hyde (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: