MDX view dimension order when used as a source to a TI

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2410
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 view dimension order when used as a source to a TI

Post by Steve Rowe »

Hi,
This issue has been fixed since 2.0.9.10.

https://www.ibm.com/support/pages/apar/PH24729
and
https://www.ibm.com/support/pages/node/6489261

If you had a stable solution prior to 2.0.9.10 and upgrade to 2.0.9.10 or higher then you will probably need to make changes to any TIs that use MDX views as a source.

Cheers,
Technical Director
www.infocat.co.uk
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: MDX view dimension order when used as a source to a TI

Post by MarenC »

Hi,

Is it or me or wasn't there a fairly recent forum topic on this issue?

If so, might be an idea to tag this to that topic.

If not, I really need better dreams!

Maren
User avatar
Steve Rowe
Site Admin
Posts: 2410
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 view dimension order when used as a source to a TI

Post by Steve Rowe »

We all need better dreams!

It was mentioned recently, by Lotsaram I think, but I'm sure he'll notice this post...
Technical Director
www.infocat.co.uk
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX view dimension order when used as a source to a TI

Post by lotsaram »

Well it was discussed at length here https://www.tm1forum.com/viewtopic.php? ... 426#p73332
But I wouldn't call that recently.

Well yeah this is my APAR https://www.ibm.com/support/pages/apar/PH24729
... and according to IBM yes this was closed as "fixed" in 2.0.9.10
... but from what I can tell it is actually not fixed and remains as broken as it ever was. Seems to be that IBM have simply "fixed" the ordering of variables in a MDX view by reclassifying from "defect" to "expected behavior". I just don't understand why no one can get through to anyone at IBM that this is a significant issue. How can you have a generic TI process if you have no way to know how to match variables to a dimension in the source cube?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX view dimension order when used as a source to a TI

Post by Wim Gielis »

I can confirm that it is not fixed and certainly not ready to be used in anything generic where you cannot know upfront the variables that you will get in what order. This is very bad since cube views with PA alternate hierarchies are not accessible using traditional views. In a generic process I am trying to export data out of a cube with (potentially) alternate hierarchies. I only need V1 and I will piece together the MDX view definition - still V1 is not always coming up as the one I need. Very frustrating.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX view dimension order when used as a source to a TI

Post by lotsaram »

Wim Gielis wrote: Wed Nov 09, 2022 5:28 pm I can confirm that it is not fixed and certainly not ready to be used in anything generic where you cannot know upfront the variables that you will get in what order. This is very bad since cube views with PA alternate hierarchies are not accessible using traditional views. In a generic process I am trying to export data out of a cube with (potentially) alternate hierarchies. I only need V1 and I will piece together the MDX view definition - still V1 is not always coming up as the one I need. Very frustrating.
You're telling me. Just how it is that IBM "don't get it" on this one is beyond me. Anyone building good, quality solutions is going to have generic utility TI processes to move data around which can be used for any cube. Currently MDX views are completely useless within such a library due to the unpredictable order of variables.

The technote which Steve linked to basically confirms that IBM's "fix" was to ensure consistent variable ordering based on the MDX query structure. Which not to mince words is nothing more than reclassifying a defect as expected behaviour.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
Steve Rowe
Site Admin
Posts: 2410
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 view dimension order when used as a source to a TI

Post by Steve Rowe »

Your use case must be rather different from mine, I'm not having issues reaching a stable soluton.

I build my views using this form.

Code: Select all

sMDXToUse='SELECT  {%sMDX5%}  ON 0,
%sFilterHead%
NONEMPTY(
	CROSSJOIN(%sMDX1%,
    	CROSSJOIN(%sMDX2%,
    		CROSSJOIN(%sMDX3%,
			%sMDX4%))),
    {%sMDX5%}
    %sFIlterEnd% ) ON 1 
 
FROM [Plan]
';
(I could clean this up more using "*" rather than Crossjoin)
The index on the MDX is the same as dimension order in the cube, just for conveinence.

Before the fix the view was in the same order as the dimension.
Post the fix the column dimension was first and the rest in order, i.e. v5,v1,v2,v3,v4.

I've not tried but can see how this could be generalised for any dimensioned cubes.

Curious on the detail of what you are doing that isn't working...

Cheers,
Technical Director
www.infocat.co.uk
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX view dimension order when used as a source to a TI

Post by lotsaram »

Steve Rowe wrote: Thu Nov 10, 2022 10:27 am I've not tried but can see how this could be generalised for any dimensioned cubes.
All well and good if you can dictate the format of the query. If you can control the way the query is constructed then you can control the order of the variables. But what if the content of the MDX statement is unknown and could be anything? How do you deal with that? That's the problem.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX view dimension order when used as a source to a TI

Post by Wim Gielis »

