TM1 MDX Count rows in Excel Containing Data

[bhinbest]

I'm trying to create a turbo integrator process in TM1 that will count the number of active rows in a selected Excel sheet, then add that number of elements into a dimension. These will be my transaction id's.

For example the first time I run it, if the Excel sheet contains 1254 rows of data, the Transaction ID dimension will add 1254 elements named 00000001, 00000002, 00000003, ..., 00001254.

The second time I run it, if the Excel sheet contains 3678 rows of data, the Transaction ID dimension will add 3678 elements named 00001255, 00001256, 00001257, ..., 00004932.

Any thoughts?

[Eric]

I must admint I find the request odd. TM1 is designed for summarized information. If oyu are looking for a database for transactional information you might want to try another system.

However, to do what you want I would just insert a column in excel and put it in the number format you want and just just copy it down. The load the flat file through TI.

Does that help?

[Marcus Scherer]

bhinbest,
you probably don't need a Transaction ID dimension at all. Your end users won't do any analysis on it. Free yourself from this OLTP point of BI. Just make sure the elements in your analysis dimensions have unique names.

Cheers,

Marcus

[Kerry372]

Maybe you could you have 3 variables:

v1 = dimnm('your dimension',dimsiz('your dimension')+if you need to add anything to get the last element);
v2 = 1+stringtonumber(v20)*1;
v3 = if(long(numbertostring(v2))=1,'0000000'|numbertostring(v2),if(long(numbertostring(v2))=2,'000000'|numbertostring(v2),if(long(numbertostring(v2))=3,'00000'|numbertostring(v2),if(long(numbertostring(v2))=4,'0000'|numbertostring(v21),if(long(numbertostring(v2))=5,'000'|numbertostring(v2),if(long(numbertostring(v2))=6,'00'|numbertostring(v2),if(long(numbertostring(v2))=7,'0'|numbertostring(v2),numbertostring(v2))))))));

Then update your dimension as you load it with the variable.

There is probably an easier way to write these.