Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Strange behaviour of Mondrian

  1. #1

    Default Strange behaviour of Mondrian with duplicates nameColumns

    Mondrian has a strange behaviour managing members with the same business key but different technical key. My question is related with the use of slowly changing dimension of type 2, but the problems come from a more generic case of duplicated business key in the dimension table.

    In this image you can see my dimension table

    where X_AUT_EXP is the Technical key, X_EXPE is the business key, and NOMBRE is a time-dependent attribute. There are only two records.

    Here is my fact table

    i´ve X_CANON as primary key, EXPE_X_EXPE and MUNI_X_MUNI as dimensions foreign keys, C_ANO that is a degenerated dimension and H_VOLUMEN that is my fact. There are only three records.
    OK?
    Now, my scheme
    Code:
     <Dimension type="StandardDimension" foreignKey="EXPE_X_EXPE" name="Expediente">
                <Hierarchy name="New Hierarchy 0" hasAll="true" allMemberName="Todos los Expedientes" primaryKey="X_AUT_EXP">
                    <Table name="SSD_D_EXPEDIENTES">
                    </Table>
                    <Level name="Expediente" column="X_AUT_EXP" nameColumn="X_EXPE" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                        <Property name="NOMBRE" column="NOMBRE" type="String">
                        </Property>
                    </Level>
                </Hierarchy>
            </Dimension>
    As you can see, i use the technical key as column id, the business key as nameColumn, and the time-dependent attribute as a member property.
    And now, my problem, that came from the fact that Mondrian doesn´t difference between two members with the same business key.
    For example, in this situation

    Mondrian returns only one record from "Expediente" dimension. The real situation is there are two records. The information is incomplete.
    This is the sql executed by Mondrian:
    Code:
    select "SSD_D_EXPEDIENTES"."X_AUT_EXP" as "c0", sum("SSD_H_VERTIDOS"."H_VOLUMEN") as "m0" 
    from "SSD_D_EXPEDIENTES" "SSD_D_EXPEDIENTES", "SSD_H_VERTIDOS" "SSD_H_VERTIDOS" 
    where "SSD_H_VERTIDOS"."EXPE_X_EXPE" = "SSD_D_EXPEDIENTES"."X_AUT_EXP" and "SSD_D_EXPEDIENTES"."X_AUT_EXP" = '10' 
    group by "SSD_D_EXPEDIENTES"."X_AUT_EXP"
    Other example


    In this case, the result is wrong. Mondrian returns a fact that doesn´t exist in data base: 2003-10-150000. The SQL:
    Code:
    select "SSD_H_VERTIDOS"."C_ANO" as "c0", "SSD_D_EXPEDIENTES"."X_AUT_EXP" as "c1", sum("SSD_H_VERTIDOS"."H_VOLUMEN") as "m0" 
    from "SSD_H_VERTIDOS" "SSD_H_VERTIDOS", "SSD_D_EXPEDIENTES" "SSD_D_EXPEDIENTES" 
    where "SSD_H_VERTIDOS"."C_ANO" = 2003 and "SSD_H_VERTIDOS"."EXPE_X_EXPE" = "SSD_D_EXPEDIENTES"."X_AUT_EXP" and "SSD_D_EXPEDIENTES"."X_AUT_EXP" = '11' 
    group by "SSD_H_VERTIDOS"."C_ANO", "SSD_D_EXPEDIENTES"."X_AUT_EXP"
    The SQL is correct, but not the result i see.

    Can someone help me?
    Thanks in advance.
    Last edited by Angel Alberto; 10-23-2009 at 03:10 AM.

  2. #2

    Default

    I´m still searching for a solution. Some idea?
    Thanks.

  3. #3

    Default

    I´m still searching...

  4. #4
    Join Date
    Jun 2006
    Posts
    282

    Default

    Hi Angel-
    Are you just trying to create a proof of concept or do you actually have a scenario where you have duplicate business keys? If it is the later, common practice is to assign a prefix to the business key that indicates some significance as it relates to the source from which it originates. The behavior you are experiencing is correct behavior as far as mondrian is concerned and for that matter SQL as well. It would be nonsensical to list the same member name twice.
    "If you want to increase your success rate, double your failure rate."
    Thomas Watson, Sr (former president of IBM)

  5. #5

    Default

    Hi microdisney, thanks for your response.
    My scenario is a classic Slowly Changing Dimension, type 2. "X_AUT_EXP" is the technical key, "X_EXPE" is the business key, and "NOMBRE" is a time-dependent attribute. As you say, it´s only a proof, so i don´t have version or star/end date attributes.
    And the problems come both from a bad sql and a bad internal join.
    I think my problem come from a bad use of Mondrian with SCD2 so, how must i use this kind of dimensions in Mondrian?
    Thanks again!

  6. #6
    Join Date
    Sep 2010
    Posts
    18

    Default

    I have extactly the same problem with SCD2 it desappears depending on the set filterting

    Really weird U__U

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.