Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: SQL generation for large dimension

  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Default SQL generation for large dimension

    Hello,

    I'm asking for a small range of a dimension which contains more than 100,000 datasets.

    The MDX query is:

    select {[Measures].[Saldo Passiv]} ON COLUMNS,
    {([Kunden-Nr].[Kunde].[13532] : [Kunden-Nr].[Kunde].[13588])} ON ROWS
    from [Konto]

    But the generated SQL reads the WHOLE dimension into cache, although it is not necessary

    SqlMemberSource.getMemberChildren: executing sql [
    select
    "DWH_KTO_KND2005"."KUNDEN_NR" as "c0"
    from
    "DWH_KTO_KND2005" "DWH_KTO_KND2005"
    group by
    "DWH_KTO_KND2005"."KUNDEN_NR"
    order by
    "DWH_KTO_KND2005"."KUNDEN_NR" ASC
    ], exec 1172 ms, exec+fetch 9641 ms, 128993 rows

    It seems like Mondrian will take all the datasets from the big dimension and then filter them by itself. Is there a possibility to configure the SQL generation, so it can also have the WHERE-clause just like this:

    WHERE "DWH_KTO_KND2005"."KUNDEN_NR" BETWEEN 13532 AND 13588

    Thanks for your help

    P.S. can Mondrian generate HAVING-clause too?

    yu

  2. #2
    Join Date
    Mar 2007
    Posts
    10

    Default

    Hi

    Add a NON EMPTY clause

    select {[Measures].[Saldo Passiv]} ON COLUMNS,
    NON EMPTY {([Kunden-Nr].[Kunde].[13532] : [Kunden-Nr].[Kunde].[13588])} ON ROWS
    from [Konto]

    And the WHERE "DWH_KTO_KND2005"."KUNDEN_NR" BETWEEN 13532 AND 13588 will be in the SQL.

    Good Luck.

  3. #3
    Join Date
    Aug 2007
    Posts
    8

    Default

    Thanks for your answer.

    But NON EMPTY can only remove the empty rows and the generated SQL remains unchanged.

    What I intend to do here is not to remove the empty rows, but to fetch only a small part of the data without going through the entire dimension. Becasue this will save a lot of time.

  4. #4
    Join Date
    Mar 2007
    Posts
    10

    Default

    Hi
    You are right, I was in a mistake.

    We have a very large dimension in our schema, so we sometimes find similar problems.
    [DIM] is a very large dimension with only 1 level.

    If we use this tipe of query:
    select
    {[measures].[MyMeasure]} on 0,
    {[DIM].[DIM (all)].[MyDIM_1], [DIM].[DIM (all)].[MyDIM_2], ....} on 1
    from [MyCube]

    Mondrian does not load the entire DIM dimension in cache (sqlMemberSource.getMemberChildren) and the segment load sql include a "WHERE DIM in (MyDIM_1, MyDIM_2, ...)" clause. I dont know if it works with a range MyDIM_1:MyDIM_10 instead of a enumeration.


    Sorry for my very poor English.
    Last edited by jcaubin; 09-05-2007 at 05:24 AM.

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.