How can I create TI Script with intangible element?

[anote]

Hi All

I want to create TI script to load data from ERP to TM1.
But my problem is in TM1 I have structure and result should be like this

Company Item name amount
X 1 A 100
X 2 B 200
X 3 C 300
X 4 D 400
Y 1 B 150
Y 2 C 170
Y 3 D 220
Z 1 B 60
Z 2 D 70

But in ERP there is no column name item as follow

Company name amount
X A 100
X B 200
X C 300
X D 400
Y B 150
Y C 170
Y D 220
Z B 60
Z D 70

How can I create TI script to load data for this case?

Now I create TI process like this

in Data tab

N=1;

While(N<5);
item=numbertostring(N);
CellputS(name,'Store',Company,item,'Name');
CellputN(amount,'Store',Company,item,'Amount');
N=N+1;
End;

But the results are like this
Company Item name amount
X 1 D 400
X 2 D 400
X 3 D 400
X 4 D 400
Y 1 D 220
Y 2 D 220
Y 3 D 220
Y 4 D 220
Z 1 D 70
Z 2 D 70
Z 3 D 70
Z 4 D 70

Please give me a suggestion

[David Usherwood]

Anote,

The reason you are getting the results you are is that (which may not be obvious if you haven't used TI a lot) that the code in the Data tab is executed once per record read from the datasource, so the data will be cloned across all your 'items'.

To do exactly what you want will probably be a bit tricky - since you need to detect when company and name changes and reset your item accordingly. Not impossible though.

I must say (to echo my esteemed former colleague Steve Rowe) that when you have a problem like that it does suggest that the design you have could bear rethinking. In this case, what is the purpose of the 'item' dimension? I would think that creating a cube without that dimension and loading your data in there would give a workable model. But there may be specific functional requirements I don't know about.

HTH

[Steve Rowe]

Thanks David!

Anote , I'm not 100% sure but it seem like your trying to recreate your relational table in an OLAP cube which is why you are getting in a mess. Your dimensionality of the cube should probably just be Store,Company and measure. Measure contains amount.

Not sure where to go from here like David I don't understand the purpose of item.

Cheers,

[anote]

Many thank David/Steve for your reply

My expect view is follow attach file


But in my ERP doesn't has item column.
ERP has only Company, Customer name, Amount

My purpose is not to use OLAP cube as relational table but i need to have item dimension to serve report view

Can it possible to use TI to load data from ERP? please give me a suggestion

[Steve Rowe]

anote,
I not sure what item is, is it product?
It would be much much more normal to have customer as a dimension rather than a piece of data. With your approach for example you won't be able to total by customer from the system. When I say you are creating a relational table in TM1 it's because you are storing string information which could be a dimension (customer) and are also kind of creating a primary key (Company & Item). As a _very_ general rule of thumb you should only ever store numeric information in a cube.

Anyway to solve the problem of getting your item number the logic could be something like this, it does rely on your data being ordered by company though which is not ideal.

In the data tab at the start

If (LastCompany@=Company);
#same company as last record
ixItem=ixItem+1;
Else
#New company
ixItem=1;
EndIf;

#Then all your load logic goes here, don't use the loop you had in your post

#Last item in the data tab

LastCompany=Company;

HTH
Cheers,
Steve

[Mike L]

anote,

If, as in your sample data, each Name has at most one Item per Company (i.e. Company+Name is a normalized key in the source) then the Item dimension serves no real purpose. It would just be a report line number in a particular view.

If, on the other hand, one Name might appear multiple times for the same Company, then Item could be useful for drilling into details of customer activity. (If I were doing this, it would count items per customer within company, rather than items per company, pushing it down to the lowest level that needs disambiguation.) Still, this is not how TM1 is normally used. A "good" model does not normally presume a particular cube view. Consider, e.g., that there would probably be no purpose for a slice of Item 3 spread by Company and Name, so this dimension does not benefit from the ability to rotate views.

That said, if this is what you need to do there is a way to do it. If the source data is sorted then you can use David's approach, bearing in mind that the data tab is executed inside a loop, as he said. (If it is not sorted then a different method is needed.) It would look something like this:

Code: Select all

===Prolog===

ItemCount=0;
CurrentCompany='';

===Data===

if( Company @= CurrentCompany );
   ItemCount=ItemCount+1;
Else;
   CurentCompany = Company;
   ItemCount = 1;
Endif;

<insert code here>

[anote]

I do follow your suggestion

prolog tab

ItemCount=0;
CurrentEntity='';

Data tab

if( Entity @= CurrentEntity );
ItemCount=ItemCount+1;
Else;
Curententity = Entity;
ItemCount = 1;
Endif;

item=numbertostring(itemcount);
CellputS(V2,'test',Entity,item,'Customer Name');
CellputN(V3,'test',Entity,item,'Amount Within Payment Term');

What i expect is

This is a result that i expect

example2.PNG (23.52 KiB) Viewed 10373 times

but my result appear as follow

example5.PNG (20.87 KiB) Viewed 10372 times

[paulsimon]

Anote

Assuming that the code you posted is the exact code then you have a mis-spelling

Curententity = Entity;

Instead of

CurrentEntity = Entity ;

Which probably means that your breaks aren't working

I tend to agree with previous posts. You should perhaps look at your design. Why wouldn't you want Customer Name as a dimension?

Regards


Paul Simon

[anote]

Thank you for your kindly help

Your code works very well

my mistake that I make a wrong type

I cannot use customer as dimension because in report need to show detail by transection

and if i use invoice as dimension, i affraid that element of the dimension will increase very fast

because i have 10 company and each company has ¬200 transection per month.

So I concern about the number of element then I need to use item dimension to reduce number of element

[Steve Rowe]

Anote,
I can understand your thought about not using the invoice dimension as it will get large quickly, this probably would not be an issue for TM1 though. Depending on hardware TM1 copes with dimensions into the 100s of thousands easily.

Using item as a notional invoice number seems like a good plan since you avoid having a large sparse dimension. I don't think this stops you putting customer as a dimension as well. You will still be able to display the data as shown in your example, additionally you have the following advantages.
1.Use less memory since you are not storing string information, currently you probably lose any performance gains form not using invoice number by storing customer name as a string.
2.Be able to derive total invoice by customer
3.Be able to drag and drop and reorientate your view.

Cheers,
Steve

[anote]

Thank you Steve for your suggestion

It's very good suggestion. I'm going to think about it.

Very useful for me.


BR,
Note

[cdredmond]

Great quality discussion here guys!
I just joined this portion of the forum and recognize that this thread is a bit old at this point.
However, for future readers, i would like to offer some additional thoughts...
Assuming an internally generated index is needed, here are two other approaches to consider:
1) Create an 'Item' numeric attribute on the Entity dimension. Each time a record is read during the Data procedure loop, add 1 to the Item attribute for the Entity in the record and use the new Item value as the string element name for the Item demension.
2) If there are more dimensions involved in the cell address ("primary key" similar to Steve's suggestion) than just Entity, make 'Item' a measure and store the latest value in Item 1 or 0 in the Item demension. This also provides the option to reuse an Item number over a time dimension.

With these two approaches one does not need to be concerned with data sort order or keeping track of the last Item number used since it is now stored as part of the cube. This also provides the ability to know the maximum Item number in use for other activities such as reporting, building dynamic subsets, etc.

Best wishes to you all!

CDR