MDX From OBIEE: Set Size Exceeds 2^64 Tuples


When creating an OBIEE report the physical queries generated by OBIEE (MDX queries to Essbase) fail with:

“Essbase Error: Internal error: Set is too large to be processed. Set size exceeds 2^32 tuples”.

Design issue which pops up when it exceeds the query size allocated during the MDX query execution.
There is a limit of 2^32 on the number of cells that can be queried in an MDX query. This limit is not documented. There is also a limit of 2^32 tuples on the size of a set that can be created in MDX.

 

This query works around the limit of 2^32 cells by using the function NONEMPTYSUBSET. This function trims members from some of the dimensions by including only those member combinations that have data associated with them. Instead of processing all dimension combinations at once, it processes crossjoined sets of only a subset of dimensions first, trims them to include only those tuples that have data, then combines it with other dimensions.

There is also an issue with the query the way it is generated by OBIEE. The query uses 10 axes. This format does not allow the query to be rewritten to allow this workaround. To workaround the limit we have to have more than one dimension on a single axis, when we can trim the set to avoid 2^32 limit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s