Page 1 of 1
DIMENSIONCOMPONENTADD
Posted: Mon Jan 17, 2011 1:50 pm
by paulfelce
Arrgh, please help...
This should be a nice easy one for experienced TM1ers.
In a TI load process if I want to create a dimension of, for example time, that looks like this
(it could be any dimension, it’s not a nasty time related one, but the structure is obvious!)
2005 Jan 2005
Feb 2005
Mar 2005
Apr 2005
May 2005
Jun 2005
….
2006
Jan 2006
Feb 2006
…
I’m using a TI Process like
DIMENSIONELMENTINSERT(‘Time’,’’,Year,’c’)
DIMENSIONELEMENTINSERT(‘Time’,Month,’m’)
DIMENSIONELEMENTCOMPONENTADD(‘Time’,Year,Month,1)
This loads from a db source that provides the years and months and will give me years with the months under the consolidations.
BUT at the bottom of my dimension I get all the months listed. I sort of want a DimensionComponentSetParent instruction.
How on earth should I do this?
Re: DIMENSIONCOMPONENTADD
Posted: Mon Jan 17, 2011 2:30 pm
by sander
I think you need to sort your dimension.
Right click on the dimension then Set Elements Order. Select automatic and Hierarchy.
Good Luck!
I assume that you get the hierarchy the right way, but also the loose n-elements at the bottom
Re: DIMENSIONCOMPONENTADD
Posted: Mon Jan 17, 2011 7:14 pm
by Alan Kirk
paulfelce wrote:Arrgh, please help...
I’m using a TI Process like
DIMENSIONELMENTINSERT(‘Time’,’’,Year,’c’)
DIMENSIONELEMENTINSERT(‘Time’,Month,’m’)
DIMENSIONELEMENTCOMPONENTADD(‘Time’,Year,Month,1)
This loads from a db source that provides the years and months and will give me years with the months under the consolidations.
BUT at the bottom of my dimension I get all the months listed. I sort of want a DimensionComponentSetParent instruction.
How on earth should I do this?
Take a look in the TM1 Reference Guide for the TI function DimensionSortOrder; should give you what you need.
Re: DIMENSIONCOMPONENTADD
Posted: Mon Jan 17, 2011 9:42 pm
by lotsaram
paulfelce wrote:... BUT at the bottom of my dimension I get all the months listed. I sort of want a DimensionComponentSetParent instruction.
How on earth should I do this?
It doesn't look like there is anything wrong with your TI code so I assume that your dimension and the element relationships have been created correctly. Just remember that how the dimensions appears when "subset ALL" is displayed is not necessarily relevant from an admin or user perspective. You can control with relative precision how the dimension looks when users interact with it through subsets, which is more important than the index order of the dimension.
As others have indicated you can also change the default index order of the dimension to be sorted by hierarchy (but be aware that if you do this then the components of each consolidation will be sorted by NAME, usually this is not a problem but for a time dimension could give undesired side effects, so unless your element naming conventions will give you the month order you want then this might not be the best option.)
Re: DIMENSIONCOMPONENTADD
Posted: Mon Jan 17, 2011 10:18 pm
by Alan Kirk
lotsaram wrote:paulfelce wrote:... BUT at the bottom of my dimension I get all the months listed. I sort of want a DimensionComponentSetParent instruction.
How on earth should I do this?
It doesn't look like there is anything wrong with your TI code so I assume that your dimension and the element relationships have been created correctly. Just remember that how the dimensions appears when "subset ALL" is displayed is not necessarily relevant from an admin or user perspective. You can control with relative precision how the dimension looks when users interact with it through subsets, which is more important than the index order of the dimension.
As others have indicated you can also change the default index order of the dimension to be sorted by hierarchy (but be aware that if you do this then the components of each consolidation will be sorted by NAME
Not necessarily. If the data source serves the months up in order such that they're added to the dimension in the correct sequence (and granted, this is a critical proviso given that the only two options are 'ByInput' and 'ByName') then this:
Code: Select all
DimensionSortOrder( SC_DIMNAME, 'ByInput', '', 'ByHierarchy' , '');
used like so:
Code: Select all
SC_DIMNAME = 'PeriodExample';
DimensionCreate (SC_DIMNAME);
DimensionElementInsert (SC_DIMNAME,'', 'Jan 09','N');
DimensionElementInsert (SC_DIMNAME,'', 'Feb 09','N');
DimensionElementInsert (SC_DIMNAME,'', 'Mar 09','N');
DimensionElementInsert (SC_DIMNAME,'', 'Apr 09','N');
DimensionElementInsert (SC_DIMNAME,'', 'May 09','N');
DimensionElementInsert (SC_DIMNAME,'', 'Jun 09','N');
DimensionElementInsert ( SC_DIMNAME,'', 'Q1','C');
DimensionElementInsert ( SC_DIMNAME,'', 'Q2','C');
DimensionElementComponentAdd(SC_DIMNAME , 'Q1', 'Jan 09', 1);
DimensionElementComponentAdd(SC_DIMNAME , 'Q1', 'Feb 09', 1);
DimensionElementComponentAdd(SC_DIMNAME , 'Q1', 'Mar 09', 1);
DimensionElementComponentAdd(SC_DIMNAME , 'Q2', 'Apr 09', 1);
DimensionElementComponentAdd(SC_DIMNAME , 'Q2', 'May 09', 1);
DimensionElementComponentAdd(SC_DIMNAME , 'Q2', 'Jun 09', 1);
DimensionSortOrder( SC_DIMNAME, 'ByInput', '', 'ByHierarchy' , '');
will yield this:
- DimHierarchy.jpg (56.43 KiB) Viewed 5203 times
Which is what I think the original poster was after.
Re: DIMENSIONCOMPONENTADD
Posted: Tue Jan 18, 2011 3:25 am
by lotsaram
I was assuming the user would be using the manual "set dimension order" right-click option in the Perspectives UI in which case the options are more limited as once dimension sort order "by Hierarchy" is selected the component sort order automatically defaults to by name, but it's true if using DimensionSortOrder on the prolog of a TI then you have more control and flexibility in how the sort order is executed.
Re: DIMENSIONCOMPONENTADD
Posted: Tue Jan 18, 2011 1:11 pm
by paulfelce
Thanks for all your input.
Alan's screenshot shows what I'm trying to achieve.
What I don't understand though is why TM1 will sometimes yield that structure, but with the months repeated at the bottom of the dimension.
I'll have to look into subsets and see if it's something to do with that. Annoying if so because Cognos BI doesn't seem to interact well with TM1 subsets.
Re: DIMENSIONCOMPONENTADD
Posted: Tue Jan 18, 2011 4:12 pm
by Steve Vincent
Sometimes the subset window looks messed up when the actual structure is fine. This is especially the case when a dim is created where the element numbers do not match that of how the structure flows down the page (ie. top is 1, next is 2 and so on). A couple of mine look like that due to being created by multiple TIs from different sources. Setting the dim order by heirarchy certainly solved mine, and most subsets i use MDX so that also resolves it.