Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: How filter a cube with a period?

  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Default How filter a cube with a period?

    I have a problem with a MDX query;
    I want to filter a cube with a given period.


    I originally created in my cube a dimension having "TimeDimension" type with four levels (Year, Quarter, Month and Day), and I filtered as well:

    WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du ${jourdebut}/${moisdebut}/${anneedebut} au ${jourfin}/${moisfin}/${anneefin}] AS
    Aggregate([Date].[Toutes les dates].[${anneedebut}].[${quarterdebut}].[${moisdebut}].[${jourdebut}] : [Date].[Toutes les dates].[${anneefin}].[${quarterfin}].[${moisfin}].[${jourfin}])
    select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
    NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
    from [MonCube]
    where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du ${jourdebut}/${moisdebut}/${anneedebut} au ${jourfin}/${moisfin}/${anneefin}])



    Each parameter ${x} is the result of a selection of a parameter of my application (SpagoBI).
    It worked well.



    The problem is that users have to fulfill 8 parameters. So it's much too heavy.
    The idea is to select only 2 input parameters (start date and end date).


    So I created a new dimension with only the field 'date' which have a String type (Date type doesn't work with my DB - Sybase IQ -). This field is written as "2011-05-30".
    So I thought parse the result of the parameter with the 'Mid' function. And I said I would adapt my MDX query as follows:

    WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du ${datedebut} au ${datefin}] AS
    Aggregate([Date].[Toutes les dates].['Mid(${datedebut},1,4)'].[A calculer].['Mid(${datedebut},6,2)'].['Mid(${datedebut},9,2)'] : [Date].[Toutes les dates].['Mid(${datefin},1,4)'].[A calculer].['Mid(${datefin},6,2)'].['Mid(${datefin},9,2)]')
    select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
    NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
    from [MonCube]
    where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du ${datedebut} au ${datefin}])



    The 'Mid' function is recovering well the year, month and day (for the quarter, I'll use Iif). However, the MDX query does not retrieve the result of the function, but the entire function.


    For example, if my parameter $datedebut = "2011-05-30" and $datefin= "2011-06-30", then the MDX query is:
    WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du 2011-05-30 au 2011-06-30}] AS
    Aggregate([Date].[Toutes les dates].['Mid(2011-05-30,1,4)'].[A calculer].['Mid(2011-05-30,6,2)'].['Mid(2011-05-30,9,2)'] : [Date].[Toutes les dates].['Mid(2011-06-30,1,4)'].[A calculer].['Mid(2011-06-30,6,2)'].['Mid(2011-06-30,9,2)'])
    select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
    NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
    from [MonCube]
    where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du ${datedebut} au ${datefin}])



    But I have the following error:
    Mondrian Error:Failed to parse query 'WITH MEMBER[Date].[Toutes les dates].[annee].[quarter].[mois].[Du 2011-05-30 au 2011-06-30}] as Aggregate([Date].[Toutes les dates].['Mid("2011-05-30",1,4)'].[Trimestre 2].['Mid("2011-05-30",6,2)'].['Mid("2011-05-30",9,2)'])


    Obviously the 'Mid("2011-05-30",1,4)' year does not exist in my cube. That's the 2011 year there is.
    The 'Mid' function returns the good results ('Mid("2011-05-30",1,4) = 2011, 'Mid("2011-05-30",6,2) = 05, 'Mid("2011-05-30",9,2) = 30).


    The result of the MDX query should be:
    WITH MEMBER [Date].[Toutes les dates].[annee].[quarter].[mois].[Du 2011-05-30 au 2011-06-30}] AS
    Aggregate([Date].[Toutes les dates].[2011].[A calculer].[05].[30] : [Date].[Toutes les dates].[2011].[A calculer].[06].[30])
    select NON EMPTY {[Measures].[Nombre 1], [Measures].[Nombre 2]} ON COLUMNS,
    NON EMPTY {[Zone].[Toutes les zones]} ON ROWS
    from [MonCube]
    where ([Date].[Toutes les dates].[annee].[quarter].[mois].[Du ${datedebut} au ${datefin}])



    The problem is that my function is inside of a member of my dmension. I think that I get to escape my function in order to execute it before the execution of the MDX query.


    What is the correct syntax? I tried with '&':[Date].[Toutes les dates].&['Mid("2011-05-30",1,4)'], with '+', ...
    But nothing works.


    Thank you for your help.

  2. #2
    Join Date
    Dec 2011
    Posts
    6

    Default

    Julian, have you an idea?
    Last edited by aolliet; 01-04-2012 at 10:39 AM.

  3. #3
    Join Date
    Jul 2007
    Posts
    2,497

    Default

    Jesus - stop making your own life harder


    > So I created a new dimension with only the field 'date' which have a String type (Date type doesn't work with my DB - Sybase IQ -). This field is written as "2011-05-30".

    What you need to do is add a new level with the entire date string, below the date level, staying with something like: DateDim -> Year -> Month -> Day -> Date, and then just do [Date].[Date].[${paramDate}]

    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  4. #4
    Join Date
    Dec 2011
    Posts
    6

    Default

    OMG... It was so simple!!! I was persuaded that my "Date" Dimension must be a "TimeDimension" type in order to filter with a period.

    Thanks pmalves!

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.