TM1 Forum

Posted: **Thu Sep 24, 2020 2:46 pm**

Afternoon or the local equivalent,

I've an MDX view. It a level 0 view of a medium sized cube, not huge by any means, with a Filter applied to the cells based on the value of one of the measures in the cube. Fairly straight forward.
If I try and display the view in PAW I get an out of memory error
If I export the view in a TI it works in a couple of seconds with no issue.

Just curious as to what is going on here, any ideas? Is displaying an MDX view so different to ASCIIOutput that I shouldn't expect to get the same result?

With a normal view I'd expect this behaviour, a view that is too large to display can be exported in TI.

With an MDX view it seems to be behaving as if the core crossjoin before the filter is displayed must be small enough to display in order to display the results post filter. 82 rows returned in total post filter.
Cheers,

Posted: **Thu Sep 24, 2020 8:52 pm**

Hi, do you want to share your MDX first and how did you display the MDX view in PAW?

Posted: **Fri Sep 25, 2020 7:46 am**

How many cells? What is the MaximumViewSize param set at?
In TI the data is read one cell at a time so there isn't any hit to render the whole data set in a grid. That could be it.

Posted: **Fri Sep 25, 2020 11:01 am**

Which version are you on? I know they improved MDX a lot in 2.0.9.1 and .2...
Colleagues of mine had some crashes in PAW in 2.0.8 with MDX. But currently I am unaware of the details...

Posted: **Fri Sep 25, 2020 11:17 am**

Hi,
The general form of the MDX I am using is as follows for a 5d cube

Code: Select all

sMDXToUse='SELECT  {%sMDX5%}  ON 0,
%sFilterHead%
NONEMPTY(
	CROSSJOIN(%sMDX1%,
    	CROSSJOIN(%sMDX2%,
    		CROSSJOIN(%sMDX3%,
			%sMDX4%))),
    {%sMDX5%}
    %sFIlterEnd% ) ON 1 
 
FROM [%sCubeName%]';

ViewCreateByMDX(sCubeName , sObjectName, sMDX, booTempOn);

Once the view is created it is available in PAW in the list of views for the cube, they also show up in Pafe. The icon is different though (sometmes...)

In a Pafe Exploration view the data is returned in less than a second, interesting they work with custom reports but not dynamic.

Only 82 cells returned in the query as I'm only returning a single measure.

MaximumViewSize is at the default of 500MB

The error in PAW states "Unable to fetch data. The query required too much memory".

Seems like an issue in PAW to me rather than something I should be expecting not to work.

Its an old PAW 2.0.43

Posted: **Fri Sep 25, 2020 8:44 pm**

Hi Steve

What I am wondering is whether the MDX is doing all the Cross Joins and then only applying the NONEMPTY at the end.

I use a slightly different form

SELECT NON EMPTY CROSSJOIN( {[actvsbud].[Actual],[actvsbud].[Budget] }, { [month].[Jan],[month].[Feb] } ) on columns FROM PNLCube

I haven't experimented enough with MDX to know if it is possible to specify NON EMPTY on each pair of joins but perhaps that is a route to explore.

Regards

Paul

Posted: **Sun Sep 27, 2020 8:38 pm**

Maybe reduce the number of crossjoin and starts from the minimum?

TM1 Forum

MDX views, where / how are they evaluated?

MDX views, where / how are they evaluated?

Re: MDX views, where / how are they evaluated?

Re: MDX views, where / how are they evaluated?

Re: MDX views, where / how are they evaluated?

Re: MDX views, where / how are they evaluated?

Re: MDX views, where / how are they evaluated?

Re: MDX views, where / how are they evaluated?