Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Using the Aggregate function for Time dimension

  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Default Using the Aggregate function for Time dimension

    Hello,

    I am trying to replace

    SELECT {[Measures].[Active energy]} ON AXIS(0), { [WindSpeedBin.Default].[1]:[50] } ON AXIS(1) FROM Energy WHERE ({ [Time.Default].[2011].[03].[20110301]:[2011].[03].[20110302] });

    with a calculated member using aggregate function to remove slicer axis usage and post processing on the time measures

    WITH MEMBER [Time].[AggregateValues] AS 'Aggregate({[Time.Default].[2011].[03].[20110301]:[2011].[03].[20110302]}) SELECT {[Measures].[Active energy]} ON AXIS(0), { [WindSpeedBin.Default].[1]:[50] } ON AXIS(1), {[Time].[AggregateValues] ON AXIS(3)} FROM Energy;

    the 'with member' requests however never returns and takes forever to run compared to milliseconds for the first request.

    Is there anything I am doing wrong?

  2. #2
    Join Date
    Sep 2011
    Posts
    3

    Default

    FYI there was a missing closing single quote... CmdRunner would not report the syntax error and just hang; I was however able to get the syntax error from the schema workbench.

  3. #3
    Join Date
    Feb 2009
    Posts
    9

    Default

    Hi,

    If I understand you correctly, you are trying to replace your Active Energy Measure. Your second MDX Statement, however, select this on another axis, which is why performance is slow.

    unfortunately I can only give you an equivalent of your query on the steelwheels data set.
    Your first query looks like this:
    Code:
    select
    {[Measures].[Quantity]} ON Axis(0),
    {[Product].[Classic Cars]:[Product].[Ships]} ON Axis(1)
    from [SteelWheelsSales]
    where {[Time].[2004].[QTR1].[Jan]:[Time].[2004].[QTR2].[Jun]}
    Which I would rewrite to:
    Code:
    with member [Measures].[AggregateValues] as 'Aggregate([Time].[2004].[QTR1].[Jan]:[Time].[2004].[QTR2].[Jun],[Measures].[Quantity])'
    select
    {[Measures].[AggregateValues]} ON Axis(0),
    {[Product].[Classic Cars]:[Product].[Ships]} ON Axis(1)
    from [SteelWheelsSales]
    If that is not what you want, then please state the purpose of your rewrite.
    Markus Burger-Scheidlin
    ITGAIN Consulting GmbH Hannover, Germany

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.