Actually in my generic script I can control the MDX. Let me pick it up again and see if I can get it working with alternate hierarchies.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Steve Rowe
Site Admin
Posts: 2410
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 view dimension order when used as a source to a TI

Post by Steve Rowe »

lotsaram wrote: Thu Nov 10, 2022 6:28 pm All well and good if you can dictate the format of the query. If you can control the way the query is constructed then you can control the order of the variables. But what if the content of the MDX statement is unknown and could be anything? How do you deal with that? That's the problem.
I can see how that would be challenging if you want to throw any random MDX view statement at a TI and be able to do something coherent with it.

(Just thinking out loud about solutions, not trying to say it isn't a problem....)
  • For a certain level of complexity of MDX you could probably have a TI whose only job was to parse an MDX statement and figure out the contents. Can certainly see how this would be challenging / impossible for all possible MDX views.
  • You could do a dimix test on the first row of the source but that doesn't allow for duplicates in hierarchies / dimension which are virtually guaranteed, so that is a non-starter.
  • Maybe we are back to the same thing we had with the legacy view types, in that a query view type would of been helpful. I can see why if you were using ViewCreateByMDX for using facing views you wouldn't want the ordering interfered with. Maybe a QueryCreateByMDX which forced the view dim order to the cube sequence would help.
    But I don't think an enforced order would help. With the introduction of hierarchies, knowing a cubes dimension order and that the MDX view is in the same order doesn't imply anything about the view as a dimension could appear multiple times in a view. A view versus a 3d cube could be in an order like this d1:h1, d2:h1, d2:h2, d2:h5, d2:h8, d3:h5. There's no mechanic for us to understand this, even if we know that the dims and hierarchies are in order.
  • There are missing functions in a TI that would allow us to interrogate the MDX view object post creation. Minimum is the full hierarchy name in each position and the cube. Not sure what else you would need? Maybe number of total number of hierarchies / "columns" in the view so we have a max value to loop over. Would also need to allow for edge cases where some of the columns are attributes etc....

Interesting problem...

Cheers,
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: MDX view dimension order when used as a source to a TI

Post by Wim Gielis »

Wim Gielis wrote: Thu Nov 10, 2022 7:10 pm Actually in my generic script I can control the MDX. Let me pick it up again and see if I can get it working with alternate hierarchies.
It's working in my generic script, where I can force the (1) variable I am interested in, to position V1 through my custom MDX.
The process will always have 1 alternate hierarchy at most, not the main hierarchy of that dimension in case there is the alternate hierarchy, and so on.

Agreed, in the most generic situation this is very hard to solve.

PAW 2.0.80, PAL 2.0.15.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX view dimension order when used as a source to a TI

Post by lotsaram »

Steve Rowe wrote: Fri Nov 11, 2022 10:48 am (Just thinking out loud about solutions, not trying to say it isn't a problem....)
  • For a certain level of complexity of MDX you could probably have a TI whose only job was to parse an MDX statement and figure out the contents. Can certainly see how this would be challenging / impossible for all possible MDX views.
  • You could do a dimix test on the first row of the source but that doesn't allow for duplicates in hierarchies / dimension which are virtually guaranteed, so that is a non-starter.
  • Maybe we are back to the same thing we had with the legacy view types, in that a query view type would of been helpful. I can see why if you were using ViewCreateByMDX for using facing views you wouldn't want the ordering interfered with. Maybe a QueryCreateByMDX which forced the view dim order to the cube sequence would help.
    But I don't think an enforced order would help. With the introduction of hierarchies, knowing a cubes dimension order and that the MDX view is in the same order doesn't imply anything about the view as a dimension could appear multiple times in a view. A view versus a 3d cube could be in an order like this d1:h1, d2:h1, d2:h2, d2:h5, d2:h8, d3:h5. There's no mechanic for us to understand this, even if we know that the dims and hierarchies are in order.
  • There are missing functions in a TI that would allow us to interrogate the MDX view object post creation. Minimum is the full hierarchy name in each position and the cube. Not sure what else you would need? Maybe number of total number of hierarchies / "columns" in the view so we have a max value to loop over. Would also need to allow for edge cases where some of the columns are attributes etc....
Done TI script to do rudimentary parsing of json and html before. Parsing MDX in TI, no thank you. TI just isn't equiped for that kind of thing.

I get that an MDX view brings a whole lot of extra complexities. There could be more variable columns from cross-joining multiple hierarchies from the same dimension, or there could be less if dimensions are omitted from the query and the default member is used. So it isn't an easy problem to solve.

Any solution involving looping the dimensions of the cube to check if the member is valid for that dimension you can just forget as the same names element could exist in many dimensions.

I would be happy with the solution of extra functions in TI to return the dimension and hierarchy which the element belongs to. This would allow for generic functions.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply