DimensionAttrs

[manpreet]

This a new function has anyone used it I wanted to use this to directly fetch the data stored in the attribute against elements.

my scenario is like in data source I am fetching data from odbc which has 3 columns e.g sr.no country(vcountry) rate so I have 1 brazil 30.

I have dimension Region which has country codes against which I have a country attribute (vCountryAttrs) in which all codes have countries against it like
1011 Brazil
1012 India
1013 Canada
1014 UK
1015 USA

so now want to load the rate in a cube but only if coulmnB (country) matches with Country attribute then only
vcountryAttrs=DimensionAttrs('Region','country');
IF(vcountry@=vCountryAttrs);
cellputn(rate,cube name, dim,dimn....);
Endif;

if I use ATTRS then I have to mention the Elements name there are more than 100 elements in the Region dimension.
I thought of while loop to fetch it easily but if I will write my cellputn in the while condition it will take longer time and I can't use the variable I have generated in while after End of while.

If anybody has an optimised way to do this.
I have also used cellgets from control object cube of element attributes and used the consolidated element to fetch data but there is nothing when I made a ASCII of it.

Anybody who has faced this issue as this is a normal scenario I think.

Thanks In Advance....

[Wim Gielis]

If vCountry is Brazil, then you can just do, if Brazil is also an alias on the Region dimension:

Code: Select all

CellPutN( vRate, 'cube', ..., ..., vCountry, ... );

No loops needed.

If not, I did not understand your question.

[lotsaram]

I think you misunderstand what DimensionAttrS is for. This is really for retrieving attributes of the dimension (typically only ever Caption). This is not for element attributes!

[manpreet]

i think i have not properly explained the scenario
data source is odbc which has 3 columns in which there is a country column also which i have to match with an attribute of a dimension named region which has codes as elements for every code a country is defined using text attribute.

now before cellputn I have to check if the country matches the attribute or codes then only it should cellputn else not.

so to call the data stored against element name and attribute
(element name) (attribute name country)
1101 UK
1102 UK
1103 UK
1104 USA
1105 USA
in my data, there can be 10 countries and in my dimension, there are 100 codes but 10 codes can have one country assigned named UK.
so in data there are countries but i have put data against 5 only
so before putting data if(country in data matches the country in attribute then cellputn else skip this what i have to do).

to bring all the countries in a variable to match the country in data i want to use dimensionattrs in the TI.
so what it will return all the countries stored against elements.

if you any doubts you can simply make a TI and check this Function.

vCountryAttrs=DimensionAttrs('DimensionName','Attributename');

Asciioutput('path',vCountryATTRS);

it gives me an error that attribute is not there in the dimension you can use it for any dimension.
thank you.

[manpreet]

I have made an Attribute named ABC in my dimension and when running the ti it gives the following error.
Error: MetaData/Data procedure line (5): Attribute "ABC" not found.

Thank You.

[Wim Gielis]

If it’s not an alias but a text attribute, use a loop. You will be amazed how quick this runs.
Use AttrPutS and CellPutN. No other functions that you are shouting here but you don’t understand.

[Wim Gielis]

Please read the documentation on the use case of a function.

[manpreet]

Hey Wim,

I used a loop it works fine to fetch data against attribute but is it a good practice to write the condition and cellputn in the same loop to load the data at the same time.

[Wim Gielis]

What would you have against a simple loop and writing data in a cube ?

[manpreet]

ncounter=1;
vDimsiz=DIMSIZ('h_region');
While(ncounter<=vDimsiz);
velement=DIMNM('h_region',ncounter);
vCountryAttrs=Attrs('h_region',velement,'ABC');
ncounter=ncounter+1;
Asciioutput('E:\Test.csv',vCountryAttrs,numbertostring(vDimsiz),velement);
End;

this gives me proper result but if i write the Asciioutput outside of the loop then only the last element name is returned
so if i have to write CellputN inside the loop like
ncounter=1;
vDimsiz=DIMSIZ('h_region');
While(ncounter<=vDimsiz);
velement=DIMNM('h_region',ncounter);
vCountryAttrs=Attrs('h_region',velement,'ABC');
ncounter=ncounter+1;
Asciioutput('E:\Test.csv',vCountryAttrs,numbertostring(vDimsiz),velement);
if(vcountry@=vCountryAttrs);
Cellputn(value,cuubename,dim1,dimn...);
EndIF;
End;

i don't want cellputn to loop in.
as the DataSource is in Lakhs so to loop that is not an optimised way to di it.
Hope Now i am Clear.
Thank You

[Wim Gielis]

Again, what do you have against a loop ? It’s extremely fast in TM1 unless your data source is 1,000,000 records and you need to loop again over 1,000,000 or so. That would lead to a big number of combinations but other than that ?

Or create a roll up structure with countries and their codes as the children. Then you can limit your loop over the children instead of the entire dimension.

[manpreet]

Hey wim,

i want to do a cellputn it has more than 10 lakh records

but to load data i have a condition.

condition is if my column b which is a country name in the data source is equal to h_region attribute which contains the country name

To fetch all the attribute names i can use while as i have 100 elements (element name is in code (1101) against which country names are specified in a text attribute so can get all the values stored against attribute by simply using the loop but putting cellputn in the loop while lead to 100 times the data to read and write according to country name.

i know with loop its possible to do all that i want but only the thing is i don't want write cellputn in the loop and try to optimise is it a better manner using the new function DimensionAttrs or some other logic.

Thank You

[Wim Gielis]

The size of the data is what it is. Apparently lakh means 100,000 (it would have been good to write this here). So you have 1 million records to process.
Then you loop over a dimension. As said, you can limit the combinations by helping TM1 and bringing more structure in the countries and the codes. Like I said, a dimension that you don't use in a cube, but contains country as parent and codes as children. Then you can limit the loop over a couple of elements, times 1,000,000, this is still feasible. It will not be lightning fast of course.

Other solution is to split the load in several loads that can run at the same time, in parallel. Seearch the forum for more on that.

About the cube, what's the reason to have the same numbers against several country elements ? Isn't it sufficient to have it against 1 (dummy) country ?

[tomok]

You can state what you would like to do until you are blue in the face but it's not going to change what is possible in TM1. TM1 is not SQL. There is no UPDATE function in TM1 like there is in SQL where you can update multiple rows (in TM1 we call them intersections) at one time by specifying where the value in one of the columns matches something. In TM1 you can only write to one intersection at a time, which is why you have to cycle through the records and do the CellPutN.

That being said, you may be able to shrink the population of records in your loop by:
1) creating an MDX subset that filters to only those elements where the element attribute matches the country
2) looping through the subet and do the CellPutN
3) destroy the subset

Keep in mind you'll have to do this for every record in your data source so you are still looping through multiple subset members for each record in your data source but at least you aren't looping through every member in the Region dimension every time.

[Wim Gielis]

A subset is fine too, but if speed is important I would definitely go for a spare dimension and a clean/easy loop over the children of a certain consolidated element. No creation and deleting of dynamic subsets, turning into static subsets, looping over the subset. (I know that you can prepopulate static subsets too, but it seems a lot of overkill to me.)

[manpreet]

thank you tomok and wim,

I used while for my problem it's working fine speed is also good as the data set is in thousands only.
If further, I do the same without a loop I while post the solution here.

Thank you.