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,
MDX views, where / how are they evaluated?
- Steve Rowe
- Site Admin
- Posts: 2423
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
MDX views, where / how are they evaluated?
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: MDX views, where / how are they evaluated?
Hi, do you want to share your MDX first and how did you display the MDX view in PAW?
-
- MVP
- Posts: 3667
- 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?
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.
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: 81
- Joined: Tue Jan 26, 2016 4:18 pm
- OLAP Product: TM1
- Version: 11.x
- Excel Version: MS365
Re: MDX views, where / how are they evaluated?
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...
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!
- Steve Rowe
- Site Admin
- Posts: 2423
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: MDX views, where / how are they evaluated?
Hi,
The general form of the MDX I am using is as follows for a 5d cube
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
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);
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
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- paulsimon
- MVP
- Posts: 808
- 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?
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
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
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: MDX views, where / how are they evaluated?
Maybe reduce the number of crossjoin and starts from the minimum?