CODEWITHSUNDEEP
mdxolapmondrian
lechbrush
lechbrush

Reputation: 66

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

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?

Upvotes: 0

Views: 349

Answers (2)

lechbrush
lechbrush

Reputation: 66

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.

Upvotes: 1

whytheq
whytheq

Reputation: 35557

"Why" I'm not sure.

There are various things you could try to speed the query up.

Try using a cross-join to take advantage of auto-exists behaviour & also move the Hierarchize inside the SELECT

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

Better still would be to apply NonEmpty to the set [~ROWS]. Do you have a measure you can use for this?:

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

Upvotes: 0

Related Questions