Page 1 of 1
Subset not found in dimension
Posted: Tue Oct 02, 2018 5:12 am
by macsir
Hi, all
I am testing TI codes in PAW and running into "Subset not found in dimension" issue when executing at VIEWSUBSETASSIGN function.
Could anyone spot it for me? The view and subset have been created via this code successfully. It looks like TI can't bind the subset under the hierarchy into the view.
Code: Select all
#Section Prolog
#****Begin: Generated Statements***
#****End: Generated Statements****
vsCube = 'Coffee';
vsHier = 'Coffee Color';
vsViewName = 'temp';
vsSubName = vsViewName;
IF( VIEWEXISTS( vsCube, vsViewName ) = 1 );
VIEWDESTROY( vsCube, vsViewName );
ENDIF;
VIEWCREATE( vsCube, vsViewName );
vsDimName = 'Coffee';
IF( HierarchySubsetExists( vsDimName, vsHier, vsSubName ) = 1 );
HierarchySUBSETDESTROY( vsDimName, vsHier, vsSubName );
ENDIF;
HierarchySUBSETCREATE( vsDimName, vsHier, vsSubName);
HierarchySUBSETELEMENTINSERT( vsDimName, vsHier, vsSubName, 'Nescafe', 0 );
VIEWSUBSETASSIGN( vsCube, vsViewName, vsDimName, vsSubName );
DATASOURCETYPE = 'VIEW';
DATASOURCENAMEFORSERVER = vsCube;
DATASOURCENAMEFORCLIENT = vsCube;
DATASOURCECUBEVIEW = vsViewName;
#Section Metadata
#Section Data
ASCIIOutput( 'e:\temp\test.txt', vsCoffee, NumberToString( vnValue ) );
#Section Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****
Re: Subset not found in dimension
Posted: Tue Oct 02, 2018 9:29 am
by lotsaram
Are you using alternate hierarchies? If yes then you need to use ViewCreateByMDX as this is the only way to address hierarchies in a view as the traditional views are not hierarchy aware.
ViewCreate can only create a traditional view which can't see hierarchies. You either need to use the same named hierarchy only with the HierarchySubset functions or else just use the old Subset functions if you are using a trad view.
Re: Subset not found in dimension
Posted: Tue Oct 02, 2018 10:11 am
by Wim Gielis
Thanks Lotsaram.
In the case of alternative hierarchies in PAW, does this imply that with ViewCreateByMDX we need to write (difficult) MDX expressions ?
Help files on this function:
https://www.ibm.com/support/knowledgece ... bymdx.html
Re: Subset not found in dimension
Posted: Tue Oct 02, 2018 4:48 pm
by lotsaram
Your MDX might be complicated it might not
Seem as almost all TM1 data is numeric and numeric data is by definition stored only at leaf intersections I think that traditional views is still what we are going to use for data processing 95% of the time. Where we might need to use MDX views as an exception are the cases where we want to filter on a value in an alternate hierarchy. There were a lot of issues with MDX views to start with in PA where they weren't usable for TI (not recognized by ViewExists, not being removed by ViewDestroy, not being able to be created as temporary, null suppression not working properly for TI, ...) but finally with 11.3 these all seem to be working. The one remaining thing where MDX views don't behave the same as trad views for TI is that ViewZeroOut doesn't work. (I'm not sure yet if IBM will claim this as a feature as opposed to a bug).
The thing to remember about the MDX views is that although we
can use them for TI they are really implemented for user query views. Whereas in a trad view if you don't assign a subset to a dimension you get the implicit subset All for the dimension but for a MDX view if you don't address a dimension on rows, columns or in the where then it will be treated as if it was in the where with the value of the default member in the same named hierarchy. (and if the default_member isn't set in }hierarchyProperties then TM1 assumes it is the element with index value 1.)
Re: Subset not found in dimension
Posted: Tue Oct 02, 2018 8:56 pm
by macsir
Thanks, lotsaram for the detailed explanation. I am surprised that viewcreate is still not hierarchy aware at the moment since IBM implemented the hierarchy as a important feature. But anyway, as you said, ViewCreateByMDX still gives us lots of flexibility. I am interested to test this bit also.