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

Force SQL to use members of a level

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Severity: Medium
    • Resolution: Unresolved
    • Affects Version/s: 3.1.6 GA (3.5.2 GA Suite Release)
    • Fix Version/s: Not Planned
    • 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

      The customer has a Teradata DB, and the fact table is huge. One of the benefits of Teradata are the partitions, but apparently, the SQLs that mondrian uses are not taking advantage of this.

      Currently, the fact table has partitions on the date (one partition per day) but if the user is generating a query filtering by month, quarter, or year (any level above Day). The Following is an example of the SQL Generated:

      select `CALENDAR_DIM`.`MONTH_LONG_NUMBER` as `c0`, `PRODUCT_DIM`.`department_category_id` as `c1`, sum(`SALES_FACT_DAY`.`qty`) as `m0`
      from `pent_rolap`.`CALENDAR_DIM` as `CALENDAR_DIM`, `pent_rolap`.`SALES_FACT_DAY` as `SALES_FACT_DAY`, `pent_rolap`.`PRODUCT_DIM` as `PRODUCT_DIM`
      where `SALES_FACT_DAY`.`start_date` = `CALENDAR_DIM`.`DAY_START_DATE` and `CALENDAR_DIM`.`MONTH_LONG_NUMBER` = '201004' and `SALES_FACT_DAY`.`prodpack_id` = `PRODUCT_DIM`.`prodpack_id` and `PRODUCT_DIM`.`department_category_id` in ('014-005', '014-010', '014-015', '014-020', '014-025', '014-030', '014-035', '014-040')
      group by `CALENDAR_DIM`.`MONTH_LONG_NUMBER`, `PRODUCT_DIM`.`department_category_id`

      Unfortunately, that query is not using in Teradata the partitions so the query is not optimal. This is a substitution of that SQL that would get advantage of the partitions:

      select `CALENDAR_DIM`.`DAY_START_DATE` as `c0`, `PRODUCT_DIM`.`department_category_id` as `c1`, sum(`SALES_FACT_DAY`.`qty`) as `m0`
      from `pent_rolap`.`CALENDAR_DIM` as `CALENDAR_DIM`, `pent_rolap`.`SALES_FACT_DAY` as `SALES_FACT_DAY`, `pent_rolap`.`PRODUCT_DIM` as `PRODUCT_DIM`
      where `SALES_FACT_DAY`.`start_date` = `CALENDAR_DIM`.`DAY_START_DATE` and `CALENDAR_DIM`.`DAY_START_DATE` in (DATE '2010-04-01', DATE '2010-04-02', DATE '2010-04-03', DATE '2010-04-04', DATE '2010-04-05', DATE '2010-04-06', DATE '2010-04-07', DATE '2010-04-08', DATE '2010-04-09', DATE '2010-04-10', DATE '2010-04-11', DATE '2010-04-12', DATE '2010-04-13', DATE '2010-04-14', DATE '2010-04-15', DATE '2010-04-16', DATE '2010-04-17', DATE '2010-04-18', DATE '2010-04-19', DATE '2010-04-20', DATE '2010-04-21', DATE '2010-04-22', DATE '2010-04-23', DATE '2010-04-24', DATE '2010-04-25', DATE '2010-04-26', DATE '2010-04-27', DATE '2010-04-28', DATE '2010-04-29', DATE '2010-04-30') and `SALES_FACT_DAY`.`prodpack_id` = `PRODUCT_DIM`.`prodpack_id` and `PRODUCT_DIM`.`department_category_id` in ('014-005', '014-010', '014-015', '014-020', '014-025', '014-030', '014-035', '014-040')
      group by `CALENDAR_DIM`.`DAY_START_DATE`, `PRODUCT_DIM`.`department_category_id`

      The customer is looking for the possibility to have an improvement of mondrian so that it is possible to configure certain level in a dimension to have its members included in the SQL. This could be in the form of a property available in a .properties file similar to:

      level.includemembers = [Calendar].[Calendar Day]

      I'm attaching the Schema file

        Attachments

        1. layout1.xml
          1 kB
        2. layout2.xml
          1 kB
        3. layout3.xml
          1.0 kB
        4. query1.log
          4 kB
        5. query2.log
          3 kB
        6. query3.log
          6 kB
        7. Sales_Schema.mondrian.xml
          12 kB

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            rhaces Rodrigo Haces (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated: