records skipped in TI process

[shinymcshires]

I built a TI process that connects via an ODBC connection to our transaction database. The purpose of the cube is to manipulate the data into a definied ascii journal entry format, then import the file into our general ledger (the general ledger and transaction system are separate). I thought I had the process figured out, until I tried to import one of the journal entries. One of the batches was not balanced, and that is when I discovered that some of the records were not being included in the cube. When I run the TI process, I don't get any errors. I've never seen this problem with any TI process I've built- if there are records that are not going to be included in the cube, they show up in the error log. I have attached jpg's of some of the TI tabs. I tried to attach more than 3 images, but I guess I can't post more than that. I appreciate your time and consideration. Thanks!

ti-variables tab.jpg (56.36 KiB) Viewed 5957 times

ti-maps-dimensions.jpg (65.07 KiB) Viewed 5958 times

ti-advanced-prolog.jpg (86.97 KiB) Viewed 5955 times

\

[David Usherwood]

Welcome to the forum!
I'm not initially sure what you are trying to do. Is is to READ from your ODBC source and update a TM1 cube, or the other way round?

I'm afraid that TI's debugging features are very limited indeed. The only approach I find useable is to insert ASCIIOUTPUT statements all over the place and look at the results. NB ASCIIOUTPUT only accepts strings, so convert the numerics using STR(value,15,3) or whatever. If you output to a CSV file you can open and filter it in Excel. The default destination for the file is the TM1 server directory - if you haven't access to that then sort out another path to which the file can be written. One last gotcha - a different file is opened on each tab of TI, so if you use the same name in the metadata and data tabs the latter will trash the former.

It's workable - you can put any content you have in TI out, inside any logic. But a grownup ETL tool would have better tools. There was talk of replacing the TI script with something else - I can dream.

[Michel Zijlema]

Just checking: could it be that you have multiple source data rows mapping to the same cell in the cube and that you forgot to setup accumulative loading?

Michel

[Steve Rowe]

Yeah I would be looking at multiple rows in the file pointing to the same cell in TM1, if you don't have accumulate ticked then you will just end up with the last value encontered loaded. If you have ignored any of the columns in your datasource then it is highly likely you have the multiple row issue.

Cheers

[shinymcshires]

Hi All-

Thanks for your feedback. I'll try to explain what I'm doing, and that might help.

We have a separate system that we use for our cash register transactions and a separate legacy system for our general ledger. Up until now, I have simply connected to the cash register database (via ODBC and Excel), manipulated the data according to the specifications in the general ledger, and imported as csv journal entry file into the general ledger.

My goal with TM1 is to use a TI process to connect via ODBC, manipulate the data using the SUBST formula, and store the data, which would be updated daily (if not more frequently). I would then select new batches to be imported (that would already be formatted) by taking a snapshot of the cube view, saving as a csv file, and import into the GL.

I did try setting to accumulate values, but I don't think that is what I want. Each unique line item transaction should have its own value.

Here's a view of the data preview

ti-datasource.jpg (66.25 KiB) Viewed 5818 times

Here's a cube view. This actually is one of the problem batches- where records are missing.

jecubeview.jpg (48.8 KiB) Viewed 5818 times

Here's a shot of the data, specifically for the same data criteria parameters as the cube view:

rawdata.jpg (76.08 KiB) Viewed 5818 times

If you compare the cube view to the raw data, you can tell that the cube view is missing two records- the one for $1135.00 and $848.00. I hope this makes sense.

Thank you again for your comments and feedback!

[shinymcshires]

I take it back-

You guys were right. Accumulate values is the correct setting. Thanks for your suggestions! I appreciate it very much.

[Steve Vincent]

That one has caught me a few times in the past where i didn't think i needed to accumulate when i did. Especially easy when you don't have a text file that easily filtered or masses amounts of data to trawl thru to find the issues. Just be careful when loading the data that if its using the same elements as a previous load, you'll need to clear the data from the cube before you reload it, otherwise you'll get double the intended values.