Problems with MONDRIAN MDX query

Question

I need to create an OLAP View just from one table in MySQL.

I need to get information from the following columns in my table:

loginNote
logoutNote
timestamp
userFirstName

So I created this Mondrian Schema:

<Schema name="Login">
  <Cube name="Login" visible="true" cache="true" enabled="true">
    <Table name="event_log">
    </Table>
    <Dimension visible="true" highCardinality="false" name="UserFirstName">
        <Hierarchy visible="true" hasAll="true" allMemberName="All UserFirstName">
          <Level name="UserFirstName" visible="true" column="userFirstName" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
          </Level>
        </Hierarchy>
    </Dimension>
<Dimension visible="true" highCardinality="false" name="LoginNote">
        <Hierarchy visible="true" hasAll="true" allMemberName="All LoginNote">
          <Level name="LoginNote" visible="true" column="loginNote" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
          </Level>
        </Hierarchy>
    </Dimension>
<Dimension visible="true" highCardinality="false" name="LogoutNote">
        <Hierarchy visible="true" hasAll="true" allMemberName="All UserFirstName">
          <Level name="LogoutNote" visible="true" column="logoutNote" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
          </Level>
        </Hierarchy>
    </Dimension>
    <Measure name="Users" column="userFirstName" aggregator="count" description="Users">
    </Measure>

I would like to know how can I run a MDX query to be able to show on the rows the LoginNote and LogoutNote information, and in the columns, the UserFirstName.

I was able to run

Select
UserFirstName.Children ON COLUMNS,
LogoutNote.Children ON ROWS
FROM Login

or

Select
UserFirstName.Children ON COLUMNS,
LoginNote.Children ON ROWS
FROM Login

but I cannot run

Select
UserFirstName.Children ON COLUMNS,
{LogoutNote.Children,LoginNote.Children} ON ROWS
FROM Login

because an error is returned:

All arguments to function '{}' must have same hierarchy.

Any help will be appreciated!

Thanks!

findango findango · Accepted Answer · 2013-03-06T03:27:05

The {...} notation is shorthand for Union(...), which combines two sets of members together. Those members must come from the same hierarchy (as the error message says), but you are including members from LogoutNote and LoginNote which are different dimensions/hierarchies.

If you want to combine hierarchies, you need to Crossjoin() them, creating a cartesian product of the two sets.

SELECT
    UserFirstName.Children ON COLUMNS,
    Crossjoin(LogoutNote.Children, LoginNote.Children) ON ROWS
FROM Login

I'm not sure if this is exactly what you expect as the results from your query, and you might want to add a NON EMPTY before that Crossjoin() to eliminate all the combinations of LoginNote and LogoutNote that have no values.

Hope that sets you on the right track.

Problems with MONDRIAN MDX query

1 Answers