MDX views, where / how are they evaluated?

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2086
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

MDX views, where / how are they evaluated?

Post by Steve Rowe » 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,

User avatar
macsir
Community Contributor
Posts: 733
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365
Contact:

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

Post by macsir » 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?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

lotsaram
MVP
Posts: 3433
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

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

Post by lotsaram » 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

scrumthing
Posts: 56
Joined: Tue Jan 26, 2016 4:18 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: too many

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

Post by scrumthing » 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...
There is no OLAP database besides TM1!

User avatar
Steve Rowe
Site Admin
Posts: 2086
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

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

Post by Steve Rowe » 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

User avatar
paulsimon
MVP
Posts: 764
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

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

Post by paulsimon » 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

User avatar
macsir
Community Contributor
Posts: 733
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365
Contact:

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

Post by macsir » Sun Sep 27, 2020 8:38 pm

Maybe reduce the number of crossjoin and starts from the minimum?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Post Reply