Adding new elements during process worksheet import

[hansstip]

Hello Forum,

Is there a way to add new elements to a dimension on the fly during the import of new data using a process worksheet instead of TI ?

I would like to be able to add new elements to a dimension on the fly during the import of new data.
I know that by using a TI process this can be achieved by selecting Maps / Dimensions / and changing the option "AsIs" in the Action column to "Update".
But this process is needed on a daily basis and we do not have the proper licence to schedule such a process with a chore.
That's why I'm looking for a way to achieve the same during an import using a process worksheet.
This is the only daily process where we need this functionality so it seems a bit overdone to upgrade our licence just for this one process.

Thanks in advance for any help

Greetings

Hans

[Duncan P]

Set a recurring task in Windows scheduled to execute a process using the program TM1RUNTI.EXE. Then you will be able to do what you want in the process.

[jim wood]

If your version of TM1 doesn't include TM1 Rune TI there is a batch tool that you can use to run processes that you can download from this very forum.

[Alan Kirk]

I know that by using a TI process this can be achieved by selecting Maps / Dimensions / and changing the option "AsIs" in the Action column to "Update".

Or better still writing the code yourself so that you get a process that does everything you want it to do, in exactly the way you want it to, rather than the cr@p-in-a-can one-size-fits-all code that you get from the code wizard.

But this process is needed on a daily basis and we do not have the proper licence to schedule such a process with a chore.

Hang on, how old is your version? (The RFA Guidelines do ask for that to be specified in a question, or you should at least add it to your profile).)

To the best of my recollection TI hasn't been licenced separately since back around version 7, maybe, possibly, into the early 8.0's and 8.1's. (And to be honest I don't recall chore scheduling being a separate licence even then; it was you had either licenced TI or you hadn't.) The potential problem is that if you are still on a 7.x version, you'd have to check whether RunTI would actually work. Even the raw API code may well be different back then.

[hansstip]

Thanks four your quick responses !

We don't have TM1RunTI, I guess that's around since 9.5.2 but we still use 9.1. (Sorry I didn't know about the RFA, I've update the profile info).
TIExecute looks promising but maybe a bit too complicated to setup for just one process.
As a (temporary) quickfix/workaround I will simpy enter all valid possible elements in the dimension (yes that would make the cube bigger)
and import the data using a process worksheet since I can schedule that on a daily basis.

But for a long-term solution I guess I should look into the licencing.
In our version we can define processes in the Server Explorer using the "Create New Process" wizard.
And then we can manually start (Run) those processes to populate the cubes.
In the Server Explorer we see the option "Chores" but cannot do anything ("Create New Chore" shows no available processes to use).
Does that look familiar ?

[Duncan P]

Adding elements to the dimension can make the cube marginally bigger - but not in the way that I suspect that you think.

If you look at the original patent Manny filed, and particularly at the pictures, you will see that it stores only the values that exist in the cube, and that it does this by creating a tree structure and looking down it using the coordinates of the cell you are after. Each coordinate is broken into hexadecimal digits and each digit takes you down another branch of the tree. First one coordinate is used, then the next in internal dimension order, and so on.

If a dimension has less than sixteen elements its coordinates will only need one level of the tree. If there are e.g. between 4096 (16^3) and 65535 (16^4 - 1) they will need four. Each level of the tree adds to the space taken up, although it is in no way linear. It will depend on how the cells are arranged, and this is the reason that dimension reordering can have such a profound effect.

So in summary unless your adding of elements takes it over a "power of 16" boundary there will be no effect. If it does then there will be an effect, but its magnitude will depend on the cell arrangement in your cube.

BTW the branches of the tree are pointers. On a 32 bit system each is 4 bytes. On a 64 bit system each is 8 bytes. This is why cube data takes up more space in 64 bit TM1 - although there is correspondingly a lot more space to go at.

[declanr]

In the Server Explorer we see the option "Chores" but cannot do anything ("Create New Chore" shows no available processes to use).
Does that look familiar ?

Not seen it before but my first port of call would be to check the tm1p.lic file, can't remember where this used to be in 9.1 but if it's missing or not correct then it can cause similar issues around "admin" tasks not working.

[hansstip]

If a dimension has less than sixteen elements its coordinates will only need one level of the tree. If there are e.g. between 4096 (16^3) and 65535 (16^4 - 1) they will need four. Each level of the tree adds to the space taken up, although it is in no way linear. It will depend on how the cells are arranged, and this is the reason that dimension reordering can have such a profound effect.

So in summary unless your adding of elements takes it over a "power of 16" boundary there will be no effect. If it does then there will be an effect, but its magnitude will depend on the cell arrangement in your cube.

I was trying to avoid a rather sparse dimension with 44000 elements of which only 1000 will be used, but it is not known in advance which 1000 that will be.
Instead of listing all 44000 possibilities in the dimension from the start I would have liked that new elements were added during the data import on-the-fly.
Then I could start with the actual 300 used values and grow to 1000 or maybe even 4000 but never reach all 44000.

Not seen it before but my first port of call would be to check the tm1p.lic file, can't remember where this used to be in 9.1 but if it's missing or not correct then it can cause similar issues around "admin" tasks not working.

I will check the licence file to activate the chores functionality. If that doesnt work I'll resort to listing all 44000 elements and importing via a process worksheet.

[Alan Kirk]

Not seen it before but my first port of call would be to check the tm1p.lic file, can't remember where this used to be in 9.1 but if it's missing or not correct then it can cause similar issues around "admin" tasks not working.

I will check the licence file to activate the chores functionality. If that doesnt work I'll resort to listing all 44000 elements and importing via a process worksheet.

I think he's right. I had a mental blank about that myself; I knew I'd seen the symptoms before but couldn't recall where, but that was it. The file is actually called Tm1p8.lic (even in versions 9 and 10 it's called that; I mention this just in case you're doing a Windows search and can't find it) and the 9.1 documentation showed that it was supposed to live in:

%ALLUSERSPROFILES%\ApplicationData\Applix\TM1\Tm1p8.lic.

(Though admittedly I've occasionally had trouble with it in there and have dumped it into C:\Windows, where it used to live many, many versions ago back in the time when OS security was rather more lax. That always seems to work as a last resort.)

If the file is indeed missing you'll probably notice some other functinality disabled like the Dimension Open menu item; basically anything to do with administrative roles, including setting up new users and groups. The licence file just enables the full Perspectives functionality, including all of the administrative tasks like setting up chores. There's no way that you could have licenced TM1 without at least one Perspectives licence; you'd never get the thing to run if you did, so it was never sold that way.