Hi Experts,
I am trying to write a TI proccess and I want to read a subset by using a variable (instead of hardcoding the subset name in the ti Data source name).
I know how to do that for a cube view using the ViewextractSkipCalcsSet in the Prolog, but I dont seem to find something similar for dimension subsets. ( and I cannot find a control cube for those dimensions hierarchies).
ANy help is really appreciated.
Thank you
Micheline
Variable Dimension Subset name in TI
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Variable Dimension Subset name in TI
Thanks
Micheline
Micheline
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: Variable Dimension Subset name in TI
In the case of a dimension subset as your data source you will need these three variables:mnasra wrote:Hi Experts,
I am trying to write a TI proccess and I want to read a subset by using a variable (instead of hardcoding the subset name in the ti Data source name).
I know how to do that for a cube view using the ViewextractSkipCalcsSet in the Prolog, but I dont seem to find something similar for dimension subsets. ( and I cannot find a control cube for those dimensions hierarchies).
ANy help is really appreciated.
Thank you
Micheline
DatasourceType = 'SUBSET';
DatasourceNameForServer = sDimName;
DatasourceDimensionSubset = sSubsetName;
In the case of a cube view used as your data source you would write something like this:
DatasourceType = 'VIEW';
DatasourceNameForServer = sCubeName;
DatasourceCubeview = sViewName;
Please read up on ViewextractSkipCalcsSet as I don't think it does what you think it does.
Kamil Arendt
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: Variable Dimension Subset name in TI
Hi Qml,
Pardon my translations.
But what I do when I want to extract data from a view in a cube, I create a Temporary view in the prolog using ViewCreate (cube, extractfromhere)
and the 3 viewextractskipcalcet, valueset and zeroset.
I do whatever I want in metadata and data and then
I finish by deleting this view in the epilog). Instead of leaving those ugly z-views to the endusers eyes.
I wanted to do the same thing when a dimension subset is my source data.
I created the subset using MDX but the name is not fixed (it has the year number in it. like now I am closing 2013 so my subset name is ACCOUNT-2013).
How do I say (and where) to TI that my data source is a dimension subset called account-2013
where 2013 is a variable.
Thanks
Micheline
Pardon my translations.
But what I do when I want to extract data from a view in a cube, I create a Temporary view in the prolog using ViewCreate (cube, extractfromhere)
and the 3 viewextractskipcalcet, valueset and zeroset.
I do whatever I want in metadata and data and then
I finish by deleting this view in the epilog). Instead of leaving those ugly z-views to the endusers eyes.
I wanted to do the same thing when a dimension subset is my source data.
I created the subset using MDX but the name is not fixed (it has the year number in it. like now I am closing 2013 so my subset name is ACCOUNT-2013).
How do I say (and where) to TI that my data source is a dimension subset called account-2013
where 2013 is a variable.
Thanks
Micheline
Thanks
Micheline
Micheline
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Variable Dimension Subset name in TI
mnasra wrote:Hi Qml,
Pardon my translations.
But what I do when I want to extract data from a view in a cube, I create a Temporary view in the prolog using ViewCreate (cube, extractfromhere)
and the 3 viewextractskipcalcet, valueset and zeroset.
I do whatever I want in metadata and data and then
I finish by deleting this view in the epilog). Instead of leaving those ugly z-views to the endusers eyes.
I wanted to do the same thing when a dimension subset is my source data.
I created the subset using MDX but the name is not fixed (it has the year number in it. like now I am closing 2013 so my subset name is ACCOUNT-2013).
How do I say (and where) to TI that my data source is a dimension subset called account-2013
where 2013 is a variable.
Thanks
Micheline
Code: Select all
Prolog:
sSubsetName = 'account-' | pYear;
sMDX = Whatever your MDX is (looking at pYear probably);
SubsetCreateByMdx ( sSubsetName, sMdx );
DataSourceType = Subset;
DataSourceNameForServer = sDim;
DataSourceDimensionSubset = sSubsetName;
Epilog;
SubsetDestroy ( sDim, sSubsetName );
Declan Rodger
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: Variable Dimension Subset name in TI
Thanks A million.
I never knew about the datasource type commands.
Much simpler than what I was doing for the cube thing.
I think it is going to work.
THank you
I never knew about the datasource type commands.
Much simpler than what I was doing for the cube thing.
I think it is going to work.
THank you
Thanks
Micheline
Micheline
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: Variable Dimension Subset name in TI
hi qml,
Thanks for being online this sunday.
I know you answered this question in a different thread, but I dont seem to be able to make it work.
It is the subset filter by pattern where the pattern is a variable. I tried almost all combinations but not working for me. Could you help?
merci.
Micheline
SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
" sname ")} ' );
Thanks for being online this sunday.
I know you answered this question in a different thread, but I dont seem to be able to make it work.
It is the subset filter by pattern where the pattern is a variable. I tried almost all combinations but not working for me. Could you help?
merci.
Micheline
SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
" sname ")} ' );
Thanks
Micheline
Micheline
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Variable Dimension Subset name in TI
mnasra wrote:hi qml,
Thanks for being online this sunday.
I know you answered this question in a different thread, but I dont seem to be able to make it work.
It is the subset filter by pattern where the pattern is a variable. I tried almost all combinations but not working for me. Could you help?
merci.
Micheline
SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
" sname ")} ' );
I am assuming that in this case you mean sName is the variable you wish to pass into the mdx code. You have enclosed it within the quotations of the whole string so TM1 can't differentiate it as a variable.
Try:
Code: Select all
SubsetCreatebyMDX(nameasYear, '{TM1FILTERBYpattern( {TM1SUBSETALL([account] )},
"' | sname | '")} ' );
Try running a few combinations through the editor manually with the record mdx option turned on and see what it gives you.
Or if its possible you could consider ditching the MDX altogether, do a while loop through the dimension on each element using the scan function (and possibly a DType or AttrS etc) then add them to a static subset if they meet your criteria.
I'm not against MDX per se but there is another option, there is also an extra parameter in the newest versions to through into the SubsetCreatebyMDX to dictate how it handles creating a subset with no elements.
Declan Rodger
-
- Posts: 136
- Joined: Tue Aug 10, 2010 5:40 pm
- OLAP Product: Planning Analytics
- Version: 2.0
- Excel Version: EXCEL 2013
Re: Variable Dimension Subset name in TI
Thanks a million. And thank you for all the suggestions.
Yes, working perfectly.
Yes, working perfectly.
Thanks
Micheline
Micheline