Sample Script to integrate csv files in a cube

[CoN73mP7]

Hi,

has anyone a sample how to load a csv file from a directory and add the data to an existing cube? This should be done without using the TurboIntegrator.

Another question: One of the elements in the csv file is a date. What happens, if the dimension doesn't have this date as an element? Is there any possibility to catch that case and add it to the dimension automatically?

Alex

[tomok]

TI is the vehicle for doing batch metadata and data changes in TM1. There is no other tool unless you write your own in the API, which I doubt anyone here has done, except for small changes, because 1) it is extremely cumbersome and 2) it would be much slower than anything you could build in TI. As to your second question, this is extremely easy to do in TI. You simply check to see if the items exists with the DIMIX function (a return value of 0 means it doesn't exist) and if it doesn't, you add it using the DimensionElementAdd function. What's with the aversion to TI?

[jim wood]

I'm not sure if it still supported but back in the day you were able to load CSV files through Excel line by Line. This was the default method pre TI,

Jim.

[tomok]

I'm not sure if it still supported but back in the day you were able to load CSV files through Excel line by Line. This was the default method pre TI,

Jim.

It is still supported but I didn't mention it because 1) it is painfully slow, and 2) you are limited to simply loading a cube, no dimension maintenance or other TI-like tasks. It's hardly useful for anything other than messing with data when doing development, IMO.

[jim wood]

I mentioned it because It's useful for loading data if you don't have TI. Saying that I'm not sure if it is possible to have TM1 without TI these days. Again back in the day it was a seperate license,

Jim.

[Alan Kirk]

I'm not sure if it still supported but back in the day you were able to load CSV files through Excel line by Line. This was the default method pre TI,

It's still there on the TM1 Menu -> Process Data option. (Edit: Chapter 5 of the Developers' Guide. No, not the User one. Knew I'd find it eventually...) It was never particularly fast - painfully slow in fact - and I recall dimly some posts that reported that it slowed down even further in one of the post 9.0 releases. (9.1 or 9.4, I think.) But you're right, it's still an option if someone doesn't want to provide access to TI, though I'd hope that Alex is aware that you can give ordinary users permission to RUN particular TIs without giving them the ability to EDIT them (if that's what the issue is).

My main issue with Process Data is that you couldn't effectively and reliably do aggregation with it. (It might be possible to read the original value through a DBR and send back the aggregated value via DBSW since the former may be / should be / could be read first; not sure I'd rely on it though.) And of course metadata changes just ain't happening with it.

There is no other tool unless you write your own in the API, which I doubt anyone here has done, except for small changes, because 1) it is extremely cumbersome and 2) it would be much slower than anything you could build in TI.

Point 2 doesn't gel with my experience; I find API apps to be extremely fast (but yes, cumbersome), and at least as fast as TI for comparable tasks. Unsurprising as the two are probably calling the same functions for what they do. I wouldn't endorse it as an option in this case - it's far too much work and reinventing the wheel is never a good idea - but speed is not a reason to avoid the API.

[CoN73mP7]

OK, I think I havn't described my problem propperly. What I do not want to use is the TurboIntegrators Frontend. I've got lots of cubes to wich I want to integrate new csv files additionally each day. thus i want to have one process that somehow gets the data from the files and puts them in the cube. Then the csv file should be moved in an archiv directory.

Can anyone tell me the functions to use in such a process and how do I create them?

I cant use TI's frontend because it would be to much to create a process for every file/cube.


Greetings
Alex

[Alan Kirk]

OK, I think I havn't described my problem propperly. What I do not want to use is the TurboIntegrators Frontend. I've got lots of cubes to wich I want to integrate new csv files additionally each day. thus i want to have one process that somehow gets the data from the files and puts them in the cube. Then the csv file should be moved in an archiv directory.

Can anyone tell me the functions to use in such a process and how do I create them?

I cant use TI's frontend because it would be to much to create a process for every file/cube.

How much commonality is there in the file structure? If the files are all very similar, and/or the cubes are all very similar, you can use a limited number of processes and specify the cube to write to in the Prolog depending on the parameters that you pass to it. (Which, incidentally, you can do via an Action Button on an Excel spreadsheet if you want.)

The most common way of archiving is the ExecuteCommand function which runs a batch file on the server.

But your question is still a bit light for details. How many cubes? How similar? To store what kind of data? Are you unnecessarily using extra cubes when you could model everything in a single cube, or two or three cubes? Put another way, are you really looking at the most efficient way of solving whatever your specific business problem is in a complete way, or just using a brute force approach of loading lots and lots of files into lots and lots of cubes because they aren't identical?

[CoN73mP7]

Thanks for your fast reply,

there are about 130 Cubes with all kind of different data, The csv-files are all in the same way for every Cube. First col: Cube name, then dimensions and last field the data for the adressed field.

So how do I proceed? Do I create a script in a new file or in a TM1 generated file? http://publib.boulder.ibm.com/infocente ... 70006.html

Here I hava list of funtions, but it never says how to use them and how to execute them.

[blackhawk]

Why don't you just write one TI script that deals with all files?

All you need to do is point TI to a file (or a dummy file) that has the most columns you will encounter. Then based on the filename or perhaps the number of dimensions in the cube you can load the data.

I have built a system that we call TM1QuickStart which has a core set of data load processes and all of the files that it loads are defined by a cube. We put all the details in a cube and then call a script that rips through each item in the cube and runs the generic load process. It works great.

You can do the same, and it will not require you to use the API.

Hope this gives you the idea of how it can be done with your system. Good luck.

[tomok]

Do I create a script in a new file or in a TM1 generated file?

TM1 does not have a "scripting" language, ala Windows Scripting Host, where you can just write a script with a text editor and execute it. As I explained in my earlier post, the TI object files are stored in text but you can't create them in a text editor because a lot of the lines in the file are created in the Turbointegrator interface and you would not know what to put on those lines and there is no documentation providing that because it is part of the inner workings of the Turbointegrator program. I'm not certain IBM would even be willing to share that with you. This means that you HAVE to use the TI front-end to create your load routines (unless you write your own via the API). That being said, there are ways to create generic load processes that can handle disparate data files so that you won't have to create a separate load routine for each one. Search this forum for that topic and you'll find several threads about it.

Here I hava list of funtions, but it never says how to use them and how to execute them.

All I can say to this is......RTFM. Sorry, but all the TI functions are fully explained in the documentation.

[David Usherwood]

And...
From your description of the files they are almost certainly an export from another TM1 server. So reading them in shouldn't be a major task - but I think that you need to work more carefully through the documentation, get some training, or involve a more experienced colleague.