Hitachi Vantara Pentaho Community Forums
Results 1 to 1 of 1

Thread: Using date interval in MDX where part makes the processing terribly slow

  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Default Using date interval in MDX where part makes the processing terribly slow

    Hi All!

    I have a problem with MDX: when I use date interval in where part the execution time will be more than 30x greater:

    If I execute this:

    Code:
    with member [Customer].[2010] as 'Aggregate(Filter([Customer].[Full Name].Members, (([Customer].CurrentMember.Properties("Created at") >= 20100601) AND ([Customer].CurrentMember.Properties("Created at") <= 20101231))))'
    select {[Measures].[Total Price]} ON COLUMNS,
      {[Customer].[2010]} ON ROWS
    from [sales]
    It will take 5.6 ms. But this:

    Code:
    with member [Customer].[2010] as 'Aggregate(Filter([Customer].[Full Name].Members, (([Customer].CurrentMember.Properties("Created at") >= 20100601) AND ([Customer].CurrentMember.Properties("Created at") <= 20101231))))'
    select {[Measures].[Total Price]} ON COLUMNS,
      {[Customer].[2010]} ON ROWS
    from [sales]
    where {[Date to Filter.Full].[2010].[Q3 2010].[2010-07].[20100707] : [Date to Filter.Full].[2011].[Q4 2011].[2011-10].[20111004]}
    will take 172.6 ms. The only difference is the date slicer in where part.

    What should I do to speed up my query?

    My schema:

    Code:
    <Schema name="Sales Schema">
    
      <Dimension type="StandardDimension" highCardinality="false" name="Customer">
        <Hierarchy name="Customer" hasAll="true" primaryKey="customer_key">
          <Table name="dim_customer" schema="public" alias="dim_cust">
          </Table>
          <Level approxRowCount="100000" name="Full Name" column="customer_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
            <NameExpression>
              <SQL dialect="generic">
                <![CDATA[dim_cust.firstname || ' ' || dim_cust.lastname]]>
              </SQL>
            </NameExpression>
            <Property name="Created at" column="created_at_date" type="Numeric">
            </Property>
          </Level>
        </Hierarchy>
      </Dimension>
    
      <Dimension type="TimeDimension" highCardinality="false" name="DateFilter">
        <Hierarchy name="Full" hasAll="true" primaryKey="date_key">
          <Table name="dim_date" schema="public">
          </Table>
          <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
          </Level>
          <Level name="Quarter" column="quarter" type="Integer" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
            <NameExpression>
              <SQL dialect="generic">
                <![CDATA[quarter_name || ' ' || year]]>
              </SQL>
            </NameExpression>
          </Level>
          <Level name="Month" column="month_id" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
          </Level>
          <Level name="Day" column="date_key" type="String" uniqueMembers="true" levelType="TimeDays" hideMemberIf="Never">
          </Level>
        </Hierarchy>
        <Hierarchy name="Days" hasAll="true" primaryKey="date_key">
          <Table name="dim_date" schema="public">
          </Table>
          <Level name="Day" column="date_key" type="String" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
          </Level>
        </Hierarchy>
      </Dimension>
    
    
      <Cube name="sales" caption="Sales" cache="true" enabled="true">
        <Table name="fct_sale" schema="public">
        </Table>
        <DimensionUsage source="Customer" name="Customer" foreignKey="customer_key" highCardinality="false">
        </DimensionUsage>
        <DimensionUsage source="DateFilter" name="Date to Filter" foreignKey="created_date_key" highCardinality="false">
        </DimensionUsage>
    
        <Measure name="Total Price" column="price" datatype="Numeric" formatString="#,###.00" aggregator="sum" visible="true">
        </Measure>
      </Cube>
    
    </Schema>
    count(dim_date) = 500
    count(dim_customer) = 57 000
    count(fct_sale) = 420 000

    Pentaho biserver-ce version: 3.9 (I have tried on 3.10 and the result was similar)
    With PosgreSQL 9.0.

    I tried to user Date to Filter.Days hierarchy instead of Full but then I got "Resultset overflow occured".

    Thanks in andvance!

    Regards,
    Zoltan
    Last edited by zfall; 11-07-2011 at 07:14 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.