[Mondrian] problem with ordering of Level elements inside a <Dimension> 's <Hierarchy>

Radha Ranjan Madhaw RMadhaw at facetime.com
Tue May 20 09:44:01 EDT 2008


Hi,

 

When I execute the following MDX query:

 

SELECT {[Measures].[URL Hits]} ON COLUMNS, {[Time].[Year].[2008]} ON
ROWS FROM [MondrianTest] WHERE [DimUsers].[User Name].[user5]

 

/* [User Name] and [Group Name] are Levels and 'user5' is a value in
corresponding 'fullname' column of 'Users' table as mentioned below. */

 

Then I get correct result <u>only when</u> the Level related to "User
Name" appears first e.g.:

 

 

<Level name="User Name" column="fullname" nameColumn="fullname"
ordinalColumn="userid" type="String" uniqueMembers="false"/>

<Level name="Group Name" column="groupname" nameColumn="groupname"
ordinalColumn="groupid" type="String" uniqueMembers="false"/>

 

 

If I reverse the order of Levels, i.e. [Group Name] Level appears first,
then even though the query executes, but the result is incorrect - and
the generated SQL puts one additional filter based on groupid=' some
value ' - which is unexpected.

 

Here DimUsers is defined as following:

------------------------------------------------------------------------
-------------------

<Dimension name="DimUsers">

<Hierarchy hasAll="true" primaryKey="DimUserViewId"
primaryKeyTable="DimUsersView" name="Hierarchy1">

                        <View alias="DimUsersView">

                                    <SQL dialect="generic">

                                                            select
"ug"."id" as "DimUserViewId", "u"."userid" as "userid",

 
"u"."fullname" as "fullname", "g"."groupid" as "groupid",

 
"g"."groupname" as "groupname"

                                                            from
"user_group" as "ug", "users" as "u", "groups" as "g"

                                                            where
"ug"."userid"  = "u"."userid"

                                                            and
"ug"."groupid" = "g"."groupid"

                                    </SQL>

                        </View>

                        <Level name="User Name" column="fullname"
nameColumn="fullname" ordinalColumn="userid" type="String"
uniqueMembers="false"/>

                        <Level name="Group Name" column="groupname"
nameColumn="groupname" ordinalColumn="groupid" type="String"
uniqueMembers="false"/>

            </Hierarchy>

</Dimension>

------------------------------------------------------------------------
-------------------

...and the usage in cube 'MondrianTest' is as following (somewhat odd
not to refer users/groups table directly, but referring the mapping
table user_group's id col as foreign key as a foreignKey attribute in
DimensionUsage):

 

<DimensionUsage name="DimUsers" source="DimUsers"
foreignKey="user_groupid"/>

------------------------------------------------------------------------
-------------------

 

Attached is the simple star schema structure.

 

Fact_url_hits is the fact table, Time is the only declared dimension
derived from a table and another dimension for which a view is defined
on top of three tables (user_group - mapping table, users and groups).

 

Just for reference, following is the where clause of  'incorrect' SQL
generated when query has [User Name].[user5] as filter and [User Name]
is the second Level defined in the Hierarchy:

 

------------------------------------------------------------------------
----------------

where 

    "fact_url_hits"."timeid" = "TimeByDay"."id" and 

    "TimeByDay"."Year" = 2008 and 

    "fact_url_hits"."user_groupid" = "DimUsersView"."DimUserViewId" and 

    "DimUsersView"."groupname" = 'group1' and 

    "DimUsersView"."fullname" = 'user5'

------------------------------------------------------------------------
----------------

 

Notice the part "DimUsersView"."groupname" = 'group1' which is totally
unexpected as only username is specified.

 

Any help will be appreciated.

 

Thanks,

Madhav

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080520/4d3883c3/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: star_schema.PNG
Type: image/png
Size: 13186 bytes
Desc: star_schema.PNG
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080520/4d3883c3/attachment.png 


More information about the Mondrian mailing list