Load same data to all lines in a dimension??

[jimicron]

Hi,

I am somewhat embarassed to ask this, but just can't seem to figure it out and it seems like it should be so easy. In Cognos Analyst (which is my background) it IS very easy. If you are creating a d-link, you would simply not select anything. Selecting nothing means data gets loaded to everything. I am wondering how to do the same in TM1 in a created variable. I just need to load my data to every line item. I know how to write the formula to send my data to one of the line items of the dimension, but not to all of them. And, I can't seem to find anything on this which is odd to me. I created screenshots, etc. in my attachment to help describe and show everything.

Thanks as always!!

[Edward Stuart]

You are most of the way there, the standard TI Wizard will be sufficient:

Goto tab Maps:

Under Cubes: Select relevant option (often Update Cube)
Data Action: Store or accumulate values

Then goto Dimensions tab:

Select each Dimension per Element Variable

Data tab:

Match Up Data as necessary

Save the process and run

More details can be found here:

http://pic.dhe.ibm.com/infocenter/ctm1/ ... ial_NC0007

Check the advanced tab once it is working and search around the IBM documentation for their functions/ meanings and you can get a feel for what is going on behind the wizard.

[jimicron]

Thanks Edward.

I know how to create a process and choose "update cube," map, and all of that - that is no issue.

But what I am not sure of is... when you look at my screenshot, in the variable I created, I am currently loading data to just "Back End" because my formula is:

vExpenseType='Back End';

However, what I really want to do is load the same data to not only "Back End" but ALL lines in my dimension: Materials, Setup, Frontend, Middle, Back End, SLT, etc.

So I am at a loss as to what my Process Variable Formula should look like in order to load to all items in my dimension.

[tomok]

With all due respect, I don't think that's what he wants Edward. I think the OP wants to store the value in the input file into every single element of the dimensions that aren't represented in the input file. For example, if the value is $10.00, he wants to store the same $10.00 in every intersection of the dimensions for which he has no choice in the input file. The way to do this is going to be to create a series of nested loops in the Data tab and do a CellPutN for each intersection. Something like:

Code: Select all

Size1 = DIMSIZ('Missing_Dim_1');
Size2 = DIMSIZ('Missing_Dim_2');
Size3 = DIMSIZ('Missing_Dim_3');
i = 1;
j - 1;
k = 1;

WHILE (i <= Size1);
  Elem1 = DIMNM('Missing_Dim_1',i);
  WHILE (j <= Size2);
  Elem2 = DIMNM('Missing_Dim_2',i);
    WHILE (x <= Sizex);
      Elem3 = DIMNM('Missing_Dim_3',i);
      IF(ELLEV('Missing_Dim_1',Elem1) = 0 & ELLEV('Missing_Dim_2',Elem2) = 0 & ELLEV('Missing_Dim_3',Elem3) = 0);
        CellPutN(ValueFromInputFile, CubeName, Dim1, Dim2, Dim3...) Dim1, Dim2, Dim3 would be replaced by either variable from the input file OR your Elem1, Elem2, Elem3
      ENDIF;
      k = k + 1;
    END:
    j = j + 1;
  END;
  i = i + 1;
END:

i don't know if the syntax is perfect but this is the idea.

[Edward Stuart]

Ahhh...

You could copy the CellPutN statement outside the Generated Statement lines and replace vElementType with the new element

# Begin Generated Statements
CellPutN(Value, Cube, v1, v2, v3, vElementType, v4)
# End Generated Statements

CellPutN(Value, Cube, v1, v2, v3, 'Materials', v4)
CellPutN(Value, Cube, v1, v2, v3, 'Setup', v4)
etc.. etc..

Not pretty or dynamic but gets the job done

[tomok]

Ahhh...

You could copy the CellPutN statement outside the Generated Statement lines and replace vElementType with the new element

# Begin Generated Statements
CellPutN(Value, Cube, v1, v2, v3, vElementType, v4)
# End Generated Statements

CellPutN(Value, Cube, v1, v2, v3, 'Materials', v4)
CellPutN(Value, Cube, v1, v2, v3, 'Setup', v4)
etc.. etc..

Not pretty or dynamic but gets the job done

Not really. Materials is the name of the dimension, not the name of an element in the dimension. You still have to loop through all the "missing" dimensions to find n-level elements for each and write to them.

[Duncan P]

An alternative is to create an '<import>' item in the missing dimension and import into that. Then use a rule to refer to that data from everywhere else in the dimension.

Your rule would look something like this

Code: Select all

['SIG Expense Type':'<import>'] = N: STET;
[] = N: ['SIG Expense Type':'<import>'];

where the first line says "leave the imported data alone" and the second says "otherwise use the imported data".

[lotsaram]

I'd go with Duncan's suggestion or a variant thereof. If you want to "load" a value to all cells the it sounds like this is really a lookup reference and not values that you want to consolidate. This is what rules are very good for. In this context you wouldn't need to feed the rule either.

[jimicron]

Thanks all!

We went ahead and did what Duncan suggested and it worked out just fine!

We then were struggling a little bit about what to feed (again, we are both new) so we then created this in Performance Modeler versus Architect and it did the feeders. In PM, it was very similar to how we do it in Analyst, so there was a comfort level there.

Makes me think though - and wondering - with code like above... and with any code... how does a person like me learn it when I am not used to it? Are there really good manuals? Training? I know in the Analyst class that I teach, I have a section on SQL and I give students in my class the basics of SQL - You always will have a SELECT and a FROM , which are mandatory, but many times you will have a WHERE clause, which is like filtering in Excel... and then I explain how to structure the statement, when to use quotes or single quotes, etc. It's a basic kick start for SQL for folks who are not familiar with it. Is there anything SIMILAR but for the code in TI processes? I have the manual printed.. but IBM documentation always seems to have a bit to be desired... so was curious if anyone has suggestions.

It seems like most of you are "coders" unlike myself. Thus, wondering of a good source other than here (this forum has been great - but I also don't want to overburden with my questions). Thanks a lot!

[Duncan P]

I came from Analyst and Contributor too. When I started TM1 I spent a week just working through all the examples in the rules guide from start to finish, trying variations out and extending the examples until I was sure I understood what it was saying. I would recommend that as an approach to get you going.

As far as TI is concerned one thing to do is to try create a process using the normal mapping pages, and then with the Turbo Integrator guide and the reference guide work through what it has written on the advanced tabs and make sure you understand it. Then you can set all the variables to "Other" and try doing the same or similar yourself. When you have got the hang of that I would download Bedrock and read through some of their processes and try to understand them.