Slow MDX query when it contains 2 or more levels of the same dimension

Question

I have a Dimension in Mondrian with the following levels:

-Dimension - Time by Minute

--Level - Hour

--Level - Minute

If I make a query using a single level, it takes a few seconds to process. However, if I add both levels to the query, the query will take half an hour.

What is more, if I duplicate the dimension into 2 so each of them has one of the levels, the Query will also take seconds.

Single level query:

WITH
SET [~COLUMNS] AS
    {[Event].[Event].[Event Name].Members}
SET [~ROWS] AS
    {[Time by Minute].[Time by Minute].[Minute].Members}
SELECT
NON EMPTY [~COLUMNS] ON COLUMNS,
NON EMPTY [~ROWS] ON ROWS
FROM [spdoc views]

Query with 2 levels:

 WITH
    SET [~COLUMNS] AS
        {[Event].[Event].[Event Name].Members}
    SET [~ROWS] AS
        Hierarchize({{[Time by Minute].[Time by Minute].[Hour].Members}, {[Time by Minute].[Time by Minute].[Minute].Members}})
    SELECT
    NON EMPTY [~COLUMNS] ON COLUMNS,
    NON EMPTY [~ROWS] ON ROWS
    FROM [spdoc views]

Any ideas why this might happen?

Answer 1

So basically this happens because of Mondrian:

One of the key performance optimisations in Mondrian is to use native.crossjoin. However, since both levels belong to the same dimension it is not possible to perform it. Thus, a solution would be to separate the levels in several dimensions when they are going to be part of the same set.