MDX oddity on }Cubes dimension

Post Reply
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

MDX oddity on }Cubes dimension

Post by PavoGa »

I ran into this problem and a search did not turn up anything quite like it.

In short, I use copies of }Cubes and }Dimensions to actually work with (sys.Cubes and sys.Dimensions respectively), and wanted to create an MDX subset on sys.Cubes that filtered out cubes that were flagged <> YES for "DEMANDLOAD" for obvious reasons. However, it would not work!

Code: Select all

FILTER( TM1SubsetAll( [sys.Cubes] ), 
    [}CubeProperties].(StrToMember("[}Cubes].[" + [sys.Cubes].currentmember.Name + "]"), [}CubeProperties].[DEMANDLOAD]) <> 'YES') 
    
Returns the complaint STRTOMEMBER: Error in value expression ""

So playing around I discovered this:

Code: Select all

# This works in sys.Cubes:
GENERATE( TM1SubsetAll( [}Cubes] ),  {StrToMember("[sys.Cubes].[" + [}Cubes].currentmember.Name + "]")})

# this does not in }Cubes:
GENERATE( TM1SubsetAll( [sys.Cubes] ),  {StrToMember("[}Cubes].[" + [sys.Cubes].currentmember.Name + "]")})
This is the case in 10.2 and PA. So TM1 does not like constructing a member from the }Cubes dimension if the source is not }Cubes? I do not recall having ever run into this, but just changed positions and no longer have access to my old MDX code base to search for anything like it. It is easy enough to work around, but think this is a bit clunky:

Code: Select all

GENERATE( FILTER( TM1SubsetAll( [}Cubes] ), [}CubeProperties].([}CubeProperties].[DEMANDLOAD]) <> 'YES'), 
    FILTER( TM1SubsetAll( [sys.Cubes] ),
        [sys.Cubes].currentmember.name = [}Cubes].currentmember.name)
    )

What am I missing or don't know about the }Cubes dimension? Not a big deal, but still odd to me.
Ty
Cleveland, TN
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 oddity on }Cubes dimension

Post by MarenC »

Hi,

your first MDX works fine for me.

Maybe its a version thing or something to do with some data, objects in your model? My version is 11.8.200.24, I will let you do the conversion!

Have you tried creating a very simple model, and testing it? This would tell you if its a version issue methinks.

Maren
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX oddity on }Cubes dimension

Post by PavoGa »

Thanks, Maren.

The test in PA was against a very simple model. What client did you test with? Wonder if that could be the problem as I was using Architect?
Ty
Cleveland, TN
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 oddity on }Cubes dimension

Post by MarenC »

Hi,

I was using Architect too.

What does your cubeproperties cube look like? Mine has demandload no for normal dims and blanks for control cubes. I changed one of the normal dims to be yes as a test.

Maren
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX oddity on }Cubes dimension

Post by PavoGa »

Same. I spun up a PA model that only has four cubes in additional to the control ones. Forget the fragment with the filter, the GENERATE code does not work either and it is dealing strictly with the two dimensions. It fails in both a 10.2 instance and in a PA one. The PA is the most recent version as I just installed it.

Kind of at a loss.
Ty
Cleveland, TN
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 oddity on }Cubes dimension

Post by MarenC »

Hi,

I tried on a later version (11.8.1200) and on a completely different windows server, and on a complex model, and it still works fine for me.

Maren
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: MDX oddity on }Cubes dimension

Post by PavoGa »

MarenC wrote: Thu Jul 14, 2022 1:57 pm Hi,

I tried on a later version (11.8.1200) and on a completely different windows server, and on a complex model, and it still works fine for me.

Maren
Thank you again for confirming it does work. Just do not know why it is not working here for me. :roll:

***UPDATE*** Okay, I have been sleep walking. As soon as I posted the above it hit me what was wrong and I cannot believe I did not see it!!!
sys.Cubes has a rollup added to it. StrToMember, naturally, could not resolve that for }Cubes. Talk about feeling stupid right now... :?
Ty
Cleveland, TN
Post Reply